Analysis of ORA-600 3020 Error recovery in Oracle database

Recover Database Report ORA-600 3020

The code is as follows

Copy Code

Recovery of Online Redo log:thread 1 Group 2 Seq 5729 Reading mem 0 mem# 0:e:\oracle\oradata\yygdb\redo02.log Tue Aug 19 19:37:29 2014 Errors in File D:\oracle\diag\rdbms\yygdb\yygdb\trace\yygdb_pr0s_4296.trc (incident=39403): Ora-00600:internal error code, arguments: [3020], [3], [240], [12583152], [], [], [], [], [], [], [], [] Ora-10567:redo is inconsistent with data block (file# 3, block# 240) Ora-10564:tablespace UNDOTBS1 Ora-01110:data file 3: ' E:\ORACLE\ORADATA\YYGDB\UNDOTBS01.DBF ' Ora-10560:block type ' ktu SMU HEADER block ' Incident Details IN:D:\ORACLE\DIAG\RDBMS\YYGDB\YYGDB\INCIDENT\INCDIR_39403\YYGDB_PR0S_4296_I39403.TRC Ora-00600:internal error code, arguments: [3020], [2], [90586], [8479194], [], [], [], [], [], [], [], [] Ora-10567:redo is inconsistent with data block (file# 2, block# 90586) Ora-10564:tablespace Sysaux Ora-01110:data file 2: ' E:\ORACLE\ORADATA\YYGDB\SYSAUX01.DBF ' Ora-10561:block type ' TRANSACTION MANAGED data block ', data object# 6087 Errors in File D:\oracle\diag\rdbms\yygdb\yygdb\trace\yygdb_ora_12460.trc (incident=39147): Ora-00600:internal error code, arguments: [3020], [3], [240], [12583152], [], [], [], [], [], [], [], [] Ora-10567:redo is inconsistent with data block (file# 3, block# 240) Ora-10564:tablespace UNDOTBS1 Ora-01110:data file 3: ' E:\ORACLE\ORADATA\YYGD Incident Details IN:D:\ORACLE\DIAG\RDBMS\YYGDB\YYGDB\INCIDENT\INCDIR_39147\YYGDB_ORA_12460_I39147.TRC Tue Aug 19 19:37:31 2014 Trace dumping is performing id=[cdmp_20140819193731] Tue Aug 19 19:37:32 2014 Recovery Slave pr0s previously exited with an exception Shutting down recovery slaves due to error 10877 Media Recovery failed with error 10877 ORA-283 signalled during:alter database RECOVER database ...

Use allow 1 corruption hop 3020 Error Resume

The code is as follows

Copy Code

Tue Aug 19 19:38:53 2014 ALTER database RECOVER database allow 1 corruption Media Recovery Start Fast Parallel Media Recovery enabled ALLOW corruption option must use serial recovery Warning:datafile (D:\ORACLE\PRODUCT\11.1.0\DB_1\ORADATA\SAMPLE\LAYOUT_DB. DBF) is offline during full Database recovery and won't be recovered Recovery of Online Redo log:thread 1 Group 2 Seq 5729 Reading mem 0 mem# 0:e:\oracle\oradata\yygdb\redo02.log Corrupting block 3 and continuing RECOVERY Errors in file d:\oracle\diag\rdbms\yygdb\yygdb\trace\yygdb_ora_12460.trc: Ora-10567:redo is inconsistent with data block (file# 3, block# 240) Ora-10564:tablespace UNDOTBS1 ORA-01110: Data file 3: ' E:\ORACLE\ORADATA\YYGDB\UNDOTBS01.DBF ' Ora-10560:block type ' ktu SMU HEADER block ' Errors in File D:\oracle\diag\rdbms\yygdb\yygdb\trace\yygdb_ora_12460.trc (incident=39148): ORA-00600: Internal error code, parameters: [3020], [2], [90586], [8479194], [], [], [], [], [], [], [], [] Ora-10567:redo is inconsistent with data block (file# 2, block# 90586) Ora-10564:tablespace Sysaux ORA-01110: Data file 2: ' E:\ORACLE\ORADATA\YYGDB\SYSAUX01.DBF ' Ora-10561:block type ' TRANSACTION MANAGED data block ', data object# 6087 Incident Details IN:D:\ORACLE\DIAG\RDBMS\YYGDB\YYGDB\INCIDENT\INCDIR_39148\YYGDB_ORA_12460_I39148.TRC Media Recovery failed with error 600 ORA-283 signalled During:alter database RECOVER database allow 1 corruption ... Tue Aug 19 19:38:56 2014 Trace dumping is performing id=[cdmp_20140819193856] Tue Aug 19 19:38:59 2014 Sweep incident[39148]: Completed Tue Aug 19 19:39:05 2014 ALTER database RECOVER database allow 1 corruption Media Recovery Start Fast Parallel Media Recovery enabled ALLOW corruption option must use serial recovery Warning:datafile (D:\ORACLE\PRODUCT\11.1.0\DB_1\ORADATA\SAMPLE\LAYOUT_DB. DBF) is offline during full Database recovery and won't be recovered Recovery of Online Redo log:thread 1 Group 2 Seq 5729 Reading mem 0 mem# 0:e:\oracle\oradata\yygdb\redo02.log Corrupting block 90586 of FILE 2 and continuing RECOVERY Errors in file d:\oracle\diag\rdbms\yygdb\yygdb\trace\yygdb_ora_12460.trc: Ora-10567:redo is inconsistent with data block (file# 2, block# 90586) Ora-10564:tablespace Sysaux ORA-01110: Data file 2: ' E:\ORACLE\ORADATA\YYGDB\SYSAUX01.DBF ' Ora-10561:block type ' TRANSACTION MANAGED data block ', data object# 6087 Completed:alter database RECOVER database allow 1 corruption

