ORA-00205: error in identifying control file

Loss of one control files or if any of the current control file is available:-

If you loss any one of the control file present in your database then you are not able to start your database you will get below mentioned error when trying to start your database :-

If database is not running and you try to start your database then loss of one controlfile will give you below error :-

ORA-00205: error in identifying control file

Below are the available actions :-

Copy the available control file to that destination where previously lost control file was available. Now open your database normally.
                                        OR
Remove the detail of the lost control file from the remaining available current controlfile and start the database normally.

If database is running then loss of one controlfile will suspend your work :-

SQL> select open_mode from v$database;
select open_mode from v$database
                      *
ERROR at line 1:
ORA-00210: cannot open the specified control file
ORA-00202: control file: +AMIT_R1/orcl/controlfile/current.260.797540569'
ORA-27041: unable to open file
SVR4 Error: 2: No such file or directory
Additional information: 3

SQL> shu abort
ORACLE instance shut down.
SQL> exit

After shutting down do one of the below mentioned actions :- 

Copy the available control file to that destination where previously lost control file was available. 

                                          OR

Remove the detail of the lost control file from the remaining available current controlfile.

Now start your database :-


SQL> startup
ORACLE instance started.

Total System Global Area 1.3700E+10 bytes
Fixed Size                  2161112 bytes
Variable Size            6878660136 bytes
Database Buffers         6811549696 bytes
Redo Buffers                7610368 bytes
Database mounted.
Database opened.

Loss of all control files

Loss of all controlfiles should never happen. We have to keep copies of all controlfiles in different locations, but there is still the possibility that we have to recover from losing all those copies. So "Prevention is better than recovery".

If we have lost all copies of current controlfile and we have a backup to recover controlfile from that , then our course of action depends on status of online log files and datafiles.

Online logs available :- 

A. If online logs are available contains redo data for recovery and datafiles are current then:-
1. Restore a backup controlfile from backup.
2. Perform complete recovery.(Specify the name of the REDO logs when asked for that)
3. Open the database with RESETLOGS option.

Note :- If online logs are available contains redo data for recovery and datafiles are not current then perform the same procedure for recovery from the loss of all controlfiles.

Online logs not available :- 

A. If online logs are not available and datafiles are current , then:-
1. Recreate the controlfile.
2. Mount the database
3. Open the database with RESETLOGS.

B. If online logs are not available and datafiles are not current then :-
1. Restore a backup controlfile.
2. Perform a Point-in-time recovery of the database.
3. Open the database with Resetlogs.

Eg :- If we loss all controlfiles then :-

When we tried to start the database we will get the following error :-

SQL> startup
ORACLE instance started.

Total System Global Area 1025298432 bytes
Fixed Size                  1341000 bytes
Variable Size             322963896 bytes
Database Buffers          696254464 bytes
Redo Buffers                4739072 bytes
ORA-00205: error in identifying control file, check alert log for more info

SQL> shu immediate;
ORA-01507: database not mounted

ORACLE instance shut down.
SQL> startup nomount;
ORACLE instance started.

Total System Global Area 1025298432 bytes
Fixed Size                  1341000 bytes
Variable Size             322963896 bytes
Database Buffers          696254464 bytes
Redo Buffers                4739072 bytes
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[cognos@rac1 ~]$ rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Wed Oct 24 19:02:28 2012

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORCL (not mounted)

RMAN> restore controlfile from autobackup;

Starting restore at 24-OCT-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=23 device type=DISK

recovery area destination: +AMIT_R1
database name (or database unique name) used for search: ORCL
channel ORA_DISK_1: AUTOBACKUP +amit_r1/ORCL/AUTOBACKUP/2012_10_24/s_797530166.287.797530169 found in the recovery area
AUTOBACKUP search with format "%F" not attempted because DBID was not set
channel ORA_DISK_1: restoring control file from AUTOBACKUP +amit_r1/ORCL/AUTOBACKUP/2012_10_24/s_797530166.287.797530169
channel ORA_DISK_1: control file restore from AUTOBACKUP complete
output file name=+AMIT_R1/orcl/controlfile/current.260.797540569
output file name=+AMIT_R1/orcl/controlfile/current.261.797540569
Finished restore at 24-OCT-12

