Disaster Recovery Scenario

I had a disaster at primary site resulting in loss of a mission critical database. I had to restore and recover the database before 08:00 am EST and I received the information around 02:00 am EST.

I spent first 10 minutes to communicate with other groups to find out a spare server to restore and recover the lost production database. The database was Oracle 10.2.0.2 on Linux with 2 node RAC cluster. The backup policy was based on RMAN directly to tapes using net backup. There was full backup on every Sunday and incremental backup during weekdays. The database size was approximately 100 GB.

I prepared a quick plan to restore and recover the database in stand lone mode (non RAC).

Server Lost: Server A: SrvA
New Server: Server B: SrvB
DB Name: ASP

Steps Taken:



1. Sent out the communication to net backup group for “alternate client request”. This request was to map the backup originating from Server A to Server B.

2. I found out the NB_ORA_CLIENT information. It was same as server name.

3. Restore the spfile and control file.

Note: It was Monday morning so RMAN had to restore Sunday’s full and then Monday's incremental backup.The database lost time was around 01:55 am EST. During the conference call with application manager we decided to stick with 01:54 am EST and as per net backup logs we had the successful archive log backup till 01:54 am EST.The restore time stamp was '2007-03-26:01:55:00'


$rman target /
rman> set dbid=3494236093 (we had a policy to store the dbid in the DBA inventory after creation of each database)
rman> startup force nomount;
rman> run{
allocate channel tape_1 type sbt
SEND 'NB_ORA_CLIENT=SrvA';
restore spfile from autobackup;
restore until time '2007-03-26:01:55:00' controlfile from autobackup;
}

Time taken: approximately 12 minutes

4. Exit from RMAN.

SQL> shutdown abort
ORACLE instance shut down.

- Copy the spfile contents in an editor and modify the following entries.
Removed the following entries:

*.cluster_database_instances
*.cluster_database
ASP2.thread=2
ASP1.thread=1
*.remote_listener=
ASP2.undo_tablespace='UNDOTBS2'
ASP1.undo_tablespace='UNDOTBS1'

Added the following:
*.undo_tablespace='UNDOTBS2'
Modified the log_Archive_dest
*.log_archive_dest='/data02/oracle/home/testitaly/archive'

Created a file in the server with name /tmp/initasp.ora

$sqlplus sys as sysdba
Sql> startup nomount pfile=’/tmp/initasp.ora’
Sql> alter database mount;


5. I used the following to restore the files. I did not get the same mount point in the new server as it was in the server A.

run {
ALLOCATE CHANNEL ch00 TYPE 'SBT_TAPE';
ALLOCATE CHANNEL ch01 TYPE 'SBT_TAPE';
SEND 'NB_ORA_CLIENT=SrvA';
set newname for datafile 1 to '/data02/home/oracle/testitaly/oradata/system01.dbf';
set newname for datafile 2 to '/data02/home/oracle/testitaly/oradata/undotbs01.dbf';
set newname for datafile 3 to '/data02/home/oracle/testitaly/oradata/sysaux01.dbf';
set newname for datafile 4 to '/data02/home/oracle/testitaly/oradata/undotbs02.dbf';
set newname for datafile 5 to '/data02/home/oracle/testitaly/oradata/users01.dbf';
set newname for datafile 6 to '/data02/home/oracle/testitaly/oradata/s_ts_fr_data01.dbf';
set newname for datafile 7 to '/data02/home/oracle/testitaly/oradata/s_ts_fr_indx01.dbf';
set newname for datafile 8 to '/data02/home/oracle/testitaly/oradata/s_ts_fmxr_data01.dbf';
set newname for datafile 9 to '/data02/home/oracle/testitaly/oradata/s_ts_fmxr_indx01.dbf';
set newname for datafile 10 to '/data02/home/oracle/testitaly/oradata/s_ts_fifa_data01.dbf';
set newname for datafile 11 to '/data02/home/oracle/testitaly/oradata/s_ts_fifa_indx01.dbf';
set newname for datafile 12 to '/data02/home/oracle/testitaly/oradata/s_ts_gmcp_data01.dbf';
set newname for datafile 13 to '/data02/home/oracle/testitaly/oradata/s_ts_gmcp_indx01.dbf';
set newname for datafile 14 to '/data02/home/oracle/testitaly/oradata/s_ts_gmfr_data01.dbf';
set newname for datafile 15 to '/data02/home/oracle/testitaly/oradata/s_ts_gmfr_indx01.dbf';
set newname for datafile 16 to '/data02/home/oracle/testitaly/oradata/s_ts_gmcp_data02.dbf';
set newname for datafile 17 to '/data02/home/oracle/testitaly/oradata/s_ts_gmfr_data02.dbf';
set newname for datafile 18 to '/data02/home/oracle/testitaly/oradata/s_ts_gmfr_data03.dbf';
set newname for datafile 19 to '/data02/home/oracle/testitaly/oradata/s_ts_gmcp_data03.dbf';
set newname for datafile 20 to '/data02/home/oracle/testitaly/oradata/s_ts_gmcp_indx02.dbf';
set newname for datafile 21 to '/data02/home/oracle/testitaly/oradata/s_ts_gmfr_indx02.dbf';
set newname for datafile 22 to '/data02/home/oracle/testitaly/oradata/s_ts_gmfr_indx03.dbf';
set newname for datafile 23 to '/data02/home/oracle/testitaly/oradata/s_ts_nr_indx01.dbf';
set newname for datafile 24 to '/data02/home/oracle/testitaly/oradata/s_ts_nr_data01.dbf';
set newname for datafile 25 to '/data02/home/oracle/testitaly/oradata/s_ts_gmcp_indx03.dbf';
set newname for datafile 26 to '/data02/home/oracle/testitaly/oradata/s_ts_gmcp_indx04.dbf';
restore database until time '2007-03-26:01:55:00';
}

