Wednesday, March 17, 2010

The following steps explain what needs to take place in order to recover from a crash where a DB control file gets corrupted:

The likely error thrown in this scenario is as follow:

Fri Mar 7 22:00:46 2008
Hex dump of (file 0, block 1) in trace file /u01/app/oracle/admin/teng/bdump/teng_j001_9438.trc
Corrupt block relative dba: 0x00000001 (file 0, block 1)
Bad check value found during control file header read
Data in bad block:
type: 21 format: 2 rdba: 0x00000001
last change scn: 0x0000.00000000 seq: 0x1 flg: 0x04
spare1: 0x0 spare2: 0x0 spare3: 0x0
consistency value in tail: 0x00001501
check value in block header: 0xf8de
computed block checksum: 0x1501

Problem Description:
====================

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.

Problem Explanation:
====================

Control file is corrupt.

Solution Description:
=====================

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.