Sunday, 6 May 2018

ORA-00600: internal error code [ kcratr_nab_less_than_odr ] in Oracle Database

ORA-00600: internal error code, arguments: [kcratr_nab_less_than_odr], [1],[12], [29106], [29107], [], [], [], [], [], [], []


ORA-00600 is an internal error which can be because of multiple reasons. To know more about this, you can refer to my previous post regarding ORA-600 Oracle Database Error.

ORA-00600 kcratr_nab_less_than_odr

Here in this case, I was unable to open the database since it was failing with ORA-00600 after trying to go ahead of mount stage. See below,

Microsoft Windows [Version 6.1.7601]
Copyright (c) 2009 Microsoft Corporation.  All rights reserved.
C:\Users\B@by>sqlplus
SQL*Plus: Release 11.2.0.1.0 Production on Thu May 3 11:53:46 2018
Copyright (c) 1982, 2010, Oracle.  All rights reserved.
Enter user-name: sys@orcl as sysdba
Enter password:
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select name,open_mode from v$database;
NAME      OPEN_MODE
--------- --------------------
ORCL      MOUNTED
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [kcratr_nab_less_than_odr], [1],
[12], [29106], [29107], [], [], [], [], [], [], []



To resolve the problem here, I tried a few steps but can’t guarantee that the same will work everywhere. Any way you can try the same at your own risk (not on production, for that refer Oracle Support).

1. Shut down the database again and bring it to mount stage to check if everything’s fine till that point.

SQL> shut immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> exit
C:\Users\B@by>set ORACLE_SID=ORCL
C:\Users\B@by>sqlplus
SQL*Plus: Release 11.2.0.1.0 Production on Thu May 3 11:57:37 2018
Copyright (c) 1982, 2010, Oracle.  All rights reserved.
Enter user-name: sys as sysdba
Enter password:
Connected to an idle instance.
SQL> startup;
ORACLE instance started.
Total System Global Area 1252663296 bytes
Fixed Size                  2175328 bytes
Variable Size             889196192 bytes
Database Buffers          352321536 bytes
Redo Buffers                8970240 bytes
Database mounted.
      
      2. Check for the CURRENT Redo log member and perform Shutdown abort. Then again bring the database to mount stage to perform recovery using backup controlfile file. Also you need to manually copy the controlfile as well after you shut down the database in order to keep things backed up.

SQL> show parameter control
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time        integer     7
control_files                        string      C:\APP\GYAN\ORADATA\ORCL\CONTR
                                                 OL01.CTL, C:\APP\GYAN\FLASH_RE
                                                 COVERY_AREA\ORCL\CONTROL02.CTL
control_management_pack_access       string      DIAGNOSTIC+TUNING
SQL>
SQL> select a.member, a.group#, b.status from v$logfile a ,v$log b where a.group
#=b.group# and b.status='CURRENT' ;
 
MEMBER                                       GROUP#          STATUS
------------------------------------------- --------- --------------
C:\APP\GYAN\ORADATA\ORCL\REDO03.LOG              3       CURRENT
         

       3. Shut down the database using SHUTDOWN ABORT command and bring it up to mount stage.

SQL> shutdown abort
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 1252663296 bytes
Fixed Size                  2175328 bytes
Variable Size             889196192 bytes
Database Buffers          352321536 bytes
Redo Buffers                8970240 bytes
Database mounted.


       4. Now perform the recovery using controlfile backup. It will ask for the logfile to recover the database. Provide the complete path for the logfile which was CURRENT.

SQL> recover database using backup controlfile until cancel;
ORA-00279: change 1181684 generated at 04/13/2018 22:53:32 needed for thread 1
ORA-00289: suggestion :
C:\APP\GYAN\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2018_05_03\O1_MF_1_12_%U_.ARC
ORA-00280: change 1181684 for thread 1 is in sequence #12
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
C:\app\gyan\oradata\orcl\redo03.log
Log applied.
Media recovery complete.
SQL>

      5. If it goes well like this till this point, you can now try opening your database using RESETLOGS option.

SQL> alter database open resetlogs;
Database altered.

       6. Check the OPEN_MODE of your database now.

SQL> select name, open_mode from V$database;
NAME      OPEN_MODE
--------- --------------------
ORCL      READ WRITE

I hope this helps !!

1 comment:

  1. This is Good information about this topic..I like it.. wordpress database fix ..Keep it Up!

    ReplyDelete