
After power failure at my customer end, database unable to open with above-mentioned oracle error message. Due to power failure controlfile got logical corruption.
This Problem is caused by Storage Problem of the Database Files. The Subsystem (eg. SAN) crashed while the Database was open. The Database then crashed because the Database Files were not accessible anymore. This caused a lost Write into the Online RedoLogs and/or causing logical corruption in controlfile so Instance Recovery is not possible and raising the ORA-600.
Error log:
2017-05-08 14:11:22.391000 +05:30 Errors in file /u01/app/oracle/diag/rdbms/db1/db1/trace/db1_ora_6324.trc (incident=1968168): ORA-00600: internal error code, arguments: [kcratr_nab_less_than_odr], [1], [160296], [591], [656], [], [], [], [], [], [], [] Incident details in: /u01/app/oracle/diag/rdbms/db1/db1/incident/incdir_1968168/db1_ora_6324_i1968168.trc Aborting crash recovery due to error 600 Errors in file /u01/app/oracle/diag/rdbms/db1/db1/trace/db1_ora_6324.trc: ORA-00600: internal error code, arguments: [kcratr_nab_less_than_odr], [1], [160296], [591], [656], [], [], [], [], [], [], [] Errors in file /u01/app/oracle/diag/rdbms/db1/db1/trace/db1_ora_6324.trc: ORA-00600: internal error code, arguments: [kcratr_nab_less_than_odr], [1], [160296], [591], [656], [], [], [], [], [], [], [] ORA-600 signalled during: ALTER DATABASE OPEN... 2017-05-08 14:11:23.773000 +05:30 Trace dumping is performing id=[cdmp_20170508141123]
Solution:
Retrive details of all the controlfiles as below:
[oracle@db1 ~]$ sqlplus / as sysdba SQL> Show parameter control_files; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ control_files string /u02/oradata/db1/control01.ctl,/u02/oradata/flash_recovery_area/db1/control02.ctl
Findout current redo log member when the power got failed.
SQL> select a.member, a.group#, b.status from v$logfile a ,v$log b where a.group#=b.group# and b.status='CURRENT' ; MEMBE RGROUP# STATUS ---------------------------- ---------- ---------------- /u02/oradata/db1/redo03.log 3 CURRENT
SQL> Shutdown abort; ORACLE instance shut down.
Consider backup of controlfile, so that we will keep the current state of controlfile in the case of any worst.
[oracle@db1 ~]$ cp /u02/oradata/db1/control01.ctl /u02/oradata/db1/control01.ctl_backup [oracle@db1 ~]$ cp /u02/oradata/flash_recovery_area/db1/control02.ctl /u02/oradata/flash_recovery_area/db1/control02.ctl_backup
Startup mount database:
[oracle@db1 ~]$ sqlplus / as sysdba Connected to an idle instance. SQL> Startup mount; ORACLE instance started. Total System Global Area 5010685952 bytes Fixed Size 2212936 bytes Variable Size 3489663928 bytes Database Buffers 1476395008 bytes Redo Buffers 42414080 bytes Database mounted.
Recover database with the help of following command and input current redo log member fetched above:
SQL> recover database using backup controlfile until cancel ; ORA-00279: change 130697634 generated at 05/08/2017 10:59:22 needed for thread1 ORA-00289: suggestion : /u02/oradata/flash_recovery_area/DB1/archivelog/2017_05_08/o1_mf_1_160296_%u_.arc ORA-00280: change 130697634 for thread 1 is in sequence #160296 Specify log: {=suggested | filename | AUTO | CANCEL} /u02/oradata/db1/redo03.log Log applied. Media recovery complete. SQL>
SQL> Alter database open resetlogs ; Database altered.
SQL> select open_mode,name from v$database; OPEN_MODE NAME -------------------- --------- READ WRITE DB1
Cheers!! Stay Tune. 🙂
You’re my hero!! I solved a similar sounding problem following these steps. Other answers I had seen did not tie back to the log files, and did not work. This one did!
Thanks Charles for writing!
Good to know you solved the problem. Stay tune. 🙂
You are my hero2. Thanks a lot
Clap, clap, clap … thank you !!!
Very help full. I recovered my database following this
thank you very much for this fruitful info.
You are a life saver! followed exact steps in similar situation..had no backup of DB–your steps worked like a charm!
Thanks a lot, It really helped me
Thanks .. very well articulated !!
AWESOME!!! It worked! THank you so much for sharing!
Just Perfect! Thank You for this info!