请选择 进入手机版 | 继续访问电脑版
Oracle技术专区 收藏本版 已有121 收藏本版 发新帖

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

[复制链接]
发表于 2016-3-8 18:09:36 | 显示全部楼层 |阅读模式
利用主库的增量备份:
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
回复

使用道具 举报

您需要登录后才可以回帖 登录 | 注册

本版积分规则

甲骨论是什么| 甲骨论( 京ICP备12024223号 )   |

Copyright © 2016 jiagulun.   All Rights Reserved.

快速回复 返回顶部 返回列表