ORA-600(3020) 錯誤解決

今天在恢複一套測試庫的時候遇到了ora-600(3020)錯誤
SQL> recover datafile 6;
ORA-00279: change 1981277 generated at 01/03/2014 14:24:15 needed for thread 1
ORA-00289: suggestion : /opt/arch/archive_1_45_834576965.arc
ORA-00280: change 1981277 for thread 1 is in sequence #45

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
。。。
。。。
ORA-00283: recovery session canceled due to errors
ORA-00600: internal error code, arguments: [3020], [6], [59], [25165883], [],[], [], [], [], [], [], []
ORA-10567: Redo is inconsistent with data block (file# 6, block# 59, file
offset is 483328 bytes)
ORA-10564: tablespace GOOLEN
ORA-01110: data file 6: '/opt/app/Oracle/oradata/goolen/goolen01.dbf'
ORA-10561: block type 'TRANSACTION MANAGED DATA BLOCK', data object# 74195
ORA-01112: media recovery not started

alter日誌報錯：
Fri Jan 03 17:21:06 2014
ALTER DATABASE RECOVER  datafile 6 
Media Recovery Start
Serial Media Recovery started
ORA-279 signalled during: ALTER DATABASE RECOVER  datafile 6  ...
ALTER DATABASE RECOVER    CONTINUE DEFAULT 
Media Recovery Log /opt/arch/archive_1_45_834576965.arc
Errors in file /opt/app/oracle/diag/rdbms/goolen/goolen/trace/goolen_ora_11763.trc:
ORA-00308: cannot open archived log '/opt/arch/archive_1_44_834576965.arc'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
ORA-10567: Redo is inconsistent with data block (file# 6, block# 59, file offset is 483328 bytes)
ORA-10564: tablespace GOOLEN
ORA-01110: data file 6: '/opt/app/oracle/oradata/goolen/goolen01.dbf'
ORA-10561: block type 'TRANSACTION MANAGED DATA BLOCK', data object# 74195
Errors in file /opt/app/oracle/diag/rdbms/goolen/goolen/trace/goolen_ora_11763.trc  (incident=15025):
ORA-00600: internal error code, arguments: [3020], [6], [59], [25165883], [], [], [], [], [], [], [], []
ORA-10567: Redo is inconsistent with data block (file# 6, block# 59, file offset is 483328 bytes)
ORA-10564: tablespace GOOLEN
ORA-01110: data file 6: '/opt/app/oracle/oradata/goolen/goolen01.dbf'
ORA-10561: block type 'TRANSACTION MANAGED DATA BLOCK', data object# 74195
Incident details in: /opt/app/oracle/diag/rdbms/goolen/goolen/incident/incdir_15025/goolen_ora_11763_i15025.trc
Media Recovery failed with error 600

trace檔案內容：
buffer tsn: 6 rdba: 0x0180003b (6/59)
scn: 0x0000.00158d60 seq: 0x01 flg: 0x04 tail: 0x8d600601
frmt: 0x02 chkval: 0xf96d type: 0x06=trans data
on-disk scn: 0x0.158d60
DUMP REDO
 Opcodes *.*
 DBAs (file#, block#):
 (6, 59) .
 SCNs: scn: 0x0000.00158d60 (1412448) thru scn: 0x0000.001e3b9c (1981340)
 Times: creation thru eternity
*Error 308 when opening Archive Log -
 /opt/arch/archive_1_44_834576965.arc
*Error is non fatal for dump - Dump will continue
*Error 308 when opening Archive Log -
 /opt/arch/archive_1_44_834576965.arc
*Error is non fatal for dump - Dump will continue
*Error 308 when opening Archive Log -
 /opt/arch/archive_1_39_834576965.arc
*Error is non fatal for dump - Dump will continue
*Error 308 when opening Archive Log -
 /opt/arch/archive_1_39_834576965.arc

 SQL> select segment_name, segment_type, owner
  2  from dba_extents
  3  where file_id = &file_id
  4    and &block_id between block_id and block_id + blocks - 1;
Enter value for file_id: 6
old  3:  where file_id = &file_id
new  3:  where file_id = 6
Enter value for block_id: 59
old  4:  and &block_id between block_id and block_id + blocks - 1
new  4:  and 59 between block_id and block_id + blocks - 1

no rows selected

SQL> select *
  2  from dba_free_space
  3  where file_id = &file_id
  4  and &block_id between block_id and block_id + blocks - 1;
Enter value for file_id: 6
old  3: where file_id = &file_id
new  3: where file_id = 6
Enter value for block_id: 59
old  4: and &block_id between block_id and block_id + blocks - 1
new  4: and 59 between block_id and block_id + blocks - 1

no rows selected

SQL> SELECT owner, segment_name, segment_type, partition_name
  2              FROM dba_segments
  3            WHERE header_file =&file_id
  4    and header_block=&block_id;
Enter value for file_id: 6
old  3:            WHERE header_file =&file_id
new  3:            WHERE header_file =6
Enter value for block_id: 59
old  4:    and header_block=&block_id
new  4:    and header_block=59

no rows selected

由於出現這個錯誤，恢複中斷
Metalink上解釋：
  This is called a 'STUCK RECOVERY'.
  There is an inconsistency between the information stored in the redo and the information stored in a database block being recovered
 
+++從以上資訊，我們可以大概猜測，oracle在做恢複時（0x0180003b (6/59)），發現redo資訊與要恢複的資料區塊資訊不一致，導致恢複無法繼續
SCNs: scn: 0x0000.00158d60 (1412448) thru scn: 0x0000.001e3b9c (1981340)

由於測試庫沒有備份，只能損失部分資料來恢複資料庫

SQL> recover datafile 6 allow 1 corruption;
ORA-00279: change 1981277 generated at 01/03/2014 14:24:15 needed for thread 1
ORA-00289: suggestion : /opt/arch/archive_1_45_834576965.arc
ORA-00280: change 1981277 for thread 1 is in sequence #45

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00279: change 1981343 generated at 01/03/2014 14:25:15 needed for thread 1
ORA-00289: suggestion : /opt/arch/archive_1_46_834576965.arc
ORA-00280: change 1981343 for thread 1 is in sequence #46

Log applied.
Media recovery complete.
SQL>
SQL> alter database datafile 6 online;

Database altered.

Oracle ORA-01555 快照過舊 說明

ORA-01078 和 LRM-00109 報錯解決方案

ORA-01555超長的Query Duration時間

ORA-00471 處理方法筆記

ORA-00314,redolog 損壞，或丟失處理方法

ORA-00257 歸檔日誌過大導致無法儲存的解決辦法