ORA-00227, ORA-00202: BRING UP INSTANCE AFTER MACHINE CRASH [ID 1072762.6]

Applies to:

Oracle Server - Enterprise Edition - Version 8.0.5.0 and later
z*OBSOLETE: NCR Unix SVR4
***Checked for relevance on 20-May-2011***

Symptoms

The OS crashed and at the time two instances were running.

After correcting the OS issue then you try to start the instance(s)
and upon mounting the database you receive the following errors:

ORA-00227: corrupt block detected in controlfile: (block %s, # blocks %s) 
Cause: A block header corruption or checksum error was detected on 
reading the controlfile. 
Action: Use the CREATE CONTROLFILE or RECOVER DATABASE USING BACKUP 
CONTROLFILE command. 

ORA-00202: control file: 
Cause: This message reports the name of the file involved in other 
messages.
Action: See the associated messages for a description of the problem.

Cause

Control file is corrupt.

Solution

Try to use one of the mirrored copies of the control file. You should have mirrored images of your control file. Oracle recommends at least 2 mirrored copies. Their locations should be listed in your "init.ora" file.

Try using one of these mirrored control files to enable you to bring up the database by copying one of them over the bad one.

 

Or 

Comment out the bad control file from the "control_files" parameter in your 
"init.ora" file and try bringing up the database with one of your mirrored 
control files.

If the above doesn't work, then all control files are corrupt and you must 
create a new control file.

Take a backup of the control files from the last backup and follow steps 
below to recreate a good control file.


===========
OPTION ONE
===========

1. The alert log should identify which control file it is trying to use.

See error ora-202.
> Note this (control1).

2. Make a backup of control files as they are now.

cp controlfiles controlfile.bk

3. Take one of the other control files and copy over the bad control file
noted above.

If this does not work, then proceed to the next step. 


==============
OPTION TWO 
==============

Comment the bad control file from the control_files parameter.

In the init.ora:

control_file = comment out the one listed in alert log from ora-202.

Try starting the instance. 

If you receive the same errors as previously with the name of the control file 
changed to controlfile2, then go to the next option.


=============
OPTION THREE
=============

Issue: 
1. Shutdown immediate. 

2. Startup nomount pfile= .

3. Alter database mount;

4. Alter database backup controlfile to trace;

5. Shutdown.

6. Check for trace file:

Either in user_dump_dest = trace file with latest time stamp; nothing 
or background_dump_dest. 

The file name should be: ora .trc.

This should contain a script to recreate the control file.

7. Save file as control.sql.

8. Edit file to start with: startup nomount.

9. Check Oracle_sid : should be set to correct sid.

10. Svrmgrl> connect internal

11. @control.sql

You will reeceive a message: Stmt processed. 

waiting on svrmgrl prompt

12. Svrmgrl media recovery complete

svrmgrl> select sysdate from dual; june 4, 1999

Database is up and open. 

13. Select on main application table: 

sqlplus : connected ok.


Solution Explanation: 
===================== 

If all the control files are corrupt, then you will need to create a new control 
file to be able to bring up your database.

Search Words: 
============= 

ORA-227
ORA-202