類比ORA-26040: Data block was loaded using the NOLOGGING option，ora-26040nologging

類比ORA-26040: Data block was loaded using the NOLOGGING option，ora-26040nologging

我們知道通過設定nologging選項，可以加快oracle的某些操作的執行速度，這在執行某些維護任務時是非常有用的，但是該選項也很危險，如果使用不當，就可能導致資料庫發生ORA-26040錯誤。

首先，構造使用環境，

SQL> select tablespace_name,logging,force_logging from dba_tablespaces;TABLESPACE_NAME        LOGGING FOR------------------------------ --------- ---SYSTEM       LOGGING NOUNDOTBS1       LOGGING NOSYSAUX       LOGGING NOTEMP       NOLOGGING NOUSERS       LOGGING NOLOGGING        LOGGING NO6 rows selected.

SQL> show userUSER is "LOGGING"SQL> select table_name,logging from user_tables;TABLE_NAME       LOG------------------------------ ---SOURCE       YESNOLOG       NONOLOG1       NO

我們使用create table table_name nologging as select * from user_tables建立了表nolog和nolog1。在建立表之前，先使用rman進行全庫的備份，表建立完成後，關閉資料庫，並使用備份來恢複，結果如下：

[oraten@yue bdump]$ rman target /Recovery Manager: Release 10.2.0.5.0 - Production on 星期四 11月 13 17:21:02 2014Copyright (c) 1982, 2007, Oracle.  All rights reserved.connected to target database: ORATEN (DBID=3658365464, not open)RMAN> list backup;using target database control file instead of recovery catalogList of Backup Sets===================BS Key  Type LV Size       Device Type Elapsed Time Completion Time    ------- ---- -- ---------- ----------- ------------ -------------------97      Full    565.31M    DISK        00:00:41     2014-11-12 09:34:45        BP Key: 65   Status: AVAILABLE  Compressed: NO  Tag: TAG20141112T093404        Piece Name: /home/app/oraten/flash_recovery_area/ORATEN/backupset/2014_11_12/o1_mf_nnndf_TAG20141112T093404_b65g8fc3_.bkp  List of Datafiles in backup set 97  File LV Type Ckp SCN    Ckp Time            Name  ---- -- ---- ---------- ------------------- ----  1       Full 1276159    2014-11-12 09:34:04 /home/app/oraten/oradata/oraten/system01.dbf  2       Full 1276159    2014-11-12 09:34:04 /home/app/oraten/oradata/oraten/undotbs01.dbf  3       Full 1276159    2014-11-12 09:34:04 /home/app/oraten/oradata/oraten/sysaux01.dbf  4       Full 1276159    2014-11-12 09:34:04 /home/app/oraten/oradata/oraten/users01.dbf  5       Full 1276159    2014-11-12 09:34:04 /home/app/oraten/oradata/oraten/logging01.dbfBS Key  Type LV Size       Device Type Elapsed Time Completion Time    ------- ---- -- ---------- ----------- ------------ -------------------98      Full    6.86M      DISK        00:00:02     2014-11-12 09:34:52        BP Key: 66   Status: AVAILABLE  Compressed: NO  Tag: TAG20141112T093404        Piece Name: /home/app/oraten/flash_recovery_area/ORATEN/backupset/2014_11_12/o1_mf_ncsnf_TAG20141112T093404_b65g9vx2_.bkp  Control File Included: Ckp SCN: 1276545      Ckp time: 2014-11-12 09:34:50  SPFILE Included: Modification time: 2014-11-12 09:14:00RMAN> restore database;Starting restore at 2014-11-13 17:21:19allocated channel: ORA_DISK_1channel ORA_DISK_1: sid=155 devtype=DISKchannel ORA_DISK_1: starting datafile backupset restorechannel ORA_DISK_1: specifying datafile(s) to restore from backup setrestoring datafile 00001 to /home/app/oraten/oradata/oraten/system01.dbfrestoring datafile 00002 to /home/app/oraten/oradata/oraten/undotbs01.dbfrestoring datafile 00003 to /home/app/oraten/oradata/oraten/sysaux01.dbfrestoring datafile 00004 to /home/app/oraten/oradata/oraten/users01.dbfrestoring datafile 00005 to /home/app/oraten/oradata/oraten/logging01.dbfchannel ORA_DISK_1: reading from backup piece /home/app/oraten/flash_recovery_area/ORATEN/backupset/2014_11_12/o1_mf_nnndf_TAG20141112T093404_b65g8fc3_.bkpchannel ORA_DISK_1: restored backup piece 1piece handle=/home/app/oraten/flash_recovery_area/ORATEN/backupset/2014_11_12/o1_mf_nnndf_TAG20141112T093404_b65g8fc3_.bkp tag=TAG20141112T093404channel ORA_DISK_1: restore complete, elapsed time: 00:00:25Finished restore at 2014-11-13 17:21:45RMAN> recover database;Starting recover at 2014-11-13 17:21:50using channel ORA_DISK_1starting media recoveryarchive log thread 1 sequence 53 is already on disk as file /home/app/oraten/flash_recovery_area/ORATEN/archivelog/2014_11_12/o1_mf_1_53_b65gj7m2_.arcarchive log thread 1 sequence 54 is already on disk as file /home/app/oraten/flash_recovery_area/ORATEN/archivelog/2014_11_12/o1_mf_1_54_b65kj77p_.arcarchive log thread 1 sequence 55 is already on disk as file /home/app/oraten/flash_recovery_area/ORATEN/archivelog/2014_11_13/o1_mf_1_55_b68w6tft_.arcarchive log filename=/home/app/oraten/flash_recovery_area/ORATEN/archivelog/2014_11_12/o1_mf_1_53_b65gj7m2_.arc thread=1 sequence=53media recovery complete, elapsed time: 00:00:14Finished recover at 2014-11-13 17:22:05RMAN> alter database open;database openedRMAN> 

