Disaster Recovery using RMAN Backups

Every DBA has to ensure that his hours spent in planning and testing the backup-recovery strategy works, especially it should be relied upon in any unexpected scenarios occur. We DBAs are always tend to be at ease when our scheduled backup jobs are performing well and are error-free, followed by couple of recovery testings scenarios.

Nevertheless, there are always scenarios which may be left unanticipated during the B/R Testing. One such scenario was haunting me since couple of days. And it took me a while to figure it out (due to known and unknown recovery conditions).

Let's take an example. Say I have a complete RMAN Backup including archivelog backups on Disks. Let's assume that the backups are all in recoverable conditions. And then, suddenly the Production Database Server goes off. Boom!!! The only way to recover the server is to rebuild it or to have us recover the Instance from the RMAN Backups on to another Server. In this post, we will see how to recover or reconstruct a Database by only using the RMAN Backups.

2 pre-conditions to use the RMAN Backups for recovery are:

1. Any Server should be available in a state where Oracle Product can be installed.
2. Same version of Oracle Product needs to be installed with which the RMAN Backup was taken

In my case the O/S is Windows and the Oracle Database Product is 9i R2 (9.2.0.8) Standard Edition.

The following steps would outline the procedure of recovering the Database using only RMAN Backups:

1. Create the Oracle Service
2. Create the relevant folders under admin and oradata folders
3. Restore the spfile from autobackup
4. Restore the controlfile from autobackup
5. Restore the Database
6. Recover the Database
7. Open the Database resetting the logs

We create an Oracle Service using oradim without passing any parameter file

c:\>oradim -new -sid DBTEST -intpwd DBTEST

Now, we need to create the relevant folders for Oracle Database, as follows:

1. Create BDUMP, CDUMP, and UDUMP folders under 'c:\oracle\admin' folder,
2. Create DBTEST folder under 'c:\oracle\oradata' folder

We connect to RMAN and start the Recovery process. First we need to set the DBID of the Database. At times we may or maynot know the DBID of the Database. There are 2 ways to find the DBID:

1. If any RMAN Logs are maintained, the DBID can be found when the initial connection is made to the target
2. If autobackup is enabled ans has the autobackup format set, with say '%F', then the filename of the autobackup has the DBID.
   In my case the file name is 'c-1103102985-20100216-02', and the DBID is '1103102985' in the filename.

And then we need to set the autobackup location which will help us identify the location where all the RMAN files.

C:\>rman
Recovery Manager: Release 9.2.0.8.0 - ProductionCopyright (c) 1995, 2002, Oracle Corporation. All rights reserved.
connected to target database (not started)

RMAN> set dbid=1103102985;
executing command: SET DBID

RMAN> startup force nomount
startup failed: ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file C:\ORACLE\PRODUCT\9.2.0\DB_1\DATABASE\INITDBTEST.ORA'
trying to start the Oracle instance without parameter files ...
Oracle instance started
Total System Global Area 97590688 bytes
Fixed Size 454048 bytes
Variable Size 46137344 bytes
Database Buffers 50331648 bytes
Redo Buffers 667648 bytes

RMAN> SET CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '\\testdb\dbtest\%F';
executing command: SET CONTROLFILE AUTOBACKUP FORMAT

Then we have to restore the SPFILE from the autobackup file. We can either give the keyword "autobackup" or directly reference the path and filename of the autobackup in the 'from' part of the command. I did the latter.

RMAN> restore spfile to 'c:\spdbtest01.ora' from "c:\c-1103102985-20100216-02";
Starting restore at 16-FEB-10
using channel ORA_DISK_1
channel ORA_DISK_1: autobackup found: c:\c-1103102985-20100216-02
channel ORA_DISK_1: SPFILE restore from autobackup complete
Finished restore at 16-FEB-10

Now, we need to shutdown the instance and startup with the restored parameter file.

RMAN> shutdown immediate
Oracle instance shut down

Remember that we have just restored the SPFILE in a non-default location and hence we would require referring a PFILE that should point to the SPFILE so as to start the instance. We need to create a text file, 'init.ora', and then mention the path and name of the restored SPFILE in it. The initDBTEST.ora would contain the following statement:

SPFILE=C:\spdbtest01.ora

And then, we need to reference the newly created PFILE to start the instance.

Alternately, you could also restore the SPFILE to the default home location, under DATABASE directory, where Oracle automatically looks for an SPFILE and thus avoid creating the PFILE to point at the SPFIL. I have chosen the former option for the demonstration.

Start the instance in NOMOUNT state using the parameter file pointing to the spfile.

RMAN> startup force pfile=’C:\initDBTEST.ora’ nomount
Oracle instance started

Total System Global Area 907482184 bytes
Fixed Size 455752 bytes
Variable Size 276824064 bytes
Database Buffers 629145600 bytes
Redo Buffers 1056768 bytes

Now, we need to restore the controlfile to bring back the database. Again here, we can either give the keyword ‘AUTOBACKUP’ or directly reference the path and filename of the autobackup in the ‘FROM’ part of the command.

