RMAN Uncatalog using DBMS_BACKUP_RESTORE

Introduction

Most of the Oracle DBAs just rely on the command line RMAN interface ( $ORACLE_HOME/bin/rman ) to perform the rman backup and recovery commands. Every rman backup file (backup piece) generated is also called a backup piece handle. Some of the rman operations can be performed via the little documented DBMS_BACKUP_RESTORE package also and it turns out to be a quite useful in some cases such as when one wants  to uncatalog many RMAN backup pieces at once.

Problem

The other day I was transferring my RMAN backup pieces from one server to another server to create a clone database using rman restore and recovery commands. Due to some transfer issue, all the backup pieces (files) generated on a given day were corrupt during the transfer. Because Oracle Flash Recovery Area (FRA) was used, all the backup pieces were transferred to the FRA area. Some of you already know that FRA has a nicely laid out directory structure with one of the subdirectories containing the date the backup piece was generated.

For e.g /backup/flash_recovery_area/APPSDB/backupset/2011_02_15/o1_mf****_.bkp. Note the date 2011_02_15 in the directory path.

I already cataloged these backup pieces together using the catalog start with '/backup/flash_recovery_area/APPSDB/backupset/2011_02_15/' noprompt command.

Because these files were corrupted during the transfer process, the rman recovery failed. I fixed the problem using the below approach.

1) spool uncatalog_files.sql

2) set lines 200

3)

select 

'exec sys.dbms_backup_restore.deleteBackupPiece('||recid||','||stamp||','''||handle||''','||set_stamp||','||set_count||','||piece#||');'
from v$backup_piece 

where handle like '%2011_02_15%';

4) exit

5) Now run the uncatalog_file.sql file

The above dbms_backup_restore.deleteBackupPiece procedure removes the backup metadata about these corrupted files and also physically removes these files from the disk. Now transfer the backup pieces again from the source server to the destination server, catalog them again and retry your recovery. This time it must be successful.

Conclusion

One can also use the CHANGE backuppiece '#backup piece file name#' UNCATALOG command. But if there are dozens of backup pieces to be uncataloged all at once, the above dynamic generation of the uncatalog commands using dbms_backup_restore.deleteBackupPiece and v$backup_piece can be used.