ogg之handlecollisions

handlecollisions

实验环境参数

GGSCI (pc6 as ogg@hyyk) 11> edit params ext_s1

extract ext_s1

setenv (NLS_LANG="AMERICAN_AMERICA.AL32UTF8")

setenv (ORACLE_SID="hyyk")

userid ogg,password oracle

gettruncates

exttrail /u01/app/oggs/dirdat/ss

table sender.*;

GGSCI (pc6 as ogg@hyyk) 13> edit params pump_s1

extract pump_s1

passthru

userid ogg,password oracle

rmthost 192.168.1.80,mgrport 7809

rmttrail /u01/app/oggd/dirdat/sd

table sender.*;

GGSCI (ogg-80) 5> edit params rep_s1

replicat rep_s1

setenv(NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)

setenv(ORACLE_SID='ogg')

userid ogg,password oracle

--handlecollisions

ASSUMETARGETDEFS

--SOURCEDEFS /u01/app/oggd/dirdef/test.def

DISCARDFILE ./dirrpt/rorabb.dsc,PURGE

userid ogg,password oracle

map sender.tb21,target receiver.tb21;

1、源和目标建表（当然源和目标都要有主键）

源：

create table tb21(col1 int primary key, col2 int);

insert into tb21 values(1,1);

insert into tb21 values(2,1);

insert into tb21 values(3,1);

insert into tb21 values(4,1);

commit;

查看数据

SENDER@hyyk> select * from tb21;

COL1 COL2

---------- ----------

1 1

2 1

3 1

4 1

目标：

create table tb21(col1 int primary key, col2 int);

insert into tb21 values(1,1);

commit;

查看数据

RECEIVER@ogg> select * from tb21;

COL1 COL2

---------- ----------

1 1

添加附加日志，加入复制链路

GGSCI (pc6 as ogg@hyyk) 8> add trandata sender.tb21

Logging of supplemental redo data enabled for table SENDER.TB21.

TRANDATA for scheduling columns has been added on table 'SENDER.TB21'.

TRANDATA for instantiation CSN has been added on table 'SENDER.TB21'.

2、测试delete

无handlecollisions

源：

delete from tb21 where col1=2;

commit;

目标端查看进程状态

GGSCI (ogg-80) 24> info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING

REPLICAT ABENDED REP_S1 00:00:04 00:00:02

复制进程abend，2018-03-18 11:09:38 ERROR OGG-01296 Error mapping from SENDER.TB21 to RECEIVER.TB21.

可以在目标上设置： 加handlecollisions（或者start rep2, skiptransaction）可以跳过，用stats rep2看一下；

GGSCI (ogg-80) 32> stats rep_s1

Sending STATS request to REPLICAT REP_S1 ...

Start of Statistics at 2018-03-18 11:13:44.

Replicating from SENDER.TB21 to RECEIVER.TB21:

*** Total statistics since 2018-03-18 11:13:34 ***

Total inserts 0.00

Total updates 0.00

Total deletes 1.00

Total discards 0.00

Total operations 1.00

Total delete collisions 1.00

这里的delete变成了delete collisions

3、测试update

如果目标上不存在这个记录

（1）update键值时：

源：

update tb21 set col1=5 where col1=3;

commit;

SENDER@hyyk> select * from tb21;

COL1 COL2

---------- ----------

1 1

5 1

4 1

无handlecollisions时，会abend；

有handlecollisions时，会变成insert；但是此时需要加：源端：FETCHOPTIONS FETCHPKUPDATECOLS（将捕获完整日志镜像到trail中，转换为对target的一个完整记录的插入，相当于fetchcol（*））

目标端查看

RECEIVER@ogg> select * from tb21;

COL1 COL2

---------- ----------

5 1

1 1

我们使用logdump查看trail

2018/03/18 11:20:44.000.000 GGSUnifiedPKUpdate Len 31 RBA 1830

Name: SENDER.TB21 (TDR Index: 1)

After Image: Partition 12 G s

0000 0009 0000 0005 0000 0001 3300 0000 0500 0000 | ............3.......

0135 0001 0005 0000 0001 31 | .5........1

Before Image Len 13 (x0000000d)

BeforeColumnLen 9 (x00000009)

Column 0 (x0000), Len 5 (x0005)

After Image Len 18 (x00000012)

Column 0 (x0000), Len 5 (x0005)

Column 1 (x0001), Len 5 (x0005)

（2）update非键值时：

源：

update tb21 set col2=4 where col1=4;

commit;

SENDER@hyyk> select * from tb21;

COL1 COL2

---------- ----------

1 1

6 5

5 1

4 4

加了handlecollisions

RECEIVER@ogg> select * from tb21;

COL1 COL2

---------- ----------

4 4

5 1

1 1

6 5

如果是ogg11g 加了handlecollisions也没有反应，数据也会不一致

需要在目标端加入INSERTMISSINGUPDATES参数，即可解决。

4、测试insert

源端insert的pk和目标冲突时，会以源端为准；

目标首先插入一条：

insert into tb21 values(6,1);

commit;

RECEIVER@ogg> select * from tb21;

COL1 COL2

---------- ----------

5 1

1 1

6 1

然后源端插入：

insert into tb21 values(6,5);

SENDER@hyyk> select * from tb21;

COL1 COL2

---------- ----------

1 1

6 5

5 1

4 1

加handlecollisions，

RECEIVER@ogg> select * from tb21;

COL1 COL2

---------- ----------

5 1

1 1

6 5

看最后目标的记录如何，实际会变成以源端为准。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/31485142/viewspace-2151967/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/31485142/viewspace-2151967/