Output:
allocated channel: ch00
channel ch00: sid=102 devtype=SBT_TAPE
channel ch00: VERITAS NetBackup for Oracle - Release 5.1 (2006040520)

allocated channel: ch01
channel ch01: sid=98 devtype=SBT_TAPE
channel ch01: VERITAS NetBackup for Oracle - Release 5.1 (2006040520)

sent command to channel: ch00
sent command to channel: ch01

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 2007-03-26:02:30:29

channel ch00: starting datafile backupset restore
channel ch00: specifying datafile(s) to restore from backup set
restoring datafile 00004 to /data02/home/oracle/testitaly/oradata/undotbs02.dbf
restoring datafile 00013 to /data02/home/oracle/testitaly/oradata/s_ts_gmcp_indx01.dbf
restoring datafile 00014 to /data02/home/oracle/testitaly/oradata/s_ts_gmfr_data01.dbf
restoring datafile 00015 to /data02/home/oracle/testitaly/oradata/s_ts_gmfr_indx01.dbf
restoring datafile 00019 to /data02/home/oracle/testitaly/oradata/s_ts_gmcp_data03.dbf
channel ch00: reading from backup piece ASP_bk_6247_1_618128164
channel ch01: starting datafile backupset restore
channel ch01: specifying datafile(s) to restore from backup set
restoring datafile 00003 to /data02/home/oracle/testitaly/oradata/sysaux01.dbf
restoring datafile 00009 to /data02/home/oracle/testitaly/oradata/s_ts_fmxr_indx01.dbf
restoring datafile 00018 to /data02/home/oracle/testitaly/oradata/s_ts_gmfr_data03.dbf
restoring datafile 00023 to /data02/home/oracle/testitaly/oradata/s_ts_nr_indx01.dbf
restoring datafile 00026 to /data02/home/oracle/testitaly/oradata/s_ts_gmcp_indx04.dbf
channel ch01: reading from backup piece ASP_bk_6250_1_618130329
channel ch01: restored backup piece 1
piece handle=ASP_bk_6250_1_618130329 tag=ASP_HOT_DB_BK_LEVEL0
channel ch01: restore complete, elapsed time: 00:16:46
channel ch01: starting datafile backupset restore
channel ch01: specifying datafile(s) to restore from backup set
restoring datafile 00002 to /data02/home/oracle/testitaly/oradata/undotbs01.dbf
restoring datafile 00005 to /data02/home/oracle/testitaly/oradata/users01.dbf
restoring datafile 00006 to /data02/home/oracle/testitaly/oradata/s_ts_icfr_data01.dbf
restoring datafile 00017 to /data02/home/oracle/testitaly/oradata/s_ts_gmfr_data02.dbf
restoring datafile 00025 to /data02/home/oracle/testitaly/oradata/s_ts_gmcp_indx03.dbf
channel ch01: reading from backup piece ASP_bk_6249_1_618130143
channel ch00: restored backup piece 1
piece handle=ASP_bk_6247_1_618128164 tag=ASP_HOT_DB_BK_LEVEL0
channel ch00: restore complete, elapsed time: 00:47:03
channel ch00: starting datafile backupset restore
channel ch00: specifying datafile(s) to restore from backup set
restoring datafile 00012 to /data02/home/oracle/testitaly/oradata/s_ts_gmcp_data01.dbf
restoring datafile 00016 to /data02/home/oracle/testitaly/oradata/s_ts_gmcp_data02.dbf
restoring datafile 00020 to /data02/home/oracle/testitaly/oradata/s_ts_gmcp_indx02.dbf
restoring datafile 00021 to /data02/home/oracle/testitaly/oradata/s_ts_gmfr_indx02.dbf
restoring datafile 00024 to /data02/home/oracle/testitaly/oradata/s_ts_nr_data01.dbf
channel ch00: reading from backup piece ASP_bk_6248_1_618128165
channel ch01: restored backup piece 1
piece handle=ASP_bk_6249_1_618130143 tag=ASP_HOT_DB_BK_LEVEL0
channel ch01: restore complete, elapsed time: 00:35:23
channel ch01: starting datafile backupset restore
channel ch01: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /data02/home/oracle/testitaly/oradata/system01.dbf
restoring datafile 00008 to /data02/home/oracle/testitaly/oradata/s_ts_fmxr_data01.dbf
restoring datafile 00010 to /data02/home/oracle/testitaly/oradata/s_ts_fifa_data01.dbf
restoring datafile 00011 to /data02/home/oracle/testitaly/oradata/s_ts_fifa_indx01.dbf
restoring datafile 00022 to /data02/home/oracle/testitaly/oradata/s_ts_gmfr_indx03.dbf
channel ch01: reading from backup piece ASP_bk_6251_1_618131406
channel ch01: restored backup piece 1
piece handle=ASP_bk_6251_1_618131406 tag=ASP_HOT_DB_BK_LEVEL0
channel ch01: restore complete, elapsed time: 00:04:15
channel ch00: restored backup piece 1
piece handle=ASP_bk_6248_1_618128165 tag=ASP_HOT_DB_BK_LEVEL0
channel ch00: restore complete, elapsed time: 00:40:18
channel ch00: starting datafile backupset restore
channel ch00: specifying datafile(s) to restore from backup set
restoring datafile 00007 to /data02/home/oracle/testitaly/oradata/s_ts_icfr_indx01.dbf
channel ch00: reading from backup piece ASP_bk_6252_1_618131902
channel ch00: restored backup piece 1
piece handle=ASP_bk_6252_1_618131902 tag=ASP_HOT_DB_BK_LEVEL0
channel ch00: restore complete, elapsed time: 00:02:15
Finished restore
released channel: ch00
released channel: ch01

