在DATAGUARD配置时如果没有在数据库级启用FORCE_LOGGING，来覆盖会话级的nologging操作。 当为了提升SQL执行速度或减少redo而使用NOLOGGING选项， 或者在segment级使用了NOLOGGING属性, 会导致缺少日志必要的信息，造成standby datafile block corruption
在DG RECOVERY介质恢复期间会将受影响的块标记为已损坏, 查询V$DATABASE_BLOCK_CORRUPTION.CORRUPTION_TYPE值为NOLOGGING。当使用ADG打开备用数据库尝试读取范围标记为“UNRECOVERABLE”的块，会看到类似于以下内容的错误消息：
ORA-01578: ORACLE data block corrupted (file # 4, block # 12521) ORA-01110: data file 4: ‘/u01/oracle/dbs/stdby/tbs_anbob_1.f’ ORA-26040: Data block was loaded using the NOLOGGING option 并不是所有SQL都可以NOLOGGING, 以下是可以加nologging选项的部分操作： direct load (SQL*Loader) direct load INSERT (using APPEND hint) CREATE TABLE … AS SELECT CREATE INDEX ALTER TABLE … MOVE PARTITION ALTER TABLE … SPLIT PARTITION ALTER INDEX … SPLIT PARTITION ALTER INDEX … REBUILD ALTER INDEX … REBUILD PARTITION INSERT, UPDATE, and DELETE on LOBs in NOCACHE NOLOGGING mode stored out of line
修复DataGuard 环境中standby端Nologging corrupted block的方法(primary无corrupted block).
# on Standby site
SQL> select * from V$DATABASE_BLOCK_CORRUPTION;
FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO
--------------- --------------- --------------- ------------------ ---------
940 246920 504 16459119164949 NOLOGGING
940 247432 504 16459119168526 NOLOGGING
940 247938 510 16459119183841 NOLOGGING
940 248450 510 16459119197461 NOLOGGING
940 248962 510 16459119210040 NOLOGGING
940 249474 510 16459119225549 NOLOGGING
384 648328 504 16459209848221 NOLOGGING
384 736898 510 16459210460008 NOLOGGING
384 961154 510 16459210975611 NOLOGGING
384 1185928 504 16459211157667 NOLOGGING
429 189576 504 16459208233811 NOLOGGING
429 222344 504 16459208252146 NOLOGGING
429 463499 501 16459208958630 NOLOGGING
429 469640 504 16459209079246 NOLOGGING
...
...
Note: this view updated by RMAN validate datafile or database.
SQL> select count(*) from V$DATABASE_BLOCK_CORRUPTION;
COUNT(*)
---------------
21138
SQL> select count(distinct(file#)) from V$DATABASE_BLOCK_CORRUPTION;
COUNT(DISTINCT(FILE#))
----------------------
247
SQL> SELECT FILE#,COUNT(*) CNT FROM V$DATABASE_BLOCK_CORRUPTION GROUP BY FILE# ORDER BY 2 DESC;
FILE# CNT
--------------- ---------------
979 398
537 295
543 290
542 289
875 277
817 274
...
...
SQL> select file#,first_nonlogged_scn from v$datafile where first_nonlogged_scn>0;
FILE# FIRST_NONLOGGED_SCN
--------------- -------------------
19 16459116809857
21 16459117211145
22 16459120367311
23 16459119165331
25 16459118695285
27 16459129015821
29 16459119060366
...
...
修复方法一： 少量的数据文件，做单个数据文件增量
# ON Standby site
1, 停止redo应用
SQL> alter database recover managed standby database cancel;
Database altered.
2， 使用数据文件offline, 保证在增量备份期间不会有redo应用跳过这些块坏。
SQL> alter database datafile 979 offline for drop;
Database altered.
Note:
offline [for] drop 不会真正的删除文件，只是标记状态recover。
3, 启动redo 应用
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT from session;
# ON Primary site
4， 基于数据文件的FIRST_NONLOGGED_SCN做数据文件SCN的增量备份
rman target /
RMAN> backup incremental from scn 16459129012653 datafile 979 format '/interface/backups/inc_forstdby_%U.bak' tag 'for standby nologging';
5， 并把备份集文件传送至STANDBY 主机，如SCP
# ON Standby site
6, 把备份集注册进备库
RMAN> catalog start with '/interface/inc_forstdby_kku7et7c_1_1.bak';
searching for all files that match the pattern /interface/inc_forstdby_kku7et7c_1_1.bak
List of Files Unknown to the Database
=====================================
File Name: /interface/inc_forstdby_kku7et7c_1_1.bak
Do you really want to catalog the above files (enter YES or NO)? yes
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: /interface/inc_forstdby_kku7et7c_1_1.bak
-- or
RMAN> CATALOG START WITH '/interface/inc_forstdby_;
7, 停止redo应用
SQL> alter database recover managed standby database cancel;
Database altered.
8, online刚才offline的数据文件， 需要停止所有open read-only的standby实例,否则会有ORA-01113&ORA-01110和ORA-01138错误,切换到mount状态
SQL> shutdown immediate;
SQL> startup mount;
SQL> ALTER DATABASE DATAFILE 979 ONLINE;
Database altered.
9 ，备库应用该文件增量备份
RMAN> RECOVER DATAFILE 979 NOREDO;
Note: noredo 表示不应用redo log, 因为恢复的是之前的增量备份当时redo已不存在也不需要。
10， 验证nologging change已经不存在,确认无记录返回
SQL> SELECT FILE#, FIRST_NONLOGGED_SCN FROM V$DATAFILE WHERE FIRST_NONLOGGED_SCN > 0 and file#=979;
11， 启动日志应用
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT from session;
12， 删除原来的增量备份集
RMAN> DELETE BACKUP TAG 'for standby nologging';
修复方法二： 大量的数据文件，做一次数据库级的增量
# on Standby site
1， 从V$DATAFILE 确认最小的 FIRST_NONLOGGED_SCN
SQL> SELECT MIN(FIRST_NONLOGGED_SCN) FROM V$DATAFILE WHERE FIRST_NONLOGGED_SCN>0;
2, 停止REDO应用
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
# on Primary site
3, 在主库执行基于最小FIRST_NONLOGGED_SCN的增量备份
RMAN> BACKUP INCREMENTAL FROM SCN 16459116809857 DATABASE FORMAT '/interface/inc_forstdby_%U' tag 'FOR STANDBY';
4, 同样传输备份集到stanby 主机
# on Standby site
5 ，把备份集注册进备库
RMAN> CATALOG START WITH '/interface/inc_forstdby_;
6， 应用增量备份
RMAN> RECOVER DATABASE NOREDO;
7， 验证
SQL> SELECT FILE#, FIRST_NONLOGGED_SCN FROM V$DATAFILE WHERE FIRST_NONLOGGED_SCN > 0;
8， 启动日志应用
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT from session;
9， 删除增量备份集
RMAN> DELETE BACKUP TAG 'for standby nologging';
修复方法三： 在12.2 版本中修复非常容易,no logging block列表已发送到备用数据库，记录在standby控制文件中，我们可以从v$nonlogged_block列出它们，可以使用一个简单的命令恢复。
DGMGRL> edit database orclb set state=apply-off;
or
SQL> alter database recover managed standby database cancel;
RMAN> report unrecoverable;
RMAN> recover database nonlogged block;