用Timestamp priority 解决oracle goldengate 双向同步，同时更新数据，数据不一致

最新推荐文章于 2023-02-02 17:56:41 发布

cuituan1897

最新推荐文章于 2023-02-02 17:56:41 发布

阅读量211

点赞数

文章标签：数据库

本人在搭建oracle goldengate　双向同步时，遇到一个问题：当两边同时更新同一条记录时，会出现两边数据不一致的问题。
A库最终拿到的是B库数据更新的数据。B库最终拿到的是A库数据更新的数据。
拓扑如下：
      A(test) <=======>B(dest)
均为oracle 10GR2
表VIP结构如下：
id                notnull varchar2(10)
node          notnull varchar2(10)
name          varchar2(10)
ca             varchar2(10)

例如：
原始记录,此时两个库的数据是一致的
id node  name ca
001 A    BB    A

T1时间，在A库操作：
update　vip set  name='A' where id ='001';
commit;
T2时间，在B库操作：
update vip set name='B' where id ='001';
commit;

过了几秒他们相互同步了数据：
A库的结果是：
id node  name ca
001 A 　　B　　 A

B库的结果是：
id node  name ca
001 A 　　A　　 A
怎么样配置才可以实现，以最后更新并commit的数据为最终数据？所谓的Timestamp priority怎么配置？

一始以为Timestamp priority　是extract或replicat的一个参数。后来仔细查看文档才发现，是一个规则。
以下是Ａ库的配置：
GGSCI (test) 6> view params exta
extract exta
setenv (NLS_LANG="AMERICAN_AMERICA.AL32UTF8" )
GETENV (NLS_LANG)
--NOCOMPRESSUPDATES
userid ggate, password ggate
tranlogoptions excludeuser ggate
rmthost 192.168.1.151,mgrport 7809
rmttrail ./dirdat/td
table aa.vip;
GGSCI (test) 3> view params repa
replicat repa
setenv (NLS_LANG="AMERICAN_AMERICA.AL32UTF8" )
GETENV (NLS_LANG)
userid ggate, password ggate
handlecollisions
assumetargetdefs
discardfile ./dirrpt/repa.dsc,purge
map aa.vip, target aa.vip;
－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
Ｂ库的配置：
GGSCI (dest) 6> view params extb
extract extb
setenv (NLS_LANG="AMERICAN_AMERICA.AL32UTF8" )
GETENV (NLS_LANG)
--NOCOMPRESSUPDATES
userid ggate, password ggate
tranlogoptions excludeuser ggate
rmthost 192.168.1.141,mgrport 7809
rmttrail ./dirdat/tg
table aa.vip;
GGSCI (dest) 7> view params repb
replicat repb
setenv (NLS_LANG="AMERICAN_AMERICA.AL32UTF8" )
GETENV (NLS_LANG)
userid ggate, password ggate
handlecollisions
assumetargetdefs
discardfile ./dirrpt/repb.dsc,purge
map aa.vip, target aa.vip;

以下是官方文档的说明：

Methods conflict　一章的说明：

Methods for resolving conflict
The following are some methods that are typically used in conflict-resolution routines.
Different methods can be used in combination so that if one method fails, another method
is applied. If you need assistance with constructing your routines, contact GoldenGate
Technical Support.
Timestamp priority
You can resolve conflicts based on a timestamp, where the record that was modified first
(or in some cases, last) always takes priority. To use timestamp priority:
● Each record must contain a timestamp column that contains the date and time that the
record was inserted or updated. You can use a database trigger or you can modify the
application code to place the timestamp in the column.
● The timestamps on all databases must be identical, and all servers must be in the same
time zone.
To detect and resolve conflicts based on a timestamp, first, try to apply the row normally.
Compare the before image of the timestamp column in the replicated row to the current
timestamp column in the database.
● If they match, there is no conflict.
● If they are different, compare the timestamp of the row in the database to the after
image of the timestamp from the replicated row.
The row with the oldest timestamp (or newest timestamp, if that is your rule) takes
priority.
...........

解决过程：

1.在表vip中增加timestamp类型列t

alter table vip add(t timestamp (6));

2.在两端的replciat配置，更改如下：
GGSCI (test) 23> view params repa
replicat repa
setenv (NLS_LANG="AMERICAN_AMERICA.AL32UTF8")
GETENV (NLS_LANG)
userid ggate, password ggate
handlecollisions
assumetargetdefs
discardfile ./dirrpt/repa.dsc,purge
map aa.vip, target aa.vip, &
REPERROR (21000, DISCARD), &
SQLEXEC (ID lookup, ON UPDATE, &
QUERY "select count(*) conflict from aa.vip where id =:P1 and &
t > :P2", &
PARAMS (P1 = id, P2 = t), BEFOREFILTER, ERROR REPORT, &
TRACE ALL),&
FILTER (lookup.conflict = 0, ON UPDATE, RAISEERROR 21000);