主库归档丢失状态 gap 的快速解决办法

李波Joker

利用主库的增量备份：
1，select sequence#,applied from v$archived_log order by sequence# ; 查看主备库的日志应用状态。
主库的正常状态：
SQL> l
  1* select sequence#,applied from v$archived_log order by sequence#
      51 NO
      51 YES
      52 NO
      52 YES
      53 YES
      53 NO
      54 YES
      54 NO
      55 YES
      55 NO

SEQUENCE# APPLIED
---------- ---------
      56 NO
      56 NO

90 rows selected.
SQL> select OPEN_MODE, DATABASE_ROLE, SWITCHOVER_STATUS, FORCE_LOGGING, DATAGUARD_BROKER, GUARD_STATUS from v$database;

OPEN_MODE          DATABASE_ROLE SWITCHOVER_STATUS FOR DATAGUAR GUARD_S
-------------------- ---------------- -------------------- --- -------- -------
READ WRITE          PRIMARY       TO STANDBY          YES DISABLED NONE
备库的正常状态：
SQL> select sequence#,applied from v$archived_log order by sequence# ;

SEQUENCE# APPLIED
---------- ---------
      47 YES
      48 YES
      49 YES
      50 YES
      51 YES
      52 YES
      53 YES
      54 YES
      55 YES
      56 IN-MEMORY

10 rows selected.
SQL> select OPEN_MODE, DATABASE_ROLE, SWITCHOVER_STATUS, FORCE_LOGGING, DATAGUARD_BROKER, GUARD_STATUS from v$database;

OPEN_MODE          DATABASE_ROLE SWITCHOVER_STATUS FOR DATAGUAR GUARD_S
-------------------- ---------------- -------------------- --- -------- -------
READ ONLY WITH APPLY PHYSICAL STANDBY NOT ALLOWED       YES DISABLED NONE

模拟故障：

alter system set log_archive_dest_state_2 = 'defer';

然后 rm  归档

启动主库传送归档：
alter system set log_archive_dest_state_2 = 'enable';

现在查看主备库的状态：

SQL> l
  1* select sequence#,applied from v$archived_log order by sequence#

      81 YES
      82 YES
      83 YES
      84 YES
      95 NO
      96 NO
      97 NO
      98 NO
      99 NO
   100 NO

SEQUENCE# APPLIED
---------- ---------
   101 NO
   102 NO

46 rows selected.

主库：

SEQUENCE# APPLIED
---------- ---------
      83 NO
      84 YES
      84 NO
      85 NO
      86 NO
      87 NO
      88 NO
      89 NO
      90 NO
      91 NO
      92 NO

SEQUENCE# APPLIED
---------- ---------
      93 NO
      94 NO
      95 NO
      95 NO
      96 NO
      96 NO
      97 NO
      97 NO
      98 NO
      98 NO
      99 NO

SEQUENCE# APPLIED
---------- ---------
      99 NO
   100 NO
   100 NO
   101 NO
   101 NO
   102 NO
   102 NO

现在看到我们的主备库已经不能正常同步了：

SQL> select OPEN_MODE, DATABASE_ROLE, SWITCHOVER_STATUS, FORCE_LOGGING, DATAGUARD_BROKER, GUARD_STATUS from v$database;

OPEN_MODE          DATABASE_ROLE SWITCHOVER_STATUS FOR DATAGUAR GUARD_S
-------------------- ---------------- -------------------- --- -------- -------
READ WRITE          PRIMARY       UNRESOLVABLE GAP    YES DISABLED NONE

解决问题：
备库：
SQL>  alter database recover managed standby database cancel;

Database altered.

查询备库的FROM SCN 值

注意这里的SCN 是我们RMAN 增量备份的起点SCN，对不同的情况，使用不同的方法查询。

如果是归档缺失，在备库使用如下查询
SQL> SELECT CURRENT_SCN FROM V$DATABASE;

CURRENT_SCN
-----------
900109