Continue open Database ORA-01578 error, database cannot open

The code is as follows

Copy Code

Thread 1 opened at log sequence 5730 Current log# 3 seq# 5730 mem# 0:e:\oracle\oradata\yygdb\redo03.log Successful open of Redo thread 1 MTTR advisory is disabled because fast_start_mttr_target are not set Tue Aug 19 19:39:34 2014 Smon:enabling Cache Recovery Errors in File D:\oracle\diag\rdbms\yygdb\yygdb\trace\yygdb_ora_12460.trc (incident=39149): Damage to Ora-01578:oracle data block (file number 3, block number 240) ORA-01110: Data file 3: ' E:\ORACLE\ORADATA\YYGDB\UNDOTBS01.DBF ' Incident Details IN:D:\ORACLE\DIAG\RDBMS\YYGDB\YYGDB\INCIDENT\INCDIR_39149\YYGDB_ORA_12460_I39149.TRC Errors in File D:\oracle\diag\rdbms\yygdb\yygdb\trace\yygdb_ora_12460.trc (incident=39150): ORA-00353: Log corruption close to block 520 change 101455257 time 08/18/2014 08:22:54 ORA-00312: Online log 1 thread 1: ' E:\ORACLE\ORADATA\YYGDB\REDO01.LOG ' Damage to Ora-01578:oracle data block (file number 3, block number 240) ORA-01110: Data file 3: ' E:\ORACLE\ORADATA\YYGDB\UNDOTBS01.DBF ' Incident Details IN:D:\ORACLE\DIAG\RDBMS\YYGDB\YYGDB\INCIDENT\INCDIR_39150\YYGDB_ORA_12460_I39150.TRC Errors in file d:\oracle\diag\rdbms\yygdb\yygdb\incident\incdir_39149\yygdb_ora_12460_i39149.trc: ORA-00354: Corrupted redo Log block header ORA-00353: Log corruption close to block 520 change 101455257 time 08/18/2014 08:22:54 ORA-00312: Online log 1 thread 1: ' E:\ORACLE\ORADATA\YYGDB\REDO01.LOG ' Damage to Ora-01578:oracle data block (file number 3, block number 240) ORA-01110: Data file 3: ' E:\ORACLE\ORADATA\YYGDB\UNDOTBS01.DBF ' Errors in File D:\oracle\diag\rdbms\yygdb\yygdb\trace\yygdb_ora_12460.trc (incident=39151): Error 1578 happened during DB Open, shutting down database USER (ospid:12460): Terminating the instance due to error 1578 Tue Aug 19 19:39:41 2014 Errors in File D:\oracle\diag\rdbms\yygdb\yygdb\trace\yygdb_dbw3_18508.trc (incident=38659): Ora-01578:oracle data Block corrupted (file #, Block #) Tue Aug 19 19:39:41 2014 Errors in File D:\oracle\diag\rdbms\yygdb\yygdb\trace\yygdb_dbw5_12160.trc (incident=38675): Ora-01578:oracle data Block corrupted (file #, Block #) Tue Aug 19 19:39:42 2014 Instance terminated by USER, PID = 12460 ORA-1092 signalled during:alter database open ... ORA-1092:OPIODR aborting process unknown ospid (5084_12460)

The database open failed because of a bad block in the undo table space, trying to modify undo_management= "MANUAL" to continue starting the database

The code is as follows

