goldengate跳過/提交一個未完成的事務
@源端
GGSCI (rhlinux) 21> stop ext_1
Sending STOP request to EXTRACT EXT_1 ...
Request processed.
GGSCI (rhlinux) 22> stop pump_1
Sending STOP request to EXTRACT PUMP_1 ...
Request processed.
GGSCI (rhlinux) 23> start ext_1
Sending START request to MANAGER ...
EXTRACT EXT_1 starting
GGSCI (rhlinux) 24> start pump_1
Sending START request to MANAGER ...
EXTRACT PUMP_1 starting
GGSCI (rhlinux) 25> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING EXT_1 00:00:19 00:00:04
EXTRACT RUNNING E_TMP 00:00:00 00:00:09
EXTRACT RUNNING PUMP_1 00:00:00 00:00:16
EXTRACT RUNNING P_TMP 00:00:00 00:00:07
GGSCI (rhlinux) 27> !
info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING EXT_1 00:00:00 00:00:08
EXTRACT RUNNING E_TMP 00:00:00 00:00:03
EXTRACT RUNNING PUMP_1 00:00:00 00:00:00
EXTRACT RUNNING P_TMP 00:00:00 00:00:01
GGSCI (rhlinux) 28> view params ext_1
extract ext_1
userid ogg,password ogg
exttrail ./dirdat/e1
table scott.emp;
table scott.dept;
table scott.t;
table scott.tv;
table scott.t2;
GGSCI (rhlinux) 29> view params pump_1
extract pump_1
userid ogg ,password ogg
rmthost 192.168.73.134, mgrport 7811
rmttrail ./dirdat/p1
--passthru
table scott.emp;
table scott.t;
table scott.dept;
table scott.tv;
table scott.t2;
@目標端
GGSCI (ogg) 20> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
REPLICAT RUNNING REP_1 00:00:23 00:00:05
REPLICAT RUNNING R_TMP 00:00:00 00:00:09
GGSCI (ogg) 21> view params rep_1
replicat rep_1
userid ogg,password ogg
assumetargetdefs
reperror(1,discard)
reperror(1403,discard)
discardfile /opt/ogg/discard/e1.log,append,megabytes 10
map scott.emp , target scott.emp;
map scott.dept, target scott.dept;
map scott.t,target scott.t;
map scott.t2,target scott.t2;
map scott.tv,target scott.tv;
重啟投遞程式
GGSCI (ogg) 23> stop rep_1
Sending STOP request to REPLICAT REP_1 ...
Request processed.
GGSCI (ogg) 24> start rep_1
Sending START request to MANAGER ...
REPLICAT REP_1 starting
@源端
重啟抽取和投遞程式
GGSCI (rhlinux) 33> stop ext_1
Sending STOP request to EXTRACT EXT_1 ...
Request processed.
GGSCI (rhlinux) 34> stop pump_1
Sending STOP request to EXTRACT PUMP_1 ...
Request processed.
GGSCI (rhlinux) 35> start ext_1
Sending START request to MANAGER ...
EXTRACT EXT_1 starting
GGSCI (rhlinux) 36> start pump_1
Sending START request to MANAGER ...
EXTRACT PUMP_1 starting
插入資料：
SQL> insert into scott.tv select 23,23,23 from dual;
1 row created.
SQL> commit;
Commit complete.
@目標端
檢視同步資料量
GGSCI (ogg) 25> stats rep_1,hourly
Sending STATS request to REPLICAT REP_1 ...
Start of Statistics at 2015-08-08 10:44:58.
Replicating from SCOTT.TV to SCOTT.TV:
*** Hourly statistics since 2015-08-08 10:44:46 ***
Total inserts 1.00
Total updates 0.00
Total deletes 0.00
Total discards 0.00
Total operations 1.00
End of Statistics.
以上是正常操作。
下面是源端事務未提交時如何將資料寫入trail檔案
1.源端強制寫入trial後，回滾事務
@源端
插入資料但不提交：
SQL> insert into scott.tv select 234,234,234 from dual;
1 row created.
使用send命令檢視未提交事務：
GGSCI (rhlinux) 40> send ext_1,showtrans
Sending SHOWTRANS request to EXTRACT EXT_1 ...
Oldest redo log file necessary to restart Extract is:
Redo Log Sequence Number 631, RBA 3629584
------------------------------------------------------------
XID: 19.16.2535
Items: 1
Extract: EXT_1
Redo Thread: 1
Start Time: 2015-07-16:12:23:55
SCN: 0.10492565 (10492565)
Redo Seq: 631
Redo RBA: 3629584
Status: Running
根據XID將該事務強制寫入trail檔案：
GGSCI (rhlinux) 41> send ext_1,forcetrans 19.16.2535
Sending forcetrans request to EXTRACT EXT_1 ...
Are you sure you sure you want to force transaction to trail file [XID 19.16.2535, Redo Thread 1, Start Time 2015-07-16:12:23:55, SCN 0.10492565 (10492565)] (y/n)?y
Sending forcetrans request to EXTRACT EXT_1 ...
Transaction [XID 19.16.2535, Redo Thread 1, Start Time 2015-07-16:12:23:55, SCN 0.10492565 (10492565)] forced to trail file.
幾秒鐘後檢視pump_1程式，該insert語句已經寫入trail
GGSCI (rhlinux) 43> stats pump_1,hourly
Sending STATS request to EXTRACT PUMP_1 ...
Start of Statistics at 2015-07-16 12:25:04.
Output to ./dirdat/p1:
Extracting from SCOTT.TV to SCOTT.TV:
*** Hourly statistics since 2015-07-16 12:19:54 ***
Total inserts 2.00
Total updates 0.00
Total deletes 0.00
Total discards 0.00
Total operations 2.00
End of Statistics.
@目標端
確認已同步：
SQL> select * from scott.tv;
T1 T2 T3
---------- ---------- --------------------------------------------------
234 234 234
23 23 23
@源端：
SQL> insert into scott.tv select 2345,2345,2345 from dual;
1 row created.
SQL> rollback;
Rollback complete.
SQL> select * from scott.tv;
T1 T2 T3
---------- ---------- ------------------------------
23 23 23
@目標端：
SQL> select * from scott.tv;
T1 T2 T3
---------- ---------- --------------------------------------------------
234 234 234
23 23 23
2.源端強制寫入trail後，提交事務：
將目標端"T1=234"的記錄重複上述操作，將rollback改為commit:
@源端：
SQL> select * from scott.tv;
T1 T2 T3
---------- ---------- ------------------------------
23 23 23
插入資料
SQL> insert into scott.tv select 234,234,234 from dual;
1 row created.
檢視事務並將其強制寫入trail
GGSCI (rhlinux) 45> send ext_1,showtrans
Sending SHOWTRANS request to EXTRACT EXT_1 ...
Oldest redo log file necessary to restart Extract is:
Redo Log Sequence Number 631, RBA 3767312
------------------------------------------------------------
XID: 12.13.2533
Items: 1
Extract: EXT_1
Redo Thread: 1
Start Time: 2015-07-16:12:32:46
SCN: 0.10492954 (10492954)
Redo Seq: 631
Redo RBA: 3767824
Status: Running
GGSCI (rhlinux) 46> send ext_1,forcetrans 12.13.2533
Sending forcetrans request to EXTRACT EXT_1 ...
Are you sure you sure you want to force transaction to trail file [XID 12.13.2533, Redo Thread 1, Start Time 2015-07-16:12:32:46, SCN 0.10492954 (10492954)] (y/n)?y
Sending forcetrans request to EXTRACT EXT_1 ...
Transaction [XID 12.13.2533, Redo Thread 1, Start Time 2015-07-16:12:32:46, SCN 0.10492954 (10492954)] forced to trail file.
繼續在該事務中插入資料並提交操作
SQL> insert into scott.tv select 2345,2345,2345 from dual;
1 row created.
SQL> commit;
Commit complete.
@目標端：
檢視同步結果
SQL> select * from scott.tv;
T1 T2 T3
---------- ---------- --------------------------------------------------
234 234 234
23 23 23
3.源端跳過事務，之後提交操作
@源端
清理垃圾資料：
SQL> select * from scott.tv;
T1 T2 T3
---------- ---------- ------------------------------
23 23 23
234 234 234
2345 2345 2345
SQL> delete from scott.tv where t1 in (234,2345);
2 rows deleted.
SQL> commit;
Commit complete.
SQL> select * from scott.tv;
T1 T2 T3
---------- ---------- ------------------------------
23 23 23
插入測試資料：
SQL> insert into scott.tv select 234,234,234 from dual;
1 row created.
跳過該事務：
GGSCI (rhlinux) 47> send ext_1,showtrans
Sending SHOWTRANS request to EXTRACT EXT_1 ...
Oldest redo log file necessary to restart Extract is:
Redo Log Sequence Number 631, RBA 4039184
------------------------------------------------------------
XID: 14.4.2547
Items: 1
Extract: EXT_1
Redo Thread: 1
Start Time: 2015-07-16:12:39:23
SCN: 0.10493336 (10493336)
Redo Seq: 631
Redo RBA: 4039184
Status: Running
GGSCI (rhlinux) 48> send ext_1,skiptrans 14.4.2547
Sending skiptrans request to EXTRACT EXT_1 ...
Are you sure you sure you want to skip transaction [XID 14.4.2547, Redo Thread 1, Start Time 2015-07-16:12:39:23, SCN 0.10493336 (10493336)]? (y/n)y
Sending skiptrans request to EXTRACT EXT_1 ...
Transaction [XID 14.4.2547, Redo Thread 1, Start Time 2015-07-16:12:39:23, SCN 0.10493336 (10493336)] skipped.
繼續插入一條資料，並提交：
SQL> insert into scott.tv select 2345,2345,2345 from dual;
1 row created.
SQL> commit;
Commit complete.
SQL> select * from scott.tv;
T1 T2 T3
---------- ---------- ------------------------------
23 23 23
234 234 234
2345 2345 2345
@目標端：
SQL> select * from scott.tv;
T1 T2 T3
---------- ---------- --------------------------------------------------
23 23 23
源端ogg跳過事務後，回滾該事務的實驗略。
綜上可知，
（1）使用如下命令可以檢視當前未提交事務：
send extract <程式名>,showtrans [thread n ] [count n]
（2）使用如下命令跳過特定事務，該事務不會被同步到目標端
send extract <程式名>,skiptrans <XID> thread <xxx>
（3）使用如下命令將事務當前狀態寫入trail，源端該事務的後續操作不會記錄到trail，
源端該事務後續的回滾和提交動作隻影響其本身，不影響目標端
Send extract <程式名>,forcetrans <XID> thread <xxx>
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26451536/viewspace-1813704/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- skip a transaction in goldengate(跳過一個事務OGG)Go
- MySQL 事務提交過程MySql
- 從一個線上問題分析binlog與內部XA事務提交過程
- tidb之dm叢集跳過某個事務實踐TiDB
- MySQL事務提交的三個階段介紹MySql
- 探究MySQL的DML提交事務的意義和DQL是否有必要提交事務MySql
- Mycat分散式事務兩階段提交過程概述分散式
- 請教一個在完整提交前臨時儲存的問題(事務)！！
- 未提交事務造成的等待事件事件
- 檢視mysql沒提交的事務MySql
- Spring中的事務提交事件Spring事件
- MySQL事務兩段式提交MySql
- java 事務提交/回滾Java
- SQL Server 查出未提交事務（長事務）SQLSQLServer
- goldengate中長事務引起的問題Go
- mysql隱式提交事務transaction一點筆記MySql筆記
- vitess兩階段提交事務Vite
- MySQL事務提交流程概述MySql
- MySQL通過performance_schema定位未提交事務所執行的SQLMySqlORM
- PostgreSQL 原始碼解讀（123）- MVCC#8(提交事務-實際提交過程)SQL原始碼MVCC#
- 一個事務插入，另外一個事務更新操作，是否會更新成功？
- MySQL實現事務的提交和回滾MySql
- MySQl事務建立，開始以及提交MySql
- 一文帶你深度解析MySQL 8.0事務提交原理MySql
- Entity Framework中 批量提交 事務處理Framework
- docker通過commit命令提交一個映象DockerMIT
- 如何通過一個SAPGUI螢幕反查這個螢幕對應的事務碼GUI
- @Transactional註解管理事務和手動提交事務
- Spring分散式事務XA事務(兩段提交2PC)實現Spring分散式
- MySQL：begin後事務為什麼不提交MySql
- 分散式事務（二）之三階段提交分散式
- 分散式事務（二）之兩階段提交分散式
- 分散式：分散式事務（CAP、兩階段提交、三階段提交）分散式
- 資料庫事務以及事務的四個特性資料庫
- 一個事務的整個流程,datafile,undo,redo的內容
- SharePlex qview工具 vs OGG logdump工具探究兩個複製工具事務開始 or 事務提交複製？View
- Goldengate 指定時間點重新抽取事務變化Go
- 分散式事務（一）—分散式事務的概念分散式