在主库执行备份：
backup incremental from scn 900109 database format '/u01/app/oracle/oradata/forstandby_%u' tag 'forstandby';

-rw-r----- 1 oracle oinstall 10092544 Mar  8 09:45 forstandby_13qvv24u
-rw-r----- 1 oracle oinstall 868352 Mar  8 09:44 forstandby_12qvv24n

将备份scp到备库：
[oracle@edsir4p1-bsr oradata]$ scp forstandby_* edsir1p8.us.oracle.com:/u01/app/oracle/oradata/
oracle@edsir1p8.us.oracle.com's password:
forstandby_12qvv24n                         100%  848KB 848.0KB/s 00:00
forstandby_13qvv24u                         100% 9856KB 9.6MB/s 00:00

在备库查看备份集备份文件的状态：

RMAN> report schema;

using target database control file instead of recovery catalog
RMAN-06139: WARNING: control file is not current for REPORT SCHEMA
Report of database schema for database with db_unique_name BSR_ST

List of Permanent Datafiles
===========================
File Size(MB) Tablespace          RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 670    SYSTEM             ***    /u01/app/oracle/oradata/bsr/system01.dbf
2 450    SYSAUX             ***    /u01/app/oracle/oradata/bsr/sysaux01.dbf
3 30    UNDOTBS1          ***    /u01/app/oracle/oradata/bsr/undotbs01.dbf
4 5       USERS             ***    /u01/app/oracle/oradata/bsr/users01.dbf

List of Temporary Files
=======================
File Size(MB) Tablespace          Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 20    TEMP                32767    /u01/app/oracle/oradata/bsr/temp01.dbf

还原备库控制文件并执行恢复操作

RMAN> STARTUP FORCE NOMOUNT;
RMAN> RESTORE STANDBY CONTROLFILE FROM '/u01/app/oracle/oradata/forstandby_13qvv24u';
RMAN> ALTER DATABASE MOUNT;
RMAN>list backup summary
RMAN> CATALOG START WITH '/u01/app/oracle/oradata';
RMAN> RECOVER DATABASE NOREDO;

备库查看：
SCN 确实是增长了
SQL> select current_scn from v$database;

CURRENT_SCN
-----------
   903720

SQL> alter database recover managed standby database using current logfile disconnect from session;

Database altered.

验证DG 同步，备库。

   102 NO
   103 YES
   104 YES
   105 YES
   106 IN-MEMORY

77 rows selected.

SQL> l
  1* select sequence#,applied from v$archived_log order by sequence#

主库的状态也已经恢复正常：

SQL> select OPEN_MODE, DATABASE_ROLE, SWITCHOVER_STATUS, FORCE_LOGGING, DATAGUARD_BROKER, GUARD_STATUS from v$database;

OPEN_MODE          DATABASE_ROLE SWITCHOVER_STATUS FOR DATAGUAR GUARD_S
-------------------- ---------------- -------------------- --- -------- -------
READ WRITE          PRIMARY       TO STANDBY          YES DISABLED NONE

总结：
1. 如果主库出新归档日志gap
常规方法是duplicate
这种方法会给主库带来IO 影响
所以，我们用一个很巧的方法，RMAN 增量备份

alert  日志也正常了：
Tue Mar 08 10:07:14 2016
ALTER SYSTEM ARCHIVE LOG
Tue Mar 08 10:07:14 2016
Thread 1 cannot allocate new log, sequence 110
Checkpoint not complete
  Current log# 1 seq# 109 mem# 0: /u01/app/oracle/oradata/bsr/redo01.log
Thread 1 advanced to log sequence 110 (LGWR switch)
  Current log# 2 seq# 110 mem# 0: /u01/app/oracle/oradata/bsr/redo02.log
Archived Log entry 186 added for thread 1 sequence 109 ID 0xfd6cbb63 dest 1:
Tue Mar 08 10:07:17 2016
LNS: Standby redo logfile selected for thread 1 sequence 110 for destination LOG_ARCHIVE_DEST_2

图文精华

主库归档丢失状态 gap 的快速解决办法