nologging对备库的影响
要么主库上的DML使用了nologging关键字，要么是objects创建时指定了nologging选项；同时如果主库需要使用包含nologging操作的日志恢复，同样会遇到类似错误
可以通过在主库开启force logging避免类似麻烦 逻辑备库sql apply会停止，并报告ora-16211: unsupported record found in the archived redo log
可以通过DBMS_LOGSTDBY.INSTANTIATE_TABLE从主库手工同步
物理备库受影响的数据文件会被标示为unrecoverable，同时alert log会纪录类似错误信息
ORA-01578: ORACLE data block corrupted (file # 1, block # 2521)ORA-01110: data file 1: '/oracle/dbs/stdby/tbs_1.dbf'ORA-26040: Data block was loaded using the NOLOGGING option1 确认哪些数据文件受到影响
SELECT NAME, UNRECOVERABLE_CHANGE# FROM V$DATAFILE;--主库
NAME UNRECOVERABLE----------------------------------------------------- -------------/oracle/dbs/tbs_1.dbf 5216/oracle/dbs/tbs_2.dbf 0/oracle/dbs/tbs_3.dbf 0/oracle/dbs/tbs_4.dbf 0SELECT NAME, UNRECOVERABLE_CHANGE# FROM V$DATAFILE;--备库
NAME UNRECOVERABLE----------------------------------------------------- -------------/oracle/dbs/stdby/tbs_1.dbf 5186/oracle/dbs/stdby/tbs_2.dbf 0/oracle/dbs/stdby/tbs_3.dbf 0/oracle/dbs/stdby/tbs_4.dbf 0 如果主库的UNRECOVERABLE_CHANGE#大于备库同条记录的值，则可以将其copy到备库
2 copy数据文件
可以通过rman的copy命令，或者begin backup进行热copy
3 开启备库应用
有可能会遇到以下错误ORA-00308: cannot open archived log 'standby1'ORA-27037: unable to obtain file statusSVR4 Error: 2: No such file or directoryAdditional information: 3ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error belowORA-01152: file 1 was not restored from a sufficiently old backupORA-01110: data file 1: '/oracle/dbs/stdby/tbs_1.dbf'一般是因为archive gap导致的
如果主库执行了unrecoverable的DML操作，则需要确认是否对其执行备份
SELECT UNRECOVERABLE_CHANGE#,TO_CHAR(UNRECOVERABLE_TIME, 'mm-dd-yyyy hh:mi:ss') FROM V$DATAFILE;
如果返回的时间值比最近备份的时间晚，则需要再做一次备份 前段时候碰到过类似的案例，不过是发生在主库，当时曾经发个帖向maclean求助 Oracle: RAC 10.2.0.5， 生产库
OS: AIX
症状：alertlog显示如下
Mon Mar 05 22:02:32 GMT+08:00 2012GATHER_STATS_JOB encountered errors. Check the trace file.
Mon Mar 05 22:02:32 GMT+08:00 2012Errors in file /u001/app/oracle/admin/justin/bdump/justin1_j000_27525252.trc:
ORA-01578: ORACLE data block corrupted (file # 1024, block # 497129)
ORA-01110: data file 8: '/s102/justin/CMX_INDX1.dbf'
ORA-26040: Data block was loaded using the NOLOGGING option
其中trace文件大致为
WARNING:Could not lower the asynch I/O limit to 160 for SQL direct I/O. It is set to -1
*** 2012-03-05 22:00:16.032
WARNING:Could not lower the asynch I/O limit to 256 for SQL direct I/O. It is set to -1
WARNING:Could not lower the asynch I/O limit to 224 for SQL direct I/O. It is set to -1
WARNING:Could not lower the asynch I/O limit to 192 for SQL direct I/O. It is set to -1
WARNING:Could not lower the asynch I/O limit to 160 for SQL direct I/O. It is set to -1
WARNING:Could not lower the asynch I/O limit to 160 for SQL direct I/O. It is set to -1
WARNING:Could not lower the asynch I/O limit to 192 for SQL direct I/O. It is set to -1
WARNING:Could not lower the asynch I/O limit to 160 for SQL direct I/O. It is set to -1
*** 2012-03-05 22:02:32.085
ORA-01578: ORACLE data block corrupted (file # 1024, block # 497129)
ORA-01110: data file 8: '/s102/justin/C_INDX1.dbf'
ORA-26040: Data block was loaded using the NOLOGGING option
*** 2012-03-05 22:02:32.103
GATHER_STATS_JOB: GATHER_TABLE_STATS('"JUSTIN"','"C_SEJ"','""', ...)
ORA-01578: ORACLE data block corrupted (file # 1024, block # 497129)
ORA-01110: data file 8: '/s102/justin/CMX_INDX1.dbf'
ORA-26040: Data block was loaded using the NOLOGGING option
查看v$database_block_corruption，返回3000多行且corruption_type为unknown
MOS上有相关文档ID 794505.1
运行
select distinct e.owner,e.segment_type,e.segment_name from dba_extents e, v$database_block_corruption c
where c.file# = e.file_id
and c.block# between e.block_id and e.block_id + e.blocks;
返回2000多行，且全为index
http://t.askmaclean.com/thread-475-1-1.html
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/15480802/viewspace-718438/ ，如需转载，请注明出处，否则将追究法律责任。
上一篇：
主库resetlogs对备库的影响
下一篇：
linux下free命令详解
请登录后发表评论
登录
全部评论
