Thursday, January 8, 2015

Performing Disaster Recovery using RMAN

Here I will be explaining how to perform disaster recovery of a database server where server has been lost because of hardware failure and needs everything to be prepared from scratch. This would require that a same type of server has been provisioned which has matching resources as we had on the actual production server. Before moving forward,
we would need to verify the following checklist.
  1. Same OS version and Release and Service pack has been installed on the new server. For this example, OS is Windows 2008 SP2 (x64).
  2. Same Oracle RDBMS version including all patches and/or PSUs/CPUs has been installed using same directory structures (although same directory structure is not required. For this example, Oracle version used is 11.2.0.3 with bundle patch 17.
  3. A full and/or incremental backup of database and archived logs is available for restoration and recovery. For this example, backup has been copied/restored from Tape drive to I:\MYDBBACKUP folder.
  4. Database name for this example is mydb.
  5. Datafiles will be restored on same location as prior to disaster (D:\ORACLE\ORADATA\mydb\). Archive destination is D:\oracle\11203\admin\mydb\archive.
  6. DBID of database is known which will be set before restoration and recovery. If DBID is not known, you would need to have a valid backup piece which includes control file backup which you can use to restore the controlfile.
Steps are as follows

Perform 11.2.0.3 installation and all required patches which were installed on the actual production server. For this example, ORACLE_HOME location is d:\oracle\11203\dbhome_1

Create following directories by. You may use command line interface to perform this task.

mkdir D:\oracle\11203\admin\mydb\adump
mkdir D:\oracle\11203\admin\mydb\dpdump
mkdir D:\oracle\11203\admin\mydb\pfile
mkdir D:\oracle\oradata\mydb\
mkdir D:\oracle\11203\diag\rdbms\mydb\mydb\trace
mkdir D:\Oracle\11203\admin\mydb\archive

On command prompt, set ORACLE_SID and create OS level service

set oracle_sid=mydb
D:\oracle\11203\dbhome_1\bin\oradim.exe -new -sid mydb -startmode auto -spfile -intpwd passw0rd

Connect to the instance from RMAN

rman target /

Set DBID and restore SPFILE from the backup

RMAN> set dbid=3456748342

executing command: SET DBID


RMAN> startup  force nomount

startup failed: ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file 'D:\ORACLE\11203\DBHOME_1\DATABASE\INITmydb.ORA'

starting Oracle instance without parameter file for retrieval of spfile
Oracle instance started

Total System Global Area     158662656 bytes

Fixed Size                     2253296 bytes
Variable Size                100666896 bytes
Database Buffers              50331648 bytes
Redo Buffers                   5410816 bytes

Restore the SPFILE

RMAN> restore spfile to 'D:\oracle\11203\dbhome_1\database\spfilemydb.ora' from 'I:\mydbbackup\C-3456748342-20140417-02';

Starting restore at 09-MAY-14
using channel ORA_DISK_1

channel ORA_DISK_1: restoring spfile from AUTOBACKUP 'I:\mydbbackup\C-3456748342-20140417-02'
channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete
Finished restore at 09-MAY-14


Start instance with restored spfile


RMAN> shutdown

Oracle instance shut down

RMAN> startup nomount

Oracle instance started

Total System Global Area    1135820800 bytes

Fixed Size                     2254464 bytes
Variable Size                587204992 bytes
Database Buffers             536870912 bytes
Redo Buffers                   9490432 bytes


Restore controlfile
RMAN> restore controlfile from 'I:\mydbbackup\C-3456748342-20140417-02';

Starting restore at 09-MAY-14
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=10 device type=DISK

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=D:\ORACLE\ORADATA\MYDB\CONTROL01.CTL
output file name=D:\ORACLE\ORADATA\MYDB\CONTROL02.CTL
Finished restore at 09-MAY-14

In case you don’t have an AUTOBACKUP of cntrolfile, you would need to restore the controlfile(s) from a valid backup set. Same is true if you don’t know the DBID of the database.
After nomount the database, use following type of command to restore the controlfile(s)

RMAN> restore controlfile from 'I:\mydbbackup\<backup_piece_name> ';

Mount database

RMAN> alter database mount;

database mounted
released channel: ORA_DISK_1

Delete current expired backups and catalogue the backups restored from the tape (Location of restoration is 'I:\mydbbackup\ in this case)

RMAN> crosscheck backup;
RMAN> delete noprompt expired backup;
RMAN> catalog start with 'I:\mydbbackup\';


List the backups. If backup location is same as where current restored backup is, all is good, otherwise we might need to "catalog" the backupsets before restore

RMAN> list backup;


List of Backup Sets
===================


BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
6062    Incr 0  1.57G      DISK        00:00:00     17-APR-14
        BP Key: 6062   Status: AVAILABLE  Compressed: YES  Tag: TAG20140417T010015
        Piece Name: I:\MYDBBACKUP\ORA_TCP5TRCV_1_1.RBF
  List of Datafiles in backup set 6062
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  5    0  Incr 32320753072 17-APR-14 D:\ORACLE\ORADATA\mydb\mydb01.DBF
  6    0  Incr 32320753072 17-APR-14 D:\ORACLE\ORADATA\mydb\USERS01.DBF

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
6063    Incr 0  1.35G      DISK        00:00:00     17-APR-14
        BP Key: 6063   Status: AVAILABLE  Compressed: YES  Tag: TAG20140417T010015
        Piece Name: I:\MYDBBACKUP\ORA_TDP5TRCV_1_1.RBF
  List of Datafiles in backup set 6063
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  1    0  Incr 32320753073 17-APR-14 D:\ORACLE\ORADATA\mydb\SYSTEM01.DBF
  7    0  Incr 32320753073 17-APR-14 D:\ORACLE\ORADATA\mydb\mydb02.DBF

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
6064    Incr 0  1.85G      DISK        00:00:00     17-APR-14
        BP Key: 6064   Status: AVAILABLE  Compressed: YES  Tag: TAG20140417T010015
        Piece Name: I:\MYDBBACKUP\ORA_TEP5TRSF_1_1.RBF
  List of Datafiles in backup set 6064
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  2    0  Incr 32320753704 17-APR-14 D:\ORACLE\ORADATA\mydb\SYSAUX01.DBF
  8    0  Incr 32320753704 17-APR-14 D:\ORACLE\ORADATA\mydb\mydb03.DBF

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
6065    Incr 0  1.26G      DISK        00:00:00     17-APR-14
        BP Key: 6065   Status: AVAILABLE  Compressed: YES  Tag: TAG20140417T010015
        Piece Name: I:\MYDBBACKUP\ORA_TFP5TRUR_1_1.RBF
  List of Datafiles in backup set 6065
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  3    0  Incr 32320753802 17-APR-14 D:\ORACLE\ORADATA\mydb\UNDOTBS01.DBF
  10   0  Incr 32320753802 17-APR-14 D:\ORACLE\ORADATA\mydb\mydb05.DBF

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
6066    Incr 0  1.08G      DISK        00:00:00     17-APR-14
        BP Key: 6066   Status: AVAILABLE  Compressed: YES  Tag: TAG20140417T010015
        Piece Name: I:\MYDBBACKUP\ORA_TGP5TSD3_1_1.RBF
  List of Datafiles in backup set 6066
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  4    0  Incr 32320754550 17-APR-14 D:\ORACLE\ORADATA\mydb\MCRSCACHE01.DBF
  9    0  Incr 32320754550 17-APR-14 D:\ORACLE\ORADATA\mydb\mydb04.DBF

Restore database. If database files are to be restored at a different location, use SET NEWNAME command in RMAN to provide new location.

Restore database

RMAN> restore database;

Starting restore at 09-MAY-14
using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00005 to D:\ORACLE\ORADATA\mydb\mydb01.DBF
channel ORA_DISK_1: restoring datafile 00006 to D:\ORACLE\ORADATA\mydb\USERS01.DBF
channel ORA_DISK_1: reading from backup piece I:\MYDBBACKUP\ORA_TCP5TRCV_1_1.RBF
channel ORA_DISK_1: piece handle=I:\MYDBBACKUP\ORA_TCP5TRCV_1_1.RBF tag=TAG20140417T010015
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:05:56
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to D:\ORACLE\ORADATA\mydb\SYSTEM01.DBF
channel ORA_DISK_1: restoring datafile 00007 to D:\ORACLE\ORADATA\mydb\mydb02.DBF
channel ORA_DISK_1: reading from backup piece I:\MYDBBACKUP\ORA_TDP5TRCV_1_1.RBF
channel ORA_DISK_1: piece handle=I:\MYDBBACKUP\ORA_TDP5TRCV_1_1.RBF tag=TAG20140417T010015
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:04:45
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00002 to D:\ORACLE\ORADATA\mydb\SYSAUX01.DBF
channel ORA_DISK_1: restoring datafile 00008 to D:\ORACLE\ORADATA\mydb\mydb03.DBF
channel ORA_DISK_1: reading from backup piece I:\MYDBBACKUP\ORA_TEP5TRSF_1_1.RBF
channel ORA_DISK_1: piece handle=I:\MYDBBACKUP\ORA_TEP5TRSF_1_1.RBF tag=TAG20140417T010015
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:07:06
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00003 to D:\ORACLE\ORADATA\mydb\UNDOTBS01.DBF
channel ORA_DISK_1: restoring datafile 00010 to D:\ORACLE\ORADATA\mydb\mydb05.DBF
channel ORA_DISK_1: reading from backup piece I:\MYDBBACKUP\ORA_TFP5TRUR_1_1.RBF
channel ORA_DISK_1: piece handle=I:\MYDBBACKUP\ORA_TFP5TRUR_1_1.RBF tag=TAG20140417T010015
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:04:26
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00004 to D:\ORACLE\ORADATA\mydb\MCRSCACHE01.DBF
channel ORA_DISK_1: restoring datafile 00009 to D:\ORACLE\ORADATA\mydb\mydb04.DBF
channel ORA_DISK_1: reading from backup piece I:\MYDBBACKUP\ORA_TGP5TSD3_1_1.RBF
channel ORA_DISK_1: piece handle=I:\MYDBBACKUP\ORA_TGP5TSD3_1_1.RBF tag=TAG20140417T010015
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:04:26
Finished restore at 09-MAY-14


List archived redo logs backups.

RMAN> list backup of archivelog all;


List of Backup Sets
===================


BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
6049    45.84M     DISK        00:00:00     16-APR-14
        BP Key: 6049   Status: AVAILABLE  Compressed: YES  Tag: TAG20140416T012457
        Piece Name: I:\MYDBBACKUP\ARC_T6P5R8FA_1_1.RBF

  List of Archived Logs in backup set 6049
  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
  ---- ------- ---------- --------- ---------- ---------
  1    6938    32319558852 15-APR-14 32320083348 15-APR-14
  1    6939    32320083348 15-APR-14 32320124521 15-APR-14

BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
6050    40.76M     DISK        00:00:00     16-APR-14
        BP Key: 6050   Status: AVAILABLE  Compressed: YES  Tag: TAG20140416T012457
        Piece Name: I:\MYDBBACKUP\ARC_T7P5R8FA_1_1.RBF

  List of Archived Logs in backup set 6050
  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
  ---- ------- ---------- --------- ---------- ---------
  1    6940    32320124521 15-APR-14 32320179845 15-APR-14
  1    6941    32320179845 15-APR-14 32320192591 16-APR-14
  1    6942    32320192591 16-APR-14 32320192600 16-APR-14

BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
6051    4.00K      DISK        00:00:00     16-APR-14
        BP Key: 6051   Status: AVAILABLE  Compressed: YES  Tag: TAG20140416T012457
        Piece Name: I:\MYDBBACKUP\ARC_T8P5R8G3_1_1.RBF

  List of Archived Logs in backup set 6051
  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
  ---- ------- ---------- --------- ---------- ---------
  1    6943    32320192600 16-APR-14 32320192610 16-APR-14

BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
6052    46.28M     DISK        00:00:00     17-APR-14
        BP Key: 6052   Status: AVAILABLE  Compressed: YES  Tag: TAG20140417T012423
        Piece Name: I:\MYDBBACKUP\ARC_TIP5TSQ8_1_1.RBF

  List of Archived Logs in backup set 6052
  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
  ---- ------- ---------- --------- ---------- ---------
  1    6944    32320192610 16-APR-14 32320646027 16-APR-14
  1    6945    32320646027 16-APR-14 32320684342 16-APR-14

BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
6053    44.98M     DISK        00:00:00     17-APR-14
        BP Key: 6053   Status: AVAILABLE  Compressed: YES  Tag: TAG20140417T012423
        Piece Name: I:\MYDBBACKUP\ARC_TJP5TSQ8_1_1.RBF

  List of Archived Logs in backup set 6053
  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
  ---- ------- ---------- --------- ---------- ---------
  1    6946    32320684342 16-APR-14 32320738278 16-APR-14
  1    6947    32320738278 16-APR-14 32320755135 17-APR-14
  1    6948    32320755135 17-APR-14 32320755144 17-APR-14

BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
6054    5.00K      DISK        00:00:00     17-APR-14
        BP Key: 6054   Status: AVAILABLE  Compressed: YES  Tag: TAG20140417T012423
        Piece Name: I:\MYDBBACKUP\ARC_TKP5TSR1_1_1.RBF

  List of Archived Logs in backup set 6054
  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
  ---- ------- ---------- --------- ---------- ---------
  1    6949    32320755144 17-APR-14 32320755156 17-APR-14


Restore archived redo logs until sequence 6949 as shown in above backup list because this is the last sequence number which was backed up and is available for recovery.

RMAN> run{
2> set until logseq 6949;
3> recover database;
4> }

executing command: SET until clause

Starting recover at 09-MAY-14
using channel ORA_DISK_1

starting media recovery

channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=6947
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=6948
channel ORA_DISK_1: reading from backup piece I:\MYDBBACKUP\ARC_TJP5TSQ8_1_1.RBF
channel ORA_DISK_1: piece handle=I:\MYDBBACKUP\ARC_TJP5TSQ8_1_1.RBF tag=TAG20140417T012423
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:15
archived log file name=D:\ORACLE\ADMIN\mydb\ARCHIVE\mydb1_6947_0788809366.ARC thread=1 sequence=6947
archived log file name=D:\ORACLE\ADMIN\mydb\ARCHIVE\mydb1_6948_0788809366.ARC thread=1 sequence=6948
media recovery complete, elapsed time: 00:00:01
Finished recover at 09-MAY-14

Open database with RESTLOGS option

SQL> alter database open resetlogs;

Database altered.

SQL> alter system switch logfile;

System altered.

SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            D:\Oracle\admin\mydb\archive
Oldest online log sequence     1
Next log sequence to archive   1
Current log sequence           3
SQL>

No comments:

Post a Comment

Popular Posts - All Times