During media failure (single disk crash) the online redo log is usually available so it is possible to recover up to the point of failure. Quite often ,Windows Oracle instance can be started in nomount mode (i.e. spfile is also available).
In this scenario all Control files are lost and it’s not multiplexed. In that case you have no option but to use a backup control file. The recovery needs to be performed from within RMAN, and requires that all logs (archived and current online logs) since the last backup are available. The logs are required because all datafiles must also be restored from backup. The database will then have to be recovered up to the time the control files went missing. This can only be done if all intervening logs are available.
In this example RMAN is not using catalog.
Try to start database and found following error.
d:> sqlplus sys/**** as sysdba;
SQL*Plus: Release 10.2.0.3.0 - Production on Tue Sep 29 11:05:12 2009
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
Connected to an idle instance.
ORCL: SQL> startup;
ORACLE instance started.
Total System Global Area 524288000 bytes
Fixed Size 1291576 bytes
Variable Size 306186952 bytes
Database Buffers 213909504 bytes
Redo Buffers 2899968 bytes
ORA-00205: error in identifying control file, check alert log for more info
In Alter log file I found following error:
Tue Sep 29 11:05:18 2009
ALTER DATABASE MOUNT
Tue Sep 29 11:05:18 2009
ORA-00202: control file: 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\CONTROL01.CTL'
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) The system cannot find the file specified.
Complete Recovery by last night RMAN BACKUP set
1. d:\> rman target /
Recovery Manager: Release 10.2.0.3.0 - Production on Tue Sep 29 10:19:36 2009
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: ORCL (not mounted)
2. RMAN> set dbid=1096578125
executing command: SET DBID
****set DBID - get this from the name of the controlfile autobackup.
For example, if autobackup name is CTL_SP_BAK_C-1507972899-20050124-00 the the DBID is 1507972899. This step will not be required if the instance is
3. RMAN> set controlfile autobackup format for device type disk to 'D:\ABC\BACKUP\RMAN\ORCL\cf_%d_%F';
executing command: SET CONTROLFILE AUTOBACKUP FORMAT
using target database control file instead of recovery catalog
**** Verify the format of your backup set
4. RMAN> restore controlfile from autobackup;
Starting restore at 29-SEP-09
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK
recovery area destination: D:\oracle\product\10.2.0\flash_recovery_area
database name (or database unique name) used for search: ORCL
channel ORA_DISK_1: no autobackups found in the recovery area
channel ORA_DISK_1: looking for autobackup on day: 20090929
channel ORA_DISK_1: looking for autobackup on day: 20090928
channel ORA_DISK_1: autobackup found: D:\ABC\BACKUP\RMAN\ORCL\cf_ORCL_c-10965
78125-20090928-03
channel ORA_DISK_1: control file restore from autobackup complete
output filename=D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\CONTROL01.CTL
output filename=D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\CONTROL02.CTL
output filename=D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\CONTROL03.CTL
Finished restore at 29-SEP-09
*** Now that control files have been restored, the instance can mount the
5. RMAN> alter database mount;
database mounted
released channel: ORA_DISK_1
6. RMAN> restore database;
Starting restore at 29-SEP-09
Starting implicit crosscheck backup at 29-SEP-09
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK
Crosschecked 7 objects
Finished implicit crosscheck backup at 29-SEP-09
Starting implicit crosscheck copy at 29-SEP-09
using channel ORA_DISK_1
Finished implicit crosscheck copy at 29-SEP-09
searching for all files in the recovery area
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2009_09_28\O1_MF_1_48_5D1QDPFJ_.ARC
File Name: D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2009_09_28\O1_MF_1_49_5D21CNOB_.ARC
File Name: D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2009_09_28\O1_MF_1_50_5D21CPS7_.ARC
File Name: D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2009_09_29\O1_MF_1_51_5D38N85L_.ARC
File Name: D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2009_09_29\O1_MF_1_52_5D45J1YK_.ARC
using channel ORA_DISK_1
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\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM01.DBF
restoring datafile 00002 to D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\UNDOTBS01.DBF
restoring datafile 00003 to D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSAUX01.DBF
restoring datafile 00004 to D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\USERS01.DBF
restoring datafile 00005 to D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\OPIS_01.DBF
restoring datafile 00006 to D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\OPIS_02.DBF
restoring datafile 00007 to D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\OPIS1_01.DBF
restoring datafile 00008 to D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\OPIS1_02.DBF
restoring datafile 00009 to D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\OPIS2_01.DBF
restoring datafile 00010 to D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\OPIS2_02.DBF
restoring datafile 00011 to D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\OPIS3_01.DBF
restoring datafile 00012 to D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\OPIS3_02.DBF
restoring datafile 00013 to D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\OPIS4_01.DBF
restoring datafile 00014 to D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\OPIS4_02.DBF
restoring datafile 00015 to D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\OPIS5_01.DBF
restoring datafile 00016 to D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\OPIS5_02.DBF
restoring datafile 00017 to D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\OPIS6_01.DBF
restoring datafile 00018 to D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\OPIS6_02.DBF
restoring datafile 00019 to D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\OPIS_S_01.DBF
restoring datafile 00020 to D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\OPIS_S_02.DBF
restoring datafile 00021 to D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\OPIS_SI_01.DBF
restoring datafile 00022 to D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\OPIS_SI_02.DBF
restoring datafile 00023 to D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\OPIS_HL7_01.DBF
restoring datafile 00024 to D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\OPIS_HL7_02.DBF
restoring datafile 00025 to D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\OPIS_HL7I_01.DB
F
restoring datafile 00026 to D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\OPIS_HL7I_02.DB
F
restoring datafile 00027 to D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\OPIS_AUD_01.DBF
restoring datafile 00028 to D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\OPIS_AUD_02.DBF
restoring datafile 00029 to D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\OPIS_AUDI_01.DB
F
restoring datafile 00030 to D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\OPIS_AUDI_02.DB
F
restoring datafile 00031 to D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\OPIS_RHAPSODY_0
1.DBF
restoring datafile 00032 to D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\OPIS_RHAPSODY_0
2.DBF
restoring datafile 00033 to D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\OPIS_OPIS_RPT_0
1.DBF
restoring datafile 00034 to D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\OPIS_OPIS_RPT_0
2.DBF
channel ORA_DISK_1: reading from backup piece D:\ABC\BACKUP\RMAN\ORCL\DB_ORCL
_T698759044_S731_P1
channel ORA_DISK_1: restored backup piece 1
piece handle=D:\ABC\BACKUP\RMAN\ORCL\DB_ORCL_T698759044_S731_P1 tag=FULL BACK
UP
channel ORA_DISK_1: restore complete, elapsed time: 00:01:39
Finished restore at 29-SEP-09
** Database must be recovered because all datafiles have been restored from backup
7. RMAN> recover database;
Starting recover at 29-SEP-09
using channel ORA_DISK_1
starting media recovery
archive log thread 1 sequence 48 is already on disk as file D:\ORACLE\PRODUCT\10
.2.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2009_09_28\O1_MF_1_48_5D1QDPFJ_.ARC
archive log thread 1 sequence 49 is already on disk as file D:\ORACLE\PRODUCT\10
.2.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2009_09_28\O1_MF_1_49_5D21CNOB_.ARC
archive log thread 1 sequence 50 is already on disk as file D:\ORACLE\PRODUCT\10
.2.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2009_09_28\O1_MF_1_50_5D21CPS7_.ARC
archive log thread 1 sequence 51 is already on disk as file D:\ORACLE\PRODUCT\10
.2.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2009_09_29\O1_MF_1_51_5D38N85L_.ARC
archive log thread 1 sequence 52 is already on disk as file D:\ORACLE\PRODUCT\10
.2.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2009_09_29\O1_MF_1_52_5D45J1YK_.ARC
archive log thread 1 sequence 53 is already on disk as file D:\ORACLE\PRODUCT\10
.2.0\ORADATA\ORCL\REDO02.LOG
channel ORA_DISK_1: starting archive log restore to default destination
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=46
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=47
channel ORA_DISK_1: reading from backup piece D:\ABC\BACKUP\RMAN\ORCL\ARCH_P
OM_1_T698759096_S734_P1
channel ORA_DISK_1: restored backup piece 1
piece handle=D:\ABC\BACKUP\RMAN\ORCL\ARCH_ORCL_1_T698759096_S734_P1 tag=TAG20
090928T114456
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
archive log filename=D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG
\2009_09_29\O1_MF_1_46_5D475JO8_.ARC thread=1 sequence=46
channel default: deleting archive log(s)
archive log filename=D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG
\2009_09_29\O1_MF_1_46_5D475JO8_.ARC recid=641 stamp=698841715
archive log filename=D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG
\2009_09_29\O1_MF_1_47_5D475K17_.ARC thread=1 sequence=47
channel default: deleting archive log(s)
archive log filename=D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG
\2009_09_29\O1_MF_1_47_5D475K17_.ARC recid=640 stamp=698841713
archive log filename=D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG
\2009_09_28\O1_MF_1_48_5D1QDPFJ_.ARC thread=1 sequence=48
archive log filename=D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG
\2009_09_28\O1_MF_1_49_5D21CNOB_.ARC thread=1 sequence=49
archive log filename=D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG
\2009_09_28\O1_MF_1_50_5D21CPS7_.ARC thread=1 sequence=50
archive log filename=D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG
\2009_09_29\O1_MF_1_51_5D38N85L_.ARC thread=1 sequence=51
archive log filename=D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG
\2009_09_29\O1_MF_1_52_5D45J1YK_.ARC thread=1 sequence=52
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO02.LOG thread=1 se
quence=53
media recovery complete, elapsed time: 00:00:18
Finished recover at 29-SEP-09
8. RMAN> alter database open;
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of alter db command at 09/29/2009 10:42:28
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
-- Recovery completed. The database must be opened with RESETLOGS
-- because a backup control file was used. Can also use
-- "alter database open resetlogs" instead.
9. RMAN> alter database open resetlogs;
database opened
**** Several points are worth emphasizing.*****
1. Recovery using a backup controlfile should be done only if a current control file is
unavailable.
2. All datafiles must be restored from backup. This means the database will need to be
recovered using archived and online redo logs. These MUST be available for recovery until the time of failure.
3. As with any database recovery involving RESETLOGS, take a fresh backup immediately.
4. Technically the above is an example of complete recovery - since all committed
transactions were recovered. However, some references consider this to be incomplete
recovery because the database log sequence had to be reset.
After recovery using a backup controlfile, all temporary files associated with locallymanaged tablespaces are no longer available. You can check that this is so by querying the view V$TEMPFILE - no rows will be returned. Therefore tempfiles must be added (or recreated) before the database is made available for general use. In the case at hand, the tempfile already exists so we merely add it to the temporary tablespace. This can be done using SQLPlus or any tool of your choice:
SQL> alter tablespace temp add tempfile
'D:\oracle_data\datafiles\ORCL\TEMP01.DBF';
Tablespace altered.
