Search

Join this Blog for direct reference of any post into your inbox. To join just click on "Join this Site" under "Follower" then Login with your Email.*** DECLARATION: I Maintain this Blog for Helping Myself While at Work and Welcome any body Needing Help!!!.*** CAUTION: Using any of the script from this Blog may contain at Own Risk. These scripts May or May not have been Tested.***

Sunday, 24 February 2013

Different RMAN Recovery Scenarios

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 the fresher or Junior DBA confidence “How to apply different Recovery scenario in different situation”. Some of the scenario’s recovery logs are from our Production database and some of them taken from other DBA’s.
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;

One of the junior DBA while working with Production environment on Thursday afternoon, due to media failure, one of the data file is corrupted. But all other data files are working fine. As the DBA restarted the database, one of data file is starts complaining. Then the DBA decided to recover that particular datafile.
SQL> startup;
ORACLE instance started.
Database mounted.
ORA-01157: cannot identify/lock data file 6 - see DBWR trace file
ORA-01110: data file 6: ‘D:\ORACLE\ORA92\MUJAZORC\USERS02.DBF'
He made the corrupted data file OFFLINE and opened the database, so users can use the database while recovering that particular data file (but in case of system01.dbf you need sufficient downtime to recover).
SQL> alter database datafile ‘D:\ORACLE\ORA92\MUJAZORC\USERS02.DBF' OFFLINE;
Database altered.
SQL> alter database open;
Database altered.
SQL> Select file_id from dba_data_files where file_name =
‘D:\ORACLE\ORA92\MUJAZORC\USERS02.DBF';
FILE_ID
----------
6
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Production With the Partitioning, OLAP and Data Mining options
Now connect the RMAN with catalog and restore and recover the datafile 6
C:\>rman target sys/****@mujazhr.world catalog catalog/catalog@rman
Recovery Manager: Release 10.2.0.1.0 - Production on Thu Jun 25 14:30:09 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: MUJAZORC
(DBID=1165034825)
connected to recovery catalog database
RMAN>run
    {
    restore datafile 6;
    recover datafile 6;
    }

Starting restore at 25-JUN-12
starting full resync of recovery catalog
full resync complete
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=135 devtype=DISK
creating datafile fno=6
name=D:\ORACLE\ORA92\MUJAZORC\USERS02.DBF
restore not done; all files readonly, offline, or already restored
Finished restore at 25-JUN-12
Starting recover at 25-JUN-12
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:01
Finished recover at 25-JUN-12
RMAN> exit
Recovery Manager complete.
C:\>sqlplus sys/oracle as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Thu Jun 25 14:34:10 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> alter database datafile ‘
D:\ORACLE\ORA92\MUJAZORC\USERS02.DBF' ONLINE;
Database altered.
SQL> Select distinct status from dba_data_files;
STATUS
---------
AVAILABLE

One of our Production database is related with Retail & Trading firm on Windows 2003 env. On Friday morning database goes down. As we restarted the database, DB complaining one of datafile is corrupted, may be this is due to the hard disk repair work happened on Thursday Evening where as other tablespaces datafiles are as it is having no issue. So we decided to recover only that particular tablespace.
SQL> startup;
Database mounted.
ORA-01157: cannot identify/lock data file 6 - see DBWR trace file
ORA-01110: data file 6: ‘D:\ORACLE\ORADATA\ISSCOHR\EDSS_DBF01.DBF '
We made this tablespace offline and open the database. So that end users can use the database while recovering the tablespace (as this is not the system tablespace).
SQL> alter database datafile ‘D:\ORACLE\ORADATA\ISSCOHR\EDSS_DBF01.DBF' OFFLINE;
Database altered.
SQL> alter database open;
alter database open
* ERROR at line 1:
ORA-01157: cannot identify/lock data file 7 - see DBWR trace file
ORA-01110: data file 7: ‘D:\ORACLE\ORADATA\ISSCOHR\EDSS_DBF02.DBF'
SQL> alter database datafile ‘D:\ORACLE\ORADATA\ISSCOHR\EDSS_DBF02.DBF' OFFLINE;
Database altered.
SQL> alter database open;
Database altered.
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 –
Production With the Partitioning, OLAP and Data Mining options
C:\>rman target sys/****@ISSCOHR.world catalog catalog/catalog@rman
Recovery Manager: Release 10.2.0.1.0 - Production on Fri Jan 12 10:13:26 2013
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: ISSCOHR (DBID=2613999945)
connected to recovery catalog database
RMAN> run{
      restore tablespace EDSS_DBF;
      recover tablespace EDSS_DBF;
      }

Starting restore at 12-JAN-13
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=128 devtype=DISK
creating datafile fno=7 name=
D:\ORACLE\ORADATA\ISSCOHR\EDSS_DBF02.DBF
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00006 to
D:\ORACLE\ORADATA\ISSCOHR\EDSS_DBF01.DBF
channel ORA_DISK_1: reading from backup piece H:\ORABACK\
WEEKLY_20130112_FULL_ISSCOHR-2587_1.DB
channel ORA_DISK_1: restored backup piece 1
piece handle= H:\ORABACK\WEEKLY_20130112_FULL_ISSCOHR-2588_1.DB
channel ORA_DISK_1: restore complete, elapsed time: 00:13:11
Finished restore at 12-JAN-13
Starting recover at 12-JAN-13
using channel ORA_DISK_1
starting media recovery
archive log thread 1 sequence 36 is already on disk as file E:\ORACLE\ARCHIVE\
ARC02244.001
archive log thread 1 sequence 37 is already on disk as file E:\ORACLE\ARCHIVE\
ARC02245.001
archive log thread 1 sequence 38 is already on disk as file E:\ORACLE\ARCHIVE\
ARC02246.001
archive log thread 1 sequence 39 is already on disk as file E:\ORACLE\ARCHIVE\
ARC02247.001
media recovery complete, elapsed time: 00:01:23
Finished recover at 12-JUN-13
SQL> connect sys/password as sysdba
Connected.
SQL> alter database datafile ‘
D:\ORACLE\ORADATA\ISSCOHR\EDSS_DBF01.DBF' ONLINE;
Database altered.
SQL> alter database datafile ‘
D:\ORACLE\ORADATA\ISSCOHR\EDSS_DBF02.DBF' ONLINE;

If SYSTEM tablespace gets corrupted and others are intact. Then you need sufficient downtime to recover this tablespace as you can not open the database without recovering the SYSTEM tablespace.
SQL> startup;
Database mounted.
ORA-01157: cannot identify/lock data file 1 - see DBWR trace file
ORA-01110: data file 1:'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM01.DBF'
Take the SYSTEM tablespace offline and recover that tablespace only. The recovery log taken from any other Test database.
SQL> alter database datafile 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM01.DBF’ OFFLINE;
Database altered.
SQL> ALTER DATABASE OPEN;
ALTER DATABASE OPEN
* ERROR at line 1:
ORA-01147: SYSTEM tablespace file 1 is offline
ORA-01110: data file 1: 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM01.DBF'
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production With the Partitioning, OLAP and Data Mining options
C:\>rman catalog=rman/rman@catdb target=sys/password@orcl
Recovery Manager: Release 10.2.0.1.0 - Production on Fri May 22 10:21:04 2009
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: ORCL (DBID=1215124933, not open)
connected to recovery catalog database
RMAN> run
     {
     restore tablespace system;
     recover tablespace system;
     }
Starting restore at 22-MAY-09
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 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 C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM01.DBF
channel ORA_DISK_1: reading from backup piece C:\RMANBACKUP\BACKUPORCL_DB_0JKFLE9Q_1_1_%S_%P
channel ORA_DISK_1: restored backup piece 1
piece handle=C:\RMANBACKUP\BACKUPORCL_DB_0JKFLE9Q_1_1_%S_%P tag=TAG20090522T094738
channel ORA_DISK_1: restore complete, elapsed time: 00:00:35
Finished restore at 22-MAY-09
Starting recover at 22-MAY-09
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:04
Finished recover at 22-MAY-09
RMAN> exit
Recovery Manager complete.
C:\>sqlplus sys/password as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Fri May 22 10:22:20 2009
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Production With the Partitioning, OLAP and Data Mining options
SQL> alter database open;
alter database open
* ERROR at line 1:
ORA-01147: SYSTEM tablespace file 1 is offline
ORA-01110: data file 1: 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM01.DBF'
SQL> alter database datafile 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM01.DBF' online;
Database altered.
SQL> ALTER DATABASE OPEN;
Database altered.
SQL> select distinct status from dba_tablespaces;
STATUS
---------
ONLINE
SQL> select distinct status from dba_data_files;
STATUS
---------
AVAILABLE

One afternoon, you restarted your database and realized that all the redo log files are gets corrupted but fortunately you did not lost the controlfile. Thus you are able to mount the database. To recover all those redo log files, you have decided to perform incomplete recovery.
SQL> startup;
ORACLE instance started.
Database mounted.
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1:'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO01.LOG'
SQL>exit
As you have taken the backup in the morning mount the database and run the RMAN backup first, just to make sure all the archived redo log files are backed up before start the actual recovery process.
Once RMAN backup is completed, you have to perform incomplete recovery and recovered until the last archived redo log. The recovery logs are taken from any other source.
C:\>rman catalog=rman/rman@catdb target=sys/password@orcl
Recovery Manager: Release 10.2.0.1.0 - Production on Sat May 23 20:36:24 2009
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: ORCL (DBID=1215124933, not open)
connected to recovery catalog database
RMAN> backup archivelog all;
Starting backup at 23-MAY-09
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=152 devtype=DISK
channel ORA_DISK_1: starting archive log backupset
channel ORA_DISK_1: specifying archive log(s) in backup set
input archive log thread=1 sequence=2 recid=31 stamp=687541158
input archive log thread=1 sequence=3 recid=32 stamp=687542042
input archive log thread=1 sequence=4 recid=33 stamp=687542062
comment=NONE
Starting Control File and SPFILE Autobackup at 23-MAY-09
piece handle=C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\C-1215124933-20090523-0A comment=NONE
Finished Control File and SPFILE Autobackup at 23-MAY-09
RMAN> list backup of archivelog from time='sysdate-1';
List of Backup Sets
===================
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
10692 37.00K DISK 00:00:01 23-MAY-09
BP Key: 10697 Status: AVAILABLE Compressed: NO Tag: TAG20090523T224042
Piece Name: C:\RMANBACKUP\BACKUPORCL_DB_07KFPFVA_7_1

List of Archived Logs in backup set 10692
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 6 551206 23-MAY-09 551263 23-MAY-09
RMAN> restore database until sequence=6 thread=1 force;
Starting restore at 23-MAY-09
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 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 C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM01.DBF
restoring datafile 00002 to C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\UNDOTBS01.DBF
restoring datafile 00003 to C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSAUX01.DBF
restoring datafile 00004 to C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\USERS01.DBF
channel ORA_DISK_1: reading from backup piece C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\02KFPFLV_1_1
channel ORA_DISK_1: restored backup piece 1
piece handle=C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\02KFPFLV_1_1 tag=TAG20090523T223542
channel ORA_DISK_1: restore complete, elapsed time: 00:00:45
Finished restore at 23-MAY-09
RMAN> recover database until sequence=6 thread=1 ;
Starting recover at 23-MAY-09
using channel ORA_DISK_1
starting media recovery
archive log thread 1 sequence 4 is already on disk as file C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\ARCHIVE\LOG_4_1_687649381.ARC
archive log thread 1 sequence 5 is already on disk as file C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\ARCHIVE\LOG_5_1_687649381.ARC
archive log filename=C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\ARCHIVE\LOG_4_1_687649381.ARC thread=1 sequence=4
archive log filename=C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\ARCHIVE\LOG_5_1_687649381.ARC thread=1 sequence=5
media recovery complete, elapsed time: 00:00:03
Finished recover at 23-MAY-09
RMAN> alter database open resetlogs;
database opened
new incarnation of database registered in recovery catalog
The database is recovered successfully. But this is incomplete recovery. Prior to oracle10g, oracle strongly recommended to takes the full database backup whenever there is incomplete recovery. But in oracle10g, it is optional. But still it is good to take the full database backup for safer side.

Unfortunately, if you lost all the data files, control files, redo log files. But luckily the current archived redo log files were intact.  In that case you are not able to mount the database since lost your controlfile too.
SQL> startup;
ORACLE instance started.
ORA-00205: error in identifying control file, check alert log for more info
You need to recover first the control file from RMAN backup. Once control file is recovered, you are able to mount the database. After the database is mounted run the RMAN backup to make sure, all the current archive log files are backed up and recover the data base until last the sequence of archived log file.
C:\>set oracle_sid=orcl
C:\>rman catalog=rman/rman@catdb target=sys/password
Recovery Manager: Release 10.2.0.1.0 - Production on Sun May 24 15:26:51 2009
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: orcl (not mounted)
connected to recovery catalog database
RMAN> restore controlfile;
Starting restore at 24-MAY-09
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=157 devtype=DISK
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: reading from backup piece C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\C-1215492928-20090524-00
channel ORA_DISK_1: restored backup piece 1
piece handle=C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\C-1215492928-20090524-00 tag=TAG20090524T152409
channel ORA_DISK_1: restore complete, elapsed time: 00:00:04
output filename=C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\CONTROL01.CTL
output filename=C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\CONTROL02.CTL
output filename=C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\CONTROL03.CTL
Finished restore at 24-MAY-09
SQL> alter database mount;
Database altered.
SQL> select archivelog_change#-1 from v$database;
ARCHIVELOG_CHANGE#-1
--------------------
547010
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production With the Partitioning, OLAP and Data Mining options
C:\>rman catalog=rman/rman@catdb target=sys/password@orcl
Recovery Manager: Release 10.2.0.1.0 - Production on Sun May 24 15:29:33 2009
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: ORCL (DBID=1215492928, not open)
connected to recovery catalog database
RMAN> run
{
       set until scn 547010;
       restore database;
       recover database;
       alter database open resetlogs;
       }

executing command: SET until clause
Starting restore at 24-MAY-09
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 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 C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM01.DBF
restoring datafile 00002 to C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\UNDOTBS01.DBF
restoring datafile 00003 to C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSAUX01.DBF
restoring datafile 00004 to C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\USERS01.DBF
channel ORA_DISK_1: reading from backup piece C:\RMANBACKUP\BACKUPORCL_DB_02KFRAN7_1_1_%S_%P
channel ORA_DISK_1: restored backup piece 1
piece handle=C:\RMANBACKUP\BACKUPORCL_DB_02KFRAN7_1_1_%S_%P tag=TAG20090524T1523
channel ORA_DISK_1: restore complete, elapsed time: 00:00:45
Finished restore at 24-MAY-09
Starting recover at 24-MAY-09
using channel ORA_DISK_1
starting media recovery
archive log thread 1 sequence 3 is already on disk as file C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\ARCHIVE\LOG_3_1_687712197.ARC
archive log filename=C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\ARCHIVE\LOG_3_1_687712197.ARC thread=1 sequence=3
media recovery complete, elapsed time: 00:00:04
Finished recover at 24-MAY-09
database opened
new incarnation of database registered in recovery catalog
starting full resync of recovery catalog
full resync complete

One of the Application programmer truncated the critical table in the evening around 3.46 PM and they need to recover the truncated table. They have to decide to go to the time based incomplete recovery of the exact 03.45 PM.
Find the Exact time for recovery and set the date and time format along with time based recovery scripts.
SQL> Select count(*) from employee;
COUNT(*)
----------
14
SQL> select to_char(sysdate,'DD-MM-YYYY:HH24:MI:SS')
from dual;
TO_CHAR(SYSDATE,'DD
-------------------
24-05-2009:15:45:42
SQL> truncate table employee;
Table truncated.
SQL> select count(*) from employee;
COUNT(*)
----------
0
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Database mounted.
SQL>
C:\>rman catalog=rman/rman@catdb target=sys/password@orcl
Recovery Manager: Release 10.2.0.1.0 - Production on Sun May 24 15:58:56 2009
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: ORCL (DBID=1215492928, not open)
connected to recovery catalog database
RMAN> run
      {
      set until time "to_date('24-05-2009:15:45:42','DD-MM-YYYY HH24:MI:SS')";
      restore database;
      recover database;
      }
executing command: SET until clause
Starting restore at 24-MAY-09
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 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 C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM01.DBF
restoring datafile 00002 to C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\UNDOTBS01.DBF
restoring datafile 00003 to C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSAUX01.DBF
restoring datafile 00004 to C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\USERS01.DBF
channel ORA_DISK_1: reading from backup piece C:\RMANBACKUP\BACKUPORCL_DB_09KFRBBR_1_1_%S_%P
channel ORA_DISK_1: restored backup piece 1
piece handle=C:\RMANBACKUP\BACKUPORCL_DB_09KFRBBR_1_1_%S_%P tag=TAG20090524T1534
channel ORA_DISK_1: restore complete, elapsed time: 00:00:45
Finished restore at 24-MAY-09
Starting recover at 24-MAY-09
using channel ORA_DISK_1
starting media recovery
archive log thread 1 sequence 2 is already on disk as file C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\ARCHIVE\LOG_2_1_687713476.ARC
archive log thread 1 sequence 3 is already on disk as file C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\ARCHIVE\LOG_3_1_687713476.ARC
archive log filename=C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\ARCHIVE\LOG_2_1_687713476.ARC thread=1 sequence=2
media recovery complete, elapsed time: 00:00:05
Finished recover at 24-MAY-09
RMAN> exit
Recovery Manager complete.
C:\>sqlplus sys/password as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Sun May 24 16:01:31 2009
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Production With the Partitioning, OLAP and Data Mining options
SQL> alter database open resetlogs;
Database altered.
SQL> connect scott/tiger@orcl
Connected.
SQL> select count(*) from employee;
COUNT(*)
----------
14
Note: If you use the ‘alter database open resetlogs’ command from SQL*PLUS then you must need to use ‘reset database’ command to connect RMAN catalog for fresh backup.

11 comments:

  1. hi ,

    i would LIKE TO QUERY that dispalys ALL the CONSTRAINTS ON a TABLE ,
    COLUMN NAME , CONSTRAINT NAME, what TYPE of CONSTRAINT(
    whether PRIMARY,FOREIGN ,CHECK ,UNIQUE, CHECK) IF it IS CHECK what IS the CONDITION given


    IS that possible TO retreive IN a single query. please HELP me out

    ReplyDelete
    Replies
    1. Dear AnuDeep,
      You can generate the script using Toad exactly what you want.
      At this moment i am out of the office so i am not able to send you the exact query.
      but using this link you can find the same:
      http://shahiddba.blogspot.com/2012/04/useful-query-for-both-dba-and-developer.html

      Hope it will help you!

      Thanks & Best Wishes
      Shahid Ahmed

      Delete
  2. Regarding RMAN ….

    Configure retention policy to redundancy 1;
    1) backup datafile 3 tag=DATAFILE3;
    2) backup datafile 3 tag=DATAFILE3;
    3) backup datafile 3 tag=DATAFILE3;
    4) backup datafile 3 tag=DATAFILE3;

    crosscheck backup;

    if redundancy=1 than all other backups 2,3,4 should mark as obsolete but while list backup of datafile 3; all backup status are still “available”

    sir why the backup status not changed to obsolete

    ReplyDelete
  3. thank you soooooooooo much sir....................

    ReplyDelete
  4. This comment has been removed by the author.

    ReplyDelete
  5. what is recovery catalog

    ReplyDelete
  6. Are you looking to make cash from your websites or blogs via popunder advertisments?
    If so, have you considered using Clickadu?

    ReplyDelete
  7. i want to take archivelog of particular scn

    ReplyDelete
  8. one scenerio if system datafile is corrupted while db is up/ruuning
    wat will happen db crash immediately or work what time it could crash,who can acesses,will it work normal or restriction

    ReplyDelete