ogg 併發複製程式自阻塞
OGG和資料庫版本如下：
軟體 版本
GoldenGate Version 12.2.0.1.160823
Oracle database 11.2.0.4.0
資料庫同時出現TMCH和LAG，如下：
GGSCI (ORADB) 1> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING 2401 00:00:00 00:00:03
REPLICAT RUNNING 1912P4R1 00:00:12 00:00:09
REPLICAT RUNNING 2401R1 00:01:28 00:17:51
2401R1程式是整合模式。
GGSCI (ORADB) 2> info 2401R1
REPLICAT 2401R1 Last Started 2019-08-27 14:30 Status RUNNING
INTEGRATED
Checkpoint Lag 00:01:28 (updated 00:18:00 ago)
Process ID 31325
Log Read Checkpoint File /u01/app/oracle/products/ogg/dirdat/2401/E0000020883
2019-08-27 15:13:14.726857 RBA 21287703
查詢複製程式的等待事件。
SQL> set pagesize 3000
SQL> set line 300
SQL> select s.SID,s.PROCESS,s.PROGRAM,s.ACTION,s.EVENT,s.WAIT_CLASS,s.SECONDS_IN_WAIT,s.STATE from v$session s where s.ACTION like '%OGG$%2401R1%'；
SID PROCESS PROGRAM
---------- ------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------------------------------------------------------
ACTION
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
EVENT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
WAIT_CLASS SECONDS_IN_WAIT STATE
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ --------------- ---------------------------------------------------------
258 698 oracle@ORADB-4024.datadept.eastmoney.com.sh
OGG$2401R1 - Apply Server
library cache pin
Concurrency 841 WAITING
452 31325 replicat@ORADB-4024.datadept.eastmoney.com.sh (T
OGG$2401R1 - Propagation Receive
library cache lock
Concurrency 841 WAITING
1091 31349 oracle@ORADB-4024.datadept.eastmoney.com.sh
OGG$2401R1 - Apply Coordinator
rdbms ipc message
Idle 1 WAITING
1155 31351 oracle@ORADB-4024.datadept.eastmoney.com.sh
OGG$2401R1 - Apply Reader
library cache pin
Concurrency 842 WAITING
1217 31353 oracle@ORADB-4024.datadept.eastmoney.com.sh
OGG$2401R1 - Apply Server
Streams apply: waiting for dependency
Concurrency 0 WAITING
1281 31355 oracle@ORADB-4024.datadept.eastmoney.com.sh
OGG$2401R1 - Apply Server
Streams apply: waiting for dependency
Concurrency 0 WAITING
1475 31552 oracle@ORADB-4024.datadept.eastmoney.com.sh
OGG$2401R1 - Apply Server
Streams apply: waiting for dependency
Concurrency 0 WAITING
7 rows selected.
可以看到2401R1程式有4個併發程式，四個實際工作的程式在等待Streams apply: waiting for dependency，library cache lock和library cache pin等待事件。
查詢資料庫等待事件。
SQL> col event for a35
SQL> select inst_id, event#, event,count(*) from gv$session where wait_class# <> 6 group by inst_id, event#,event order by 1,4 desc;
INST_ID EVENT# EVENT COUNT(*)
---------- ---------- ------------------------------------- ----------
1 342 Streams apply: waiting for dependency 3
1 286 library cache pin 2
1 289 library cache: mutex X 1
1 350 SQL*Net message to client 1
1 287 library cache lock 1
從library cache lock和library cache pin入手分析。
SQL> set linesize 200
SQL> set arraysize 1000
SQL> set sqlblankline on
SQL> set echo on
SQL> set feedback on
SQL> set heading on
SQL> set timing on time on
15:39:44 SQL>
15:39:44 SQL> SELECT distinct decode(kglpnreq,0,'holding_session: '||s.sid,'waiting_session: '||s.sid) sid,
15:39:44 2 s.SERIAL#, kglpnmod "Pin Mode", kglpnreq "Req Pin",a.sql_text,kglnaown "Owner", kglnaobj "Object"
15:39:44 3 FROM x$kglpn p, v$session s,v$sqlarea a,v$session_wait sw,x$kglob x
15:39:44 4 WHERE p.kglpnuse=s.saddr
15:39:44 5 AND kglpnhdl=sw.p1raw
15:39:45 6 and kglhdadr=sw.p1raw
15:39:45 7 and s.event like 'library cache%'
15:39:45 8 and (a.hash_value, a.address) IN (
15:39:45 9 select
15:39:45 10 DECODE (sql_hash_value,
15:39:45 11 0,
15:39:45 12 prev_hash_value,
15:39:45 13 sql_hash_value
15:39:45 14 ),
15:39:45 15 DECODE (sql_hash_value, 0, prev_sql_addr, sql_address)
15:39:45 16 from v$session s2
15:39:45 17 where s2.sid=s.sid
15:39:45 18 )
15:39:45 19 ;
SID SERIAL# Pin Mode
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------- ----------
Req Pin
----------
SQL_TEXT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Owner
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Object
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
holding_session: 1155 22119 2
0
/* SQL Analyze(1155,0) */ insert into "NADMIN"."NEWLOG2" select * from "NADMIN"."NEWLOG2"
NADMIN
NEWLOG2
holding_session: 258 46181 2
0
INSERT /*+ restrict_all_ref_cons */ INTO "NADMIN"."NEWLOG2" ("TABLENAME","RECORDID","DOFLAG","COMMITTIMESTAMP","CSN","BEFOREAFTERINDICATOR") VALUES (:1 ,:2 ,:3 ,:4 ,:5 ,:6 )
NADMIN
NEWLOG2
waiting_session: 1155 22119 0
3
/* SQL Analyze(1155,0) */ insert into "NADMIN"."NEWLOG2" select * from "NADMIN"."NEWLOG2"
NADMIN
JG_TRG_NEWLOG2_SYST
waiting_session: 258 46181 0
2
INSERT /*+ restrict_all_ref_cons */ INTO "NADMIN"."NEWLOG2" ("TABLENAME","RECORDID","DOFLAG","COMMITTIMESTAMP","CSN","BEFOREAFTERINDICATOR") VALUES (:1 ,:2 ,:3 ,:4 ,:5 ,:6 )
NADMIN
JG_TRG_NEWLOG2_SYST
4 rows selected.
檢視這幾個會話的阻塞情況
15:41:14 SQL> select sid,serial#,blocking_session from v$session where sid in (1155,258);
SID SERIAL# BLOCKING_SESSION
---------- ---------- ----------------
258 46181 1155
1155 22119 1475
2 rows selected.
Elapsed: 00:00:00.02
15:41:37 SQL> select sid,serial#,blocking_session from v$session where sid in 1475;
SID SERIAL# BLOCKING_SESSION
---------- ---------- ----------------
1475 44855
1 row selected.
Elapsed: 00:00:00.09
會話1155阻塞了會話258，會話1475又阻塞了會話1155，所以阻塞的源頭是1475會話。該會話的等待事件是Streams apply: waiting for dependency。也就是說開了併發的複製程式，它的子程式之間形成了一條阻塞鏈。搜尋MOS，可以參考相關文件ID（1566466.1，2359146.1）。
2401R1程式無法成功stop，直接kill
kill 2401R1
會話仍然存在，然後殺掉阻塞源頭。
Alter system kill session '1475,35578' immediate;
最後重啟程式恢復正常。
start 2401R1
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/31480688/viewspace-2655187/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- OGG 複製程式拆分
- mysql 併發複製MySql
- OGG複製程式掛起abended處理
- MySQL併發複製系列二：多執行緒複製MySql執行緒
- Java併發程式設計：阻塞佇列Java程式設計佇列
- Java併發程式設計——阻塞佇列Java程式設計佇列
- 資料複製的併發控制
- 程式執行緒、同步非同步、阻塞非阻塞、併發並行執行緒非同步並行
- 併發-0-同步/非同步/阻塞/非阻塞/程式/執行緒非同步執行緒
- OGG雙向DML複製操作
- OGG單向DDL複製操作
- ogg中抽取或複製程式（extract or replicat）中表的的重複配置
- OGG同步複製時與相容觸發器解決方法觸發器
- OGG複製程式延遲高，優化方法一(使用索引)優化索引
- OGG 指定csn啟動複製程式的另一種方式
- OGG的抽取程式合併
- OGG複製程式出現 OGG-00868， 904-ORA-00904 "VISIBILITY"
- 聊聊併發（四）——阻塞佇列佇列
- ogg複製程式報ORA-01438錯誤處理
- Goldengate的複製程式出錯OGG-01296Go
- MySQL併發複製系列一：binlog組提交MySql
- 11、協程和io教程01 -- 併發 並行 同步 非同步 阻塞 非阻塞 以及 IO多路複用並行非同步
- 程式與執行緒、同步與非同步、阻塞與非阻塞、併發與並行執行緒非同步並行
- 併發程式設計之臨界區\阻塞\非阻塞\死鎖\飢餓\活鎖程式設計
- ogg12 oracle to oracle 雙向DDL複製Oracle
- ogg長交易查詢及增加複製表
- OGG複製與加密欄位的衝突加密
- Redis實現併發阻塞鎖方案Redis
- Java併發——阻塞佇列集(下)Java佇列
- Java併發——阻塞佇列集(上)Java佇列
- ♻️同步和非同步；並行和併發；阻塞和非阻塞非同步並行
- 多執行緒高併發程式設計(9) -- CopyOnWrite寫入時複製執行緒程式設計
- 沃趣科技李春：MySQL併發複製探祕MySql
- oracle時間格式記錄異常導致OGG複製程式報錯Oracle
- OGG 12c mysql複製到oracle部署方案MySqlOracle
- 【進階之路】併發程式設計（三）-非阻塞同步機制程式設計
- JAVA併發之阻塞佇列淺析Java佇列
- Java併發系列 — 阻塞佇列（BlockingQueue）Java佇列BloC