RMAN 修复主库 nologging 操作导致物理备库的坏块

Rolling a Standby Forward using an RMAN Incremental Backup To Fix The Nologging Changes (文档 ID 958181.1)

情景： 物理备库查询遇到 ORA-01578 报错

ORA-01578: ORACLE data block corrupted (file # 1, block # 2521)
ORA-01110: data file 1: '/vobs/oracle/dbs/stdby/tbs_1.f'
ORA-26040: Data block was loaded using the NOLOGGING option

1. 物理备库确认nologging 起点 SCN 

SQL> SELECT FILE#, FIRST_NONLOGGED_SCN FROM V$DATAFILE WHERE FIRST_NONLOGGED_SCN > 0;

FILE#      FIRST_NONLOGGED_SCN
---------- -------------------
         4              225979
         5              230184

2.停止日志应用

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

3 备库OFFLINE 掉出现nologging坏块的数据文件

SQL> ALTER DATABASE DATAFILE 4 OFFLINE FOR DROP;
SQL> ALTER DATABASE DATAFILE 5 OFFLINE FOR DROP;

4. 备库再开启日志应用

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;

5. 主库做个基于SCN 的增量备份（SCN 由步骤1查询所得）

RMAN> BACKUP INCREMENTAL FROM SCN 225979 DATAFILE 4 FORMAT '/tmp/ForStandby_%U' TAG 'FOR STANDBY';
RMAN> BACKUP INCREMENTAL FROM SCN 230184 DATAFILE 5 FORMAT '/tmp/ForStandby_%U' TAG 'FOR STANDBY';

6.将主库的增量备份传输到备库

scp /tmp/ForStandby_* standby:/tmp

7.使用rman 将备份注册到备库的控制文件中

RMAN> CATALOG START WITH '/tmp/ForStandby_';

8.备库停止日志应用

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

9.将出现nologging 坏块的数据文件ONLINE

SQL> ALTER DATABASE DATAFILE 4 ONLINE;
SQL> ALTER DATABASE DATAFILE 5 ONLINE;

10.恢复出现nologging坏块的数据文件

RMAN> RECOVER DATAFILE 4, 5 NOREDO;

11.查询v$DATAFILE 视图，确认 nologging 的坏块已经被修复（下面查询结果返回应该是 0 行）

SQL> SELECT FILE#, FIRST_NONLOGGED_SCN FROM V$DATAFILE WHERE FIRST_NONLOGGED_SCN > 0;

12.重建standby控制文件

shutdown immediate; --关闭standby 

sqlplus sys/sys_password@prim_db as sysdba  --sqlplus 连接到主库

alter database create standby controlfile as '<File-Specification>'; --创建standby 控制文件

startup;  --开启standby数据库

13. 删除控制文件中记录的增量备份

RMAN> DELETE BACKUP TAG 'FOR STANDBY';

14.删除磁盘上的增量备份文件

rm /tmp/ForStandby_*

15.开启日志应用

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;

16.为了防止再次遇到nologging坏块，主库开启force logging

SQL> alter database force logging;

SQL> select force_logging from v$database;

FOR
---
YES