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

NEXT STEPS – (888)-429-5322