STANDBY中NOLOGGING操作的监控

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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/8242091/viewspace-614038/