RMAN> alter database mount;

database mounted
released channel: ORA_DISK_1

RMAN> recover database;

Starting recover at 24-OCT-12

Starting implicit crosscheck backup at 24-OCT-12

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=26 device type=DISK

Crosschecked 34 objects

Finished implicit crosscheck backup at 24-OCT-12

Starting implicit crosscheck copy at 24-OCT-12

using channel ORA_DISK_1

Finished implicit crosscheck copy at 24-OCT-12

searching for all files in the recovery area

cataloging files...

cataloging done

List of Cataloged Files

=======================

File Name: +amit_r1/ORCL/AUTOBACKUP/2012_10_24/s_797530166.287.797530169

using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 14 is already on disk as file +AMIT_R1/orcl/onlinelog/group_2.265.790789025

archived log file name=+AMIT_R1/orcl/onlinelog/group_2.265.790789025 thread=1 sequence=14

media recovery complete, elapsed time: 00:00:01

Finished recover at 24-OCT-12

RMAN> alter database open resetlogs;

database opened

RMAN> exit

Recovery Manager complete.

[cognos@rac1 ~]$ sqlplus

SQL*Plus: Release 11.2.0.1.0 Production on Wed Oct 24 19:07:46 2012

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Enter user-name: /as sysdba

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Partitioning, Automatic Storage Management, OLAP, Data Mining

and Real Application Testing options

SQL> select open_mode from v$database;

OPEN_MODE

--------------------

READ WRITE

Restore of database completed after loss of all control files but having onlinelog files available.

I hope this article helped you.

Regards,

Amit Rath

ORA-01207: File is more recent than control file - old control file

Few days ago I faced this issue in my database, due to some problem in my ASM disks, my database closed abruptly and I was getting the following error when I was trying to open my database using STARTUP command.

Errors:-
===========
ORA-01122: database file 6 failed verification check
ORA-01110: data file 6: '+AMIT_R1/orcl/datafile/amit.271.795743985'
ORA-01207: file is more recent than control file - old control file.

Cause :-
===========
The information in this file is inconsistent with information from the control file.
The datafile header CHECKPOINT count is beyond the controlfile CHECKPOINT count.

Solution :-
=========== 
1. Mount the database.
 SQL> startup mount;
ORACLE instance started.

Total System Global Area 1025298432 bytes
Fixed Size                  1341000 bytes
Variable Size             322963896 bytes
Database Buffers          696254464 bytes
Redo Buffers                4739072 bytes 
Database mounted. 

2. Recreate the control file of the database.
Please refer to 
http://amit7oracledba.blogspot.in/2012/10/how-to-recreate-control-file-in-oracle.html 
only to recreate your control file.


3. After creation of control file, database is in mount stage

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
MOUNTED

4. Recover the database.
SQL> recover database;

If it does recovery from the redo logs then from above command only your database will be recovered , but if the error is "control file is more recent than the file" then it asks you for archive logs to recover then you have to use below command 

SQL> recover database using backup controlfile until cancel;

it will ask you for archive logs, give the path of all archive logs which it needs until it gives you message, MEDIA RECOVERY COMPLETED.

5. Open the database.
SQL> alter database open;

Database altered.
SQL> select open_mode from v$database;

OPEN_MODE
--------------------
READ WRITE

 Database recovery complete.

I hope this article helped you.

Regards,
Amit Rath 

How to recreate a control file in Oracle Database

We should only recreate our control file when we are in certain circumstances :-

1. All copies of control files present in database are lost or corrupted.
2. We are restoring a backup in which control file is corrupted or missing.
3. We need to change a hard limit database parameter in the control file.
4. If we are moving our database to another server are files are present in different location.
5. Oracle customer support advices us to do so.

Recreating a control file of that database which is able to mount or open.

