错误ORA-26040: Data block was loaded using the NOLOGGING optio

我们知道通过设置nologging选项，可以加快oracle的某些操作的执行速度，这在执行某些维护任务时是非常有用的，但是该选项也很危险

我们知道通过设置nologging选项，可以加快Oracle的某些操作的执行速度，这在执行某些维护任务时是非常有用的，但是该选项也很危险，如果使用不当，就可能导致数据库发生ORA-26040错误。

首先，构造使用环境，

SQL> select tablespace_name,logging,force_logging from dba_tablespaces;

TABLESPACE_NAME LOGGING FOR
------------------------------ --------- ---
SYSTEM LOGGING NO
UNDOTBS1 LOGGING NO
SYSAUX LOGGING NO
TEMP NOLOGGING NO
USERS LOGGING NO
LOGGING LOGGING NO

6 rows selected.

SQL> show user
USER is "LOGGING"
SQL> select table_name,logging from user_tables;

TABLE_NAME LOG
------------------------------ ---
SOURCE YES
NOLOG NO
NOLOG1 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 2014

Copyright (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 catalog

List 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.dbf

BS 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:00

RMAN> restore database;

Starting restore at 2014-11-13 17:21:19
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=155 devtype=DISK

channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /home/app/oraten/oradata/oraten/system01.dbf
restoring datafile 00002 to /home/app/oraten/oradata/oraten/undotbs01.dbf
restoring datafile 00003 to /home/app/oraten/oradata/oraten/sysaux01.dbf
restoring datafile 00004 to /home/app/oraten/oradata/oraten/users01.dbf
restoring datafile 00005 to /home/app/oraten/oradata/oraten/logging01.dbf
channel ORA_DISK_1: reading from backup piece /home/app/oraten/flash_recovery_area/ORATEN/backupset/2014_11_12/o1_mf_nnndf_TAG20141112T093404_b65g8fc3_.bkp
channel ORA_DISK_1: restored backup piece 1
piece handle=/home/app/oraten/flash_recovery_area/ORATEN/backupset/2014_11_12/o1_mf_nnndf_TAG20141112T093404_b65g8fc3_.bkp tag=TAG20141112T093404
channel 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_1

starting media recovery