Power outage ORA-600

1: a power outage occurred on the weekend. The newly configured Intranet Test Database active recovery uard environment is automatically disabled. The following error is reported after the master database is started this morning:

[oracle@db1 ~]$ sqlplus /nologSQL*Plus: Release 11.2.0.1.0 Production on Mon Jun 17 17:00:57 2013Copyright (c) 1982, 2009, Oracle.  All rights reserved.SQL> conn /as sysdbaConnected to an idle instance.SQL> startupORACLE instance started.Total System Global Area 1.3362E+10 bytesFixed Size                  2217952 bytesVariable Size            6777997344 bytesDatabase Buffers         6576668672 bytesRedo Buffers                4960256 bytesDatabase mounted.ORA-00600: internal error code, arguments: [kcratr_nab_less_than_odr], [1],[6], [301353], [301354], [], [], [], [], [], [], []

2: first, recover the database and then try to pull the database. The problem persists.

SQL> recover database;Media recovery complete.SQL> alter database open;alter database open*ERROR at line 1:ORA-00600: internal error code, arguments: [kcratr_nab_less_than_odr], [1],[6], [301353], [301354], [], [], [], [], [], [], []

3: view the alert Log and related trace files to determine whether problems may occur in the control file.

[oracle@db1 ~]$ tail -f alert_db.logErrors in file /u01/app/oracle/diag/rdbms/db1/db/trace/db_ora_32462.trc:ORA-00600: internal error code, arguments: [kcratr_nab_less_than_odr], [1], [6], [301353], [301354], [], [], [], [], [], [], []Errors in file /u01/app/oracle/diag/rdbms/db1/db/trace/db_ora_32462.trc:ORA-00600: internal error code, arguments: [kcratr_nab_less_than_odr], [1], [6], [301353], [301354], [], [], [], [], [], [], []ORA-600 signalled during: ALTER DATABASE OPEN...Trace dumping is performing id=[cdmp_20130617170117]Mon Jun 17 17:02:13 2013Sweep [inc][24153]: completedSweep [inc2][24153]: completedMon Jun 17 17:02:38 2013ALTER DATABASE RECOVER  databaseMedia Recovery Start started logmerger processParallel Media Recovery started with 4 slavesMon Jun 17 17:02:38 2013Recovery of Online Redo Log: Thread 1 Group 3 Seq 6 Reading mem 0  Mem# 0: /u01/app/oracle/oradata/DB/onlinelog/o1_mf_3_8vpmjgoq_.log  Mem# 1: /u01/app/oracle/flash_recovery_area/DB/onlinelog/o1_mf_3_8vpmkm9x_.logMedia Recovery Complete (db)Completed: ALTER DATABASE RECOVER  databaseMon Jun 17 17:02:54 2013alter database openBeginning crash recovery of 1 threads parallel recovery started with 3 processesStarted redo scanCompleted redo scan read 152 KB redo, 0 data blocks need recoveryErrors in file /u01/app/oracle/diag/rdbms/db1/db/trace/db_ora_32462.trc  (incident=24154):ORA-00600: internal error code, arguments: [kcratr_nab_less_than_odr], [1], [6], [301353], [301354], [], [], [], [], [], [], []Incident details in: /u01/app/oracle/diag/rdbms/db1/db/incident/incdir_24154/db_ora_32462_i24154.trcMon Jun 17 17:02:55 2013Trace dumping is performing id=[cdmp_20130617170255]Aborting crash recovery due to error 600Errors in file /u01/app/oracle/diag/rdbms/db1/db/trace/db_ora_32462.trc:ORA-00600: internal error code, arguments: [kcratr_nab_less_than_odr], [1], [6], [301353], [301354], [], [], [], [], [], [], []Errors in file /u01/app/oracle/diag/rdbms/db1/db/trace/db_ora_32462.trc:ORA-00600: internal error code, arguments: [kcratr_nab_less_than_odr], [1], [6], [301353], [301354], [], [], [], [], [], [], []ORA-600 signalled during: alter database open...Mon Jun 17 17:03:13 2013Sweep [inc][24154]: completedSweep [inc2][24154]: completed

4: The trace control script is generated to restore the control file.