1. First we have to generate a ascii dump of the control file.

When database is mounted or open :-
SQL> alter database backup controlfile to trace;

          Database altered

Trace file will be generated in User_dump directory.
SQL> show parameter user_dump_dest

NAME                                  TYPE           VALUE
------------------------                  ---------          ----------------------------
user_dump_dest                  string            /u04/app/cognos/diag/rdbms/orc
                                                                 l/orcl/trace

Navigate to this directory and locate the latest trace file by using ls -ltr
[cognos@rac1 ~]$ cd /u04/app/cognos/diag/rdbms/orcl/orcl/trace/
[cognos@rac1 trace]$ ls -ltr

2. Create control file creation script from backup trace file :-

open the trace file named like _orc_1234.trc. It appears like an ordinary trace file but we are interested in the part having create control file script. Modify the trace file, delete every thing above the "CREATE CONTROLFILE" and after the "CHARACTER SET" option.

3. Shut down your database with immediate option.
SQL> shu immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

4. Startup the database in nomount mode.
SQL> startup nomount;
ORACLE instance started.

Total System Global Area 1025298432 bytes
Fixed Size                  1341000 bytes
Variable Size             322963896 bytes
Database Buffers          696254464 bytes
Redo Buffers                4739072 bytes
SQL>

5. Take the control file script and use it to create the control file of the database.
SQL> CREATE CONTROLFILE REUSE DATABASE "ORCL" RESETLOGS  NOARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 (
    '+AMIT_R1/orcl/onlinelog/group_1.262.790789015',
    '+AMIT_R1/orcl/onlinelog/group_1.263.790789019'
  ) SIZE 50M BLOCKSIZE 512,
  GROUP 2 (
    '+AMIT_R1/orcl/onlinelog/group_2.264.790789023',
    '+AMIT_R1/orcl/onlinelog/group_2.265.790789025'
  ) SIZE 50M BLOCKSIZE 512,
  GROUP 3 (
    '+AMIT_R1/orcl/onlinelog/group_3.266.790789027',
    '+AMIT_R1/orcl/onlinelog/group_3.267.790789029'
  ) SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
  '+AMIT_R1/orcl/datafile/system.256.790788811',
  '+AMIT_R1/orcl/datafile/sysaux.257.790788811',
  '+AMIT_R1/orcl/datafile/undotbs1.258.790788813',
  '+AMIT_R1/orcl/datafile/users.259.790788813',
  '+AMIT_R1/orcl/datafile/example.269.790789095',
  '+AMIT_R1/orcl/datafile/amit.271.795743985'
CHARACTER SET AL32UTF8
;
  2    3    4    5    6    7    8    9   10   11   12   13   14   15   16   17   18   19   20   21   22   23   24   25   26   27   28   29

Control file created.

Once the control file successfully created database is automatically mounted.

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
MOUNTED

6. Once the database is mounted open the database with reset logs option.

SQL> alter database open resetlogs;

Database altered.

We have started the database with resetlogs so it's important to take a backup immediately.

7. After the database is open add the existing temp file

SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '+AMIT_R1/orcl/tempfile/temp.268.790789087' size 1429M REUSE;

Tablespace altered.

NOTE :- If we are using this control file creation script for a new database then some change have to be made in control file creation script, instead of "CREATE CONTROLFILE REUSE" we have to use "CREATE CONTROLFILE SET" and instead of "NORESETLOGS" we have to use "RESETLOGS".

Recreating a control file of that database which is not able to mount.

In this scenario when we donot have a control file then :-

1. Restore control file from backup.
                         OR
    Create a script of control file from beginning

CREATE CONTROLFILE REUSE DATABASE "ORCL" RESETLOGS  NOARCHIVELOG

  Follow the format listing :
  1. location of redo logs.
  2. Location of Datafiles.
  3. Specify the Characterset.

Once all things are listed correctly, use this to receate your control file.

SQL> startup nomount;

Create your control file from the script created earlier.

I hope this article helped you.

Regards,
Amit Rath