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. 🙂