错误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)

Pixelfox AI

多功能AI图像编辑工具

2108

查看详情

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