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
