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 option

1 確認哪些資料檔案受到影響

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                                          0

SELECT 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 status

SVR4 Error: 2: No such file or directory

Additional information: 3

ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below

ORA-01152: file 1 was not restored from a sufficiently old backup

ORA-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