Monday, July 27, 2009

Extract Controlfile, Datafiles & Archivelogs from RMAN Backupsets

The other day, one of my colleagues posed me this interesting and thought-provoking question/scenario: "Suppose, you were to lose both your production and your RMAN catalog databases irrevertably. You do have a full/incremental RMAN backup, however. How are you going to recover?"


How indeed? I scratched my head and shone a torch upon the dark recesses of my memory to come up with an answer. The light (of the torch) did indeed reveal something - that is that a note existed in Metalink which list the method that could be used to delve into a RMAN backup set to extract the controlfile, the datafiles and also the archivelogs and Voilà, I hit the nail right on the head.


I did some more digging and found a bunch of "Notes" in Metalink that pointed me to the eventual answer to the above question. The Notes that I am referring to are listed below. A summary of the procedure to be followed in such scenarios is also provided. All the notes mentioned use the package dbms_backup_restore.


Scenario:


A successful production backup (RMAN) is available (cold or hot - if hot, then the archivelogs are also available) while the production and the RMAN catalog databases are irrevertably lost.



Assumptions:


1. RMAN is being used to backup the database.


2. The RMAN backup is a "good" backup and is restore and recovery ready.


3. The list of datafiles (file name + full file path) and their datafile numbers is available (this can be obtained from the RMAN backup log file).



Solution Summary (from Note ID: 388057.1):


1. Start the target instance (nomount).


2. Restore the latest controlfile, ideally from the same backupset as the last incremental to be restored (Note 388052.1 : restore controlfile using PLSQL).


3. Make further copies if necessary as per the target database init.ora (pfile).


4. Mount the target instance (alter database mount).


NOTE: (RMAN may be able to be used to restore / recover the database at this point if the controlfile restored knows about the backup required to be restored and recovered).

5. Restore the datafiles from the level 0 backupset (Note 388053.1 : restore datafiles using PLSQL).


6. Restore (apply) the datafiles from the incremental backupsets (Note 388054.1 : restore incremental backups using PLSQL).


7. Restore the archivelogs from the archivelog backupset (Note 388055.1 : restore archivelogs using PLSQL).


8. Using traditional recovery techniques, recover the database using SQLPLUS. (until cancel using backup controlfile).


9. Open the database (resetlogs).


10. Rebuild the recovery catalog & re-register the target database.


11. Make backups of the target database and recovery catalog database.


References:


1. 60545.1 - How to Extract Controlfiles, Datafiles, and Archived Logs from RMAN Backupsets


2. 388052.1 - How to Extract the Control file from an RMAN backupset using PL/SQL


3. 388053.1 - How to Extract Datafile(s) from an RMAN backupset Level-0(Full) backup using PL/SQL.


4. 388054.1 - How to Extract Incremental Backups of Datafile(s) from an RMAN Incremental backupset Level-1 or higher using PL/SQL.


5. 388055.1 - How to Extract archive logs from an RMAN using PL/SQL.


6. 388057.1 - How to Restore an RMAN Backed up database using PL/SQL.

No comments: