生產上的一套 11G 物理備庫出現故障，存放資料檔案和歸檔日誌的掛載盤發生故障，導致出現資料塊錯誤。
資料庫報警日誌中的錯誤如下：

ORA-10562: Error occurred while applying redo to data block (file# 28, block# 1336760)

ORA-10564: tablespace TS_TRAN_INDEX

ORA-01110: data file 28: '/ORADATA/data/GTFDBDG/datafile/o1_mf_ts_tran__9jqlcv5b_.dbf'

ORA-10560: block type '0'

ORA-00600: internal error code, arguments: [4553], [2], [0], [], [], [], [], [], [], [], [], []

Errors in file /u01/app/oracle/diag/rdbms/gtfdbdg/gtfdb/trace/gtfdb_pr0f_3437.trc:

ORA-00338: log 10 of thread 1 is more recent than control file

ORA-00312: online log 10 thread 1: '/ORADATA/redolog/GTFDBDG/onlinelog/o1_mf_10_c3l6yy9y_.log'

ORA-00338: log 10 of thread 1 is more recent than control file

ORA-00312: online log 10 thread 1: '/ORADATA/redolog/GTFDBDG/onlinelog/o1_mf_10_c3l6yxxm_.log'

ORA-00607: Internal error occurred while making a change to a data block

ORA-00602: internal programming exception

ORA-07445: exception encountered: core dump [kdxlin()+4108] [SIGSEGV] [ADDR:0xC] [PC:0x9188AE2] [Address not mapped to object] []

Errors in file /u01/app/oracle/diag/rdbms/gtfdbdg/gtfdb/trace/gtfdb_pr0f_3437.trc:

ORA-00338: log 15 of thread 2 is more recent than control file

ORA-00312: online log 15 thread 2: '/ORADATA/redolog/GTFDBDG/onlinelog/o1_mf_15_c3l6z47s_.log'

ORA-00338: log 15 of thread 2 is more recent than control file

ORA-00312: online log 15 thread 2: '/ORADATA/redolog/GTFDBDG/onlinelog/o1_mf_15_c3l6z3vo_.log'

Slave exiting with ORA-10562 exception

Errors in file /u01/app/oracle/diag/rdbms/gtfdbdg/gtfdb/trace/gtfdb_pr0d_26650.trc:

ORA-10562: Error occurred while applying redo to data block (file# 31, block# 1321407)

ORA-10564: tablespace TS_TRAN_INDEX

ORA-01110: data file 31: '/ORADATA/data/GTFDBDG/datafile/o1_mf_ts_tran__9mqld014_.dbf'

ORA-10561: block type 'TRANSACTION MANAGED INDEX BLOCK', data object# 194589

ORA-00607: Internal error occurred while making a change to a data block

ORA-00602: internal programming exception

ORA-07445: exception encountered: core dump [kdxlin()+4108] [SIGSEGV] [ADDR:0xC] [PC:0x9188AE2] [Address not mapped to object] []

--從”ORA-10562: Error occurred while applying redo to data block (file# 28, block# 1336760)

”報錯來看，可能有資料塊錯誤。

--檢查報錯的資料檔案

Rman target /

backup validate datafile 31;

--這個時候訪問v$database_block_corruption可以看到詳細的壞塊的資訊:

SQL> select * from v$database_block_corruption;

FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO

---------- ---------- ---------- ------------------ ---------

31 1328883 1 0 FRACTURED

--檢查歸檔日誌的同步情況，在備庫執行，找到備庫應用的最後一個歸檔日誌的結束SCN

SQL> select SEQUENCE#,THREAD#,APPLIED,FIRST_CHANGE#,NEXT_CHANGE# from V$ARCHIVED_LOG ORDER BY SEQUENCE#;

SEQUENCE# THREAD# APPLIED FIRST_CHANGE# NEXT_CHANGE#

---------- ---------- --------- ------------- ------------

16535 1 YES 9274753621 9275101420

16536 1 YES 9275101420 9275448973

16537 1 YES 9275448973 9275794430

16538 1 YES 9275794430 9276383738

16539 1 YES 9276383738 9276734893

16540 1 YES 9276734893 9277087582

16541 1 NO 9277087582 9277443081

16542 1 NO 9277443081 9277791872

16543 1 NO 9277791872 9278138695

16544 1 NO 9278138695 9278487470

16545 1 NO 9278487470 9278757984

--在主庫上使用RMAN進行增量資料備份，備份從備份應用的最後 SCN 開始

rman target / << OEF

run

{

allocate channel d1 type disk;

allocate channel d2 type disk;

backup as compressed backupset incremental from SCN 9277087582 database format '/u01/app/oracle/bk/20160108/rmanback_incr_%d_%T_%U.bak'

include current controlfile for standby filesperset=5 tag 'forstandby160108';

release channel d1;

release channel d2;

}

exit

EOF

--複製備份好的 RMAN 備份到備庫上面

cd /u01/app/oracle/bk

scp * oracle@10.100.20.71:/u01/app/oracle/bk

--備庫

--透過 RMAN 註冊傳輸過來的備份

rman target /

catalog start with '/u01/app/oracle/bk';

--進行增量恢復

run {

allocate channel ch1 type disk;

allocate channel ch2 type disk;

recover database noredo;

release channel ch1;

release channel ch2;

}

--恢復完成後，嘗試進行歸檔日誌應用

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

--檢視備庫日誌的應用情況

select SEQUENCE#,THREAD#,APPLIED,FIRST_CHANGE#,NEXT_CHANGE# from V$ARCHIVED_LOG ORDER BY SEQUENCE#;

--在日誌應用完成後，嘗試開啟資料庫

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

alter database open;

--同時，”ORA-00338: log 10 of thread 1 is more recent than control file”，”ORA-00338: log 15 of thread 2 is more recent than control file”，可以知道 standby log 裡面的SCN 已經和控制檔案不一致

--清空報錯的日誌

ALTER DATABASE CLEAR LOGFILE GROUP 10;

ALTER DATABASE CLEAR LOGFILE GROUP 15;

到這來，問題還沒真正解決。使用增量恢復後，物理備庫執行了幾個小時後，又發生報錯。

Slave exiting with ORA-10562 exception

Errors in file /u01/app/oracle/diag/rdbms/gtfdbdg/gtfdb/trace/gtfdb_pr0d_26650.trc:

ORA-10562: Error occurred while applying redo to data block (file# 31, block# 1321407)

ORA-10564: tablespace TS_TRAN_INDEX

ORA-01110: data file 31: '/ORADATA/data/GTFDBDG/datafile/o1_mf_ts_tran__9mqld014_.dbf'

ORA-10561: block type 'TRANSACTION MANAGED INDEX BLOCK', data object# 194589

ORA-00607: Internal error occurred while making a change to a data block

ORA-00602: internal programming exception

ORA-07445: exception encountered: core dump [kdxlin()+4108] [SIGSEGV] [ADDR:0xC] [PC:0x9188AE2] [Address not mapped to object] []

--檢視壞塊的資訊，所列的壞塊和之前的壞塊一致，壞塊並沒有被修復

SQL> select * from v$database_block_corruption;

FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO

---------- ---------- ---------- ------------------ ---------

31 1328883 1 0 FRACTURED

--在主庫上，使用 COPY 格式備份出 31 號資料檔案，傳送到備庫

run

{

allocate channel d1 type disk;

backup as copy datafile 31 format '/u01/app/oracle/bk/20160110/rmanback_%d_%T_%U.bak';

release channel d1;

}

--將物理備庫關閉，將原來的31號資料檔案備份，使用複製過來的資料檔案替換掉備庫原來的資料檔案

cp rmanback_GTFDB_20160111_data_D-GTFDB_I-4241661131_TS-TS_TRAN_INDEX_FNO-31_jkqr44rv.bak /ORADATA/data/GTFDBDG/datafile/o1_mf_ts_tran__9mqld014_.dbf

--啟動資料庫到 mount 狀態，應用歸檔日誌

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

--檢視歸檔日誌的應用情況

select SEQUENCE#,THREAD#,APPLIED,FIRST_CHANGE#,NEXT_CHANGE# from V$ARCHIVED_LOG ORDER BY SEQUENCE#;

--等歸檔日誌應用完畢後，關閉歸檔日誌應用，以只讀方式開啟資料庫

ALTER DATABASE OPEN;

--啟用日誌實時同步

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;

至此，物理備庫恢復正常。

Oracle 11g物理備庫出現壞塊，導致歸檔日誌應用報錯（ORA-10562、ORA-01110）

相關文章