利用RMAN增量备份(Incremental Backup)修复standby 环境中的nologging corupted blocks
有时为了提升SQL执行速度或减少redo而使用NOLOGGING选项， 或者在segment 级使用NOLOGGING属性， 将使用最少的信息记录到online redo logfile，但是对于DataGuard环境是基于redo应用，所以这也是在DATAGUARD配置时需要在数据库级启用FORCE_LOGGING原因，覆盖会话级的nologging操作。如果缺少了日志必要的信息，在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的方法(生产端无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;
微信扫一扫，打赏作者吧～
对不起，这篇文章暂时关闭评论。