记录一起ORA-600 3020故障恢复

xifenfei

recover database 报ORA-600 3020

1. Recovery of Online Redo Log: Thread 1 Group 2 Seq 5729 Reading mem 0
   
2.   Mem# 0: E:\ORACLE\ORADATA\YYGDB\REDO02.LOG
   
3. Tue Aug 19 19:37:29 2014
   
4. Errors in file d:\oracle\diag\rdbms\yygdb\yygdb\trace\yygdb_pr0s_4296.trc  (incident=39403):
   
5. ORA-00600: internal error code, arguments: [3020], [3], [240], [12583152], [], [], [], [], [], [], [], []
   
6. ORA-10567: Redo is inconsistent with data block (file# 3, block# 240)
   
7. ORA-10564: tablespace UNDOTBS1
   
8. ORA-01110: data file 3: 'E:\ORACLE\ORADATA\YYGDB\UNDOTBS01.DBF'
   
9. ORA-10560: block type 'KTU SMU HEADER BLOCK'
   
10. Incident details in: d:\oracle\diag\rdbms\yygdb\yygdb\incident\incdir_39403\yygdb_pr0s_4296_i39403.trc
    
11. ORA-00600: internal error code, arguments: [3020], [2], [90586], [8479194], [], [], [], [], [], [], [], []
    
12. ORA-10567: Redo is inconsistent with data block (file# 2, block# 90586)
    
13. ORA-10564: tablespace SYSAUX
    
14. ORA-01110: data file 2: 'E:\ORACLE\ORADATA\YYGDB\SYSAUX01.DBF'
    
15. ORA-10561: block type 'TRANSACTION MANAGED DATA BLOCK', data object# 6087
    
16. Errors in file d:\oracle\diag\rdbms\yygdb\yygdb\trace\yygdb_ora_12460.trc  (incident=39147):
    
17. ORA-00600: internal error code, arguments: [3020], [3], [240], [12583152], [], [], [], [], [], [], [], []
    
18. ORA-10567: Redo is inconsistent with data block (file# 3, block# 240)
    
19. ORA-10564: tablespace UNDOTBS1
    
20. ORA-01110: data file 3: 'E:\ORACLE\ORADATA\YYGD
    
21. Incident details in: d:\oracle\diag\rdbms\yygdb\yygdb\incident\incdir_39147\yygdb_ora_12460_i39147.trc
    
22. Tue Aug 19 19:37:31 2014
    
23. Trace dumping is performing id=[cdmp_20140819193731]
    
24. Tue Aug 19 19:37:32 2014
    
25. Recovery Slave PR0S previously exited with an exception
    
26. Shutting down recovery slaves due to error 10877
    
27. Media Recovery failed with error 10877
    
28. ORA-283 signalled during: ALTER DATABASE RECOVER  database  ...

复制代码

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

1. Tue Aug 19 19:38:53 2014
   
2. ALTER DATABASE RECOVER  database allow 1 corruption  
   
3. Media Recovery Start
   
4. Fast Parallel Media Recovery enabled
   
5. ALLOW CORRUPTION option must use serial recovery
   
6. Warning: Datafile 10 (D:\ORACLE\PRODUCT\11.1.0\DB_1\ORADATA\SAMPLE\LAYOUT_DB.DBF) is offline during full
   
7. database recovery and will not be recovered
   
8. Recovery of Online Redo Log: Thread 1 Group 2 Seq 5729 Reading mem 0
   
9.   Mem# 0: E:\ORACLE\ORADATA\YYGDB\REDO02.LOG
   
10. CORRUPTING BLOCK 240 OF FILE 3 AND CONTINUING RECOVERY
    
11. Errors in file d:\oracle\diag\rdbms\yygdb\yygdb\trace\yygdb_ora_12460.trc:
    
12. ORA-10567: Redo is inconsistent with data block (file# 3, block# 240)
    
13. ORA-10564: tablespace UNDOTBS1
    
14. ORA-01110: 数据文件 3: 'E:\ORACLE\ORADATA\YYGDB\UNDOTBS01.DBF'
    
15. ORA-10560: block type 'KTU SMU HEADER BLOCK'
    
16. Errors in file d:\oracle\diag\rdbms\yygdb\yygdb\trace\yygdb_ora_12460.trc  (incident=39148):
    
17. ORA-00600: 内部错误代码, 参数: [3020], [2], [90586], [8479194], [], [], [], [], [], [], [], []
    
18. ORA-10567: Redo is inconsistent with data block (file# 2, block# 90586)
    
19. ORA-10564: tablespace SYSAUX
    
20. ORA-01110: 数据文件 2: 'E:\ORACLE\ORADATA\YYGDB\SYSAUX01.DBF'
    
21. ORA-10561: block type 'TRANSACTION MANAGED DATA BLOCK', data object# 6087
    
22. Incident details in: d:\oracle\diag\rdbms\yygdb\yygdb\incident\incdir_39148\yygdb_ora_12460_i39148.trc
    
23. Media Recovery failed with error 600
    
24. ORA-283 signalled during: ALTER DATABASE RECOVER  database allow 1 corruption  ...
    
25. Tue Aug 19 19:38:56 2014
    
26. Trace dumping is performing id=[cdmp_20140819193856]
    
27. Tue Aug 19 19:38:59 2014
    
28. Sweep Incident[39148]: completed
    
29. Tue Aug 19 19:39:05 2014
    
30. ALTER DATABASE RECOVER  database allow 1 corruption  
    
31. Media Recovery Start
    
32. Fast Parallel Media Recovery enabled
    
33. ALLOW CORRUPTION option must use serial recovery
    
34. Warning: Datafile 10 (D:\ORACLE\PRODUCT\11.1.0\DB_1\ORADATA\SAMPLE\LAYOUT_DB.DBF) is offline during full
    
35. database recovery and will not be recovered
    
36. Recovery of Online Redo Log: Thread 1 Group 2 Seq 5729 Reading mem 0
    
37.   Mem# 0: E:\ORACLE\ORADATA\YYGDB\REDO02.LOG
    
38. CORRUPTING BLOCK 90586 OF FILE 2 AND CONTINUING RECOVERY
    
39. Errors in file d:\oracle\diag\rdbms\yygdb\yygdb\trace\yygdb_ora_12460.trc:
    
40. ORA-10567: Redo is inconsistent with data block (file# 2, block# 90586)
    
41. ORA-10564: tablespace SYSAUX
    
42. ORA-01110: 数据文件 2: 'E:\ORACLE\ORADATA\YYGDB\SYSAUX01.DBF'
    
43. ORA-10561: block type 'TRANSACTION MANAGED DATA BLOCK', data object# 6087
    
44. Completed: ALTER DATABASE RECOVER  database allow 1 corruption  
    
45. 继续open数据库报ORA-01578错误,数据库无法open
    
46. Thread 1 opened at log sequence 5730
    
47.   Current log# 3 seq# 5730 mem# 0: E:\ORACLE\ORADATA\YYGDB\REDO03.LOG
    
48. Successful open of redo thread 1
    
49. MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
    
50. Tue Aug 19 19:39:34 2014
    
51. SMON: enabling cache recovery
    
52. Errors in file d:\oracle\diag\rdbms\yygdb\yygdb\trace\yygdb_ora_12460.trc  (incident=39149):
    
53. ORA-01578: ORACLE 数据块损坏 (文件号 3, 块号 240)
    
54. ORA-01110: 数据文件 3: 'E:\ORACLE\ORADATA\YYGDB\UNDOTBS01.DBF'
    
55. Incident details in: d:\oracle\diag\rdbms\yygdb\yygdb\incident\incdir_39149\yygdb_ora_12460_i39149.trc
    
56. Errors in file d:\oracle\diag\rdbms\yygdb\yygdb\trace\yygdb_ora_12460.trc  (incident=39150):
    
57. ORA-00353: 日志损坏接近块 520 更改 101455257 时间 08/18/2014 08:22:54
    
58. ORA-00312: 联机日志 1 线程 1: 'E:\ORACLE\ORADATA\YYGDB\REDO01.LOG'
    
59. ORA-01578: ORACLE 数据块损坏 (文件号 3, 块号 240)
    
60. ORA-01110: 数据文件 3: 'E:\ORACLE\ORADATA\YYGDB\UNDOTBS01.DBF'
    
61. Incident details in: d:\oracle\diag\rdbms\yygdb\yygdb\incident\incdir_39150\yygdb_ora_12460_i39150.trc
    
62. Errors in file d:\oracle\diag\rdbms\yygdb\yygdb\incident\incdir_39149\yygdb_ora_12460_i39149.trc:
    
63. ORA-00354: 损坏重做日志块标头
    
64. ORA-00353: 日志损坏接近块 520 更改 101455257 时间 08/18/2014 08:22:54
    
65. ORA-00312: 联机日志 1 线程 1: 'E:\ORACLE\ORADATA\YYGDB\REDO01.LOG'
    
66. ORA-01578: ORACLE 数据块损坏 (文件号 3, 块号 240)
    
67. ORA-01110: 数据文件 3: 'E:\ORACLE\ORADATA\YYGDB\UNDOTBS01.DBF'
    
68. Errors in file d:\oracle\diag\rdbms\yygdb\yygdb\trace\yygdb_ora_12460.trc  (incident=39151):
    
69. Error 1578 happened during db open, shutting down database
    
70. USER (ospid: 12460): terminating the instance due to error 1578
    
71. Tue Aug 19 19:39:41 2014
    
72. Errors in file d:\oracle\diag\rdbms\yygdb\yygdb\trace\yygdb_dbw3_18508.trc  (incident=38659):
    
73. ORA-01578: ORACLE data block corrupted (file # , block # )
    
74. Tue Aug 19 19:39:41 2014
    
75. Errors in file d:\oracle\diag\rdbms\yygdb\yygdb\trace\yygdb_dbw5_12160.trc  (incident=38675):
    
76. ORA-01578: ORACLE data block corrupted (file # , block # )
    
77. Tue Aug 19 19:39:42 2014
    
78. Instance terminated by USER, pid = 12460
    
79. ORA-1092 signalled during: alter database open...
    
80. ORA-1092 : opiodr aborting process unknown ospid (5084_12460)

复制代码

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

1. Tue Aug 19 19:50:06 2014
   
2. alter database open
   
3. Beginning crash recovery of 1 threads
   
4. parallel recovery started with 16 processes
   
5. Started redo scan
   
6. Completed redo scan
   
7. 3 redo blocks read, 0 data blocks need recovery
   
8. Started redo application at
   
9. Thread 1: logseq 5731, block 2, scn 101497289
   
10. Recovery of Online Redo Log: Thread 1 Group 1 Seq 5731 Reading mem 0
    
11.   Mem# 0: E:\ORACLE\ORADATA\YYGDB\REDO01.LOG
    
12. Completed redo application of 0.00MB
    
13. Completed crash recovery at
    
14. Thread 1: logseq 5731, block 5, scn 101517294
    
15. 0 data blocks read, 0 data blocks written, 3 redo blocks read
    
16. Tue Aug 19 19:50:08 2014
    
17. Thread 1 advanced to log sequence 5732 (thread open)
    
18. Thread 1 opened at log sequence 5732
    
19.   Current log# 2 seq# 5732 mem# 0: E:\ORACLE\ORADATA\YYGDB\REDO02.LOG
    
20. Successful open of redo thread 1
    
21. MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
    
22. Tue Aug 19 19:50:08 2014
    
23. SMON: enabling cache recovery
    
24. Dictionary check beginning
    
25. Tablespace 'TEMP' #3 found in data dictionary,
    
26. but not in the controlfile. Adding to controlfile.
    
27. File #3 is offline, but is part of an online tablespace.
    
28. data file 3: 'E:\ORACLE\ORADATA\YYGDB\UNDOTBS01.DBF'
    
29. File #10 is offline, but is part of an online tablespace.
    
30. data file 10: 'D:\ORACLE\PRODUCT\11.1.0\DB_1\ORADATA\SAMPLE\LAYOUT_DB.DBF'
    
31. File #11 is offline, but is part of an online tablespace.
    
32. data file 11: 'D:\ORACLE\PRODUCT\11.1.0\DB_1\ORADATA\SAMPLE\LAYOUT.DBF'
    
33. Dictionary check complete
    
34. Verifying file header compatibility for 11g tablespace encryption..
    
35. Verifying 11g file header compatibility for tablespace encryption completed
    
36. SMON: enabling tx recovery
    
37. *********************************************************************
    
38. WARNING: The following temporary tablespaces contain no files.
    
39.          This condition can occur when a backup controlfile has
    
40.          been restored.  It may be necessary to add files to these
    
41.          tablespaces.  That can be done using the SQL statement:
    
42.   
    
43.          ALTER TABLESPACE <tablespace_name> ADD TEMPFILE
    
44.   
    
45.          Alternatively, if these temporary tablespaces are no longer
    
46.          needed, then they can be dropped.
    
47.            Empty temporary tablespace: TEMP
    
48. *********************************************************************
    
49. Database Characterset is ZHS16GBK
    
50. Opening with internal Resource Manager plan : on 4 X 8 NUMA system
    
51. **********************************************************
    
52. WARNING: Files may exists in db_recovery_file_dest
    
53. that are not known to the database. Use the RMAN command
    
54. CATALOG RECOVERY AREA to re-catalog any such files.
    
55. If files cannot be cataloged, then manually delete them
    
56. using OS command.
    
57. One of the following events caused this:
    
58. 1. A backup controlfile was restored.
    
59. 2. A standby controlfile was restored.
    
60. 3. The controlfile was re-created.
    
61. 4. db_recovery_file_dest had previously been enabled and
    
62.    then disabled.
    
63. **********************************************************
    
64. Hex dump of (file 1, block 7065) in trace file
    
65. d:\oracle\diag\rdbms\yygdb\yygdb\trace\yygdb_ora_14296.trc
    
66. Corrupt block relative dba: 0x00401b99 (file 1, block 7065)
    
67. Fractured block found during buffer read
    
68. Data in bad block:
    
69. type: 6 format: 2 rdba: 0x00401b99
    
70. last change scn: 0x0000.060c1f83 seq: 0x1 flg: 0x06
    
71. spare1: 0x0 spare2: 0x0 spare3: 0x0
    
72. consistency value in tail: 0xac3b0601
    
73. check value in block header: 0x2e13
    
74. computed block checksum: 0xa4ac
    
75. Reread of rdba: 0x00401b99 (file 1, block 7065) found same corrupted data
    
76. Errors in file d:\oracle\diag\rdbms\yygdb\yygdb\trace\yygdb_ora_14296.trc  (incident=42814):
    
77. ORA-01578: ORACLE 数据块损坏 (文件号 1, 块号 7065)
    
78. ORA-01110: 数据文件 1: 'E:\ORACLE\ORADATA\YYGDB\SYSTEM01.DBF'
    
79. Incident details in: d:\oracle\diag\rdbms\yygdb\yygdb\incident\incdir_42814\yygdb_ora_14296_i42814.trc
    
80. Errors in file d:\oracle\diag\rdbms\yygdb\yygdb\trace\yygdb_ora_14296.trc  (incident=42815):
    
81. ORA-01578: ORACLE 数据块损坏 (文件号 1, 块号 7065)
    
82. ORA-01110: 数据文件 1: 'E:\ORACLE\ORADATA\YYGDB\SYSTEM01.DBF'
    
83. Incident details in: d:\oracle\diag\rdbms\yygdb\yygdb\incident\incdir_42815\yygdb_ora_14296_i42815.trc
    
84. Tue Aug 19 19:50:12 2014
    
85. Trace dumping is performing id=[cdmp_20140819195012]
    
86. Tue Aug 19 19:50:12 2014
    
87. Sweep Incident[42814]: completed
    
88. Hex dump of (file 1, block 7065) in trace file
    
89. d:\oracle\diag\rdbms\yygdb\yygdb\incident\incdir_42814\yygdb_m000_11592_i42814_a.trc
    
90. Corrupt block relative dba: 0x00401b99 (file 1, block 7065)
    
91. Fractured block found during validation
    
92. Data in bad block:
    
93. type: 6 format: 2 rdba: 0x00401b99
    
94. last change scn: 0x0000.060c1f83 seq: 0x1 flg: 0x06
    
95. spare1: 0x0 spare2: 0x0 spare3: 0x0
    
96. consistency value in tail: 0xac3b0601
    
97. check value in block header: 0x2e13
    
98. computed block checksum: 0xa4ac
    
99. Reread of blocknum=7065, file=E:\ORACLE\ORADATA\YYGDB\SYSTEM01.DBF. found same corrupt data
    
100. Reread of blocknum=7065, file=E:\ORACLE\ORADATA\YYGDB\SYSTEM01.DBF. found same corrupt data
     
101. Reread of blocknum=7065, file=E:\ORACLE\ORADATA\YYGDB\SYSTEM01.DBF. found same corrupt data
     
102. Reread of blocknum=7065, file=E:\ORACLE\ORADATA\YYGDB\SYSTEM01.DBF. found same corrupt data
     
103. Reread of blocknum=7065, file=E:\ORACLE\ORADATA\YYGDB\SYSTEM01.DBF. found same corrupt data
     
104. Hex dump of (file 1, block 7065) in trace file
     
105. d:\oracle\diag\rdbms\yygdb\yygdb\trace\yygdb_ora_14296.trc
     
106. Corrupt block relative dba: 0x00401b99 (file 1, block 7065)
     
107. Fractured block found during buffer read
     
108. Data in bad block:
     
109. type: 6 format: 2 rdba: 0x00401b99
     
110. last change scn: 0x0000.060c1f83 seq: 0x1 flg: 0x06
     
111. spare1: 0x0 spare2: 0x0 spare3: 0x0
     
112. consistency value in tail: 0xac3b0601
     
113. check value in block header: 0x2e13
     
114. computed block checksum: 0xa4ac
     
115. Reread of rdba: 0x00401b99 (file 1, block 7065) found same corrupted data
     
116. Corrupt Block Found
     
117.          TSN = 0, TSNAME = SYSTEM
     
118.          RFN = 1, BLK = 7065, RDBA = 4201369
     
119.          OBJN = 1164, OBJD = 1164, OBJECT = SYS_FBA_BARRIERSCN, SUBOBJECT =
     
120.          SEGMENT OWNER = SYS, SEGMENT TYPE = Table Segment
     
121. Errors in file d:\oracle\diag\rdbms\yygdb\yygdb\trace\yygdb_ora_14296.trc  (incident=42816):
     
122. ORA-01578: ORACLE 数据块损坏 (文件号 1, 块号 7065)
     
123. ORA-01110: 数据文件 1: 'E:\ORACLE\ORADATA\YYGDB\SYSTEM01.DBF'
     
124. Incident details in: d:\oracle\diag\rdbms\yygdb\yygdb\incident\incdir_42816\yygdb_ora_14296_i42816.trc
     
125. Trace dumping is performing id=[cdmp_20140819195014]
     
126. Errors in file d:\oracle\diag\rdbms\yygdb\yygdb\trace\yygdb_ora_14296.trc  (incident=42817):
     
127. Starting background process FBDA
     
128. Tue Aug 19 19:50:18 2014
     
129. FBDA started with pid=86, OS id=17700
     
130. replication_dependency_tracking turned off (no async multimaster replication found)
     
131. Errors in file d:\oracle\diag\rdbms\yygdb\yygdb\trace\yygdb_fbda_17700.trc  (incident=42910):
     
132. ORA-01578: ORACLE data block corrupted (file # 1, block # 7065)
     
133. ORA-01110: data file 1: 'E:\ORACLE\ORADATA\YYGDB\SYSTEM01.DBF'
     
134. Trace dumping is performing id=[cdmp_20140819195018]
     
135. Errors in file d:\oracle\diag\rdbms\yygdb\yygdb\trace\yygdb_fbda_17700.trc  (incident=42911):
     
136. ORA-01578: ORACLE data block corrupted (file # 1, block # 7065)
     
137. ORA-01110: data file 1: 'E:\ORACLE\ORADATA\YYGDB\SYSTEM01.DBF'
     
138. LOGSTDBY: Validating controlfile with logical metadata
     
139. LOGSTDBY: Validation complete

复制代码

数据库不完全open成功，报了604错误，通过分析undo$，直接使用_offline_rollback_segments屏蔽了status$=5的回滚段,数据库open正常,因为system有大量坏块,幸运的是使用exp导出来几个业务用户的表数据全部OK.
数据库备份重于一切,别寄希望数据库非常规恢复
具体见：一起ORA-600 3020故障恢复的大体思路