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 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; 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 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
...
四、小結
節點3 2020年09月02日17點24分啟動了mrp程式至今。節點1 apply lag值正好為節點mrp啟動至今的時間約 776天22小時38分 。
五、解決方案
-the end-
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/28373936/viewspace-2919270/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- V$DATAGUARD_STATS中 transport lag 或 apply finish time 是空值APP
- rac的系統時間與物理備庫lag
- Oracle 11g RAC如何把物理DG變成只讀庫Oracle
- ORACLE DG之備庫角色Oracle
- 【DG】Oracle 19c使用dbca來搭建物理DG--主rac備racOracle
- 11g dg 備庫搭建多種方式
- 11G RAC+DG搭建
- Oracle 11g RAC查詢物理備庫延遲情況Oracle
- 【DATAGUARD】DG系列之11g物理備庫的搭建
- Oracle RAC+DG搭建Oracle
- ORACLE DG從庫 Rman備份恢復Oracle
- Oracle 11g R2 DG 備庫怎樣應用redolog資料Oracle
- 【DATAGUARD】DG系列之11g邏輯備庫的搭建
- ORACLE DG 11G 搭建Oracle
- ORACLE 11G 建立 DATAGUARD(雙節點RAC-->單例項DG)Oracle單例
- 一步一步搭建Oracle 11g RAC+ DG詳解Oracle
- Oracle DG從庫 Rman備份恢復測試Oracle
- ORACLE19C RAC+DGOracle
- Oracle RAC DG手動切換Oracle
- oracle 11g RAC 安裝前準備指令碼Oracle指令碼
- oracle 11g之物理備庫管理Oracle
- oracle 11g dg搭建筆記Oracle筆記
- Oracle DG備庫手動管理新增資料檔案Oracle
- ORACLE RAC+DG調整redo大小Oracle
- oracle11g 搭建 rac+dgOracle
- DG學習筆記(9)_備份與RAC筆記
- Oracle 11g R2 DG 備庫啟動報錯,需要恢復日誌處理措施Oracle
- 如何調優物理備庫的重作日誌應用速率_redo apply_dg_data guardAPP
- Oracle 12c 使用RMAN搭建物理備庫(RAC to RAC)Oracle
- 【DG】備庫斷檔問題
- Oracle DG異構主備支援Oracle
- Oracle DG搭建2(冷備方式)Oracle
- oracle DG 11g新特性彙總Oracle
- 【DataGuard】手工冷備搭建 Oracle 11g DataGuard 物理備庫Oracle
- Oracle 11g dataguard check real time applyOracleAPP
- oracle rac 備份Oracle
- oracle 9i 10G 11G 的RAC 穩定性比較Oracle
- Oracle RAC+DG 表空間擴容Oracle