Oracle Database Disaster Recovery using Disk Based RMAN Backup

Scenario:
- Database name is MYDB
- Production [source] machine is completely out of order and inaccessible
- Have scheduled RMAN Backup on remote storage disks
- RMAN Backups are taken with "CONTROLFILE AUTOBACKUP ON;" configuration of RMAN
[if "CONTROLFILE AUTOBACKUP OFF;" then we need to create a pfile manually]
- All the needed RMAN backup pieces have been identified
- Oracle SW on the target machine has been installed
- Below two mount points have been mounted on the target machine and on which RMAN backup pieces are kept:
/data01/oradata/RMAN_RESTORE/LOC1
/data02/oradata/RMAN_RESTORE/LOC2
- Latest AUTOBACKUPed CONTROLFILE has been identified [ c-[DBID]-[YYYYMMDD]-[2 digit HEX_SEQUENCE for the day],expl:c-2169732094-20110614-0d ] and kept on /data02/oradata/RMAN_RESTORE/LOC2
- Now have to restore and recover the MYDB database
- For all the steps below, please export ORACLE_HOME & ORACLE_SID properly and use oracle user

[If you are on windows create a oracle DB service for MYDB as below on new machine before proceeding:
Please run the "Command Prompt" right click and "run as administrator" if current user is not "administrator"
oradim -new -sid MYDB -startmode m
]

1. Restore the spfile:
$ export ORACLE_SID=MYDB
rman> startup nomount
rman> restore spfile from '/data02/oradata/RMAN_RESTORE/LOC2/c-2169732094-20110614-0d';

- In above command "c-2169732094-20110614-0d" is the latest autobackuped controlfile.
- Spfile is now restored in the default location [$ORACLE_HOME/dbs/spfileMYDB].
- We may need to edit the spfile to create pfile file, because some some parameter may needed to be chaged an startup with pfile

rman> shutdown immediate
$ rman target /
rman> startup nomount [or startup nomount pfile='PFILE_LOCATION']


2. Restore controlfile and mount the database

- To avoid below error we need to create related directory locations or change control file location:
ORA-19504: failed to create file "/data01/oradata/MYDB/cntl/control02.ctl"
ORA-27040: file create error, unable to create file
Linux-x86_64 Error: 2: No such file or directory

rman> restore controlfile from '/data02/oradata/RMAN_RESTORE/LOC2/c-2169732094-20110614-0d';

- On above, this is the same file from which spfile has been restored.

rman> startup mount [or force]

3. Check needed configuration

- Check latest scn as no redo log file available
SQL> select group#, first_change#, status, archived from v$log order by FIRST_CHANGE# desc;

GROUP# FIRST_CHANGE# STATUS ARC
---------- ------------- ---------------- ---
1 44958649 CURRENT NO ---<<<< this is the target SCN
3 44958631 ACTIVE YES
2 44958342 ACTIVE YES

- optionally plan for change the datafile location
- use below command to find current datafile location then plan for new location:
rman> REPORT SCHEMA;
-we can use below set of commands to crosscheck whether we have all the needed backup pieces:
RMAN> list backup;
RMAN> list backup of database completed after [date];
RMAN> list backup of database completed between [start date] and [end date];

4. Register the calalog location [to identify the backup pieces from the disk locations]
- for each location where RMAN backup piece is kept please run " catalog start with" as below:

rman> catalog start with '/data01/oradata/RMAN_RESTORE/LOC1';
rman> catalog start with '/data02/oradata/RMAN_RESTORE/LOC2';


5. restore the database
- For similar location of the datafiles & tempfiles use below command:
rman> restore database;

- For changed locations [step 3.] of the datafiles & tempfiles use below command canbe used:
rman> run {
SET NEWNAME FOR DATAFILE 1 TO '[new location for datafile 1]';
SET NEWNAME FOR DATAFILE 5 TO '[new location for datafile 5]';
SET NEWNAME FOR DATAFILE 15 TO '[new location for datafile 15]';
SET NEWNAME FOR TEMPFILE 1 TO '[new location for tempfile 1]';
allocate channel 'dev_0' type disk;
restore database;
SWITCH DATAFILE ALL;
release channel 'dev_0';
}

6. Recover and open the database to the SCN identified at step 3:
rman> recover database until scn 44958649;
rman> alter database open resetlogs;

7. create a password file and give an entry in oratab file
$ orapwd file=$ORACLE_HOME/dbs/orapwMYDB password=password entries=5
[ for windows:$ orapwd file=$ORACLE_HOME/database/orapwMYDB password=password entries=5 ]
$ vi /etc/oratab
MYDB:/oracle/orabase/product/11.2.0/dbhome_1:N


Relater links:
http://web.njit.edu/info/oracle/DOC/backup.102/b14191/rcmcatdb002.htm
http://www.orafaq.com/wiki/Oracle_database_Backup_and_Recovery_FAQ
http://repettas.wordpress.com/2008/11/01/rman-restore-and-recover-of-a-database-when-the-repository-and-spfileinitora-files-are-lost/