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/，如需轉載，請註明出處，否則將追究法律責任。

goldengate跳過/提交一個未完成的事務

相關文章