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.
we would need to verify the following checklist.
- Same OS version and Release and Service pack has been installed on the new server. For this example, OS is Windows 2008 SP2 (x64).
- 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.
- 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.
- Database name for this example is mydb.
- Datafiles will be restored on same location as prior to disaster (D:\ORACLE\ORADATA\mydb\). Archive destination is D:\oracle\11203\admin\mydb\archive.
- 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.
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