Wednesday, June 15, 2011

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/

2 comments:

  1. The information shared on the oracle disaster recovery solutions is perfectly described in the blog, which is really appreciative, quality content and useful information. Thanks for sharing.

    ReplyDelete
  2. Well written post. I appreciate your guidance for sharing about Disaster recovery . I really need to know about it. Great work!

    ReplyDelete