模拟ORA-26040: Data block was loaded using the NOLOGGING option

最新推荐文章于 2024-07-30 08:30:00 发布

ezbit

最新推荐文章于 2024-07-30 08:30:00 发布

阅读量2.2k

点赞数 1

CC 4.0 BY-SA版权

分类专栏： Oracle Database 文章标签： oracle block corrupt.loggin

本文链接：https://blog.youkuaiyun.com/yidian815/article/details/41082751

Oracle Database 专栏收录该内容

50 篇文章

订阅专栏

我们知道通过设置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

archive 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_.arc
archive 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_.arc
archive 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_.arc
archive log filename=/home/app/oraten/flash_recovery_area/ORATEN/archivelog/2014_11_12/o1_mf_1_53_b65gj7m2_.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 opened

RMAN>

alert 文件中的内容如下：

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

从上面我们看出，一切正常，数据库成功恢复了，但是：

SQL> conn logging/logging
Connected.
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 oad
Direct Loader invalidate block range redo entry
Direct Loader invalidate block range redo entry
Direct Loader invalidate block range redo entry
Direct Loader invalidate block range redo entry
Direct Loader invalidate block range redo entry
Direct Loader invalidate block range redo entry
Direct Loader invalidate block range redo entry
Direct Loader invalidate block range redo entry
Direct Loader invalidate block range redo entry
Direct Loader invalidate block range redo entry
Direct Loader invalidate block range redo entry
Direct Loader invalidate block range redo entry
Direct Loader invalidate block range redo entry
Direct Loader invalidate block range redo entry
Direct Loader invalidate block range redo entry

看来日志中对nologging是有记录的，在rman恢复时，会根据记录将某些块设置为逻辑损坏。