一、环境信息
操作系统：Linux 7.6
数据库：Oracle 11g RAC 主库3节点 DG备库 3节点RAC
数据库补丁：11.2.0.4.160719 (23054319)
$opatch lsinv|grep desc
Patch 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;
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 2021
alter database recover managed standby database using current logfile disconnect from session
ORA-1153 signalled during: alter database recover managed standby database using current logfile disconnect from session…
Sat Sep 04 00:47:28 2021
RFS[679]: Assigned to RFS process 28312
RFS[679]: No standby redo logfiles available for thread 3
…
节点3 alert日志：
…
Data Guard Broker initializing…
Data Guard Broker initialization complete
This instance was first to open
Picked Lamport scheme to generate SCNs
Wed Sep 02 17:24:04 2020
SMON: enabling cache recovery
Dictionary check beginning
Dictionary check complete
Database Characterset is AL32UTF8
No Resource Manager plan active
Wed Sep 02 17:24:10 2020
Starting background process GTX0
Wed Sep 02 17:24:10 2020
GTX0 started with pid=72, OS id=16156
replication_dependency_tracking turned off (no async multimaster replication found)
Physical standby database opened for read only access.
Completed: alter database open
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION
Attempt to start background Managed Standby Recovery process
Wed Sep 02 17:24:16 2020
MRP0 started with pid=73, OS id=16178
…
四、小结
数据库备库的mrp目前在节点3运行，gv$dataguard视图apply lag值为0表示DG同步正常，
正常情况apply lag在mrp进程所在节点有值，其他节点为空。节点1节点2021年09月04日启动过mrp进程并报错ORA-1153，
节点3 2020年09月02日17点24分启动了mrp进程至今。节点1 apply lag值正好为节点mrp启动至今的时间约776天22小时38分。
五、解决方案
1）该信息不影响DG同步可以忽略。
2）将备库节点1的数据库open；或者重启一下备库节点1数据库。
-the end-
