首页 » Cloud, ORACLE 9i-23ai » 利用RMAN增量备份(Incremental Backup)修复standby 环境中的nologging corupted blocks

利用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;
打赏

,

对不起,这篇文章暂时关闭评论。