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

http://blog.sina.com.cn/u/5438469990

首页博文目录关于我

个人资料

微博

加好友发纸条

写留言加关注

博客等级：
博客积分：

博客访问：
关注人气：
获赠金笔：0支
赠出金笔：0支
荣誉徽章：

正文字体大小：大中小

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

(2016-03-08 18:10:15)

分类： dataguard

利用主库的增量备份：

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

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

96 NO

97 NO

98 NO

99 NO

SEQUENCE# APPLIED

---------- ---------

99 NO

100 NO

101 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

阅读┊ 收藏 ┊ 喜欢 ▼ ┊打印┊举报/Report

前一篇：12C 列存详解2

后一篇：Oracle Data Guard 主库归档文件删除策略

新浪BLOG意见反馈留言板　欢迎批评指正