Monday, February 1, 2010

RMAN and SBT_TAPE backup pieces - how to catalog it

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 Linux 32 bit
  • Oracle Windows 32 bit
  • Oracle 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.

v_dev varchar2(50);             
v_fullname varchar2(4000);
recid number;
stamp number;

v_dev:=sys.dbms_backup_restore.deviceAllocate(type=>'sbt_tape', ident=>'test');

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;


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.



Abdul Bari said...

Hi Marcin ,

I have read your notes, its do nice to hear that we can catalog the missing backup pieces into RMAN.
I have been trying to catalog backup piece into RMAN, but ending up with below error m could you please help me to resolve this issue.


allocated channel: ORA_SBT_TAPE_1
channel ORA_SBT_TAPE_1: sid=992 devtype=SBT_TAPE
channel ORA_SBT_TAPE_1: Veritas NetBackup for Oracle - Release 6.5 (2007072323)
ORA-19870: error reading backup piece dbf_ORAUSPA_20091228_4234_1
ORA-19507: failed to retrieve sequential file, handle="dbf_ORAUSPA_20091228_4234_1", parms=""
ORA-27029: skgfrtrv: sbtrestore returned error
ORA-19511: Error received from media manager layer, error text:
Failed to open backup file for restore.
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of catalog command at 02/12/2010 05:25:34
RMAN-06209: List of failed objects
RMAN-06211: ==========================
RMAN-06212: Object Type Filename/Handle
RMAN-06213: --------------- ---------------------------------------------------
RMAN-06214: Backup Piece dbf_ORAUSPA_20091228_4234_1

Marcin Przepiorowski said...


Two questions:
- is this backup piece on tape ?
- are you restoring backup on this same machine where you backup it ?


Anonymous said...

1.Yes backup is on tapes.
2.I trying to catalog the backuppiece into RMAN Catalog.

Marcin Przepiorowski said...


Try to control file first.
If backup is on the tape
try to allocate channel before that - it is possible that Netbackup will required additional parameter in case when you backup this backupiece using different server name.

Did you try to run command to list all backup sets from tape ?

dba1974 said...

my backup system is CommVault. I am trying to catalog a backup piece but no sucess: any help?

I tried following two methods:

RMAN> run {
2> ALLOCATE CHANNEL ch01 TYPE 'sbt_tape' PARMS='BLKSIZE=262144,SBT_LIBRARY=/opt/hds/Base64/,
3> ENV=(CvClientName=tnsq01952,CvOraSID=etpschp6,CvInstanceName=Instance001,CV_restCopyPrec=2)';
4> catalog device type 'sbt_tape' backuppiece 'ue6mrv51o_s161222_p1_t767530040';
6> }

using target database control file instead of recovery catalog
allocated channel: ch01
channel ch01: sid=263 devtype=SBT_TAPE
channel ch01: CommVault Systems for Oracle: Version 9.0.0(BUILD84)

released channel: ch01
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of catalog command at 11/22/2011 23:37:06
RMAN-06470: DEVICE TYPE is supported only when automatic channels are used

RMAN> ALLOCATE CHANNEL for maintenance device TYPE 'sbt_tape' PARMS='BLKSIZE=262144,SBT_LIBRARY=/opt/hds/Base64/,
2> ENV=(CvClientName=tnsq01952,CvOraSID=etpschp6,CvInstanceName=Instance001,CV_restCopyPrec=2)';

allocated channel: ORA_MAINT_SBT_TAPE_1
channel ORA_MAINT_SBT_TAPE_1: sid=263 devtype=SBT_TAPE
channel ORA_MAINT_SBT_TAPE_1: CommVault Systems for Oracle: Version 9.0.0(BUILD84)

RMAN> catalog device type 'sbt_tape' backuppiece 'ue6mrv51o_s161222_p1_t767530040';

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of catalog command at 11/22/2011 23:47:11
RMAN-06471: no configuration found to allocate channels for SBT_TAPE


Marcin Przepiorowski said...

Hi dba1974,

If you need additional parameters to SBT_TAPE you need to configure it using following command



catalog device type 'SBT_TAPE' backuppiece 'your backup piece';

If you want to clear your configuration you can use following command


Hope it will help you with your issue.

prasad said...

thank you for post and it helped resolve my issue.