Data Guard出现gap sequence修复实例

环境：Oracle 11.2.0.4 Data Guard

故障现象：
客户在备库告警日志中发现GAP sequence提示信息：

Mon Nov 21 09:53:29 2016 Media Recovery Waiting for thread 1 sequence 12034 Fetching gap sequence in thread 1, gap sequence 12034-12078 Mon Nov 21 09:55:20 2016 FAL[client]: Failed to request gap sequence GAP - thread 1 sequence 12034-12078 DBID 3493955325 branch 881855745 FAL[client]: All defined FAL servers have been attempted. ------------------------------------------------------------ Check that the CONTROL_FILE_RECORD_KEEP_TIME initialization parameter is defined to a value that's sufficiently large enough to maintain adequate log switch information to resolve archivelog gaps. ------------------------------------------------------------

修复过程：

1.查询备库SCN

2.确定主库是否添加数据文件

3.备库停止日志应用

4.主库增量备份并传输到备库上

5.备库上进行恢复

6.主库上创建standby controlfile文件并传输到备库

7.备库恢复控制文件

8.清空备库日志组

9.备库重设flashback

10.备库重新接收并应用日志

11.备库重新开启read only模式

12.验证修复是否成功

Reference

1.查询备库SCN

查询备库当前SCN，如果人为造成控制文件、数据文件、数据文件头的SCN不一致，那么需要根据日志中gap的起始sequence#找出对应的SCN。可以查看文末reference中惜分飞的博客评论部分。

SQL> col CURRENT_SCN for 999999999999999999 SQL> SELECT CURRENT_SCN FROM V$DATABASE; CURRENT_SCN ------------------- 11906842766974 2.确定主库是否添加数据文件（这里没有） select FILE#,name from v$datafile where CREATION_CHANGE#> =11906842766974;

确定主库在这个scn之后是否有添加数据文件，如果添加文件，需要手工在备库添加。本次没有遇到。

3.备库停止日志应用 SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL; 4.主库增量备份并传输到备库上

主库进行增量备份然后传输到备库上

RMAN> backup as compressed backupset INCREMENTAL from scn 11906842766974 database format '/backup/dumpfile/%u.bak'; $ scp *.bak 192.168.56.158:/oradata/rman/ 5.备库上进行恢复 RMAN> CATALOG START WITH '/oradata/rman/'; --注意如果此时库是read only则需要置换为mount后再进行recover操作； RMAN> RECOVER DATABASE NOREDO; 6.主库上创建standby controlfile文件并传输到备库

主库上创建standby controlfile文件并传输到备库

RMAN> BACKUP CURRENT CONTROLFILE FOR STANDBY FORMAT '/home/oracle/std_ctl.bak'; [oracle@localhost ~]$ scp std_ctl.bak 192.168.56.158:/home/oracle/ 7.备库恢复控制文件

备库关库，启动到nomount状态后恢复控制文件，最后启动到mount状态

RMAN> shutdown; RMAN> STARTUP NOMOUNT; RMAN> RESTORE STANDBY CONTROLFILE FROM '/home/oracle/std_ctl.bak'; RMAN> alter database mount; 8.清空备库日志组（这里不用）

本次DG中使用了standby log模式，不需要此步骤。

SQL> ALTER DATABASE CLEAR LOGFILE GROUP 1;

如果配置了physical standby redo log则不需该步骤；

如果没有采用standby log模式，有几组需要清空几组。

9.备库重设flashback（根据实际情况选做，这里本身就没开启）

备库重设flashback（根据实际情况选做，这里DG环境备库本身就没开启，所以不用操作）

SQL> ALTER DATABASE FLASHBACK OFF; SQL> ALTER DATABASE FLASHBACK ON; 10.备库重新接收并应用日志

备库重新接收并应用日志：

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE using current logfile DISCONNECT FROM SESSION;

恢复过程备库最后的日志（最后需要出现Media Recovery Waiting for 字样）：

Mon Nov 21 17:17:05 2016 Managed Standby Recovery starting Real Time Apply Parallel Media Recovery started with 32 slaves Waiting for all non-current ORLs to be archived... All non-current ORLs have been archived. Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE using current logfile DISCONNECT FROM SESSION Media Recovery Log /oradata/arch/1_12131_881855745.dbf Mon Nov 21 17:18:59 2016 Media Recovery Log /oradata/arch/1_12132_881855745.dbf Mon Nov 21 17:20:44 2016 Media Recovery Log /oradata/arch/1_12133_881855745.dbf Mon Nov 21 17:21:02 2016 Media Recovery Log /oradata/arch/1_12134_881855745.dbf Mon Nov 21 17:22:22 2016 Media Recovery Waiting for thread 1 sequence 12135 (in transit) 11.备库重新开启read only模式