RMAN>
Time taken: 3 hours approximately

6. I executed the following after fetching the records from v$logfile.

alter database rename file '/oracle03/oradata/ASP/redo_g1_t1_m1.dbf' to '/data02/home/oracle/testitaly/oradata/redo_g1_t1_m1.dbf';
alter database rename file '/oracle04/oradata/ASP/redo_g1_t1_m2.dbf' to '/data02/home/oracle/testitaly/oradata/redo_g1_t1_m2.dbf';
alter database rename file '/oracle03/oradata/ASP/redo_g2_t1_m1.dbf' to '/data02/home/oracle/testitaly/oradata/redo_g2_t1_m1.dbf';
alter database rename file '/oracle04/oradata/ASP/redo_g2_t1_m2.dbf' to '/data02/home/oracle/testitaly/oradata/redo_g2_t1_m2.dbf';
alter database rename file '/oracle03/oradata/ASP/redo_g3_t1_m1.dbf' to '/data02/home/oracle/testitaly/oradata/redo_g3_t1_m1.dbf';
alter database rename file '/oracle04/oradata/ASP/redo_g3_t1_m2.dbf' to '/data02/home/oracle/testitaly/oradata/redo_g3_t1_m2.dbf' ;
alter database rename file '/oracle03/oradata/ASP/redo_g4_t2_m1.dbf' to '/data02/home/oracle/testitaly/oradata/redo_g4_t2_m1.dbf';
alter database rename file '/oracle04/oradata/ASP/redo_g4_t2_m2.dbf' to '/data02/home/oracle/testitaly/oradata/redo_g4_t2_m2.dbf';
alter database rename file '/oracle03/oradata/ASP/redo_g5_t2_m1.dbf' to '/data02/home/oracle/testitaly/oradata/redo_g5_t2_m1.dbf' ;
alter database rename file '/oracle04/oradata/ASP/redo_g5_t2_m2.dbf' to '/data02/home/oracle/testitaly/oradata/redo_g5_t2_m2.dbf';
alter database rename file '/oracle03/oradata/ASP/redo_g6_t2_m1.dbf' to '/data02/home/oracle/testitaly/oradata/redo_g6_t2_m1.dbf';
alter database rename file '/oracle04/oradata/ASP/redo_g6_t2_m2.dbf' to '/data02/home/oracle/testitaly/oradata/redo_g6_t2_m2.dbf';


