Hi,
There has been a discussion on oracle-l mailing list about RMAN and backup pieces on tape which are non in recovery catalog anymore. A few people including me response that those backup pieces can be restored using dbms_backup_restore PL/SQL package if we only know a backup piece name.
Update 01/02/2010: Now is more solutions - I have started my investigation a few days ago.
I investigated this problem a little deeper and I found another solution. I did it because I was not aware that according to official Oracle documentation CATALOG command is not working with ‘SBT_TAPE’ interface.
I have tested this solution on:
- Oracle 11.1.0.7 Linux 32 bit
- Oracle 10.2.0.1 Windows 32 bit
- Oracle 10.2.0.4 Linux 32 bit
I’ve no possibility to check this on other machines now but if you can give me a shout what a results are.
Let’s story begin.
Only thing I knew was a backup piece name. This is a prerequisite but it can be found quite easily by Media Manager admin or by you itself if you know your Media Manager software. See example below for Legato Networker:
[oracle@piorovm usr]$ ./sbin/mminfo -q "client=piorovm" -r "ssid,name,totalsize,savetime,volume" ssid name total date volume 4267926146 RMAN:3bl4n6ru_1_1 9961944 01/29/2010 piorovm.localdomain.001 4234372063 RMAN:3dl4n76v_1_1 805331112 01/29/2010 piorovm.localdomain.001 4217594914 RMAN:3el4n792_1_1 1389143616 01/29/2010 piorovm.localdomain.001 4200817814 RMAN:3fl4n7cl_1_1 477378960 01/29/2010 piorovm.localdomain.001 4251148936 RMAN:c-3537902502-20100129-03 10224108 01/29/2010 piorovm.localdomain.001 4184040645 RMAN:c-3537902502-20100129-04 10224108 01/29/2010 piorovm.localdomain.001
Or another example for Verita Netbackup (by Allen Brandon from Oracle-l list)
/opt/oracle ->bplist -t 4 -k-l -s 1/25/2010 -e 1/26/2010 -R / -rw------- oracle oracle 12582912 Jan 25 21:21 /c-18889999-20100125-09 -rw------- oracle oracle 2359296 Jan 25 21:18 /xxprd_al_52855_1_709244222 -rw------- oracle oracle 3383296K Jan 25 21:15 /xxprd_db_52852_1_709243545
Next thing is to add these names to control file or recovery catalog. First of all I started with very low level solution and dbms_backup_restore package. There is a function called inspectBackupPiece which required only backup piece name. Channel has to be allocated before that command. This gave me a hope that I can do it. I have created a small PL/SQL script and I have executed it.
DECLARE
v_dev varchar2(50);
v_fullname varchar2(4000);
recid number;
stamp number;
BEGIN
v_dev:=sys.dbms_backup_restore.deviceAllocate(type=>'sbt_tape', ident=>'test');
sys.dbms_backup_restore.inspectBackupPiece('3fl4n7cl_1_1',v_fullname,recid,stamp);
sys.dbms_backup_restore.deviceDeallocate;
END;
/
And that was it – Backup piece has been added to control file.
Ok, if there is a possibility to does this using PL/SQL package there should be possibility do this using RMAN command line. I was trying with allocating ‘SBT_TAPE’ channel and catalog command but without success. Because I have very short memory about command syntax and I know that RMAN is helping with that a little bit I gave a try:
RMAN> catalog ; RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-00558: error encountered while parsing input commands RMAN-01009: syntax error: found ";": expecting one of: "archivelog, backup, backuppiece, controlfilecopy, clone, datafilecopy, device, db_recovery_file_dest, recovery, start" RMAN-01007: at line 2 column 1 file: standard input
As you can see in error message is a short information about what is expected. Hmmm a device I think it is a good direction. Let’s try again
RMAN> catalog device type; RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-00558: error encountered while parsing input commands RMAN-01009: syntax error: found ";": expecting one of: "double-quoted-string, disk, equal, identifier, single-quoted-string" RMAN-01007: at line 1 column 20 file: standard input
Ok that’s it – RMAN is expecting a disk or any double or single quoted string.
Final step is to give RMAN a chance:
RMAN> list backupset; RMAN>
There is no backup in control file.
RMAN> catalog device type 'sbt_tape' backuppiece '3dl4n76v_1_1'; released channel: ORA_DISK_1 released channel: ORA_SBT_TAPE_1 allocated channel: ORA_SBT_TAPE_1 channel ORA_SBT_TAPE_1: SID=170 device type=SBT_TAPE channel ORA_SBT_TAPE_1: NMO v5.0.0.0 cataloged backup piece backup piece handle=3dl4n76v_1_1 RECID=103 STAMP=709599103
and now
RMAN> list backupset;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
77 Full Unknown SBT_TAPE 00:00:00 29-JAN-10
BP Key: 103 Status: AVAILABLE Compressed: NO Tag: TAG20100129T224031
Handle: 3dl4n76v_1_1 Media:
List of Datafiles in backup set 77
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 8788185 29-JAN-10 +DATA/pioro/datafile/system.260.695313067 Yes we have one already added via catalog command.
After that I have found two notes on MOS where this syntax is mentioned no 727655.1 and not public no550082.1
This example is very typical for a lot of new solutions – if you are not aware that something is impossible you can try and do that.
regards,
Marcin
