Resolved ORA-00205: Error in identifying Control file, check alert log for more info

By | January 12, 2017

Repair-ORA-0025-Oracle-Data-Block-Corruption copy

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 data database errors could evolve and connection may not be well established. ORA-00205 is one such error which arises due to misallocation of a control file or might have dislocated.

Oracle database contains control file which is a small binary file which records the physical structure of the database. 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.

Guide Lines for Control Files

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:

  • 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.

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.

 Back Up Control Files

Back up of control files 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.

Manage the Size of Control Files

A control file size is the value set for the MAXDATAFILESMAXLOGFILESMAXLOGMEMBERSMAXLOGHISTORY, 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 and you will get error like

 Cause of ORA-00205 error:

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.

Possible Action to fix ORA-00205 error

 Check all control files are online available and also ensure that these are the same files that the system created at cold start time.

Then shutdown your system first which probably is not working now.

Create pfile from spfile by using command:

SQL> create pfile from spfile;

In directory $ORACLE_HOME\database 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 datafile locations; you can rename datafiles 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 and then replace the missing one with one of the others.

downloadnow buynow learn-more-button-orange

Steps to Repair ORA-01578: Oracle Data Block Corruption

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

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.

2

Step 3: Click an object to see its preview.

3

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.

4

Step 5: Give the user name, password and path of the blank database where you want to save the repaired database objects.
5
Step 6: Repairing and restoring various database objects after establishing a connection with blank oracle database.
6