Saturday, January 15, 2011

ORA-00214 and Control File Mismatch

One of the many reasons, as why a clean Oracle database shutdown should always be a 'must' ingredient of server reboot SOP, is ORA-214.

The scenario goes like this:

The instance was in nomount stage and was unable to mount the database.
First checked the uptime, and it was found that the server was rebooted some 5 minutes ago, and instance tried to come up automatically but failed to mount the database.
Checked the alert log and found the following error:

ORA-214 signaled during: ALTER DATABASE   MOUNT...

Made a quick searched about ORA-00214 and it was about mismatched control files. It was probably due to the fact that control files got out of sync as the not-so-graceful reboot of the server was done without first cleanly shutting down the database.

This is what doc says:

ORA-00214: control file "string" version string inconsistent with file "string" version string.

Cause: An inconsistent set of control files, datafiles/logfiles, and redo files was used.

Action: Use a consistent set of control files, datafiles/logfiles, and redo log files. That is, all the files must be for the same database and from the same time period.

checked the locations of control files:

SQL> select instance_name,status from v$instance;

INSTANCE_NAME            STATUS
-------------            -------------
ORCL                STARTED


SQL> show parameter control_files

NAME        TYPE    VALUE
----        ----    ----
control_files    string    /u01/app/oracle/oradata/ORCL/control01.ctl, /u01/app/oracle/oradata/ORCL/control02.ctl,/data/oradata/ORCL/control03.ctl

Checked which control files are different. Here having odd numbers of control file is a life saver, because we have the good chance of finding even number of control files match and which one is the awry one.

[oracle@mytest ORCL]$ diff /u01/app/oracle/oradata/ORCL/control01.ctl  /u01/app/oracle/oradata/ORCL/control02.ctl

[oracle@mytest ORCL]$ diff /u01/app/oracle/oradata/ORCL/control01.ctl /data/oradata/ORCL/control03.ctl
Binary files /u01/app/oracle/oradata/ORCL/control01.ctl and /data/oradata/ORCL/control03.ctl differ

[oracle@mytest ORCL]$ diff /u01/app/oracle/oradata/ORCL/control02.ctl /data/oradata/ORCL/control03.ctl
Binary files /u01/app/oracle/oradata/ORCL/control02.ctl and /data/oradata/ORCL/control03.ctl differ


yes, the control03.ctl is the bad one.
Shutdown the instance.
Replace the bad control03.ctl with the good copy from either control01.ctl or control02.ctl, and then start the instance.

It should be all good.

2 comments:

imran said...

Very Informative as usual. What we will have to do to create a new 3rd Control File from the 1st or 2nd???

Fahd Mirza said...

Just shutdown instance, copy the control file to some new location, make an entry for it in the control_files parameter in parameter file and start the instance.