Search This Blog

Wednesday, June 29, 2011

Alter database open fails with ORA-00600 kcratr_nab_less_than_odr

Db: 11.2.0.1

Symptoms

After Power Fail Alter database open fails with

ORA-00600: internal error code, arguments: [kcratr_nab_less_than_odr]

Changes

Power failure

Cause

There was a power failure causing logical corruption in controlfile

Solution


Option a
------------

SQL>Startup mount ;

SQL>Show parameter control_files

Query 1
------------

sql>select a.member,a.group#,b.status from v$logfile a ,v$log b where a.group#=b.group# and b.status='CURRENT'

Note down the name of the redo log

SQL>Shutdown abort ;

Take a OS Level back of the controlfile (This is to ensure we have a backup of current state of controlfile)

SQL>Startup mount ;

SQL>recover database using backup controlfile until cancel ;

Enter location of redo log shown as current in Query 1 when prompted for recovery

Hit Enter

SQL>Alter database open resetlogs ;

Option b
-----------


Recreate the controlfile using the Controlfile recreation script

With database in mount stage

rman target /

rman> spool log to '/tmp/rman.log';

Rman> list backup ;

Rman > exit

Keep this log handy

Go to sqlplus

SQL> Show parameter control_files

Keep this location handy.

SQL>oradebug setmypid

SQL>Alter session set tracefile_identifier='controlfilerecreate' ;

SQL>Alter database backup controlfile to trace ;

SQL>Oradebug tracefile_name ; --> This command will give the path and name of the trace file


Go to this location ,Open this trace file and select the controlfile recreation script with NO Resetlogs option


SQL>Shutdown immediate;


Rename the existing controlfile to _old ---> This is Important as we need to have a backup of existing controlfile since we plan to recreate it

SQL>Startup nomount

Now run the Controlfile recreation script with NO Resetlogs mode

SQL>Alter database open ;

For database version 10g and above

Once database is opened you can recatalog the rman backup information present in the list /tmp/rman.log using

Rman> Catalog start with '' ;


Once the database has been opened using the option a or option b its recommended to take a hot backup of the database.
Same Steps are applicable to Rac if all instance are down with same error.

31 comments:

Mike Warriner said...

THANK YOU!

Spent about 5 hours getting nowhere and this fixed it in the past 5 minutes. wonderful.

Anonymous said...

Thanks .. It helped me.

Satish Amalkar said...

Thanks a lot Mike for Relief :)

Satish Amalkar said...

Thanks A lot for relief info.....

Anonymous said...

Thanks a lot !

Amina said...

THANK YOU!!!!!!!!!!!!
a great help....and great detailed list of the problem!
it worked in the first shot :)
thank you :)

James Brown said...

This might be an old post, but it was wonderful information and helped me fix my Oracle problem. I'm back up and running now. Thank you!

Anonymous said...

thanx

maulik pokiya said...

much needed help. thanx a lot....

maulik pokiya said...

much needed help. thanx a lot...

tut natha said...

one minute solution,..

trims,..

tut natha said...

one minute solution,.. :)

terimakasih,.

tut natha said...

one minute solution,.. :)

terimakasih,.

Anonymous said...

Very good solution. Thanks.

Anonymous said...

THANKS A LOT!!!

Excellent solution!

...saved me hours!!

Anonymous said...

Battled for 2 hours, then found your blog and solved it in 5 minutes. Thank you for your kindness in placing the solution!

Anonymous said...

Great Solution !!!

Saved me days.

Eugene Moses said...

Thank you for the guidance. I could recover the crashed DBA installed in my windows by blindly following the steps

atul mahajan said...

Thanx a lot..

Anonymous said...

dude u are the champ....thanks a ton!!!

Anonymous said...

Thank you!I was clueless about the issue.This post saved my time!

Anonymous said...

THANK YOU!!!!

Anonymous said...

Thank you so much, I am a newbie and your excellent steps helped me get my locally installed database back so I can do my last week of homework before finals!!! A+++ to you!!!

Maxi from Argentina said...

Thankyou very mutch, excelent Blog and solution.

Anonymous said...

very good article really, thanks it reduced my headache.

Anonymous said...

Great!

Anonymous said...

Awesome. Thanks

Anonymous said...

Great! I tried first option. It works fine! Thanks!

Anonymous said...

There is a missing ; in Option a after the following line:
sql>select a.member,a.group#,b.status from v$logfile a ,v$log b where a.group#=b.group# and b.status='CURRENT'

took me some time to realize why the command did not work properly. perhaps there are some other inexperienced users like me out there whom this will help

Anonymous said...

Work like a charm!

Many thanks :)

Anonymous said...

thank you, it`s work, you are save my work