ITPub博客
首页 博客 专栏 学院 社区 IT新媒体评选
发博

首页 > 数据库 > Oracle > goldengate跳过/提交一个未完成的事务

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

原创 Oracle 作者：to_be_dba 时间：2015-10-10 16:35:34 0 删除 编辑

@源端

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博客 ” ，链接：https://blog.itpub.net/26451536/viewspace-1813704/ ，如需转载，请注明出处，否则将追究法律责任。

0
0
上一篇： OGG-extract进程对应的多余trail文件的删除
下一篇： 根据条件动态更新不同表的数据
请登录后发表评论 登录
全部评论
to_be_Dba

注册时间：2011-11-23

  • 博文量
    170
  • 访问量
    484904

最新文章

支持我们 作者招募 用户协议 FAQ Contact Us

北京盛拓优讯信息技术有限公司. 版权所有  京ICP备16024965号-8 北京市公安局海淀分局网监中心备案编号：11010802021510 niuxiaotong@pcpop.com 17352615567 未成年人举报专区

广播电视节目制作经营许可证(京) 字第1234号 中国互联网协会会员

违法和不良信息举报电话:18101376593