rman_pipes.txt

SQL> SQL> @rman @rman_ rman_pipes SQL> SQL> connect / as sysdba Connected. Connected. SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL>

REM REM REM REM

DEMO_ _PIPE Demo the RMAN pipe interface using pipe called DEMO THe procedure once created is invoked : exec rman_ rman_cmd( cmd(';') >;'); RMAN is started with RMAN PIPE DEMO_ DEMO_PIPE timeout 600 target system/ system/oracle Terminator messages is : RMANRMAN-00572 "waiting for dbms_ dbms_pipe input" input"

SQL> SQL> REM For UNIX do the following: following: SQL> SQL> Host rman pipe demo_ demo_pipe timeout 600 target / \& SQL> SQL> SQL> > SQL SQL> SQL> SQL> SQL>

REM REM For Windows do the Following: Following: REM REM host start cmd /k "rman PIPE DEMO_ DEMO_PIPE timeout 600 target system/ system/oracle" oracle"

SQL> SQL> REM check that the rman session is started waiting for pipe input SQL> SQL> pause SQL> SQL> SQL> > SQL SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL>

set numwidth 8 col event for a27 a27 col p1 p1text for a10 a10 col p2 p2text for a10 a10 col p3 p3text for a10 a10 REM first show the event and parameters pause

SQL> SQL> 2 3 4 5 6

select sid, sid, seq#, seq#,event #,event, event, p1text, text,p1,p1raw, raw, p2text, text,p2,p2raw from v$ v$session_ session_wait where event like '%pipe% pipe%' order by event; event; SID

SEQ# SEQ# EVENT

P1 P1TEXT

P1 P1

P1RAW

P2 P2TEXT

P2 P2

-------- -------- -------- -------------- -------- ---------------- ------------- --------------------79 191 pipe get handle address 5.79E 00000000227CA CA10 79E+08 00000000227 CA10C 10C buffer length 4096 0000000000001000 1 row selected. selected. SQL> SQL> SQL> > REM clean up in case of data left in pipe SQL SQL> SQL> pause SQL> SQL> exec dbms_ dbms_pipe. pipe.purge( purge('ORA$ ORA$RMAN_ RMAN_DEMO_ DEMO_PIPE_ PIPE_IN' IN'); PL/ PL/SQL procedure successfully completed. completed. SQL> SQL> exec dbms_ dbms_pipe. pipe.purge( purge('ORA$ ORA$RMAN_ RMAN_DEMO_ DEMO_PIPE_ PIPE_OUT' OUT'); PL/ PL/SQL procedure successfully completed. completed. SQL> SQL> SQL> > REM create the procedure SQL SQL> SQL> pause create the procedure SQL> SQL> 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21

create or replace procedure rman_ rman_cmd (cmd varchar2 (cmd varchar2) as in_ in_pipe_ pipe_name varchar2 varchar2(2000) 2000); out_ out_pipe_ pipe_name varchar2 varchar2(2000) 2000); v_ varchar2 v_info varchar2(255) 255); v_status integer( integer(2); begin -in_ in_pipe_ pipe_name := 'ORA$ ORA$RMAN_ RMAN_DEMO_ DEMO_PIPE_ PIPE_IN'; IN'; out_ out_pipe_ pipe_name := 'ORA$ ORA$RMAN_ RMAN_DEMO_ DEMO_PIPE_ PIPE_OUT'; OUT'; dbms_ dbms_output. output.put_ put_line( line('Begin RMAN command : ' || cmd) cmd); /* * Cleanup pipe : read old messages before executing new command */ v_ v_status := dbms_ dbms_pipe. pipe.receive_ receive_message( message(out_ out_pipe_ pipe_name, name, 0); while (v_status = 0) loop dbms_ dbms_pipe. pipe.unpack_ unpack_message( message(v_info) info); dbms_ dbms_output. output.put_ put_line( line('Old : ' || v_info) info); Page 1

P2RAW

22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100

rman_pipes.txt if (v_info like '%RMANRMAN-00572%' 00572%') or (v (v_info like 'Recovery Manager complete% complete%') then v_ v_status := 99; 99; else v_ v_status := dbms_ dbms_pipe. pipe.receive_ receive_message( message(out_ out_pipe_ pipe_name, name, 10) 10); end if; if; end loop; loop;

/* * End CleanUp */

/* * Send message */ dbms_ dbms_pipe. pipe.pack_ pack_message( message(cmd) cmd); v_ v_status := dbms_ dbms_pipe. pipe.send_ send_message( message(in_ in_pipe_ pipe_name) name); if v v_ _status 0 then dbms_ dbms_output. output.put_ put_line( line('Error in writing to pipe : ' || in_ in_pipe_ pipe_name) name); return; return; end if; if; /* * Wait for the results */ v_ v_status := 0; /* * Status 0 : Sucees * Status 1 : Time Out */ while (v_status = 0) or (v_status = 1) loop v_ v_status := dbms_ dbms_pipe. pipe.receive_ receive_message( message(out_ out_pipe_ pipe_name, name, 30) 30); if v v_ _status = 0 then dbms_ dbms_pipe. pipe.unpack_ unpack_message( message(v_info) info); /* * Check for termination record( record(s) */ if (v_info like '%RMANRMAN-00572%' 00572%') or (v (v_info like 'Recovery Manager complete% complete%') then dbms_ dbms_output. output.put_ put_line( line(v_info) info); dbms_ dbms_output. output.put_ put_line( line('End output RMAN command : ' || cmd) cmd); exit; exit; else /* * Check for Errors * RMANRMAN-00571 is an informative line and not an error. error. */ if v_ v_info like '%RMAN-% RMAN-%' -%' then if v_ v_info not like '%00571%' %00571%' then dbms_ dbms_output. output.put_ put_line( line('*** ERROR *** : ' || v_info) info); while (dbms_ dbms_pipe. pipe.receive_ receive_message( message(out_ out_pipe_ pipe_name, name, 5) = 0) loop dbms_ dbms_pipe. pipe.unpack_ unpack_message( message(v_info) info); dbms_ dbms_output. output.put_ put_line( line(v_info) info); end loop; loop; exit; exit; end if; if; else /* * Print output */ dbms_ dbms_output. output.put_ put_line( line(v_info) info); end if; if; end if; if; else if v_ v_status 1 then dbms_ dbms_output. output.put_ put_line( line('Error on reading pipe : ' || out_ out_pipe_ pipe_name) name); exit; exit; end if; if; end if; if; end loop; loop;

-end rman_ rman_cmd; cmd; /

Procedure created. created. SQL> SQL> REM check for errors SQL> > pause SQL Page 2

rman_pipes.txt

SQL> SQL> show errors No errors. errors. SQL> SQL> SQL> SQL> REM see the pipes SQL> SQL> pause SQL> SQL> col name for a24 a24 SQL> > select * SQL 2 from v$ v$db_ db_pipes; pipes; OWNERID NAME

TYPE

PIPE_ PIPE_SIZE

-------- ------------------------ ------- --------0 ORA$ ORA$RMAN_ RMAN_DEMO_ DEMO_PIPE_ PIPE_OUT

PRIVATE

1407

0 ORA$ ORA$RMAN_ RMAN_DEMO_ DEMO_PIPE_ PIPE_IN

PRIVATE

1406

2 rows selected. selected. SQL> SQL> SQL> > REM now try some RMAN commands via the procedure SQL SQL> SQL> pause SQL> rman_ cmd( SQL> exec rman _cmd ('show all;' all;') ;'); Begin RMAN command : show all; all; using target database control file instead of recovery catalog RMAN configuration parameters for database with db_ db_unique_ unique_name TUTOR5 TUTOR5C_NLEDU02 NLEDU02 are: are: CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default CONFIGURE BACKUP OPTIMIZATION OFF; OFF; # default CONFIGURE DEFAULT DEVICE TYPE TO DISK; DISK; # default CONFIGURE CONTROLFILE AUTOBACKUP OFF; OFF; # default CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; BACKUPSET; # default CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default CONFIGURE MAXSETSIZE TO UNLIMITED; UNLIMITED; # default CONFIGURE ENCRYPTION FOR DATABASE OFF; OFF; # default CONFIGURE ENCRYPTION ALGORITHM 'AES128 AES128'; 128'; # default CONFIGURE COMPRESSION ALGORITHM 'BZIP2 BZIP2'; # default CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; NONE; # default CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/fs01 fs01/ 01/app/ app/oracle/ oracle/product/ product/11. 11.1.0si/ si/dbs/ dbs/snapcf_ snapcf_tutor5 tutor5c.f'; # default RMANRMAN-00572: 00572: waiting for DBMS_ DBMS_PIPE input End output RMAN command : show all; all; PL/ PL/SQL procedure successfully completed. completed. SQL> SQL> pause SQL> rman_ cmd( SQL> exec rman _cmd ('CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 3 DAYS;' DAYS;') ;'); Begin RMAN command : CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 3 DAYS; DAYS; new RMAN configuration parameters: parameters: CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 3 DAYS; DAYS; new RMAN configuration parameters are successfully stored RMANRMAN-00572: 00572: waiting for DBMS_ DBMS_PIPE input End output RMAN command : CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 3 DAYS; DAYS; PL/ PL/SQL procedure successfully completed. completed. Page 3

rman_pipes.txt SQL> SQL> pause SQL> rman_ cmd( SQL> exec rman _cmd ('BACKUP SPFILE;' SPFILE;') ;'); Begin RMAN command : BACKUP SPFILE; SPFILE; Starting backup at 1919-MAYMAY-09 allocated channel: channel: ORA_ ORA_DISK_ DISK_1 channel ORA_ ORA_DISK_ DISK_1: SID= SID=93 device type= type=DISK channel ORA_ ORA_DISK_ DISK_1: starting full datafile backup set channel ORA_ ORA_DISK_ DISK_1: specifying datafile( datafile(s) in backup set including current SPFILE in backup set channel ORA_ ORA_DISK_ DISK_1: starting piece 1 at 1919-MAYMAY-09 channel ORA_ ORA_DISK_ DISK_1: finished piece 1 at 1919-MAYMAY-09 piece handle=/ handle=/home =/home/ home/tutor5 tutor5c/flash/ flash/TUTOR5 TUTOR5C_NLEDU02 NLEDU02/ 02/backupset/ backupset/2009_ 2009_05_ 05_19/ 19/o1_mf_ mf_nnsnf_ nnsnf_TAG20090519 TAG20090519T 20090519T114837_ 114837_51503 olf_ olf_.bkp tag= tag=TAG20090519 TAG20090519T 20090519T114837 comment= comment=NONE channel ORA_ ORA_DISK_ DISK_1: backup set complete, complete, elapsed time: time: 00: 00:00: 00:01 Finished backup at 1919-MAYMAY-09 RMANRMAN-00572: 00572: waiting for DBMS_ DBMS_PIPE input End output RMAN command : BACKUP SPFILE; SPFILE; PL/ PL/SQL procedure successfully completed. completed. SQL> SQL> pause SQL> rman_ cmd( SQL> exec rman _cmd ('LIST BACKUP of tablespace SYSTEM;' SYSTEM;') ;'); Begin RMAN command : LIST BACKUP of tablespace SYSTEM; SYSTEM; List of Backup Sets =================== BS Key

Type LV Size

Device Type Elapsed Time Completion Time

------- ---- -- ---------- ----------- ------------ --------------3

Full

BP Key: Key: 3

491. 491.11M 11M

DISK

Status: Status: AVAILABLE

00: 00:00: 00:48

Compressed: Compressed: NO

1919-MAYMAY-09

Tag: Tag: TAG20090519 TAG20090519T 20090519T114430

Piece Name: Name: /home/ home/tutor5 tutor5c/flash/ flash/TUTOR5 TUTOR5C_NLEDU02 NLEDU02/ 02/backupset/ backupset/2009_ 2009_05_ 05_19/ 19/o1_mf_ mf_nnndf_ nnndf_TAG20090519 TAG20090519T 20090519T114430_ 114430_514zvz 514zvz7 zvz7j_.bk p List of Datafiles in backup set 3 File LV Type Ckp SCN

Ckp Time

Name

---- -- ---- ---------- --------- ---1 Full 165594661 1919-MAYMAY-09 /home/ home/tutor5 tutor5c/data/ data/TUTOR5 TUTOR5C_NLEDU02 NLEDU02/ 02/datafile/ datafile/o1_mf_ mf_system_ system_3cxr3 cxr3bkq_ bkq_.dbf RMANRMAN-00572: 00572: waiting for DBMS_ DBMS_PIPE input End output RMAN command : LIST BACKUP of tablespace SYSTEM; SYSTEM; PL/ PL/SQL procedure successfully completed. completed. SQL> SQL> pause SQL> SQL> exec rman_ rman_cmd( cmd('LIST BACKUP SUMMARY;' SUMMARY;') ;'); Begin RMAN command : LIST BACKUP SUMMARY; SUMMARY; List of Backups =============== Key

TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag

------- -- -- - ----------- --------------- ------- ------- ---------- --Page 4

rman_pipes.txt 1

B

F

A DISK

1919-MAYMAY-09

1

1

NO

TAG20090519 TAG20090519T 20090519T114130

2

B

A

A DISK

1919-MAYMAY-09

1

1

NO

TAG20090519 TAG20090519T 20090519T114342

3

B

F

A DISK

1919-MAYMAY-09

1

1

NO

TAG20090519 TAG20090519T 20090519T114430

4

B

F

A DISK

1919-MAYMAY-09

1

1

NO

TAG20090519 TAG20090519T 20090519T114430

5

B

A

A DISK

1919-MAYMAY-09

1

1

NO

TAG20090519 TAG20090519T 20090519T114532

6

B

F

A DISK

1919-MAYMAY-09

1

1

NO

TAG20090519 TAG20090519T 20090519T114837

RMANRMAN-00572: 00572: waiting for DBMS_ DBMS_PIPE input End output RMAN command : LIST BACKUP SUMMARY; SUMMARY; PL/ PL/SQL procedure successfully completed. completed. SQL> SQL> pause SQL> rman_ cmd( SQL> exec rman _cmd ('REPORT NEED BACKUP;' BACKUP;') ;'); Begin RMAN command : REPORT NEED BACKUP; BACKUP; RMAN retention policy will be applied to the command RMAN retention policy is set to recovery window of 3 days Report of files that must be backed up to satisfy 3 days recovery window File Days

Name

---- ----- ----------------------------------------------------RMANRMAN-00572: 00572: waiting for DBMS_ DBMS_PIPE input End output RMAN command : REPORT NEED BACKUP; BACKUP; PL/ PL/SQL procedure successfully completed. completed. SQL> SQL> pause SQL> rman_ cmd( SQL> exec rman _cmd ('CONFIGURE RETENTION POLICY CLEAR;' CLEAR;') ;'); Begin RMAN command : CONFIGURE RETENTION POLICY CLEAR; CLEAR; old RMAN configuration parameters: parameters: CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 3 DAYS; DAYS; RMAN configuration parameters are successfully reset to default value RMANRMAN-00572: 00572: waiting for DBMS_ DBMS_PIPE input End output RMAN command : CONFIGURE RETENTION POLICY CLEAR; CLEAR; PL/ PL/SQL procedure successfully completed. completed. SQL> SQL> pause SQL> rman_ cmd( EXIT;' ;') SQL> exec rman _cmd ('EXIT ;' ); Begin RMAN command : EXIT; EXIT; Recovery Manager complete. complete. End output RMAN command : EXIT; EXIT; PL/ PL/SQL procedure successfully completed. completed. SQL> SQL>

Page 5