I verified the changes from the v$logfile. The above statements updated the controflile.

7. Login to database and executed the v$datafile to see the controlfile information. It was still showing old mount point information.

Sqlplus> select * from v$datafile;
FILE# NAME
---------- --------------------------------------------------
1 /oracle11/oradata/ASP/system01.dbf
2 /oracle11/oradata/ASP/undotbs01.dbf
3 /oracle11/oradata/ASP/sysaux01.dbf
4 /oracle11/oradata/ASP/undotbs02.dbf
5 /oracle11/oradata/ASP/users01.dbf
6 /oracle11/oradata/ASP/s_ts_icfr_data01.dbf
7 /oracle12/oradata/ASP/s_ts_icfr_indx01.dbf
8 /oracle07/oradata/ASP/s_ts_fmxr_data01.dbf
9 /oracle09/oradata/ASP/s_ts_fmxr_indx01.dbf
10 /oracle08/oradata/ASP/s_ts_fifa_data01.dbf
11 /oracle10/oradata/ASP/s_ts_fifa_indx01.dbf
12 /oracle11/oradata/ASP/s_ts_gmcp_data01.dbf
13 /oracle12/oradata/ASP/s_ts_gmcp_indx01.dbf
14 /oracle08/oradata/ASP/s_ts_gmfr_data01.dbf
15 /oracle10/oradata/ASP/s_ts_gmfr_indx01.dbf
16 /oracle08/oradata/ASP/s_ts_gmcp_data02.dbf
17 /oracle08/oradata/ASP/s_ts_gmfr_data02.dbf
18 /oracle08/oradata/ASP/s_ts_gmfr_data03.dbf
19 /oracle07/oradata/ASP/s_ts_gmcp_data03.dbf
20 /oracle12/oradata/ASP/s_ts_gmcp_indx02.dbf
21 /oracle10/oradata/ASP/s_ts_gmfr_indx02.dbf
22 /oracle10/oradata/ASP/s_ts_gmfr_indx03.dbf
23 /oracle09/oradata/ASP/s_ts_nr_indx01.dbf
24 /oracle07/oradata/ASP/s_ts_nr_data01.dbf
25 /oracle10/oradata/ASP/s_ts_gmcp_indx03.dbf
26 /oracle09/oradata/ASP/s_ts_gmcp_indx04.dbf
27 /oracle08/oradata/ASP/s_ts_burn_data01.dbf
28 /oracle09/oradata/ASP/s_ts_burn_indx01.dbf


8. I executed the following:

$rman target /
$ run {
Switch datafile all;
}

Unfortunately it was little late (I did not see the desire output from v$datafile as it was still showing the same output as above)

Without wasting anytime I ran:

alter database rename file ‘/’ to '/data02/home/oracle/testitaly/oradata/ ';