alert 檔案中的內容如下：

Thu Nov 13 17:21:20 CST 2014Full restore complete of datafile 5 /home/app/oraten/oradata/oraten/logging01.dbf.  Elapsed time: 0:00:00   checkpoint is 1276159Full restore complete of datafile 4 /home/app/oraten/oradata/oraten/users01.dbf.  Elapsed time: 0:00:00   checkpoint is 1276159  last deallocation scn is 672889Full restore complete of datafile 2 /home/app/oraten/oradata/oraten/undotbs01.dbf.  Elapsed time: 0:00:01   checkpoint is 1276159  last deallocation scn is 1252646Full restore complete of datafile 3 /home/app/oraten/oradata/oraten/sysaux01.dbf.  Elapsed time: 0:00:03   checkpoint is 1276159  last deallocation scn is 842824Thu Nov 13 17:21:41 CST 2014Full restore complete of datafile 1 /home/app/oraten/oradata/oraten/system01.dbf.  Elapsed time: 0:00:10   checkpoint is 1276159  last deallocation scn is 399219Thu Nov 13 17:21:51 CST 2014alter database recover datafile list clearThu Nov 13 17:21:51 CST 2014Completed: alter database recover datafile list clearThu Nov 13 17:21:51 CST 2014alter database recover datafile list 1 , 2 , 3 , 4 , 5Completed: alter database recover datafile list 1 , 2 , 3 , 4 , 5Thu Nov 13 17:21:51 CST 2014alter database recover if needed startMedia Recovery Start parallel recovery started with 2 processesORA-279 signalled during: alter database recover if needed start...Thu Nov 13 17:21:51 CST 2014alter database recover logfile '/home/app/oraten/flash_recovery_area/ORATEN/archivelog/2014_11_12/o1_mf_1_53_b65gj7m2_.arc'Thu Nov 13 17:21:51 CST 2014Media Recovery Log /home/app/oraten/flash_recovery_area/ORATEN/archivelog/2014_11_12/o1_mf_1_53_b65gj7m2_.arcThu Nov 13 17:21:52 CST 2014Recovery of Online Redo Log: Thread 1 Group 3 Seq 54 Reading mem 0  Mem# 0: /home/app/oraten/oradata/oraten/redo03.logThu Nov 13 17:21:54 CST 2014Recovery of Online Redo Log: Thread 1 Group 1 Seq 55 Reading mem 0  Mem# 0: /home/app/oraten/oradata/oraten/redo01.logThu Nov 13 17:21:59 CST 2014Recovery of Online Redo Log: Thread 1 Group 2 Seq 56 Reading mem 0  Mem# 0: /home/app/oraten/oradata/oraten/redo02.logThu Nov 13 17:22:02 CST 2014Media Recovery Complete (oraten)Completed: alter database recover logfile '/home/app/oraten/flash_recovery_area/ORATEN/archivelog/2014_11_12/o1_mf_1_53_b65gj7m2_.arc'Thu Nov 13 17:22:11 CST 2014alter database openThu Nov 13 17:22:11 CST 2014LGWR: STARTING ARCH PROCESSESARC0 started with pid=21, OS id=6628Thu Nov 13 17:22:11 CST 2014ARC0: Archival startedARC1: Archival startedLGWR: STARTING ARCH PROCESSES COMPLETEARC1 started with pid=22, OS id=6630Thu Nov 13 17:22:12 CST 2014Thread 1 opened at log sequence 56  Current log# 2 seq# 56 mem# 0: /home/app/oraten/oradata/oraten/redo02.logSuccessful open of redo thread 1Thu Nov 13 17:22:12 CST 2014MTTR advisory is disabled because FAST_START_MTTR_TARGET is not setThu Nov 13 17:22:12 CST 2014ARC0: Becoming the 'no FAL' ARCHARC0: Becoming the 'no SRL' ARCHThu Nov 13 17:22:12 CST 2014ARC1: Becoming the heartbeat ARCHThu Nov 13 17:22:12 CST 2014SMON: enabling cache recoveryThu Nov 13 17:22:12 CST 2014Successfully onlined Undo Tablespace 1.Thu Nov 13 17:22:12 CST 2014SMON: enabling tx recoveryThu Nov 13 17:22:12 CST 2014Database Characterset is AL32UTF8Opening with internal Resource Manager plan replication_dependency_tracking turned off (no async multimaster replication found)Starting background process QMNCQMNC started with pid=23, OS id=6632Thu Nov 13 17:22:12 CST 2014db_recovery_file_dest_size of 2048 MB is 28.34% used. This is auser-specified limit on the amount of space that will be used by thisdatabase for recovery-related files, and does not reflect the amount ofspace available in the underlying filesystem or ASM diskgroup.Thu Nov 13 17:22:13 CST 2014Completed: alter database open