SQL> select open_mode from v$database;OPEN_MODE--------------------MOUNTEDSQL> alter database backup controlfile to trace as '/tmp/1.ctl';Database altered.[oracle@db1 ~]$ cat /tmp/1.ctlCREATE CONTROLFILE REUSE DATABASE "DB" NORESETLOGS FORCE LOGGING ARCHIVELOG    MAXLOGFILES 16    MAXLOGMEMBERS 3    MAXDATAFILES 100    MAXINSTANCES 8    MAXLOGHISTORY 292LOGFILE  GROUP 1 (    '/u01/app/oracle/oradata/DB/onlinelog/o1_mf_1_8vpmdkl9_.log',    '/u01/app/oracle/flash_recovery_area/DB/onlinelog/o1_mf_1_8vpmdryt_.log'  ) SIZE 512M BLOCKSIZE 512,  GROUP 2 (    '/u01/app/oracle/oradata/DB/onlinelog/o1_mf_2_8vpmfqmw_.log',    '/u01/app/oracle/flash_recovery_area/DB/onlinelog/o1_mf_2_8vpmhk2z_.log'  ) SIZE 512M BLOCKSIZE 512,  GROUP 3 (    '/u01/app/oracle/oradata/DB/onlinelog/o1_mf_3_8vpmjgoq_.log',    '/u01/app/oracle/flash_recovery_area/DB/onlinelog/o1_mf_3_8vpmkm9x_.log'  ) SIZE 512M BLOCKSIZE 512--STANDBY LOGFILE--   GROUP 4 (--     '/u01/app/oracle/oradata/DB1/onlinelog/o1_mf_4_8vpq6nvy_.log',--     '/u01/app/oracle/flash_recovery_area/DB1/onlinelog/o1_mf_4_8vpq7wk8_.log'--   ) SIZE 512M BLOCKSIZE 512,--   GROUP 5 (--     '/u01/app/oracle/oradata/DB1/onlinelog/o1_mf_5_8vpqbh6s_.log',--     '/u01/app/oracle/flash_recovery_area/DB1/onlinelog/o1_mf_5_8vpqcmbj_.log'--   ) SIZE 512M BLOCKSIZE 512,--   GROUP 6 (--     '/u01/app/oracle/oradata/DB1/onlinelog/o1_mf_6_8vpqf3rz_.log',--     '/u01/app/oracle/flash_recovery_area/DB1/onlinelog/o1_mf_6_8vpqfv5w_.log'--   ) SIZE 512M BLOCKSIZE 512,--   GROUP 7 (--     '/u01/app/oracle/oradata/DB1/onlinelog/o1_mf_7_8vpqgw0j_.log',--     '/u01/app/oracle/flash_recovery_area/DB1/onlinelog/o1_mf_7_8vpqhcql_.log'--   ) SIZE 512M BLOCKSIZE 512DATAFILE  '/u01/app/oracle/oradata/DB/datafile/o1_mf_system_8vpm8hf3_.dbf',  '/u01/app/oracle/oradata/DB/datafile/o1_mf_sysaux_8vpm8hjq_.dbf',  '/u01/app/oracle/oradata/DB/datafile/o1_mf_undotbs1_8vpm8hl4_.dbf',  '/u01/app/oracle/oradata/DB/datafile/o1_mf_users_8vpm8hn1_.dbf'CHARACTER SET ZHS16GBK;SQL> shutdown immediateORA-01109: database not openDatabase dismounted.ORACLE instance shut down.SQL> startup nomountORACLE instance started.Total System Global Area 1.3362E+10 bytesFixed Size                  2217952 bytesVariable Size            6777997344 bytesDatabase Buffers         6576668672 bytesRedo Buffers                4960256 bytesSQL> @/tmp/1.ctl;Control file created.

5. After recreating the control file, recover the database and open the database successfully.

SQL> alter database open;alter database open*ERROR at line 1:ORA-01113: file 1 needs media recoveryORA-01110: data file 1:'/u01/app/oracle/oradata/DB/datafile/o1_mf_system_8vpm8hf3_.dbf'SQL> recover database;Media recovery complete.SQL> alter database open;Database altered.SQL> select open_mode,database_role from v$database;  OPEN_MODE            DATABASE_ROLE-------------------- ----------------READ WRITE           PRIMARY

6: add temporary tablespace data files by observing alert logs

[oracle@db1 ~]$ tail -f alert_db.logErrors in file /u01/app/oracle/diag/rdbms/db1/db/trace/db_m001_381.trc:ORA-25153: Temporary Tablespace is EmptyMon Jun 17 17:17:25 2013Errors in file /u01/app/oracle/diag/rdbms/db1/db/trace/db_j007_400.trc:ORA-25153: Temporary Tablespace is EmptyErrors in file /u01/app/oracle/diag/rdbms/db1/db/trace/db_j007_400.trc:ORA-12012: error on auto execute of job 12696ORA-25153: Temporary Tablespace is EmptyORA-06512: at "DBSNMP.BSLN_INTERNAL", line 2073ORA-06512: at line 1Mon Jun 17 17:18:20 2013alter tablespace temp add tempfileCompleted: alter tablespace temp add tempfileSQL> alter tablespace temp add tempfile;Tablespace altered.

7: Observe physical database and find the following problems

[root@db2 ~]# su - oracle[oracle@db2 ~]$ sqlplus /nologSQL*Plus: Release 11.2.0.1.0 Production on Mon Jun 17 17:18:50 2013Copyright (c) 1982, 2009, Oracle.  All rights reserved.SQL> conn /as sysdbaConnected to an idle instance.SQL> startupORACLE instance started.Total System Global Area 1.3362E+10 bytesFixed Size                  2217952 bytesVariable Size            6777997344 bytesDatabase Buffers         6576668672 bytesRedo Buffers                4960256 bytesDatabase mounted.ORA-10458: standby database requires recoveryORA-01196: file 1 is inconsistent due to a failed media recovery sessionORA-01110: data file 1:'/u01/app/oracle/oradata/DB2/datafile/o1_mf_system_08oc7c38_.dbf'SQL> alter database open read only;alter database open read only*ERROR at line 1:ORA-10458: standby database requires recoveryORA-01196: file 1 is inconsistent due to a failed media recovery sessionORA-01110: data file 1:'/u01/app/oracle/oradata/DB2/datafile/o1_mf_system_08oc7c38_.dbf'

8: Close physical standby, start the hot backup of the system tablespace on the master database, copy the corresponding files to the specified location in the slave database, and re-Synchronize normally.

SQL> shutdown immediateORA-01109: database not openDatabase dismounted.ORACLE instance shut down.SQL> alter tablespace system begin backup;Tablespace altered.[oracle@db1 datafile]$ pwd/u01/app/oracle/oradata/DB/datafile[oracle@db1 datafile]$ scp o1_mf_system_8vpm8hf3_.dbf root@db2:/tmpSQL> alter tablespace system end backup;Tablespace altered.

This article is from the "Bo Yue" blog and will not be reproduced!