Simulate ORA-26040: Data block was loaded using the NOLOGGING option, ora-26040nologging

Simulate ORA-26040: Data block was loaded using the NOLOGGING option, ora-26040nologging

We know that setting the nologging option can speed up the execution of some oracle operations, which is very useful when executing some maintenance tasks, but this option is also very dangerous. If it is not used properly, may cause a ORA-26040 error in the database.

First, construct the use environment,

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

We used create table table_name nologging as select * from user_tables to create nolog and nolog1 tables. Before creating a table, use rman to back up the entire database. After the table is created, close the database and use the backup to restore it. The result is as follows:

[Oraten @ yue bdump] $ rman target/Recovery Manager: Release 10.2.0.5.0-Production on Thursday November 13 17:21:02 2014 Copyright (c) 1982,200 7, 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.31 m disk 00:00:41 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 09:34:04/home/app/oraten/oradata/oraten/system01.dbf 2 full 1276159 09:34:04/home/app/oraten/oradata/oraten/undotbs01.dbf 3 Full 1276159 09:34:04/home/app/oraten/oradata/oraten/sysaux01.dbf 4 Full 1276159 09:34:04/home /app/oraten/oradata/oraten/users01.dbf 5 Full 1276159 09:34:04/home/app/oraten/oradata/oraten/logging01.dbfBS Key Type LV Size Device Type Elapsed Time Completion Time ------------- ---------- ----------- ------------ ------------------- 98 Full 6.86 m disk 00:00:02 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 defined ded: Ckp SCN: 1276545 Ckp time: 09:34:50 SPFILE defined ded: Modification time: 2014-11-12 09: 14: 00 RMAN> restore database; Starting restore at 17: 21: 19 allocated 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/parse datafile 00003 to/home /app/oraten/oradata/oraten/export datafile 00004 to/home/app/oraten/oradata/oraten/users01.dbfrestoring datafile 00005 to/home/app/oraten/oradata/oraten/Export 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 1 piece handle =/home/app/oraten/flash_recovery_area/ORATEN/backupset/2014_11_12/snapshot _. bkp tag = TAG20141112T093404channel ORA_DISK_1: restore complete, elapsed time: 00: 00: 25 Finished restore at 2014-11-13 17: 21: 45 RMAN> recover database; Starting recover at 2014-11-13 17: 21: 50 using 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/release 7m2 _. 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_000054_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_000053_b65gj 7m2 _. arc thread = 1 sequence = 53 media recovery complete, elapsed time: 00: 00: 14 Finished recover at 2014-11-13 17: 22: 05 RMAN> alter database open; database openedRMAN>

The alert file contains the following content:

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

We can see from the above that everything is normal and the database is successfully restored,:

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

The database reports an error. It seems that the database is not necessarily normal if the database is recovered successfully.
Let's take a look at the dump content of the log,

[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

It seems that there is a record of nologging in the log. When rman is restored, some blocks will be set as logical corruption according to the record.