Ora 00205 error in identifying control file check alert log for more info

Overview:

Facing error messages meanwhile working with the Oracle database is very disturbing as it breaks the flow of your work. Isn’t it…?

Our today’s article is also about one such annoying Oracle ORA-00205: error in identifying control file and it’s possible fixes.

Oracle is free, open business management software available for both personal and commercial use. The RDMS equipped system helps users to process credit card payments, handles customer, support, and manages customer orders.

Oracle, networking software does the task of data exchange between machines using underlying services to establish communication between the components. During data exchange there may be few errors in Oracle due to which database errors could evolve and connection may not be well established.

What Causes ORA-00205 Error?

ORA-00205 is one such error which arises due to dislocation of a control file or might have allocated in some wrong location.

Oracle database contains control file; it’s a small binary file that keeps record of the database physical structure. The control file includes:

• The database name
• Names and locations of associated data files and redo log files
• The timestamp of the database creation
• The current log sequence number
• Checkpoint information

Whenever the database is open, the control file must be available for writing by the Oracle database. If the control file cannot be accessed or it is missing, the database cannot be mounted and the recovery of the database becomes very difficult. The creation of the control file is at the same time when the database is created.

On some Operating systems, at least one copy of the control file is created during the creation of the database and on some OS multiple files are created. You should always create two or more copies of the control file during database creation or later also you could change particular setting in the control files.

The system is not able to find a control file of the specified name and size that is required to execute the same. This may be due to data corruption or some hardware/software issue that makes the access of the file impossible.

How To Fix ORA-00205: Error In Identifying Control File, Check Alert Log For More Info?

Fix 1# Check For All Your Control Files

Check all control files whether they are available online or not. Also ensure that these are the same files that the system created at cold start time.

Close your database if it is already opened.

Create pfile from spfile by using command:

SQL> create pfile from spfile;

In directory $ORACLE_HOMEdatabase a newly created pfile is present. (Its name is init<SID>.ora)

Edit this pfile to correct control file locations. Then follow this command:

SQL> create spfile from pfile;

After that, you will be able to see control files. Also if you have changed data file locations; you can rename data files in mount mode.

However if you have lost only one of many control files, then the repair procedure is different.  Having at least two control file (normally three) is recommended.

So, that if you need to identify the surviving control files in that case just replaces the missing one with one of the others.

Fix 2# Provide Filenames For The Control Files

Specify control file name using CONTROL_FILES initialization parameter in the database initialization parameter file.

If you do not specify files for CONTROL_FILES before database creation, then following things will happen:

• operating system specific default name is taken
• Using Oracle-managed files, then the initialization parameters you set is to enable name and location of the control files
• Using Automatic Storage Management, incomplete ASM filenames could be placed in the DB_CREATE_FILE_DESTand DB_RECOVERY_FILE_DEST initialization parameters.

Fix 3# Multiplex Control Files On Different Disks

Two control files should be taken for every Oracle Database and should be stored on a different physical disk. If anyhow a control file is damaged (say due to a disk failure) the associated instance must be shut down possibly soon. When the drive has been repaired the damaged control file can be restored using the copy made on the other disk and can be restarted. Thus, no media recovery is required in this case.

Fix 4# Manage The Size Of Control Files

A control file size is the value set for the MAXDATAFILES, MAXLOGFILES, MAXLOGMEMBERS, MAXLOGHISTORY, and MAXINSTANCES parameters in the CREATE DATABASE statement that created the associated database. If values of these parameters is increased the size of control file increases

If your control files are missing from your database then you will not be able to start your database. At last you will start getting ORA-00205: error in identifying control file, check alert log for more info.

[Automated Solution] To Fix ORA-00205: Error In Identifying Control File, Check Alert Log For More Info

If the above manual fixes won’t help you to fix ORA-00205: error in identifying control file then immediately go with the experts recommended option i .e Oracle File Repair Tool. As this tool efficiently fixes all kinds of Oracle database errors  and issues.

• This tool successfully repairs and recover damaged/corrupted/lost database DBF file.
• You can also recovers your lost clusters, views, table space, tables, triggers, roles, index, schema, synonyms, and sequence and cluster tables etc.
• It shows preview of all recoverable Oracle database objects before making the recovery.
• It recover all the queries of the procedures, triggers, views, package body, package, functions, and database links within the text files.
• The software is well compatible with  almost every versions of Windows OS like Windows 10/ 8.1/ 8/ 7.

