首先先介紹下ogg的工作同步流程如下：
1.源端抽取進(jìn)程從源庫redo log抽取變化的數(shù)據(jù)(已提交的事務(wù))，寫入到本地trail文件中。
2.成功將已提交事務(wù)寫入trail文件后，抽取進(jìn)程會(huì)更新檢查點(diǎn)。
2.源端傳輸進(jìn)程將trail文件通過tcp/ip發(fā)送到配置參數(shù)指定的目標(biāo)數(shù)據(jù)庫。
3.目標(biāo)端server collector進(jìn)程對投遞過來的數(shù)據(jù)塊重新組裝為trail文件。
3.目標(biāo)端復(fù)制進(jìn)程讀取操作系統(tǒng)目錄下的trail文件，形成sql語句，在目標(biāo)數(shù)據(jù)庫中執(zhí)行。還有參數(shù)解讀
lag at chkpt :進(jìn)程當(dāng)前正在讀取的redo日志記錄中的時(shí)間戳與當(dāng)前時(shí)間間隔。
time since chkpt 表示進(jìn)程從最近一次檢查點(diǎn)commit提交數(shù)據(jù)后，到當(dāng)前時(shí)間未提交數(shù)據(jù)的時(shí)長
checkpoint:存儲(chǔ)進(jìn)程的讀取數(shù)據(jù)庫日志的位置rba及trail文件中寫入位置信息，每當(dāng)進(jìn)程看到事務(wù)commit時(shí)，檢查點(diǎn)文件會(huì)產(chǎn)生一個(gè)檢查點(diǎn)。1.問題現(xiàn)象
8月17早上開始出現(xiàn)抽取進(jìn)程一直刷ora-01027警告，持續(xù)幾個(gè)小時(shí)，內(nèi)容都是如Long Running Transaction:長事務(wù)XID 5226.4.27923，同一個(gè)長事務(wù)。
另外還有ora-01028不支持的壓縮表導(dǎo)致導(dǎo)致進(jìn)程自動(dòng)關(guān)閉然后重啟，這個(gè)應(yīng)該是另外一個(gè)問題導(dǎo)致了進(jìn)程的重啟操作。共計(jì)是兩個(gè)問題。
截取進(jìn)程的部分日志如下：
2025-08-17 11:22:26 INFO OGG-01487 Oracle GoldenGate Capture for Oracle, xx.prm: DDL found, operation [create table "xx".CMP4$626139 organization heap tablespace "xx_DATA" compress for all operations nologging as select /*+ DYNAMIC_SAMPLING(0) */ * from "xx".CMP3$626139 mytab (size 188)], start SCN [17501796651397], commit SCN [17501796651658] instance [xx1 (1)], DDL seqno [4101308], marker seqno [4118924].
2025-08-17 11:22:26 INFO OGG-00488 Oracle GoldenGate Capture for Oracle, xx.prm: DDL operation excluded [not included by any filter], optype [CREATE], objtype [TABLE], objowner [xx], objname [CMP4$626139].
2025-08-17 11:22:26 INFO OGG-01487 Oracle GoldenGate Capture for Oracle, xx.prm: DDL found, operation [drop table "xx".CMP3$626139 purge (size 38)], start SCN [17501796651720], commit SCN [17501796651812] instance [xx1 (1)], DDL seqno [4101311], marker seqno [4118927].
2025-08-17 11:22:26 INFO OGG-00487 Oracle GoldenGate Capture for Oracle, xx.prm: DDL operation included [include mapped], optype [DROP], objtype [TABLE], objowner [xx], objname [CMP3$626139].
2025-08-17 11:22:27 INFO OGG-00497 Oracle GoldenGate Capture for Oracle, xx.prm: Writing DDL operation to extract trail file.
2025-08-17 11:22:27 INFO OGG-01487 Oracle GoldenGate Capture for Oracle, xx.prm: DDL found, operation [drop table "xx".CMP4$626139 purge (size 38)], start SCN [17501796651824], commit SCN [17501796652051] instance [xx1 (1)], DDL seqno [4101312], marker seqno [4118928].
2025-08-17 11:22:27 INFO OGG-00488 Oracle GoldenGate Capture for Oracle, xx.prm: DDL operation excluded [not included by any filter], optype [DROP], objtype [TABLE], objowner [xx], objname [CMP4$626139].
2025-08-17 11:23:08 WARNING OGG-01027 Oracle GoldenGate Capture for Oracle, xx.prm: Long Running Transaction: XID 5226.4.27923, Items 1, Extract xx, Redo Thread 1, SCN 4074.3508022147 (17501204786051), Redo Seq #88435, Redo RBA 371576848.
2025-08-17 11:24:15 ERROR OGG-01028 Oracle GoldenGate Capture for Oracle, xx.prm: Object with object number 1069184 is compressed. Table compression is not supported.
2025-08-17 11:24:16 INFO OGG-00991 Oracle GoldenGate Capture for Oracle, xx.prm: EXTRACT xx stopped normally.
2025-08-17 11:27:20 INFO OGG-00975 Oracle GoldenGate Manager for Oracle, mgr.prm: EXTRACT xx starting.
2025-08-17 11:27:20 INFO OGG-00965 Oracle GoldenGate Manager for Oracle, mgr.prm: EXTRACT xx restarted automatically.
2025-08-17 11:27:21 INFO OGG-00992 Oracle GoldenGate Capture for Oracle, xx.prm: EXTRACT xx starting.
2025-08-17 11:27:21 INFO OGG-03035 Oracle GoldenGate Capture for Oracle, xx.prm: Operating system character set identified as UTF-8. Locale: en_US, LC_ALL:.
2025-08-17 11:27:21 INFO OGG-01815 Oracle GoldenGate Capture for Oracle, xx.prm: Virtual Memory Facilities for: BR
/backup/ogg/BR/xx.2.抽取進(jìn)程信息
lag at chkpt 沒有延遲,說明抽取進(jìn)程讀取功能是正常的，表示抽取進(jìn)程還在一直讀取redo日志。
time since chkpt 抽取進(jìn)程巡檢發(fā)現(xiàn)近兩天的延遲，表示ogg無法推進(jìn)檢查點(diǎn)，很大程度說明有長事務(wù)執(zhí)行時(shí)間較長。
讀取檢查點(diǎn)一直停留在11點(diǎn)
3.長事務(wù)阻塞分析
ogg必須等待事務(wù)提交才能處理后續(xù)數(shù)據(jù)，導(dǎo)致后續(xù)數(shù)據(jù)擠壓，延遲持續(xù)增長;因?yàn)闊o法處理數(shù)據(jù)，所以無法寫入Trail文件；因?yàn)闊o法寫入Trail文件，所以檢查點(diǎn)位置就永遠(yuǎn)停滯
1）找到日志里5226對應(yīng)的addr
select t.addr,t.XIDUSN,t.XIDSLOT,t.XIDSQN,t.START_DATE from gv$transaction t;
2）通過taddr找到對應(yīng)的prev_sql_id
select t.PREV_SQL_ID,t.SQL_ID from gv$session t where t.taddr='xx';
3）根據(jù)sqlid找到對應(yīng)的具體sql操作
select sql_text from gv$sqltext t where t.SQL_ID = 'xx';
4）找到對應(yīng)的sid和serial#查殺會(huì)話，再次查詢事務(wù)已消失
select t.sid,t.serial#,t.PREV_SQL_ID,t.SQL_ID from gv$session t where t.taddr='xx';
ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;
4.重啟抽取進(jìn)程報(bào)錯(cuò)
長事務(wù)殺掉后，事務(wù)回滾可能也需要一段時(shí)間，延遲目前還在增加！
現(xiàn)在不管是stop，還是發(fā)起send等命令都是卡死超時(shí)狀態(tài)
強(qiáng)制停止進(jìn)程后啟動(dòng)正常，延遲時(shí)間減少
kill extract xx;
start extract xx;
5.檢查抽取進(jìn)程狀態(tài)
send xx,status
過一會(huì)再次執(zhí)行，發(fā)現(xiàn)sequence和scn已經(jīng)變化
6.不支持的壓縮表處理
通過對象號查詢
SELECT owner, object_name, object_type ,a.OBJECT_ID FROM dba_objects a WHERE object_id = 1069184;
查詢數(shù)據(jù)庫所有的壓縮表
select a.TABLE_NAME,a.OWNER,a.COMPRESSION,a.COMPRESS_FOR from dba_tables a;
抽取進(jìn)程中排除壓縮表
TABLEEXCLUDE owner.table_name;
附1.長交易事務(wù)查詢與跳過
send extract xxx , showtrans thread 1 count 10 應(yīng)該對上transaction查詢的內(nèi)容
SEND EXTRACT xxx, SKIPTRANS 5226.4.27923 THREAD 1 //跳過交易
SEND EXTRACT xxx, FORCETRANS 5226.4.27923 THREAD 1 //強(qiáng)制認(rèn)為該交易已經(jīng)提交