Loss Of Control-file in various Scenario's

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

Enjoy   J J J