Oracle 11g RAC DG备库gv$dataguard_stats apply lag值较大

一、环境信息操作系统：Linux 7.6数据库：Oracle 11g RAC 主库3节点 DG备库 3节点RAC数据库补丁：1 1.2.0.4.160719 (23054319)$opatch lsinv|grep descPatch description:  "OCW Patch Set Update : 11.2.0.4.160719 (23054319)"Patch description:  "Database Patch Set Update : 11.2.0.4.170418 (24732075)" 二、问题现象 gv$dataguard视图apply lag值为+00 00:00:00表示DG同步正常，Oracle 11g中 正常情况apply lag在mrp进程所在节点有值，其他节点为空。 节点1 apply lag值为+776 22:38:01。DG同步正常。

SQL> select inst_id,name, value from gv$dataguard_stats where name in ('transport lag', 'apply lag') order by 1;
   INST_ID NAME                 VALUE
---------- -------------------- --------------------
         1 transport lag        +00 00:00:00
         1 apply lag          +776 22:38:01
         2 apply lag
         2 transport lag        +00 00:00:00
         3 apply lag          +00 00:00:00
         3 transport lag        +00 00:00:00
6 rows selected.

三、分析过程1、在备库每个节点检查 v$dataguard_stats  apply lag值在备库3个节点分别执行 v$dataguard_stats  apply lag值均为+00 00:00:00

SQL> select name, value from v$dataguard_stats where name in ('transport lag', 'apply lag') order by 1;
NAME                 VALUE
-------------------- --------------------
apply lag            +00 00:00:00
transport lag          +00 00:00:00

2、计算apply lag值为 +776 22:38:01 时间 时间大约是2020年09月02日 17点24分

SQL> select to_char(sysdate-776-22/24-38/24/60-01/24/60/60,'yyyy-mm-dd hh24:mi:ss') from dual;
TO_CHAR(SYSDATE-776
-------------------
2020-09-02 17:24:26
SQL>

3、检查主库日志同步情况主库：

SQL> SELECT MAX(SEQUENCE#), THREAD# FROM GV$ARCHIVED_LOG
    WHERE RESETLOGS_CHANGE# = (SELECT MAX(RESETLOGS_CHANGE#) FROM GV$ARCHIVED_LOG)
     GROUP BY THREAD#;
MAX(SEQUENCE#)    THREAD#
-------------- ----------
        843258          1
        831828          2
        829922          3

备库：

SQL> SELECT MAX(SEQUENCE#), THREAD# FROM GV$ARCHIVED_LOG
     WHERE RESETLOGS_CHANGE# = (SELECT MAX(RESETLOGS_CHANGE#) FROM GV$ARCHIVED_LOG)
     GROUP BY THREAD#;
MAX(SEQUENCE#)    THREAD#
-------------- ----------
        843258          1
        831829          2
        829922          3

4、检查实例启动时间实例启动的时间看着和 2020年09月02日 17点24分没有直接关系

SQL> select INST_ID,INSTANCE_NAME,HOST_NAME,STARTUP_TIME,STATUS,THREAD# from gv$instance;
   INST_ID INSTANCE_NAME        HOST_NAME            STARTUP_TIME        STATUS          THREAD#
---------- -------------------- -------------------- ------------------- ------------ ----------
         3 proddb1                racnode1         2020-03-10 15:50:06   OPEN                         3
         2 proddb2                racnode2         2019-09-10 22:32:19  MOUNTED              2
         1 proddb3                racnode3         2019-09-10 22:31:34  MOUNTED              1

5、数据库日志分析 节点1 alert日志：....Sat Sep 04 00:47:25 2021alter database recover managed standby database using current logfile disconnect from sessionORA-1153 signalled during: alter database recover managed standby database using current logfile disconnect from session...Sat Sep 04 00:47:28 2021RFS[679]: Assigned to RFS process 28312RFS[679]: No standby redo logfiles available for thread 3...节点3 alert日志：...Data Guard Broker initializing...Data Guard Broker initialization completeThis instance was first to openPicked Lamport scheme to generate SCNsWed Sep 02 17:24:04 2020SMON: enabling cache recoveryDictionary check beginningDictionary check completeDatabase Characterset is AL32UTF8No Resource Manager plan activeWed Sep 02 17:24:10 2020Starting background process GTX0Wed Sep 02 17:24:10 2020GTX0 started with pid=72, OS id=16156replication_dependency_tracking turned off (no async multimaster replication found)Physical standby database opened for read only access.Completed: alter database openALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSIONAttempt to start background Managed Standby Recovery processWed Sep 02 17:24:16 2020MRP0 started with pid=73, OS id=16178... 四、小结