nologging操作的介绍
当DML操作以NOLOGGING方式执行时，或者SQLLoader以UNRECOVERABLE方式进行直接路径加载时，会禁止生成重做日志或者只会生成很少的日志信息，这些可以加快这些操作的速度。但在这些情况下，由于重做日志中缺乏记录，DML修改不会被捕获。如果这些联机重做日志或归档日志被用来恢复数据文件，那么Oracle会将对应的数据块标志为无效（Soft Corrupt），而且下一次访问这些数据块时，会报ORA-01578和ORA-26040错误。为了保证正确记录表修改日志，可以在表空间级别或数据库级别设置FORCE LOGGING。一旦设置，Oracle就可以自动为所有的NOLOGGING操作和UNRECOVERABLE操作生成重做日志信息。
查询当前数据的FORCE LOGGING状态：
SQL> select force_logging from v$database; FORCE_LOGGING -------------------------------------------------------------------------------- NO
在主库上执行一个nologging的操作
归档数据库中的不可恢复操作（无重做日志），需要及时发现，及时备份，避免介质恢复时产生逻辑坏块。
执行一个nologging操作
SQL> create table t nologging as select object_id,object_name from dba_objects where rownum<=10; Table created. SQL> set linesize 200 col name form a50 select name,unrecoverable_change#,to_char(unrecoverable_time-1,'yyyy/MM/dd hh24:mi:dd') unrecoverable_time from v$datafile; SQL> SQL> SQL> NAME UNRECOVERABLE_CHANGE# UNRECOVERABLE_TIME -------------------------------------------------- --------------------- ------------------- +DATA/orcl/datafile/system.256.1028903219 0 +DATA/orcl/datafile/sysaux.257.1028903219 0 +DATA/orcl/datafile/undotbs1.258.1028903219 0 +DATA/orcl/datafile/users.259.1028903219 0 +DATA/orcl/datafile/example.269.1028903297 4534712 2020/05/11 15:59:11
使用v$datafile.unrecoverable_change#查看不可恢复操作scn；如果数据库为非归档模式，该列不更新。
在从库上查询nologging的对象
SQL> select * from t; select * from t * ERROR at line 1: ORA-01578: ORACLE data block corrupted (file # 1, block # 91041) ORA-01110: data file 1: '/u01/app/oracle/oradata/orcl/system01.dbf' ORA-26040: Data block was loaded using the NOLOGGING option
检查坏块
RMAN> backup validate tablespace system; Starting backup at 13-MAY-20 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=134 device type=DISK channel ORA_DISK_1: starting full datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set input datafile file number=00001 name=/u01/app/oracle/oradata/orcl/system01.dbf channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03 List of Datafiles ================= File Status Marked Corrupt Empty Blocks Blocks Examined High SCN ---- ------ -------------- ------------ --------------- ---------- 1 OK 1 14838 94721 988324 File Name: /u01/app/oracle/oradata/orcl/system01.dbf Block Type Blocks Failing Blocks Processed ---------- -------------- ---------------- Data 0 63193 Index 0 13038 Other 0 3651 channel ORA_DISK_1: starting full datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set including current control file in backup set including current SPFILE in backup set channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01 List of Control File and SPFILE =============================== File Type Status Blocks Failing Blocks Examined ------------ ------ -------------- --------------- SPFILE OK 0 2 Control File OK 0 594 Finished backup at 13-MAY-20 [oracle@oracle11g-2 ~]$ dbv file=/u01/app/oracle/oradata/orcl/system01.dbf DBVERIFY: Release 11.2.0.4.0 - Production on Wed May 13 11:10:39 2020 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. DBVERIFY - Verification starting : FILE = /u01/app/oracle/oradata/orcl/system01.dbf DBV-00201: Block, DBA 4285345, marked corrupt for invalid redo application DBVERIFY - Verification complete Total Pages Examined : 94720 Total Pages Processed (Data) : 63193 Total Pages Failing (Data) : 0 Total Pages Processed (Index): 13038 Total Pages Failing (Index): 0 Total Pages Processed (Other): 3651 Total Pages Processed (Seg) : 1 Total Pages Failing (Seg) : 0 Total Pages Empty : 14838 Total Pages Marked Corrupt : 1 Total Pages Influx : 0 Total Pages Encrypted : 0 Highest block SCN : 988324 (0.988324)
用rman和dbv均发现一个坏块
检查从库的日志：
/u01/app/oracle/diag/rdbms/orcl/orcl/trace/alert_orcl.log Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_3332.trc (incident=9753): ORA-01578: ORACLE data block corrupted (file # 1, block # 91041) ORA-01110: data file 1: '/u01/app/oracle/oradata/orcl/system01.dbf' ORA-26040: Data block was loaded using the NOLOGGING option Incident details in: /u01/app/oracle/diag/rdbms/orcl/orcl/incident/incdir_9753/orcl_ora_3332_i9753.trc Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_3332.trc (incident=9754): ORA-01578: ORACLE data block corrupted (file # 1, block # 91041) ORA-01110: data file 1: '/u01/app/oracle/oradata/orcl/system01.dbf' ORA-26040: Data block was loaded using the NOLOGGING option Incident details in: /u01/app/oracle/diag/rdbms/orcl/orcl/incident/incdir_9754/orcl_ora_3332_i9754.trc Wed May 13 10:55:34 2020
检查坏块视图
SQL> select * from v$database_block_corruption ; FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO ---------- ---------- ---------- ------------------ --------- 1 91041 1 987602 NOLOGGING SQL>
在迪备的日志中没有找到ora错误！ grep ORA- /var/log/dbackup3/agent.log
rman 的 report unrecoverable;
RMAN> report unrecoverable 2> ; using target database control file instead of recovery catalog Report of files that need backup due to unrecoverable operations File Type of Backup Required Name ---- ----------------------- ----------------------------------- 13 full /u01/app/oracle/oradata/orcl/soe10.dbf 14 full /u01/app/oracle/oradata/orcl/soe11.dbf 17 full /u01/app/oracle/oradata/orcl/soe14 18 full /u01/app/oracle/oradata/orcl/soe15 RMAN> backup tablespace soe10; Starting backup at May 19 2020 15:47:52 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=1132 device type=DISK channel ORA_DISK_1: starting full datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set input datafile file number=00013 name=/u01/app/oracle/oradata/orcl/soe10.dbf channel ORA_DISK_1: starting piece 1 at May 19 2020 15:47:52 channel ORA_DISK_1: finished piece 1 at May 19 2020 15:49:47 piece handle=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/01v0jkp8_1_1 tag=TAG20200519T154752 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:01:55 Finished backup at May 19 2020 15:49:47 RMAN> report unrecoverable 2> ; Report of files that need backup due to unrecoverable operations File Type of Backup Required Name ---- ----------------------- ----------------------------------- 14 full /u01/app/oracle/oradata/orcl/soe11.dbf 17 full /u01/app/oracle/oradata/orcl/soe14 18 full /u01/app/oracle/oradata/orcl/soe15 RMAN> quit
