Database Recovery Scenario Part 1

In this article we will discuss about different type of database recovery scenarios. The target database name as well as catalog database can be different. Consider you have sufficient backup for this example such as daily incremental backup for all targets database on (Sat-Thurs day) and Weekly full backup on (Friday). The Daily and weekly backup scripts includes datafile, archive log and control & spfile autobackup. The target databases versions are Oracle 9i/10g where as platform can be windows 2003 and LINUX.  The motive of this article is to give  confidence “How to apply different Recovery scenario in different situation”. 
One fine morning our DBserver is restarted due to hard disk crashed and OS issue. Once the disk is repaired and OS is restored then the database is mounted successfully but we cannot able to open the database. It needs media recovery with the following errors. As most of the tablespace datafiles are corrupted, we decided to do complete database recovery. 
SQL> startup;
Database mounted.
ORA-01157: cannot identify/lock data file 1 - see DBWR trace file
ORA-01110: data file 1: ‘D:\ORACLE\ORADATA\SADHAN\SYSTEM01.DBF'
The corruption happens Wednesday morning session so we have daily incremental night backup of Sun-Wed with full backup of Saturday (29th September 2012) and entire month archive log on the disk. We just mounted the database.
C:\rman target sys/****@sadhan catalog catalog/catalog@rman
Recovery Manager: Release 9.2.0.1.0 – Production Copyright (c) 1995, 2002, Oracle Corporation.  All rights reserved.
RMAN> SHUTDOWN IMMEDIATE;
database dismounted
Oracle instance shut down
RMAN> STARTUP MOUNT;
connected to target database (not started)
RMAN> RESTORE DATABASE;
Starting restore at 03-OCT-12
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=13 devtype=DISK
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to D:\ORACLE\ORADATA\SADHAN\SYSTEM01.DBF
restoring datafile 00002 to D:\ORACLE\ORADATA\SADHAN\UNDOTBS01.DBF
restoring datafile 00009 to G:\ORA_DBF_EXTENDED\SDH_HRMS01.DBF
channel ORA_DISK_1: restored backup piece 1
piece handle=H:\ORABACK\MONTHLY_20120929_FULL_SADHAN-2981_1.DB tag=SADHAN_FULL_D BBACKUP params=NULL
channel ORA_DISK_1: restore complete
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00005 to D:\ORACLE\ORADATA\SADHAN\TOOLS01.DBF
restoring datafile 00011 to G:\ORA_DBF_EXTENDED\SDH_SHTR01.DBF
restoring datafile 00012 to G:\ORA_DBF_EXTENDED\SDH_FIN01.DBF
channel ORA_DISK_1: restored backup piece 1
piece handle=H:\ORABACK\MONTHLY_20120929_FULL_SADHAN-2983_1.DB tag=SADHAN_FULL_D BBACKUP params=NULL
channel ORA_DISK_1: restore complete
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00007 to D:\ORACLE\ORADATA\SADHAN\SDH_TIMS01.DBF
restoring datafile 00013 to F:\ORACLE\SADHAN\SDH_EDSS02.DBF
channel ORA_DISK_1: restored backup piece 1
piece handle=H:\ORABACK\MONTHLY_20120929_FULL_SADHAN-2985_1.DB tag=SADHAN_FULL_D BBACKUP params=NULL
channel ORA_DISK_1: restore complete
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00004 to D:\ORACLE\ORADATA\SADHAN\INDX01.DBF
restoring datafile 00010 to F:\ORACLE\SADHAN\SDH_EDSS01.DBF
restoring datafile 00014 to D:\ORACLE\ORADATA\SADHAN\INDX02.DBF
channel ORA_DISK_1: restored backup piece 1
piece handle=H:\ORABACK\MONTHLY_20120929_FULL_SADHAN-2982_1.DB tag=SADHAN_FULL_D BBACKUP params=NULL
channel ORA_DISK_1: restore complete
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00003 to D:\ORACLE\ORADATA\SADHAN\EXAMPLE01.DBF
restoring datafile 00006 to D:\ORACLE\ORADATA\SADHAN\USERS01.DBF
restoring datafile 00008 to D:\ORACLE\ORADATA\SADHAN\SDH_TIMS02.DBF
channel ORA_DISK_1: restored backup piece 1
piece handle=H:\ORABACK\MONTHLY_20120929_FULL_SADHAN-2984_1.DB tag=SADHAN_FULL_D BBACKUP params=NULL
channel ORA_DISK_1: restore complete
Finished restore at 03-OCT-12
RMAN> RECOVER DATABASE;
Starting recover at 03-OCT-12
using channel ORA_DISK_1
channel ORA_DISK_1: starting incremental datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00001: D:\ORACLE\ORADATA\SADHAN\SYSTEM01.DBF
destination for restore of datafile 00002: D:\ORACLE\ORADATA\SADHAN\UNDOTBS01.DBF
destination for restore of datafile 00009: G:\ORA_DBF_EXTENDED\SDH_HRMS01.DBF
channel ORA_DISK_1: restored backup piece 1
piece handle=H:\ORABACK\DAILY_20121003_CUMUL_SADHAN-3036_1.DB tag=CUMULATIVE_L1_DATAFILE params=NULL
channel ORA_DISK_1: restore complete
channel ORA_DISK_1: starting incremental datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00005: D:\ORACLE\ORADATA\SADHAN\TOOLS01.DBF
destination for restore of datafile 00011: G:\ORA_DBF_EXTENDED\SDH_SHTR01.DBF
destination for restore of datafile 00012: G:\ORA_DBF_EXTENDED\SDH_FIN01.DBF
channel ORA_DISK_1: restored backup piece 1
piece handle=H:\ORABACK\DAILY_20121003_CUMUL_SADHAN-3038_1.DB tag=CUMULATIVE_L1_DATAFILE params=NULL
channel ORA_DISK_1: restore complete
channel ORA_DISK_1: starting incremental datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00004: D:\ORACLE\ORADATA\SADHAN\INDX01.DBF
destination for restore of datafile 00010: F:\ORACLE\SADHAN\SDH_EDSS01.DBF
destination for restore of datafile 00014: D:\ORACLE\ORADATA\SADHAN\INDX02.DBF
channel ORA_DISK_1: restored backup piece 1
piece handle=H:\ORABACK\DAILY_20121003_CUMUL_SADHAN-3037_1.DB tag=CUMULATIVE_L1_DATAFILE params=NULL
channel ORA_DISK_1: restore complete
channel ORA_DISK_1: starting incremental datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00007: D:\ORACLE\ORADATA\SADHAN\SDH_TIMS01.DBF
destination for restore of datafile 00013: F:\ORACLE\SADHAN\SDH_EDSS02.DBF
channel ORA_DISK_1: restored backup piece 1
piece handle=H:\ORABACK\DAILY_20121003_CUMUL_SADHAN-3040_1.DB tag=CUMULATIVE_L1_DATAFILE params=NULL
channel ORA_DISK_1: restore complete
channel ORA_DISK_1: starting incremental datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00003: D:\ORACLE\ORADATA\SADHAN\EXAMPLE01.DBF
destination for restore of datafile 00006: D:\ORACLE\ORADATA\SADHAN\USERS01.DBF
destination for restore of datafile 00008: D:\ORACLE\ORADATA\SADHAN\SDH_TIMS02.DBF
channel ORA_DISK_1: restored backup piece 1
piece handle=H:\ORABACK\DAILY_20121003_CUMUL_SADHAN-3039_1.DB tag=CUMULATIVE_L1_DATAFILE params=NULL
channel ORA_DISK_1: restore complete
channel ORA_DISK_1: starting incremental datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00001: D:\ORACLE\ORADATA\SADHAN\SYSTEM01.DBF
destination for restore of datafile 00002: D:\ORACLE\ORADATA\SADHAN\UNDOTBS01.DBF
destination for restore of datafile 00009: G:\ORA_DBF_EXTENDED\SDH_HRMS01.DBF
channel ORA_DISK_1: restored backup piece 1
piece handle=H:\ORABACK\DAILY_20121003_INCR_SADHAN-3041_1.DB tag=DIFFERENTIAL_L1_DATAFILE params=NULL
channel ORA_DISK_1: restore complete
channel ORA_DISK_1: starting incremental datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00005: D:\ORACLE\ORADATA\SADHAN\TOOLS01.DBF
destination for restore of datafile 00011: G:\ORA_DBF_EXTENDED\SDH_SHTR01.DBF
destination for restore of datafile 00012: G:\ORA_DBF_EXTENDED\SDH_FIN01.DBF
channel ORA_DISK_1: restored backup piece 1
piece handle=H:\ORABACK\DAILY_20121003_INCR_SADHAN-3043_1.DB tag=DIFFERENTIAL_L1_DATAFILE params=NULL
channel ORA_DISK_1: restore complete
channel ORA_DISK_1: starting incremental datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00004: D:\ORACLE\ORADATA\SADHAN\INDX01.DBF
destination for restore of datafile 00010: F:\ORACLE\SADHAN\SDH_EDSS01.DBF
destination for restore of datafile 00014: D:\ORACLE\ORADATA\SADHAN\INDX02.DBF
channel ORA_DISK_1: restored backup piece 1
piece handle=H:\ORABACK\DAILY_20121003_INCR_SADHAN-3042_1.DB tag=DIFFERENTIAL_L1_DATAFILE params=NULL
channel ORA_DISK_1: restore complete
channel ORA_DISK_1: starting incremental datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00007: D:\ORACLE\ORADATA\SADHAN\SDH_TIMS01.DBF
destination for restore of datafile 00013: F:\ORACLE\SADHAN\SDH_EDSS02.DBF
channel ORA_DISK_1: restored backup piece 1
piece handle=H:\ORABACK\DAILY_20121003_INCR_SADHAN-3045_1.DB tag=DIFFERENTIAL_L1_DATAFILE params=NULL
channel ORA_DISK_1: restore complete
channel ORA_DISK_1: starting incremental datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00003: D:\ORACLE\ORADATA\SADHAN\EXAMPLE01.DBF
destination for restore of datafile 00006: D:\ORACLE\ORADATA\SADHAN\USERS01.DBF
destination for restore of datafile 00008: D:\ORACLE\ORADATA\SADHAN\SDH_TIMS02.DBF
channel ORA_DISK_1: restored backup piece 1
piece handle=H:\ORABACK\DAILY_20121003_INCR_SADHAN-3044_1.DB tag=DIFFERENTIAL_L1_DATAFILE params=NULL
channel ORA_DISK_1: restore complete
starting media recovery
media recovery failed
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 10/03/2012 20:00:52
ORA-00283: recovery session canceled due to errors
RMAN-11003: failure during parse/execution of SQL statement: alter database recover if needed start
ORA-00283: recovery session canceled due to errors
ORA-00600: internal error code, arguments: [3020], [37833581], [1], [4504], [70575], [244], [], []
ORA-10567: Redo is inconsistent with data block (file# 9, block# 84845)
ORA-10564: tablespace SDH_HRMS_DBF
ORA-01110: data file 9: 'G:\ORA_DBF_EXTENDED\SDH_HRMS01.DBF'
ORA-10561: block type 'TRANSACTION MANAGED INDEX BLOCK', data object# 26918
RMAN> ALTER DATABASE OPEN;
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of alter db command at 10/03/2012 20:01:30
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: 'D:\ORACLE\ORADATA\SADHAN\SYSTEM01.DBF'
At this stage we do not have to be panic. We have already restored the database successfully. This is due to the reason. It is very old database created in noresetlogs mode. We cannot open it without performing incomplete recovery. 
Now Login with SQL*Plus in mount phase
SQL> recover database until cancel;
Press enter as long as you reach to the missing log
CANCEL
SQL> alter database open resetlogs;

Note: You must take fresh full backup after opening the database with resetlogs option and must perform "reset database" to connect rman target to take fresh backup.
C:\CONNECT RMAN TARGET SYS/SYSMAN@SADHAN.WORD CATALOG CATALOG/CATALOG@RMAN
RMAN> RESET DATABASE;