STANDBY中NOLOGGING操作的監控

westzq1984發表於2009-09-05

SQL> create table ctais2.no_test nologging as select /*+append */ * from dba_objects;

 

Table created.

 

SQL> alter system archive log current;

 

System altered.

 

SQL> SELECT ts#,file#,a.UNRECOVERABLE_CHANGE#,to_char(a.UNRECOVERABLE_TIME,'yyyymmdd hh24miss') FROM v$datafile a;

 

       TS#      FILE# UNRECOVERABLE_CHANGE# TO_CHAR(A.UNREC

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

         0          1                     0

         1          2                     0

         2          3                     0

         4          4                     0

         5          5               1004932 20090904 125926

 

切換日誌，讓STANDBY資料庫也應用日誌，可以看到在STANDBY資料庫中，NOLOGGING操作表現不出來

後臺日誌也沒有任何錯誤

 

SQL> SELECT ts#,file#,a.UNRECOVERABLE_CHANGE#,to_char(a.UNRECOVERABLE_TIME,'yyyymmdd hh24miss') FROM v$datafile a;

 

       TS#      FILE# UNRECOVERABLE_CHANGE# TO_CHAR(A.UNREC

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

         0          1                     0

         1          2                     0

         2          3                     0

         4          4                     0

         5          5                     0

 

嘗試OPEN READONLY資料庫

SQL> shutdown immediate

ORA-01109: database not open

 

 

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

 

Total System Global Area  205520896 bytes

Fixed Size                  1266608 bytes

Variable Size              67112016 bytes

Database Buffers          134217728 bytes

Redo Buffers                2924544 bytes

Database mounted.

Database opened.

SQL>

 

SQL> SELECT ts#,file#,a.UNRECOVERABLE_CHANGE#,to_char(a.UNRECOVERABLE_TIME,'yyyymmdd hh24miss') FROM v$datafile a;

 

       TS#      FILE# UNRECOVERABLE_CHANGE# TO_CHAR(A.UNREC

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

         0          1                     0

         1          2                     0

         2          3                     0

         4          4                     0

         5          5                     0

 

SQL> analyze table ctais2.no_test validate structure;

analyze table ctais2.no_test validate structure

*

ERROR at line 1:

ORA-01578: ORACLE data block corrupted (file # 5, block # 3084)

ORA-01110: data file 5: '/dev/raw/raw13'

ORA-26040: Data block was loaded using the NOLOGGING option

 

 

SQL> SELECT ts#,file#,a.UNRECOVERABLE_CHANGE#,to_char(a.UNRECOVERABLE_TIME,'yyyymmdd hh24miss') FROM v$datafile a;

 

       TS#      FILE# UNRECOVERABLE_CHANGE# TO_CHAR(A.UNREC

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

         0          1                     0

         1          2                     0

         2          3                     0

         4          4                     0

         5          5                     0

 

這個時候DBV驗證也會發現問題

DBV-00201: Block, DBA 20975318, marked corrupt for invalid redo application

 

DBV-00201: Block, DBA 20975319, marked corrupt for invalid redo application

 

DBV-00201: Block, DBA 20975320, marked corrupt for invalid redo application

 

DBV-00201: Block, DBA 20975321, marked corrupt for invalid redo application

 

DBV-00201: Block, DBA 20975322, marked corrupt for invalid redo application

Page 6401 is marked corrupt

Corrupt block relative dba: 0x01401901 (file 5, block 6401)

Completely zero block found during dbv:

 

Page 6402 is marked corrupt

Corrupt block relative dba: 0x01401902 (file 5, block 6402)

Completely zero block found during dbv:

 

Page 6403 is marked corrupt

Corrupt block relative dba: 0x01401903 (file 5, block 6403)

Completely zero block found during dbv:

 

對於NOLOGGING操作的記錄，應該是通過監控v$database.UNRECOVERABLE_CHANGE#,如果存在NOLOGGING操作，最後從主庫重新COPY資料檔案過去恢復

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/8242091/viewspace-614038/，如需轉載，請註明出處，否則將追究法律責任。

相關文章