Steps To Use  Oracle File Repair tool

Step 1: Open Oracle data recovery tool. You’ll see a pop-up window showing options to select or search corrupt Oracle databases in your computer.

Step 2: Hit Scan File to initiate the scan process after selecting the oracle database. The recoverable database objects get listed in left-side pane.

Step 3: Click an object to see its preview.

Step 4:  Click Start Repair in the icon bar to start the repair process. A pop-up window is displayed which show the steps needed to perform further. Click next and continue.

Step 5: Give the user name, password and path of the blank database where you want to save the repaired database objects.

Wrap Up:

At last I want to conclude that always keep backup of your control files even if you have successfully overcome this Oracle error ORA00205.

As, it is very important whenever the physical structure of the database is changed. Structural changes could be adding, dropping, or renaming datafiles/tablespace/groups/redo files as applicable to each.

Don’t forget to share your opinion regarding this post in the comment section…..!

Cause :- This error can occur for lost/corrupted any control file.
In my situation I have unfortunately lost one of control file from two files.

Solution :- Create pfile from spfile > Edit pfile > Change «*.control_files=» parameter. Open database using pfile.

SQL> select status from v$instance;

STATUS
————
STARTED

SQL> alter database mount;
alter database mount
*
ERROR at line 1:
ORA-00205: error in identifying control file, check alert log for more info

SQL> show parameter contr

NAME                                 TYPE        VALUE
———————————— ———— ——————————
control_file_record_keep_time        integer     7
control_files                        string      D:APPORADATADBRMANCONTROL0
                                                 1.CTL, D:APPFLASH_RECOVERY_A
                                                 READBRMANCONTROL02.CTL
control_management_pack_access       string      DIAGNOSTIC+TUNING
SQL> create pfile=’D:pfile.ora’ from spfile;

File created.

Now edit pfile

=================================== Old Pfile ===================================================
dbrman.__db_cache_size=218103808
dbrman.__java_pool_size=4194304
dbrman.__large_pool_size=4194304
dbrman.__oracle_base=’D:app’#ORACLE_BASE set from environment
dbrman.__pga_aggregate_target=255852544
dbrman.__sga_target=381681664
dbrman.__shared_io_pool_size=0
dbrman.__shared_pool_size=146800640
dbrman.__streams_pool_size=0
*.audit_file_dest=’D:appadminDBRMANadump’
*.audit_trail=’db’
*.compatible=’11.2.0.0.0′
*.control_files=’D:apporadataDBRMANcontrol01.ctl’,’D:apporadataDBRMANcontrol02.ctl’
*.db_block_size=8192
*.db_domain=»
*.db_name=’DBRMAN’
*.db_recovery_file_dest=’D:appflash_recovery_area’
*.db_recovery_file_dest_size=4039114752
*.diagnostic_dest=’D:app’
*.dispatchers='(PROTOCOL=TCP) (SERVICE=DBRMANXDB)’
*.memory_target=634388480
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile=’EXCLUSIVE’
*.undo_tablespace=’UNDOTBS1′
==================================== After Editing New Pfile ==========================================
dbrman.__db_cache_size=218103808
dbrman.__java_pool_size=4194304
dbrman.__large_pool_size=4194304
dbrman.__oracle_base=’D:app’#ORACLE_BASE set from environment
dbrman.__pga_aggregate_target=255852544
dbrman.__sga_target=381681664
dbrman.__shared_io_pool_size=0
dbrman.__shared_pool_size=146800640
dbrman.__streams_pool_size=0
*.audit_file_dest=’D:appadminDBRMANadump’
*.audit_trail=’db’
*.compatible=’11.2.0.0.0′
*.control_files=’D:apporadataDBRMANcontrol01.ctl’
*.db_block_size=8192
*.db_domain=»
*.db_name=’DBRMAN’
*.db_recovery_file_dest=’D:appflash_recovery_area’
*.db_recovery_file_dest_size=4039114752
*.diagnostic_dest=’D:app’
*.dispatchers='(PROTOCOL=TCP) (SERVICE=DBRMANXDB)’
*.memory_target=634388480
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile=’EXCLUSIVE’
*.undo_tablespace=’UNDOTBS1′
============================================================================================================

