Oracle Recovery Manager BACKUP RECOVERY Test Sample.

By: David Rincon

Learn More!

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

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

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

connected to target database: CDSIDB (DBID=2726679705, not open)

RMAN> @CDSIDB_recover_tbs.rcv

RMAN> RUN

2> {

4> ALLOCATE CHANNEL t1 DEVICE TYPE ‘SBT_TAPE’;

5> ALLOCATE CHANNEL t2 DEVICE TYPE ‘SBT_TAPE’;

7> RESTORE TABLESPACE USERS;

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

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 >

About the Author: David Rincon

David Rincon, with a journey beginning in 1992, has dedicated over three decades to Oracle technologies. As the Founder and Managing Partner of Cornerstone Data Systems, INC., David and his team prioritize understanding their clients' needs, aiming to maximize the value of their technology investments. Prior to Cornerstone, David established The DBA Group, LLC, an Oracle-focused firm which was later acquired by Zanett in 2007, a NASDAQ-listed company subsequently absorbed by KPMG. Apart from his business ventures, David shared his expertise as a consultant instructor for Oracle University, guiding many on the Oracle DBA Masters Curriculum. Specializing in areas such as Oracle DBA, Oracle RAC, and Oracle Enterprise Manager Grid Control, David also holds several Oracle certifications, testament to his commitment to the field. Through his endeavors, David Rincon has quietly and consistently contributed to the Oracle community, helping organizations navigate their technology journeys.

Start The Conversation Today!

Schedule Your Consultation
Enjoy a Personalized Strategy Session
Level-Up Your IT

Oracle Recovery Manager / Netbackup Database Recovery Test

BACKUP RRECOVERY Test Sample

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

About the Author: David Rincon

Categories

Recent Posts

Why Cloud-Managed Services Are Important for Data Security

How to Improve Business Agility with Cloud Solutions

The Future of Cloud Computing: Trends to Watch

Start The Conversation Today!

NEXT STEPS – (888)-429-5322

Submit the Form!

Give us a Call!

Oracle Recovery Manager / Netbackup Database Recovery Test

BACKUP RRECOVERY Test Sample

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

About the Author: David Rincon

Share This Story, Choose Your Platform!

Categories

Recent Posts

Why Cloud-Managed Services Are Important for Data Security

How to Improve Business Agility with Cloud Solutions

The Future of Cloud Computing: Trends to Watch

Start The Conversation Today!

NEXT STEPS – (888)-429-5322

Submit the Form!

Give us a Call!