ITPub博客

首页 > 数据库 > Oracle > Oracle 11g物理备库出现坏块,导致归档日志应用报错(ORA-10562、ORA-01110)

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

原创 Oracle 作者:feelpurple 时间:2016-01-12 14:17:49 0 删除 编辑

生产上的一套 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 fileORA-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博客 ” ,链接:http://blog.itpub.net/26506993/viewspace-1975293/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2014-08-01

  • 博文量
    404
  • 访问量
    1179917