SQL> ALTER DATABASE MOUNT;
ALTER DATABASE MOUNT
*
ERROR at line 1:
ORA-00205: error in identifying control file, check alert log for more info

SQL> SHUTDOWN IMMEDIATE;
ORA-01507: database not mounted

ORACLE instance shut down.
SQL> CONN /AS SYSDBA
Connected to an idle instance.
SQL> STARTUP PFILE=D:pfile.ora;
ORACLE instance started.

Total System Global Area  535662592 bytes
Fixed Size                  1375792 bytes
Variable Size             310378960 bytes
Database Buffers          218103808 bytes
Redo Buffers                5804032 bytes
Database mounted.
Database opened.
SQL>

Today i intentionaly deleted one of three available Control File of one of the instance ‘orcl’. Below are the steps i performed to recover the lost DB Controlfile to bring back the Database up.

This is what i recieved in SQL command line when tried to start the effected the instance.
SQL> startup
ORACLE instance started.

Total System Global Area  306184192 bytes
Fixed Size                  1219112 bytes
Variable Size             109053400 bytes
Database Buffers          192937984 bytes
Redo Buffers                2973696 bytes
ORA-00205: error in identifying control file, check alert log for more info

Pre – Checks:
* First check if the entry is available in PFILE and check status of control_files paramter.
* Veify the Control Files entries in PFILE and supply changes to the spfile if discrepancies found by using create spfile/pfile statement.

Example Entry in an PFILE (INIT.ORA)
*.control_files=’/u01/app/oracle/oradata/orcl/control01.ctl’,’/u01/app/oracle/oradata/orcl/control02.ctl’,’/u01/app/oracle/oradata/orcl/control03.ctl’

SQL> show parameter control_files

NAME                                 TYPE        VALUE
———————————— ———– ——————————
control_file_record_keep_time        integer     7
control_files                        string      /u01/app/oracle/oradata/orcl/c
ontrol01.ctl, /u01/app/oracle/
oradata/orcl/control02.ctl, /u
01/app/oracle/oradata/orcl/con
trol03.ctl

* If you have ASM setup on the same machine, it is recommended that you should always check ASM state. It should be always running without any errors during startup (i.e. DISK Groups errors etc.) otherwise would cause issues while restoring/recovering files that are created using ASM signatures.

Scenario 1: Corrupt or Missing control file

When attempting to start the database you see the following error.

Looking the alert log you see that the /u02/app/oracle/oradata/orcl/ctl/control01.ctl is corrupt.

Or you may see the file is missing when looking in the alert log.

In either case, to recover from this problem is simple and does not require going to the last backup provided your control files are multiplexed. To resolve this problem we simply copy a control file from one of the other locations.

Identify the control files:

We know from the alert log that /u02/app/oracle/oradata/orcl/ctl/control01.ctl is the control file that is having problems so we will copy the one from the /u01/app/oracle/flash_recovery_area/orcl/control02.ctl and then mount and open the database.

By having a multiplexed copy of the control file we were able to recover from a corrupt or missing control file without having to go to a database backup.

Scenario 2: All control files are corrupt or missing

When starting the database we see the following error.

In anticipation of restoring a control we get a list of the control file locations.

Looking at the alert log we see that none of the control files are available.

Since all of the control files are missing we will need to restore them from a backup using RMAN.

Currently none of the control files are mounted so RMAN does not know about the backups or any pre-configured RMAN settings. All settings are at their default.

In order to use the backups we will need to tell RMAN the Database ID. If you do not have or know the database you have two options available.

Option 1: Shutdown the database and re-start it in RMAN

You will again receive an error stating that the control files could not be found. That is OK because in the next step we are going to restore them. Because the RMAN configuration is back to the default we are going to have to pass the entire path along with the file name of the latest control file autobackup to the restore control file command.

After restoring the control files we can see mount the database.

Now that the database has been mounted we can see that our custom RMAN settings are back.

Now it would appear we are ready to open the database.

We just restored the control files. In order to open the database we have to use the RESETLOGS option.

The restored control files are older than the data files. Hence in this case we need to restore the database.

Next we recover the database.

Now that the database has been restored and recovered we can finally open the database, again with the RESETLOGS option.

I think after seeing what is required to restore the control files you will agree that multiplexing the control files is a good thing to do.

* Thanks Dilip Kumar Singh for highlighting the errors in the post and helped me to rectify 🙂