從上面我們看出，一切正常，資料庫成功恢複了，但是：

SQL> conn logging/loggingConnected.SQL> select * from nolog;select * from nolog              *ERROR at line 1:ORA-01578: ORACLE data block corrupted (file # 5, block # 44)ORA-01110: data file 5: '/home/app/oraten/oradata/oraten/logging01.dbf'ORA-26040: Data block was loaded using the NOLOGGING option

資料庫報錯，看來恢複成功並不一定資料庫就是正常的。
再看一下，日誌的的dump內容，

[oraten@yue udump]$ strings oraten_ora_10509.trc | grep oadDirect Loader invalidate block range redo entryDirect Loader invalidate block range redo entryDirect Loader invalidate block range redo entryDirect Loader invalidate block range redo entryDirect Loader invalidate block range redo entryDirect Loader invalidate block range redo entryDirect Loader invalidate block range redo entryDirect Loader invalidate block range redo entryDirect Loader invalidate block range redo entryDirect Loader invalidate block range redo entryDirect Loader invalidate block range redo entryDirect Loader invalidate block range redo entryDirect Loader invalidate block range redo entryDirect Loader invalidate block range redo entryDirect Loader invalidate block range redo entry

看來日誌中對nologging是有記錄的，在rman恢複時，會根據記錄將某些塊設定為邏輯損壞。