goldengate跳过/提交一个未完成的事务

最新推荐文章于 2022-03-30 23:45:05 发布

cuili9760

最新推荐文章于 2022-03-30 23:45:05 发布

阅读量375

点赞数

@源端

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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26451536/viewspace-1813704/

cuili9760

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
goldengate跳过/提交一个未完成的事务

@源端 GGSCI (rhlinux)21> stop ext_1 Sending STOP requestto EXTRACT EXT_1 ...
复制链接

扫一扫