主库归档丢失状态 gap 的快速解决办法
利用主库的增量备份：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 Mar8 09:45 forstandby_13qvv24u
-rw-r----- 1 oracle oinstall 868352 Mar8 09:44 forstandby_12qvv24n
将备份scp到备库：
$ 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
页:
[1]