Copy Code

Tue Aug 19 19:50:06 2014 ALTER DATABASE Open Beginning crash recovery of 1 threads Parallel recovery started with processes Started Redo scan Completed Redo Scan 3 redo blocks read, 0 data blocks need recovery Started Redo application at Thread 1:logseq 5731, Block 2, SCN 101497289 Recovery of Online Redo log:thread 1 Group 1 Seq 5731 Reading Mem 0 mem# 0:e:\oracle\oradata\yygdb\redo01.log Completed Redo Application of 0.00MB Completed Crash Recovery at Thread 1:logseq 5731, Block 5, SCN 101517294 0 data blocks read, 0 data blocks written, 3 redo blocks Read Tue Aug 19 19:50:08 2014 Thread 1 advanced to log sequence 5732 (thread open) Thread 1 opened at log sequence 5732 Current log# 2 seq# 5732 mem# 0:e:\oracle\oradata\yygdb\redo02.log Successful open of Redo thread 1 MTTR advisory is disabled because fast_start_mttr_target are not set Tue Aug 19 19:50:08 2014 Smon:enabling Cache Recovery Dictionary Check Beginning Tablespace ' TEMP ' #3 found in data dictionary, But not in the controlfile. adding to Controlfile. The File #3 is offline, but are part of the online tablespace. Data file 3: ' E:\ORACLE\ORADATA\YYGDB\UNDOTBS01.DBF ' The File #10 is offline, but are part of the online tablespace. Data file: ' D:\ORACLE\PRODUCT\11.1.0\DB_1\ORADATA\SAMPLE\LAYOUT_DB. DBF ' The File #11 is offline, but are part of the online tablespace. Data file: ' D:\ORACLE\PRODUCT\11.1.0\DB_1\ORADATA\SAMPLE\LAYOUT.DBF ' Dictionary Check Complete Verifying file header compatibility for 11g tablespace encryption. Verifying 11g file Header compatibility for tablespace encryption completed smon:enabling TX Recovery ********************************************************************* Warning:the following temporary tablespaces contain no files. This condition can occur when a backup controlfile has been restored. It may is necessary to add files to Tablespaces. That can is done using the SQL statement: ALTER tablespace <tablespace_name> ADD tempfile Alternatively, if these temporary tablespaces are no longer Needed, then they can be dropped. Empty Temporary Tablespace:temp ********************************************************************* Database Characterset is ZHS16GBK Opening with internal Resource Manager plan:on 4 X 8 NUMA System ********************************************************** Warning:files may exists in Db_recovery_file_dest That are is not known to the database. Use the RMAN command CATALOG RECOVERY area to re-catalog any such files. If files cannot be cataloged, then manually delete them Using OS command. One of the following events caused this: 1. A backup Controlfile was restored. 2. A standby Controlfile was restored. 3. The controlfile was re-created. 4. Db_recovery_file_dest had previously been enabled and Then disabled. ********************************************************** Hex Dump of (file 1, block 7065) in trace file D:\oracle\diag\rdbms\yygdb\yygdb\trace\yygdb_ora_14296.trc Corrupt block relative dba:0x00401b99 (file 1, block 7065) Fractured block found during buffer read Data in bad block: Type:6 Format:2 rdba:0x00401b99 Last Change scn:0x0000.060c1f83 seq:0x1 flg:0x06 spare1:0x0 spare2:0x0 spare3:0x0 Consistency value in tail:0xac3b0601 Check value in block Header:0x2e13 Computed Block CHECKSUM:0XA4AC Reread of rdba:0x00401b99 (file 1, block 7065) found same corrupted data Errors in File D:\oracle\diag\rdbms\yygdb\yygdb\trace\yygdb_ora_14296.trc (incident=42814): Damage to Ora-01578:oracle data block (file Number 1, block number 7065) ORA-01110: Data file 1: ' E:\ORACLE\ORADATA\YYGDB\SYSTEM01.DBF ' Incident Details IN:D:\ORACLE\DIAG\RDBMS\YYGDB\YYGDB\INCIDENT\INCDIR_42814\YYGDB_ORA_14296_I42814.TRC Errors in File D:\oracle\diag\rdbms\yygdb\yygdb\trace\yygdb_ora_14296.trc (incident=42815): Damage to Ora-01578:oracle data block (file Number 1, block number 7065) ORA-01110: Data file 1: ' E:\ORACLE\ORADATA\YYGDB\SYSTEM01.DBF ' Incident Details IN:D:\ORACLE\DIAG\RDBMS\YYGDB\YYGDB\INCIDENT\INCDIR_42815\YYGDB_ORA_14296_I42815.TRC Tue Aug 19 19:50:12 2014 Trace dumping is performing id=[cdmp_20140819195012] Tue Aug 19 19:50:12 2014 Sweep incident[42814]: Completed Hex Dump of (file 1, block 7065) in trace file D:\oracle\diag\rdbms\yygdb\yygdb\incident\incdir_42814\yygdb_m000_11592_i42814_a.trc Corrupt block relative dba:0x00401b99 (file 1, block 7065) Fractured block found during validation Data in bad block: Type:6 Format:2 rdba:0x00401b99 Last Change scn:0x0000.060c1f83 seq:0x1 flg:0x06 spare1:0x0 spare2:0x0 spare3:0x0 Consistency value in tail:0xac3b0601 Check value in block Header:0x2e13 Computed Block CHECKSUM:0XA4AC Reread of blocknum=7065, file=e:\oracle\oradata\yygdb\system01.dbf. Found same corrupt data Reread of blocknum=7065, file=e:\oracle\oradata\yygdb\system01.dbf. Found same corrupt data Reread of blocknum=7065, file=e:\oracle\oradata\yygdb\system01.dbf. Found same corrupt data Reread of blocknum=7065, file=e:\oracle\oradata\yygdb\system01.dbf. Found same corrupt data Reread of blocknum=7065, file=e:\oracle\oradata\yygdb\system01.dbf. Found same corrupt data Hex Dump of (file 1, block 7065) in trace file D:\oracle\diag\rdbms\yygdb\yygdb\trace\yygdb_ora_14296.trc Corrupt block relative dba:0x00401b99 (file 1, block 7065) Fractured block found during buffer read Data in bad block: Type:6 Format:2 rdba:0x00401b99 Last Change scn:0x0000.060c1f83 seq:0x1 flg:0x06 spare1:0x0 spare2:0x0 spare3:0x0 Consistency value in tail:0xac3b0601 Check value in block Header:0x2e13 Computed Block CHECKSUM:0XA4AC Reread of rdba:0x00401b99 (file 1, block 7065) found same corrupted data Corrupt block Found TSN = 0, Tsname = SYSTEM RFN = 1, BLK = 7065, rdba = 4201369 Objn = 1164, OBJD = 1164, OBJECT = SYS_FBA_BARRIERSCN, subobject = SEGMENT OWNER = SYS, SEGMENT TYPE = Table SEGMENT Errors in File D:\oracle\diag\rdbms\yygdb\yygdb\trace\yygdb_ora_14296.trc (incident=42816): Damage to Ora-01578:oracle data block (file Number 1, block number 7065) ORA-01110: Data file 1: ' E:\ORACLE\ORADATA\YYGDB\SYSTEM01.DBF ' Incident Details IN:D:\ORACLE\DIAG\RDBMS\YYGDB\YYGDB\INCIDENT\INCDIR_42816\YYGDB_ORA_14296_I42816.TRC Trace dumping is performing id=[cdmp_20140819195014] Errors in File D:\oracle\diag\rdbms\yygdb\yygdb\trace\yygdb_ora_14296.trc (incident=42817): Starting background Process Fbda Tue Aug 19 19:50:18 2014 Fbda started with pid=86, OS id=17700 Replication_dependency_tracking turned off (no async multimaster replication found) Errors in File D:\oracle\diag\rdbms\yygdb\yygdb\trace\yygdb_fbda_17700.trc (incident=42910): Ora-01578:oracle data Block Corrupted (file # 1, Block # 7065) Ora-01110:data file 1: ' E:\ORACLE\ORADATA\YYGDB\SYSTEM01.DBF ' Trace dumping is performing id=[cdmp_20140819195018] Errors in File D:\oracle\diag\rdbms\yygdb\yygdb\trace\yygdb_fbda_17700.trc (incident=42911): Ora-01578:oracle data Block Corrupted (file # 1, Block # 7065) Ora-01110:data file 1: ' E:\ORACLE\ORADATA\YYGDB\SYSTEM01.DBF ' Logstdby:validating Controlfile with logical metadata Logstdby:validation Complete ORA-604 signalled during:alter database open ...

Database incomplete open successful, reported 604 errors, through the analysis of undo$, directly using _offline_rollback_segments shielded status$=5 rollback segment, the database open normal, because system has a lot of bad blocks, Luckily, use exp to export a few business users to the table data all OK.

Author: Xi-FEI
Link:http://www.xifenfei.com/5461.html
Contact: Mobile Phone (13429648788) QQ (107644445)

Database backup is more than everything, don't expect database to be not normal recovery