oracle数据库ORA-600 3020错误恢复思路分析

作者：袖梨 2022-06-29

recover database 报ORA-600 3020

代码如下

复制代码

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 ...

使用allow 1 corruption跳3020错误继续恢复

代码如下

复制代码

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 10 (D:＼ORACLE＼PRODUCT＼11.1.0＼DB_1＼ORADATA＼SAMPLE＼LAYOUT_DB.DBF) is offline during full
database recovery and will not 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 240 OF FILE 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: 数据文件 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: 内部错误代码, 参数: [3020], [2], [90586], [8479194], [], [], [], [], [], [], [], []
ORA-10567: Redo is inconsistent with data block (file# 2, block# 90586)
ORA-10564: tablespace SYSAUX
ORA-01110: 数据文件 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 10 (D:＼ORACLE＼PRODUCT＼11.1.0＼DB_1＼ORADATA＼SAMPLE＼LAYOUT_DB.DBF) is offline during full
database recovery and will not 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: 数据文件 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

继续open数据库报ORA-01578错误,数据库无法open

代码如下

复制代码

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 is 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):
ORA-01578: ORACLE 数据块损坏 (文件号 3, 块号 240)
ORA-01110: 数据文件 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: 日志损坏接近块 520 更改 101455257 时间 08/18/2014 08:22:54
ORA-00312: 联机日志 1 线程 1: 'E:＼ORACLE＼ORADATA＼YYGDB＼REDO01.LOG'
ORA-01578: ORACLE 数据块损坏 (文件号 3, 块号 240)
ORA-01110: 数据文件 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: 损坏重做日志块标头
ORA-00353: 日志损坏接近块 520 更改 101455257 时间 08/18/2014 08:22:54
ORA-00312: 联机日志 1 线程 1: 'E:＼ORACLE＼ORADATA＼YYGDB＼REDO01.LOG'
ORA-01578: ORACLE 数据块损坏 (文件号 3, 块号 240)
ORA-01110: 数据文件 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)

由于undo 表空间有坏块，导致数据库open失败,尝试修改undo_management= “MANUAL”，继续启动数据库

代码如下

复制代码

Tue Aug 19 19:50:06 2014
alter database open
Beginning crash recovery of 1 threads
parallel recovery started with 16 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 is 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.
File #3 is offline, but is part of an online tablespace.
data file 3: 'E:＼ORACLE＼ORADATA＼YYGDB＼UNDOTBS01.DBF'
File #10 is offline, but is part of an online tablespace.
data file 10: 'D:＼ORACLE＼PRODUCT＼11.1.0＼DB_1＼ORADATA＼SAMPLE＼LAYOUT_DB.DBF'
File #11 is offline, but is part of an online tablespace.
data file 11: '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 be necessary to add files to these
         tablespaces. That can be done using the SQL statement:

         ALTER TABLESPACE 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 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):
ORA-01578: ORACLE 数据块损坏 (文件号 1, 块号 7065)
ORA-01110: 数据文件 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):
ORA-01578: ORACLE 数据块损坏 (文件号 1, 块号 7065)
ORA-01110: 数据文件 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):
ORA-01578: ORACLE 数据块损坏 (文件号 1, 块号 7065)
ORA-01110: 数据文件 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...

数据库不完全open成功，报了604错误，通过分析undo$，直接使用_offline_rollback_segments屏蔽了status$=5的回滚段,数据库open正常,因为system有大量坏块,幸运的是使用exp导出来几个业务用户的表数据全部OK.

作者：惜分飞
链接：http://www.xifenfei.com/5461.html
联系：手机(13429648788) QQ(107644445)

数据库备份重于一切,别寄希望数据库非常规恢复