RMAN> restore controlfile from "c:\c-1103102985-20100216-02";
Starting restore at 16-FEB-10
using channel ORA_DISK_1
channel ORA_DISK_1: restoring controlfile
channel ORA_DISK_1: restore complete
replicating controlfile
input filename=C:\ORACLE\ORADATA\DBTEST\CONTROL01.CTL
output filename=C:\ORACLE\ORADATA\DBTEST\CONTROL02.CTL
output filename=C:\ORACLE\ORADATA\DBTEST\CONTROL03.CTL
Finished restore at 16-FEB-10

With the restored controlfiles, now we are in a position to restore the Database, for which we need to mount the Instance first and the fire the restore command. Once restore is complete we need to fire the recover command for applying the archivelogs.

RMAN> alter database mount;
using target database controlfile instead of recovery catalog
database mounted

RMAN> restore database;
Starting restore at 16-FEB-10
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=16 devtype=DISK
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to C:\ORACLE\ORADATA\DBTEST\SYSTEM01.DBF
restoring datafile 00002 to C:\ORACLE\ORADATA\DBTEST\UNDOTBS01.DBF
restoring datafile 00003 to C:\ORACLE\ORADATA\DBTEST\DRSYS01.DBF
restoring datafile 00004 to C:\ORACLE\ORADATA\DBTEST\EXAMPLE01.DBF
restoring datafile 00005 to C:\ORACLE\ORADATA\DBTEST\INDX01.DBF
restoring datafile 00006 to C:\ORACLE\ORADATA\DBTEST\TOOLS01.DBF
restoring datafile 00007 to C:\ORACLE\ORADATA\DBTEST\USERS01.DBF
.
.
.
restoring datafile 00023 to C:\ORACLE\ORADATA\DBTEST\CWMLITE01.DBF
channel ORA_DISK_1: restored backup piece 1
piece handle=\\TESTDB\DBTEST\DBTEST_01L65FRL_1_1_20100216 tag=FULL_160210_1200 params=NULL
channel ORA_DISK_1: restored backup piece 2
piece handle=\\TESTDB\DBTEST\DBTEST_01L65FRL_2_1_20100216 tag=FULL_160210_1200 params=NULL
channel ORA_DISK_1: restored backup piece 3
piece handle=\\TESTDB\DBTEST\DBTEST_01L65FRL_3_1_20100216 tag=FULL_160210_1200 params=NULL
channel ORA_DISK_1: restored backup piece 4
piece handle=\\TESTDB\DBTEST\DBTEST_01L65FRL_4_1_20100216 tag=FULL_160210_1200 params=NULL
channel ORA_DISK_1: restored backup piece 5
piece handle=\\TESTDB\DBTEST\DBTEST_01L65FRL_5_1_20100216 tag=FULL_160210_1200 params=NULL
channel ORA_DISK_1: restored backup piece 6
piece handle=\\TESTDB\DBTEST\DBTEST_01L65FRL_6_1_20100216 tag=FULL_160210_1200 params=NULL
channel ORA_DISK_1: restored backup piece 7
piece handle=\\TESTDB\DBTEST\DBTEST_01L65FRL_7_1_20100216 tag=FULL_160210_1200 params=NULL
channel ORA_DISK_1: restored backup piece 8
piece handle=\\TESTDB\DBTEST\DBTEST_01L65FRL_8_1_20100216 tag=FULL_160210_1200 params=NULL
channel ORA_DISK_1: restored backup piece 9
piece handle=\\TESTDB\DBTEST\DBTEST_01L65FRL_9_1_20100216 tag=FULL_160210_1200 params=NULL
channel ORA_DISK_1: restore complete
Finished restore at 16-FEB-10

RMAN> recover database;
Starting recover at 17-FEB-10
using channel ORA_DISK_1
starting media recovery
channel ORA_DISK_1: starting archive log restore to default destination
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=2
channel ORA_DISK_1: restored backup piece 1
piece handle=\\TESTDB\DBTEST\DBTEST_03L65IJE_1_1_20100216 tag=FULL_160210_1230 params=NULL
channel ORA_DISK_1: restore complete
archive log filename=C:\ORACLE\ORADATA\DBTEST\ARCHIVE\ARC00002.001 thread=1 sequence=2
channel ORA_DISK_1: starting archive log restore to default destination
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=3
channel ORA_DISK_1: restored backup piece 1
piece handle=\\TESTDB\DBTEST\DBTEST_05L65Q1B_1_1_20100216 tag=FULL_160210_1430 params=NULL
channel ORA_DISK_1: restore complete
archive log filename=C:\ORACLE\ORADATA\DBTEST\ARCHIVE\ARC00003.001 thread=1 sequence=3
unable to find archive log
archive log thread=1 sequence=4
RMAN-00571: =========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: =========================================================
RMAN-03002: failure of recover command at 02/17/2010 10:25:28
RMAN-06054: media recovery requesting unknown log: thread 1 scn 34558001

Don't Panic when you see the above RMAN Error Message. You just need to open the database with resetlogs as the recovery is over.

RMAN> alter database open resetlogs;
database opened

RMAN>

Looks simple! Yet it is a critical test case, as this is the what your full RMAN backups should do, i.e. A Full Recovery.