Oracle Recovery Manager BACKUP RECOVERY Test Sample.
By: David Rincon
STEP 1: Identify data file to remove (Simulate DATAFILE loss).
STEP 2: Delete DATAFILE (ASM protection disallows).
STEP 3: Simulate DATFILE loss by shutdown and manual delete of DATAFILE.
STEP 4: Attempt to startup database.
STEP 5: Database startup fails due to missing file.
STEP 6: Prepare recovery script for suitable restore operation..
STEP 7: Place database in mount mode.
STEP 8: Connect to RMAN and execute recovery script.
STEP 9: Recovery Complete – Open Database in Single Node to confirm recovery.
STEP 10: Cluster Database Restart After Recovery.
BACKUP RRECOVERY Test Sample
The backup and recovery test consists of a simulation of data file loss. Using the previous backup along with archive logs a recovery of a data file which was removed from ASM storage. The database was initially brought down as a simulation of a maintenance operation, once down the USERS table space’s data file on ASM was manually deleted. Once deleted the database failed to restart due to the missing data file. A recovery operation was performed to restore the file from backups and bring the table space back online. Below is the session showing the recovery scenario.
STEP 1: Identify data file to remove (Simulate DATAFILE loss)
ASMCMD [+DG_CDSI_DATA_1/CDSIDB/DATAFILE] > ls -ltr
Type Redund Striped Time Sys Name
DATAFILE UNPROT COARSE DEC 12 01:00:00 Y NGEXT.263.659556283
DATAFILE UNPROT COARSE DEC 12 01:00:00 Y SYSAUX.260.659487795
DATAFILE UNPROT COARSE DEC 12 01:00:00 Y SYSTEM.259.659487785
DATAFILE UNPROT COARSE DEC 12 01:00:00 Y CDSIDB.262.659556167
DATAFILE UNPROT COARSE DEC 12 01:00:00 Y CDSIDB.264.659646545
DATAFILE UNPROT COARSE DEC 12 01:00:00 Y CDSIDB.265.659646641
DATAFILE UNPROT COARSE DEC 12 01:00:00 Y CDSIDB.266.659646723
DATAFILE UNPROT COARSE DEC 12 01:00:00 Y CDSIDB.267.659646791
DATAFILE UNPROT COARSE DEC 12 01:00:00 Y CDSIDB.268.659646945
DATAFILE UNPROT COARSE DEC 12 01:00:00 Y CDSIDB.269.659647517
DATAFILE UNPROT COARSE DEC 12 01:00:00 Y CDSIDB.270.659651075
DATAFILE UNPROT COARSE DEC 12 01:00:00 Y CDSIDB.271.659651123
DATAFILE UNPROT COARSE DEC 12 01:00:00 Y CDSIDB.272.659651153
DATAFILE UNPROT COARSE DEC 12 01:00:00 Y CDSIDB.273.659653635
DATAFILE UNPROT COARSE DEC 12 01:00:00 Y CDSIDB.274.659653665
DATAFILE UNPROT COARSE DEC 12 01:00:00 Y USERS.261.659487803
ASMCMD [+DG_CDSI_DATA_1/CDSIDB/DATAFILE] > ls
NGEXT.263.659556283
SYSAUX.260.659487795
SYSTEM.259.659487785
CDSIDB.262.659556167
CDSIDB.264.659646545
CDSIDB.265.659646641
CDSIDB.266.659646723
CDSIDB.267.659646791
CDSIDB.268.659646945
CDSIDB.269.659647517
CDSIDB.270.659651075
CDSIDB.271.659651123
CDSIDB.272.659651153
CDSIDB.273.659653635
CDSIDB.274.659653665
USERS.261.659487803
STEP 2: Delete DATAFILE (ASM protection disallows)
ASMCMD [+DG_CDSI_DATA_1/CDSIDB/DATAFILE] > rm USERS.*
You may delete multiple files and/or directories.
Are you sure? (y/n) y
ORA-15032: not all alterations performed
ORA-15028: ASM file ‘+DG_CDSI_DATA_1/CDSIDB/DATAFILE/USERS.261.659487803’ not dropped; currently being accessed (DBD ERROR: OCIStmtExecute)
ASMCMD [+DG_CDSI_DATA_1/CDSIDB/DATAFILE] > exit
rac1.cdsi.com:oracle:/home/oracle >srvctl stop database -d CDSIDB
rac1.cdsi.com:oracle:/home/oracle >srvctl status database -d CDSIDB
Instance CDSIDB1 is not running on node rac1
Instance CDSIDB2 is not running on node rac2
Instance CDSIDB3 is not running on node rac3
rac1.cdsi.com:oracle:/home/oracle >oenv
ORA_CRS_HOME=/crs/oracle/product/10.2.0/crs
ORA_ASM_HOME=/asm/app/oracle/product/10.2.0/asm_1
ORACLE_SID=+ASM1
ORACLE_BASE=/orab/app/oracle
ORACLE_HOME=/asm/app/oracle/product/10.2.0/asm_1
rac1.cdsi.com:oracle:/home/oracle >asmcmd
ASMCMD> exit
rac1.cdsi.com:oracle:/home/oracle >asmcmd -p
ASMCMD [+] > lsdg
State Type Rebal Unbal Sector Block AU Total_MB Free_MB Req_mir_free_MB Usable_file_MB Offline_disks Name
MOUNTED EXTERN N N 512 4096 1048576 143356 55573 0 55573 0 DG_OLREDO_1/
MOUNTED EXTERN N N 512 4096 1048576 546648 435126 0 435126 0 DG_CDSI_DATA_1/
MOUNTED EXTERN N N 512 4096 1048576 337905 337458 0 337458 0 DG_CDSI_FRA_1/
MOUNTED EXTERN N N 512 4096 1048576 184316 134172 0 134172 0 DG_CDSI_UNDO_1/
ASMCMD [+] > cd DG_CDSI_DATA_1
ASMCMD [+DG_CDSI_DATA_1] > ls
MIGRATE/
CDSIDB/
ASMCMD [+DG_CDSI_DATA_1] > cd CDSIDB
ASMCMD [+DG_CDSI_DATA_1/CDSIDB] > ls
CONTROLFILE/
DATAFILE/
DATAGUARDCONFIG/
control01.ctl
control02.ctl
control03.ctl
dr1CDSIDB.dat
dr2CDSIDB.dat
ASMCMD [+DG_CDSI_DATA_1/CDSIDB] > ls -lrtr
Type Redund Striped Time Sys Name
Y CONTROLFILE/
Y DATAFILE/
Y DATAGUARDCONFIG/
N dr1CDSIDB.dat => +DG_CDSI_DATA_1/CDSIDB/DATAGUARDCONFIG/CDSIDB.284.672878133
N dr2CDSIDB.dat => +DG_CDSI_DATA_1/CDSIDB/DATAGUARDCONFIG/CDSIDB.285.672878145
N control01.ctl => +DG_CDSI_DATA_1/CDSIDB/CONTROLFILE/Current.256.659487773
N control02.ctl => +DG_CDSI_DATA_1/CDSIDB/CONTROLFILE/Current.257.659487773
N control03.ctl => +DG_CDSI_DATA_1/CDSIDB/CONTROLFILE/Current.258.659487773
ASMCMD [+DG_CDSI_DATA_1/CDSIDB] > cd DATAFILE
ASMCMD [+DG_CDSI_DATA_1/CDSIDB/DATAFILE] > ls
NGEXT.263.659556283
SYSAUX.260.659487795
SYSTEM.259.659487785
CDSIDB.262.659556167
CDSIDB.264.659646545
CDSIDB.265.659646641
CDSIDB.266.659646723
CDSIDB.267.659646791
CDSIDB.268.659646945
CDSIDB.269.659647517
CDSIDB.270.659651075
CDSIDB.271.659651123
CDSIDB.272.659651153
CDSIDB.273.659653635
CDSIDB.274.659653665
STEP 3: Simulate DATFILE loss by shutdown and manual delete of DATAFILE
USERS.261.659487803
ASMCMD [+DG_CDSI_DATA_1/CDSIDB/DATAFILE] > rm USERS.*
You may delete multiple files and/or directories.
Are you sure? (y/n) y
ASMCMD [+DG_CDSI_DATA_1/CDSIDB/DATAFILE] > ls -ltr
Type Redund Striped Time Sys Name
DATAFILE UNPROT COARSE DEC 12 13:00:00 Y NGEXT.263.659556283
DATAFILE UNPROT COARSE DEC 12 13:00:00 Y SYSAUX.260.659487795
DATAFILE UNPROT COARSE DEC 12 13:00:00 Y SYSTEM.259.659487785
DATAFILE UNPROT COARSE DEC 12 13:00:00 Y CDSIDB.262.659556167
DATAFILE UNPROT COARSE DEC 12 13:00:00 Y CDSIDB.264.659646545
DATAFILE UNPROT COARSE DEC 12 13:00:00 Y CDSIDB.265.659646641
DATAFILE UNPROT COARSE DEC 12 13:00:00 Y CDSIDB.266.659646723
DATAFILE UNPROT COARSE DEC 12 13:00:00 Y CDSIDB.267.659646791
DATAFILE UNPROT COARSE DEC 12 13:00:00 Y CDSIDB.268.659646945
DATAFILE UNPROT COARSE DEC 12 13:00:00 Y CDSIDB.269.659647517
DATAFILE UNPROT COARSE DEC 12 13:00:00 Y CDSIDB.270.659651075
DATAFILE UNPROT COARSE DEC 12 13:00:00 Y CDSIDB.271.659651123
DATAFILE UNPROT COARSE DEC 12 13:00:00 Y CDSIDB.272.659651153
DATAFILE UNPROT COARSE DEC 12 13:00:00 Y CDSIDB.273.659653635
DATAFILE UNPROT COARSE DEC 12 13:00:00 Y CDSIDB.274.659653665
ASMCMD [+DG_CDSI_DATA_1/CDSIDB/DATAFILE] > exit
STEP 4: Attempt to startup database
rac1.cdsi.com:oracle:/home/oracle >cd /orabackup/rman/recovery_test/
rac1.cdsi.com:oracle:/orabackup/rman/recovery_test >ls -ltr
total 1
-rw-r–r– 1 oracle dba 136 Dec 12 13:23 channel_set.rcv
-rw-r–r– 1 oracle dba 195 Dec 12 13:25 CDSIDB_recover_tbs.sh
rac1.cdsi.com:oracle:/orabackup/rman/recovery_test >vi CDSIDB_recover_tbs.sh
rac1.cdsi.com:oracle:/orabackup/rman/recovery_test >oenv
ORA_CRS_HOME=/crs/oracle/product/10.2.0/crs
ORA_ASM_HOME=/asm/app/oracle/product/10.2.0/asm_1
ORACLE_SID=+ASM1
ORACLE_BASE=/orab/app/oracle
ORACLE_HOME=/asm/app/oracle/product/10.2.0/asm_1
rac1.cdsi.com:oracle:/orabackup/rman/recovery_test >cd
rac1.cdsi.com:oracle:/home/oracle >. sethome db
ORA_CRS_HOME=/crs/oracle/product/10.2.0/crs
ORA_ASM_HOME=/asm/app/oracle/product/10.2.0/asm_1
ORACLE_SID=CDSIDB1
ORACLE_BASE=/orab/app/oracle
ORACLE_HOME=/ora/app/oracle/product/10.2.0/db_1
rac1.cdsi.com:oracle:/home/oracle >srvctl start database -d CDSIDB
PRKP-1001 : Error starting instance CDSIDB1 on node rac1
CRS-0215: Could not start resource ‘ora.CDSIDB.CDSIDB1.inst’.
PRKP-1001 : Error starting instance CDSIDB2 on node rac2
CRS-0215: Could not start resource ‘ora.CDSIDB.CDSIDB2.inst’.
PRKP-1001 : Error starting instance CDSIDB3 on node rac3
CRS-0215: Could not start resource ‘ora.CDSIDB.CDSIDB3.inst’.
rac1.cdsi.com:oracle:/home/oracle >ob
rac1.cdsi.com:oracle:/orab/app/oracle >cd admin/
rac1.cdsi.com:oracle:/orab/app/oracle/admin >ls -ltr
total 12
drwxr-x— 7 oracle dba 4096 Jul 7 22:39 +ASM
drwxr-xr-x 11 oracle dba 4096 Aug 17 12:15 CDSIDB
drwxr-xr-x 8 oracle dba 4096 Oct 27 22:31 tools
ORA_CRS_HOME=/crs/oracle/product/10.2.0/crs
ORA_ASM_HOME=/asm/app/oracle/product/10.2.0/asm_1
ORACLE_SID=CDSIDB1
ORACLE_BASE=/orab/app/oracle
ORACLE_HOME=/ora/app/oracle/product/10.2.0/db_1
STEP 5: Database startup fails due to missing file
rac1.cdsi.com:oracle:/orab/app/oracle/admin/CDSIDB/bdump >sqlplus
SQL*Plus: Release 10.2.0.4.0 – Production on Fri Dec 12 13:38:03 2008
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Enter user-name: sys as sysdba
Enter password:
Connected to an idle instance.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 1610612736 bytes
Fixed Size 2182600 bytes
Variable Size 320385592 bytes
Database Buffers 1275068416 bytes
Redo Buffers 12976128 bytes
Database mounted.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01157: cannot identify/lock data file 6 – see DBWR trace file
ORA-01110: data file 6: ‘+DG_CDSI_DATA_1/CDSIDB/datafile/users.261.659487803
SQL> shutdown immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
STEP 6: Prepare recovery script for suitable restore operation
rac1.cdsi.com:oracle:/home/oracle >cd /orabackup/rman/
rac1.cdsi.com:oracle:/orabackup/rman >ls
log recovery_test scripts
rac1.cdsi.com:oracle:/orabackup/rman >cd recovery_test/
rac1.cdsi.com:oracle:/orabackup/rman/recovery_test >ls -ltr
total 1
-rw-r–r– 1 oracle dba 136 Dec 12 13:23 channel_set.rcv
-rw-r–r– 1 oracle dba 193 Dec 12 13:35 CDSIDB_recover_tbs.sh
rac1.cdsi.com:oracle:/orabackup/rman/recovery_test >oenv
ORA_CRS_HOME=/crs/oracle/product/10.2.0/crs
ORA_ASM_HOME=/asm/app/oracle/product/10.2.0/asm_1
ORACLE_SID=CDSIDB1
ORACLE_BASE=/orab/app/oracle
ORACLE_HOME=/ora/app/oracle/product/10.2.0/db_1
rac1.cdsi.com:oracle:/orabackup/rman/recovery_test >cat CDSIDB_recover_tbs.rcv
RUN
{
ALLOCATE CHANNEL t1 DEVICE TYPE ‘SBT_TAPE’;
ALLOCATE CHANNEL t2 DEVICE TYPE ‘SBT_TAPE’;
RESTORE TABLESPACE USERS;
RECOVER TABLESPACE USERS;
RELEASE CHANNEL t1;
RELEASE CHANNEL t2;
}
STEP 7: Place database in mount mode
rac1.cdsi.com:oracle:/orabackup/rman/recovery_test >sqlplus
SQL*Plus: Release 10.2.0.4.0 – Production on Fri Dec 12 13:42:38 2008
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Enter user-name: sys as sysdba
Enter password:
Connected to an idle instance.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 1610612736 bytes
Fixed Size 2182600 bytes
Variable Size 320385592 bytes
Database Buffers 1275068416 bytes
Redo Buffers 12976128 bytes
Database mounted.
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
STEP 8: Connect to RMAN and execute recovery script
rac1.cdsi.com:oracle:/orabackup/rman/recovery_test >rman target / catalog=rman11g——–@wsgrid
Recovery Manager: Release 10.2.0.4.0 – Production on Fri Dec 12 13:43:03 2008
Copyright (c) 1982, 2007, Oracle. All rights reserved.
connected to target database: CDSIDB (DBID=2726679705, not open)
RMAN> @CDSIDB_recover_tbs.rcv
RMAN> RUN
2> {
3>
4> ALLOCATE CHANNEL t1 DEVICE TYPE ‘SBT_TAPE’;
5> ALLOCATE CHANNEL t2 DEVICE TYPE ‘SBT_TAPE’;
6>
7> RESTORE TABLESPACE USERS;
8>
9> RECOVER TABLESPACE USERS;
10>
11> RELEASE CHANNEL t1;
12> RELEASE CHANNEL t2;
13> }
using target database control file instead of recovery catalog
allocated channel: t1
channel t1: sid=384 instance=CDSIDB1 devtype=DISK
allocated channel: t2
channel t2: sid=383 instance=CDSIDB1 devtype=DISK
Starting restore at 12-DEC-08
channel t1: starting datafile backupset restore
channel t1: specifying datafile(s) to restore from backup set
restoring datafile 00006 to +DG_CDSI_DATA_1/CDSIDB/datafile/users.261.659487803
channel t1: reading from backup piece /orabackup/CDSIDB/channel1/rman_20081212_CDSIDB_online_dmk21eis_1_1.dbBkup
channel t1: restored backup piece 1
piece handle=/orabackup/CDSIDB/channel1/rman_20081212_CDSIDB_online_dmk21eis_1_1.dbBkup tag=CDSIDB2_20081212_DB_ONLINE
channel t1: restore complete, elapsed time: 00:15:27
Finished restore at 12-DEC-08
Starting recover at 12-DEC-08
starting media recovery
media recovery complete, elapsed time: 00:00:02
Finished recover at 12-DEC-08
released channel: t1
released channel: t2
RMAN> **end-of-file**
RMAN> exit
STEP 9: Recovery Complete – Open Database in Single Node to confirm recovery
Recovery Manager complete.
rac1.cdsi.com:oracle:/orabackup/rman/recovery_test >sqlplus
SQL*Plus: Release 10.2.0.4.0 – Production on Fri Dec 12 14:03:14 2008
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Enter user-name: sys as sysdba
Enter password:
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
SQL> alter database open;
Database altered.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
STEP 10: Cluster Database Restart After Recovery
rac1.cdsi.com:oracle:/orabackup/rman/recovery_test >srvctl start database -d CDSIDB
rac1.cdsi.com:oracle:/orabackup/rman/recovery_test >
Start a conversation about Managed Services
- We provide a personalized experience
- Thought & strategic leadership is included for all our clients
- Flexible arrangements working on-site or remote
- Boutique services, so that you don’t pay for services you don’t need
- One-on-one support & training for your staff
- We provide you with expert-level resources with real world experience
