生产上的一套 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;

至此，物理备库恢复正常。

ITPub博客

Oracle 11g物理备库出现坏块，导致归档日志应用报错（ORA-10562、ORA-01110）

最新文章