A Control file is a small binary file that is part of an Oracle database. The control file is used to keep track of the database's status and physical structure. The control file is absolutely crucial to database operation . Here , we will discuss the various scenario's when control file(s) get lost or corrupt.
CASE 1 : If one of the controlfile get lost or corrupted
when the database is shut down and on startup we get the following error due to loss of controlfile.
C:\>sqlplus sys/xxxx@noida as sysdba
SQL*Plus: Release 11.1.0.6.0 - Production on Mon Apr 18 15:41:33 2011
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 318046208 bytes
Fixed Size 1332920 bytes
Variable Size 239077704 bytes
Database Buffers 71303168 bytes
Redo Buffers 6332416 bytes
ORA-00205: error in identifying control file, check alert log for more info
Checked the Alert log file and the following information are in the alert log file.
ALTER DATABASE MOUNT
Mon Apr 18 15:42:12 2011
ORA-00210: cannot open the specified control file
ORA-00202: control file: 'D:\ORACLE\ORADATA\NOIDA\CONTROL02.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.
Mon Apr 18 15:42:14 2011
Checker run found 1 new persistent data failures
ORA-205 signalled during: ALTER DATABASE MOUNT
To
solve this issue, copy one of the existing control file (say
control01.ctl or control03.ctl ) and paste it where the missing control
file was earlier residing and rename the controlfile which one is
missing, as in above example, control file (CONTROL02.CTL) is missing and then following the below steps:
SQL> alter database mount;
Database altered.
SQL> alter database open;
Database altered.
SQL> select name,open_mode from v$database ;
NAME OPEN_MODE
--------- ----------
NOIDA READ WRITE
CASE 2: When all the controlfile are lost
If
we have valid backup and if all the control files are lost
then we can recover the control files from autobackup of
controlfile or by specifying the location of autobackup control file.
C:\>sqlplus sys/xxxx@noida as sysdba
SQL*Plus: Release 11.1.0.6.0 - Production on Mon Apr 18 16:21:55 2011
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> startup nomount
ORACLE instance started.
Total System Global Area 318046208 bytes
Fixed Size 1332920 bytes
Variable Size 272632136 bytes
Database Buffers 37748736 bytes
Redo Buffers 6332416 bytes
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
C:\>rman target sys/xxxx@noida
Recovery Manager: Release 11.1.0.6.0 - Production on Mon Apr 18 16:33:34 2011
Copyright (c) 1982, 2007, Oracle. All rights reserved.
connected to target database: NOIDA (not mounted)
RMAN> restore controlfile from 'D:\orcl_bkp\cf\C-1502483083-20110418-01'; (location of controlfile)
Starting restore at 18-APR-11
using channel ORA_DISK_1
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
output file name=D:\ORACLE\ORADATA\NOIDA\CONTROL01.CTL
output file name=D:\ORACLE\ORADATA\NOIDA\CONTROL02.CTL
output file name=D:\ORACLE\ORADATA\NOIDA\CONTROL03.CTL
Finished restore at 18-APR-11
RMAN> alter database mount;
database mounted
released channel: ORA_DISK_1
RMAN> recover database;
Starting recover at 18-APR-11
Starting implicit crosscheck backup at 18-APR-11
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=153 device type=DISK
Crosschecked 7 objects
Finished implicit crosscheck backup at 18-APR-11
Starting implicit crosscheck copy at 18-APR-11
using channel ORA_DISK_1
Finished implicit crosscheck copy at 18-APR-11
searching for all files in the recovery area
cataloging files...
no files cataloged
using channel ORA_DISK_1
starting media recovery
archived log for thread 1 with sequence 19 is already on disk as file D:\ORACLE\ORADATA\NOIDA\RE
archived log file name=D:\ORACLE\ORADATA\NOIDA\REDO01.LOG thread=1 sequence=19
media recovery complete, elapsed time: 00:00:05
Finished recover at 18-APR-11
RMAN> alter database open resetlogs;
database opened
CASE 3 : When we donot have any backup and and all control files are lost or corrupted
SQL> startup nomount
ORACLE instance started.
Total System Global Area 318046208 bytes
Fixed Size 1332920 bytes
Variable Size 281020744 bytes
Database Buffers 29360128 bytes
Redo Buffers 6332416 bytes
Now we create the controlfile manually on command prompt
SQL> CREATE CONTROLFILE REUSE DATABASE "NOIDA" NORESETLOGS archivelog
MAXLOGFILES 5
MAXLOGMEMBERS 3
MAXDATAFILES 10
MAXINSTANCES 1
MAXLOGHISTORY 113
LOGFILE
GROUP 1 'D:\oracle\oradata\noida\REDO01.LOG' SIZE 50M,
GROUP 2 'D:\oracle\oradata\noida\REDO02.LOG' SIZE 50M,
GROUP 3 'D:\oracle\oradata\noida\REDO03.LOG' SIZE 50M
DATAFILE
'D:\oracle\oradata\noida\SYSTEM01.DBF' ,
'D:\oracle\oradata\noida\USERS01.DBF' ,
'D:\oracle\oradata\noida\EXAMPLE01.DBF' ,
'D:\oracle\oradata\noida\SYSAUX01.DBF' ,
'D:\oracle\oradata\noida\TRANS.DBF' ,
'D:\oracle\oradata\noida\UNDOTBS01.DBF' ;
Control file created.
SQL> archive log list
Database log mode Archive Mode
Automatic archival Disabled
Archive destination D:\archive\
Oldest online log sequence 1
Next log sequence to archive 1
Current log sequence 1
SQL> select first_change# ,group# from v$log;
FIRST_CHANGE# GROUP#
------------- ----------
1313491 1
0 3
0 2
SQL> alter database open;
SQL> select name,open_mode from v$database;
NAME OPEN_MODE
--------- ----------
NOIDA READ WRITE
