一、環(huán)境信息
操作系統(tǒng)：Linux 7.6
數(shù)據(jù)庫(kù)：Oracle 11g RAC 主庫(kù)3節(jié)點(diǎn) DG備庫(kù) 3節(jié)點(diǎn)RAC
數(shù)據(jù)庫(kù)補(bǔ)丁：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)”
二、問(wèn)題現(xiàn)象
gv$dataguard視圖apply lag值為+00 00:00:00表示DG同步正常，Oracle 11g中正常情況apply lag在mrp進(jìn)程所在節(jié)點(diǎn)有值，其他節(jié)點(diǎn)為空。節(jié)點(diǎn)1 apply lag值為+776 22:38:01。DG同步正常。
SQL> select inst_id,name, value from gv$dataguard_stats where name in (‘transport lag’, ‘a(chǎn)pply 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.
三、分析過(guò)程
1、在備庫(kù)每個(gè)節(jié)點(diǎn)檢查v$dataguard_stats apply lag值
在備庫(kù)3個(gè)節(jié)點(diǎn)分別執(zhí)行 v$dataguard_stats apply lag值均為+00 00:00:00
SQL> select name, value from v$dataguard_stats where name in (‘transport lag’, ‘a(chǎn)pply lag’) order by 1;
NAME VALUE
apply lag +00 00:00:00
transport lag +00 00:00:00
2、計(jì)算apply lag值為+776 22:38:01 時(shí)間
時(shí)間大約是2020年09月02日 17點(diǎn)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、檢查主庫(kù)日志同步情況
主庫(kù)：
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
備庫(kù)：
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、檢查實(shí)例啟動(dòng)時(shí)間
實(shí)例啟動(dòng)的時(shí)間看著和2020年09月02日 17點(diǎn)24分沒有直接關(guān)系
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、數(shù)據(jù)庫(kù)日志分析
節(jié)點(diǎn)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
…
節(jié)點(diǎn)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
…
四、小結(jié)
數(shù)據(jù)庫(kù)備庫(kù)的mrp目前在節(jié)點(diǎn)3運(yùn)行，gv$dataguard視圖apply lag值為0表示DG同步正常，
正常情況apply lag在mrp進(jìn)程所在節(jié)點(diǎn)有值，其他節(jié)點(diǎn)為空。節(jié)點(diǎn)1節(jié)點(diǎn)2021年09月04日啟動(dòng)過(guò)mrp進(jìn)程并報(bào)錯(cuò)ORA-1153，
節(jié)點(diǎn)3 2020年09月02日17點(diǎn)24分啟動(dòng)了mrp進(jìn)程至今。節(jié)點(diǎn)1 apply lag值正好為節(jié)點(diǎn)mrp啟動(dòng)至今的時(shí)間約776天22小時(shí)38分。
五、解決方案
1）該信息不影響DG同步可以忽略。
2）將備庫(kù)節(jié)點(diǎn)1的數(shù)據(jù)庫(kù)open；或者重啟一下備庫(kù)節(jié)點(diǎn)1數(shù)據(jù)庫(kù)。
-the end-
