ORACLE DATAGUARD的主备库同步，主要是依靠日志传输到备库，备库应用日志或归档来实现。当主、备库间日志传输出现GAP，备库将不再与主库同步。因此需对日志传输状态进行监控，确保主、备库间日志没有GAP，或发现GAP后及时处理。除了在告警日志中查看日志同步情况外，还可以通过查看相关视图来对日志传输状态进行监控。
1.主、备库查看当前日志状况
#主庫
[oracle@DB01 ~]$ sql / as sysdba
SQL> set sqlformat ansiconsole
SQL> select sequence#,status from v$log;
SEQUENCE# STATUS
4137 CURRENT
4135 INACTIVE
4136 INACTIVE
#備庫(12c)
[oracle@DB02 ~]$ sql / as sysdba
SQL> set sqlformat ansiconsole
SQL> select sequence#,status from v$log;
SEQUENCE# STATUS
0 UNUSED
0 UNUSED
0 UNUSED
#備庫(11g)
SQL> select sequence#,status from v$log;
SEQUENCE# STATUS
4137 CLEARING_CURRENT
4135 CLEARING
4136 CLEARING
- 备库查看RFS接收日志和MRP应用日志同步主库情况
SQL> select process, sequence#, status, delay_mins from v$managed_standby;
PROCESS SEQUENCE# STATUS DELAY_MINS
ARCH 3923 CLOSING 0
DGRD 0 ALLOCATED 0
DGRD 0 ALLOCATED 0
ARCH 4136 CLOSING 0
ARCH 4038 CLOSING 0
ARCH 4137 CLOSING 0
RFS 0 IDLE 0
RFS 0 IDLE 0
RFS 4138 IDLE 0
RFS 0 IDLE 0
MRP0 4138 APPLYING_LOG 0
- 檢查备库是否和主库同步
SQL> select archived_thread#,archived_seq#,applied_thread#,applied_seq# from v$archive_dest_status where archived_thread#=1;
ARCHIVED_THREAD# ARCHIVED_SEQ# APPLIED_THREAD# APPLIED_SEQ#
1 4137 0 0
1 4137 1 4137
- 备库查看已经归档的redo
SQL> SELECT REGISTRAR,CREATOR,THREAD#,APPLIED,SEQUENCE#,FIRST_CHANGE#,NEXT_CHANGE#, COMPLETION_TIME FROM V$ARCHIVED_LOG where COMPLETION_TIME>trunc(sysdate);
REGISTRAR CREATOR THREAD# APPLIED SEQUENCE# FIRST_CHANGE# NEXT_CHANGE# COMPLETION_TIME
RFS ARCH 1 YES 4102 71872403111 71872533218 10-JUL-19
RFS ARCH 1 YES 4103 71872533218 71872687844 10-JUL-19
...
...
RFS ARCH 1 YES 4135 71876353402 71876519473 10-JUL-19
RFS ARCH 1 YES 4136 71876519473 71876659857 10-JUL-19
RFS ARCH 1 IN-MEMORY 4137 71876659857 71876726451 10-JUL-19
- 备库查看已经应用的REDO
SELECT THREAD#,SEQUENCE#,FIRST_CHANGE#,NEXT_CHANGE# FROM V$LOG_HISTORY;
- 主、备库查看是否有gap
#主庫
SQL> select status,gap_status from v$archive_dest_status where dest_id=2;
STATUS GAP_STATUS
VALID NO GAP
#備庫
SQL> select * from v$archive_gap;
no rows selected
- 如步驟6（主、备库查看是否有gap）發現存在gap則如下方法處理
#在主库中查询缺失的日志的所在路径和名称
SELECT NAME FROM V$ARCHIVED_LOG
WHERE THREAD#=1 AND DEST_ID=1
AND SEQUENCE# BETWEEN xxx AND yyy;
#拷贝到备库的相应目录
[oracle@DB01 2019_07_10]$ pwd
/u03/fra/MPCDB01/archivelog/2019_07_10
[oracle@DB01 2019_07_10]$
scp o1_mf_1_4137_glbox8q7_.arc oracle@DB02:/u03/fra/MPCDB02/archivelog/2019_07_10
#備庫手工注册日志文件
SQL> ALTER DATABASE REGISTER LOGFILE '/u03/fra/MPCDB02/archivelog/2019_07_10/o1_mf_1_4137_glbox8q7_.arc';
#備庫再次查看gap
SQL> select * from v$archive_gap;
- 查詢主備庫的歸檔情況
select t.recid,t.name,t.sequence#,t.first_time,t.next_time,t.completion_time,t.archived,t.applied,t.deleted
from v$archived_log t where t.completion_time>sysdate-3/24 order by t.sequence# desc,t.recid desc;
#主庫
RECID NAME SEQUENCE# FIRST_TIME NEXT_TIME COMPLETION_TIME ARCHIVED APPLIED DELETED
8254 /u03/fra2/MPCDB01/archivelog/2019_07_10/o1_mf_1_4149_glc7dbsn_.arc 4149 10-JUL-19 10-JUL-19 10-JUL-19 YES NO NO
8253 sl 4149 10-JUL-19 10-JUL-19 10-JUL-19 YES NO NO
8252 /u03/fra2/MPCDB01/archivelog/2019_07_10/o1_mf_1_4148_glc5qsxt_.arc 4148 10-JUL-19 10-JUL-19 10-JUL-19 YES NO NO
8251 sl 4148 10-JUL-19 10-JUL-19 10-JUL-19 YES YES NO
...
8244 /u03/fra2/MPCDB01/archivelog/2019_07_10/o1_mf_1_4144_glbzs862_.arc 4144 10-JUL-19 10-JUL-19 10-JUL-19 YES NO NO
8243 sl 4144 10-JUL-19 10-JUL-19 10-JUL-19 YES YES NO
#備庫
RECID NAME SEQUENCE# FIRST_TIME NEXT_TIME COMPLETION_TIME ARCHIVED APPLIED DELETED
3795 /u03/fra2/MPCDB02/archivelog/2019_07_10/o1_mf_1_4149_glc7ddlz_.arc 4149 10-JUL-19 10-JUL-19 10-JUL-19 YES IN-MEMORY NO
3794 /u03/fra2/MPCDB02/archivelog/2019_07_10/o1_mf_1_4148_glc5qrqq_.arc 4148 10-JUL-19 10-JUL-19 10-JUL-19 YES YES NO
...
3790 /u03/fra2/MPCDB02/archivelog/2019_07_10/o1_mf_1_4144_glbzs74s_.arc 4144 10-JUL-19 10-JUL-19 10-JUL-19 YES YES NO
