When reading the
backup and recovery user's guide of the 11gR2 database (again), I came across the RMAN pipe interface. Maybe I came across this before, but I haven't really paid any attention to it. Untill now.
The RMAN pipe interface enables you to interact with RMAN when connected via a different tool or interface, like SQL*Plus. This means that you don't have to be connected to the host on which the database resides or you don't even have to be connected as sysdba to execute RMAN commands. Let's first look at the way to do this, and then we'll get back as to why you would want this (or not...)
According to the documentation, it is fairly easy to setup the pipe mode. Simply start the rman executable like this:
[oracle@localhost ~]$ rman pipe orcl target / catalog rman@rmancat
Recovery Manager: Release 11.2.0.2.0 - Production on Wed Apr 20 11:16:05 2011
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
recovery catalog database Password:
After supplying the password, the prompt does nothing. this means that rman is now running in pipe mode and not accepting any input via the CLI.
If you want to verify that pipe mode is enabled, use sqlplus to check if the pipes are created as private pipes:
[oracle@localhost ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.2.0 Production on Wed Apr 20 12:27:30 2011
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select type, name from v$db_pipes;
TYPE NAME
------- ----------------------------------------
PRIVATE ORA$RMAN_ORCL_IN
PRIVATE ORA$RMAN_ORCL_OUT
So, now we have the pipes that the documentation told us we would have. So, how do we use these? The documentation suggests using dbms_pipe.pack_message and dbms_pipe.send_message:
DECLARE
l_return integer;
BEGIN
dbms_pipe.pack_message('list backup summary;');
l_return := dbms_pipe.send_message('ORA$RMAN_ORCL_IN');
END;
/
Now, we have sent the first command to rman using the "IN" pipe. This means that we should expect some output on the "OUT" pipe as well:
DECLARE
l_return integer;
l_text varchar2(4000);
BEGIN
l_return := dbms_pipe.receive_message('ORA$RMAN_ORCL_OUT');
dbms_pipe.unpack_message(l_text);
dbms_output.put_line(l_text);
END;
/
This returns one line of text. Usually, rman returns more than one line, so we'll have to repeat this untill we get all the lines. The last line to expect is a line saying "RMAN-00572: waiting for DBMS_PIPE input", which means that rman is waiting for a new command on the pipe.
Example interface
This calls for a procedure to handle some of this stuff for us. Or even better: we create a package. The following is a simplified example of how to handle an rman command using a pipe.
CREATE OR REPLACE PACKAGE rman_pipe
AS
PROCEDURE do_command( p_command IN varchar2 );
END;
/
CREATE OR REPLACE PACKAGE BODY rman_pipe
AS
PROCEDURE pack_message (p_command IN varchar2)
IS
l_return integer;
BEGIN
--
-- Pack the command (check for closing semi-colon)
--
IF substr(p_command,-1) != ';' THEN
dbms_pipe.pack_message(p_command||';');
ELSE
dbms_pipe.pack_message(p_command);
END IF;
--
-- Send the message
--
l_return := dbms_pipe.send_message('ORA$RMAN_ORCL_IN');
IF l_return = 0 THEN
dbms_output.put_line('.');
dbms_output.put_line('RMAN command sent');
dbms_output.put_line('.');
ELSIF l_return = 1 THEN
dbms_output.put_line('Timed Out');
ELSIF l_return = 3 THEN
dbms_output.put_line('An Interrupt Occurred');
ELSE
dbms_output.put_line('Unknown Return Code '||l_return);
END IF;
END pack_message;
--
PROCEDURE unpack_message (p_timeout IN integer default dbms_pipe.maxwait)
IS
l_return integer := 0;
l_text varchar2(4000) := 'No text';
BEGIN
--
-- Check the pipe for messages untill ORA-00572 is received
--
WHILE l_return = 0
AND l_text != 'RMAN-00572: waiting for DBMS_PIPE input'
LOOP
l_return := dbms_pipe.receive_message('ORA$RMAN_ORCL_OUT', p_timeout);
IF l_return = 0 THEN
dbms_pipe.unpack_message(l_text);
dbms_output.put_line(l_text);
ELSIF l_return = 1 THEN
dbms_output.put_line('Timed Out');
ELSIF l_return = 2 THEN
dbms_output.put_line('Record in the pipe is too large for the buffer');
ELSIF l_return = 3 THEN
dbms_output.put_line('An Interrupt Occurred');
ELSE
dbms_output.put_line('Unknown Return Code '||l_return);
END IF;
END LOOP;
END unpack_message;
--
PROCEDURE do_command( p_command IN varchar2 )
IS
BEGIN
unpack_message(0);
pack_message(p_command);
unpack_message;
END do_command;
END;
/
This is not as basic as when I started out, though. The problems I ran into, in chronological order:
- When retrieving the results, you'll have to check if you receive the RMA-00572 message
=> after receiving this, stop extracting from the pipe
- When packing the command, I sometimes forgot to put a semi-colon after the command
=> so I created the part where he checks for a semi-colon
- When issuing the first command, there is something in the buffer:
connected to target database: ORCL (DBID=1229390655)
connected to recovery catalog database
RMAN-00572: waiting for DBMS_PIPE input
This causes the first time you request the messages up to RMAN-00572, you will only get this part. On the next command you will get the output of the previous command, etc.
=> first, receive any old messages on the pipe, then execute the command and get the results
- when no previous messages are on the pipe, you will wait indefinitely for a message
=> use a timeout for the first request for messages on the pipe, so you won't get stuck there
Conclusion
Still, this is a simple interface, but think of the possibilities:
You can now grant "execute" priviliges to a user, making that user the rman admin for this database
No connection via the host or sysdba is needed
Sure there are some (major?) drawbacks to overcome at this point:
When starting pipe mode as a background process (nohup &) the pipe mode will end when receiving an error (as with a malformed command)
When starting pipe mode as a background process, you have to provide the password on the commandline