alter database rename file '/oracle11/oradata/ASP/system01.dbf' to '/data02/home/oracle/testitaly/oradata/system01.dbf';
alter database rename file '/oracle11/oradata/ASP/undotbs01.dbf' to '/data02/home/oracle/testitaly/oradata/undotbs01.dbf';
alter database rename file '/oracle11/oradata/ASP/sysaux01.dbf' to '/data02/home/oracle/testitaly/oradata/sysaux01.dbf';
alter database rename file '/oracle11/oradata/ASP/undotbs02.dbf' to '/data02/home/oracle/testitaly/oradata/undotbs02.dbf';
alter database rename file '/oracle11/oradata/ASP/users01.dbf' to '/data02/home/oracle/testitaly/oradata/users01.dbf';
alter database rename file '/oracle11/oradata/ASP/s_ts_icfr_data01.dbf' to '/data02/home/oracle/testitaly/oradata/s_ts_icfr_data01.dbf';
alter database rename file '/oracle12/oradata/ASP/s_ts_icfr_indx01.dbf' to '/data02/home/oracle/testitaly/oradata/s_ts_icfr_indx01.dbf';
alter database rename file '/oracle07/oradata/ASP/s_ts_fmxr_data01.dbf' to '/data02/home/oracle/testitaly/oradata/s_ts_fmxr_data01.dbf';
alter database rename file '/oracle09/oradata/ASP/s_ts_fmxr_indx01.dbf' to '/data02/home/oracle/testitaly/oradata/s_ts_fmxr_indx01.dbf';
alter database rename file '/oracle08/oradata/ASP/s_ts_fifa_data01.dbf' to '/data02/home/oracle/testitaly/oradata/s_ts_fifa_data01.dbf';
alter database rename file '/oracle10/oradata/ASP/s_ts_fifa_indx01.dbf' to '/data02/home/oracle/testitaly/oradata/s_ts_fifa_indx01.dbf';
alter database rename file '/oracle11/oradata/ASP/s_ts_gmcp_data01.dbf' to '/data02/home/oracle/testitaly/oradata/s_ts_gmcp_data01.dbf';
alter database rename file '/oracle12/oradata/ASP/s_ts_gmcp_indx01.dbf' to '/data02/home/oracle/testitaly/oradata/s_ts_gmcp_indx01.dbf';
alter database rename file '/oracle08/oradata/ASP/s_ts_gmfr_data01.dbf' to '/data02/home/oracle/testitaly/oradata/s_ts_gmfr_data01.dbf';
alter database rename file '/oracle10/oradata/ASP/s_ts_gmfr_indx01.dbf' to '/data02/home/oracle/testitaly/oradata/s_ts_gmfr_indx01.dbf';
alter database rename file '/oracle08/oradata/ASP/s_ts_gmcp_data02.dbf' to '/data02/home/oracle/testitaly/oradata/s_ts_gmcp_data02.dbf';
alter database rename file '/oracle08/oradata/ASP/s_ts_gmfr_data02.dbf' to '/data02/home/oracle/testitaly/oradata/s_ts_gmfr_data02.dbf';
alter database rename file '/oracle08/oradata/ASP/s_ts_gmfr_data03.dbf' to '/data02/home/oracle/testitaly/oradata/s_ts_gmfr_data03.dbf';
alter database rename file '/oracle07/oradata/ASP/s_ts_gmcp_data03.dbf' to '/data02/home/oracle/testitaly/oradata/s_ts_gmcp_data03.dbf';
alter database rename file '/oracle12/oradata/ASP/s_ts_gmcp_indx02.dbf' to '/data02/home/oracle/testitaly/oradata/s_ts_gmcp_indx02.dbf';
alter database rename file '/oracle10/oradata/ASP/s_ts_gmfr_indx02.dbf' to '/data02/home/oracle/testitaly/oradata/s_ts_gmfr_indx02.dbf';
alter database rename file '/oracle10/oradata/ASP/s_ts_gmfr_indx03.dbf' to '/data02/home/oracle/testitaly/oradata/s_ts_gmfr_indx03.dbf';
alter database rename file '/oracle09/oradata/ASP/s_ts_nr_indx01.dbf' to '/data02/home/oracle/testitaly/oradata/s_ts_nr_indx01.dbf';
alter database rename file '/oracle07/oradata/ASP/s_ts_nr_data01.dbf' to '/data02/home/oracle/testitaly/oradata/s_ts_nr_data01.dbf';
alter database rename file '/oracle10/oradata/ASP/s_ts_gmcp_indx03.dbf' to '/data02/home/oracle/testitaly/oradata/s_ts_gmcp_indx03.dbf';
alter database rename file '/oracle09/oradata/ASP/s_ts_gmcp_indx04.dbf' to '/data02/home/oracle/testitaly/oradata/s_ts_gmcp_indx04.dbf';

9. Recover the database.

$rman target /
$run {
ALLOCATE CHANNEL ch00 TYPE 'SBT_TAPE';
ALLOCATE CHANNEL ch01 TYPE 'SBT_TAPE';
SEND 'NB_ORA_CLIENT=SrvA';
recover database until time '2007-03-26:01:55:00';
RELEASE CHANNEL ch00;
RELEASE CHANNEL ch01;
}

Time to recover: 1 hour approximately (RMAN applied the full and incremental changes)

I was doing tail –f alert_ASP.log to see the progress

10. Open the database
sql> alter database open resetlogs;

11. Added the temporary files.

12. Verified the following details.

SQL> select distinct status from v$datafile;

STATUS
-------
ONLINE
SYSTEM

SQL> select * from v$recover_file;

no rows selected