Shulou(Shulou.com)05/31 Report--

Editor to share with you how to deal with ORA-00600 errors in oracle, I believe most people do not know much about it, so share this article for your reference, I hope you can learn a lot after reading this article, let's go to know it!

Due to the abnormal power outage at the customer site, the oracle database cannot be started again. Let's take a look at it remotely.

The database can only be mount and cannot be started.

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

I can't try recover or resetlogs open.

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

Alert log display error

~ Sun Jan 14 19:52:29 2018ALTER DATABASE OPENBeginning crash recovery of 1 threadsparallel recovery started with 3 processes.Started redo scanCompleted redo scanread 2300 KB redo, 0 data blocks need recoveryErrors in file d:\ app\ oracle\ diag\ rdbms\ prjdb\ 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\ prjdb\ incident\ incdir_315209\ prjdb_ora_1644_i315209.trcAborting crash recovery due to error 600Errors in file d:\ app\ oracle\ diag\ rdbms\ prjdb\ prjdb\ trace\ prjdb_ora_1644.trc:ORA-00600: internal error code, arguments: [kcratr_nab_less_than_odr], [1], [4864], [4870], [], [] [], [] Errors in file d:\ app\ oracle\ diag\ rdbms\ prjdb\ 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...~

Combined with the errors ORA-00600: internal error code, arguments: [kcratr_nab_less_than_odr], [1], [29904], [4864], [4870] in ALERT, LGWR failed to write redo log due to abnormal power outage on the server.

The next time you restart the database, you need to do an instance-level recovery, and you can't get this redo information from the online log file, because the last time you lost power, you failed to write the log.

In the error of ORA-00600, the parameters [1], [29904], [4864] and [4870] mean that the instance needs to restore the redo file with sequence 29904 and the log block numbered 4870, but in fact it can only recover to the 4864th log block, so the database cannot be started normally.

So what are we gonna do? First check the checkpoint_change# information in the control file and datafile records.

Data file checkpoint (recorded in control file)

SQL > select file#,checkpoint_change#,last_change# from v$datafile where rownum select checkpoint_change# from vested database China Checkpoint database-664607310

Data file header checkpoint (recorded in data file)

SQL > select file#,checkpoint_change# from v$datafile_header where rownum alter database backup controlfile to trace as'/ tmp/controlfile.trc'

Generate the file to extract the database building script as follows, start the database to the nomount state, and execute the following script.

Note: for a similar restore operation, back up the existing database first. Even if the database can no longer be started. We also need to prevent more serious problems caused by the restore operation.

CREATE CONTROLFILE REUSE DATABASE "PRJDB" NORESETLOGS FORCE LOGGING ARCHIVELOG MAXLOGFILES 16 MAXLOGMEMBERS 3 MAXDATAFILES 200 MAXINSTANCES 8 MAXLOGHISTORY 584LOGFILE GROUP 1'D:\ APP\ ORACLE\ ORADATA\ PRJDB\ REDO01.LOG' SIZE 50m BLOCKSIZE 512, GROUP 2'D:\ APP\ ORACLE\ ORADATA\ PRJDB\ REDO02.LOG' SIZE 50m BLOCKSIZE 512, GROUP 3'D:\ APP\ ORACLE\ ORADATA\ PRJDB\ REDO03.LOG' SIZE 50m BLOCKSIZE 512DATAFILE 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 database status

SQL > select status from vastness instance status-MOUNTED

-11. Try to restart it and see that it needs to be restored (in fact, I know it won't work, but it's like wayward looking at the error report).

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

-12. Restore the database, in fact, did nothing, recover is just a formality, but must go through this process.

SQL > recover database;Media recovery complete.

Database started, successful

SQL > alter database open;Database altered.SQL > select status from vicious instance-- OPEN

Let's look at the checkpoint_change# value again. Let's unify it.

SQL > select file#,checkpoint_change#,last_change# from v$datafile where rownum select checkpoint_change# from vault database * * CheckPOINT changes-664649053SQL > select file#,checkpoint_change# from v$datafile_header where rownum

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.