The ORA-00600 kcratr_nab_less_than_odr Solution

The ORA-00600 kcratr_nab_less_than_odr Solution

Today, due to abnormal power outages at the customer site, Oracle database cannot be started again. Let's go up remotely.

1. The database can only be mounted and cannot be started.

SQL> select status from v $ instance;
STATUS
------------
MOUNTED
SQL> ALTER DATABASE OPEN;
ALTER DATABASE OPEN
*
ERROR at line 1:
The ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

2. Failed to try recover and resetlogs open

SQL> recover database;
ORA-00283: recovery session canceled due to errors
ORA-01610: recovery using the backup controlfile option must be done
SQL> ALTER DATABASE OPEN resetlogs;
Alter database open resetlogs
*
ERROR at line 1:
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: 'd: \ APP \ ORACLE \ ORADATA \ PRJDB \ SYSTEM01.DBF'

3. Alert log Display Error

~~~~~~~~~~~~~~~~
Sun Jan 14 19:52:29 2018
ALTER DATABASE OPEN
Beginning crash recovery of 1 threads
Parallel recovery started with 3 processes
......
Started redo scan
Completed redo scan
Read 2300 KB redo, 0 data blocks need recovery
Errors in file d: \ app \ oracle \ diag \ rdbms \ prjdb \ trace \ prjdb_ora_1644.trc (incident = 315209 ):
ORA-00600: internal error code, arguments: [kcratr_nab_less_than_odr], [1], [29904], [4864], [4870], [], [], [], [], [], [], [], [], []
Incident details in: d: \ app \ oracle \ diag \ rdbms \ prjdb \ incident \ incdir_315209 \ prjdb_ora_1644_i315209.trc
Aborting crash recovery due to error 600
Errors in file d: \ app \ oracle \ diag \ rdbms \ prjdb \ trace \ prjdb_ora_1644.trc:
ORA-00600: internal error code, arguments: [kcratr_nab_less_than_odr], [1], [29904], [4864], [4870], [], [], [], [], [], [], [], [], []
Errors in file d: \ app \ oracle \ diag \ rdbms \ prjdb \ trace \ prjdb_ora_1644.trc:
ORA-00600: internal error code, arguments: [kcratr_nab_less_than_odr], [1], [29904], [4864], [4870], [], [], [], [], [], [], [], [], []
ORA-600 signalled during: alter database open...
~~~~~~~~~~~~~~~~~~~

4. combined with the error ORA-00600 in ALERT: internal error code, arguments: [kcratr_nab_less_than_odr], [1], [29904], [4864], [4870], due to abnormal server power failure, this causes LGWR to fail to write the redo log,
The next time you restart the database, you need to perform instance-level recovery and cannot obtain the redo information from the online log file because the log writing failed during the last power failure.

5. so in the ORA-00600 error, the several parameters [1], [29904], [4864], [4870] mean that the instance needs to restore the redo file with sequence 29904, you need to restore the log block numbered 4870, but you can only restore the log block to 4,864th, so the database cannot be started normally.

6. What should we do? Check the checkpoint_change # information recorded in the control file and datafile.

Data File checkpoint (recorded in the control file)

SQL> select file #, checkpoint_change #, last_change # from v $ datafile where rownum <5;
FILE # CHECKPOINT_CHANGE # LAST_CHANGE #
----------------------------------------
1 664629049
2 664629049
3 664629049
4 664629049

System checkpoint (recorded in the control file)

SQL> select checkpoint_change # from v $ database;
CHECKPOINT_CHANGE #
----------------------------------------
664607310

Data File Header checkpoint (recorded in the data file)

SQL> select file #, checkpoint_change # from v $ datafile_header where rownum <5;
FILE # CHECKPOINT_CHANGE #
----------------------------
1 664629049
2 664629049
3 664629049
4 664629049

-7. The preceding three checkpoint_change # Must be consistent (except for read-only and offline tablespaces) before the database can be opened normally. Otherwise, you will need to perform step-by-step processing. When the database is shut down normally, a new checkpoint is generated, and the preceding three checkpoint_change # are written. At the same time, last_change # in the data file also records the checkpoint, that is to say, the three checkpoint_change # And last_change # records the same value.

-8. The error above and the inconsistency of checkpoint_change # can be confirmed, that is, the control file, due to power failure. The controlfile is corrupted (checkpoint_change # inconsistent ).

-9. Because there is no backup, we can only solve this problem by recreating controlfile.

Specifies the path for generating the trace file
SQL & gt; alter database backup controlfile to trace as '/tmp/controlfile. trc ';

The script for generating file extraction database creation is as follows. Start the database to the nomount state and execute the following script.
Note: For similar recovery operations, back up the existing database first. Even if the database cannot be started. We also need to prevent more serious problems caused by recovery operations.

Create controlfile reuse database "PRJDB" NORESETLOGS FORCE LOGGING ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 200
MAXINSTANCES 8
# MAXLOGHISTORY 584
LOGFILE
GROUP 1 'd: \ APP \ ORACLE \ ORADATA \ PRJDB \ REDO01.LOG SIZE 50 m blocksize 512,
GROUP 2 'd: \ APP \ ORACLE \ ORADATA \ PRJDB \ REDO02.LOG SIZE 50 m blocksize 512,
GROUP 3 'd: \ APP \ ORACLE \ ORADATA \ PRJDB \ REDO03.LOG SIZE 50 m blocksize 512
DATAFILE
'D: \ APP \ ORACLE \ ORADATA \ PRJDB \ SYSTEM01.DBF ',
'D: \ APP \ ORACLE \ ORADATA \ PRJDB \ SYSAUX01.DBF ',
'D: \ APP \ ORACLE \ ORADATA \ PRJDB \ UNDOTBS01.DBF ',
'D: \ APP \ ORACLE \ ORADATA \ PRJDB \ USERS01.DBF'
Character set US7ASCII;

-10. Check the database status

SQL> select status from v $ instance;
STATUS
------------
MOUNTED

-11. Try restarting and you will see that it needs to be restored (in fact, I know this cannot be started, but it is like looking at the error as willfully as possible ).

SQL> alter database open;
Alter database open
*
ERROR at line 1:
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: 'd: \ APP \ ORACLE \ ORADATA \ PRJDB \ SYSTEM01.DBF'

-12. In fact, nothing is done to restore the database. recover is a pass, but this process must be followed.

SQL> recover database;
Media recovery complete.

11. The database is started successfully.

SQL> alter database open;
Database altered.
SQL> select status from v $ instance;
STATUS
-----------
OPEN

12. Check the checkpoint_change # value again.
SQL> select file #, checkpoint_change #, last_change # from v $ datafile where rownum <5;
FILE # CHECKPOINT_CHANGE # LAST_CHANGE #
----------------------------------------
1 664649053
2 664649053
3 664649053
4 664649053

SQL> select checkpoint_change # from v $ database;
CHECKPOINT_CHANGE #
------------------
664649053

SQL> select file #, checkpoint_change # from v $ datafile_header where rownum <5;
FILE # CHECKPOINT_CHANGE #
----------------------------
1 664649053
2 664649053
3 664649053
4 664649053

Finally, let's take a look at it again. Backup is really important! Very easy! Databases without backups are not just as simple as streaking! No problem. Shame! Something went wrong. Hurt yourself !!

Refer to: https://www.bkjia.com/Linux/2018-03/151561.htm for how to recreate Control Files

Https://www.bkjia.com/topicnews.aspx? Tid = 12

This article permanently updates link: https://www.bkjia.com/Linux/2018-03/151562.htm