ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 用Timestamp priority 解决oracle goldengate 双向同步,同时更新数据,数据不一致

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

原创 Linux操作系统 作者:hbm1985 时间:2012-05-10 07:43:32 0 删除 编辑

本人在搭建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的一个参数。后来仔细查看文档才发现,是一个规则。
以下是A库的配置:
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;
----------------------------------------------
B库的配置:
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);

3.因为要对比timestamp,所以每次更新vip表时,必须同时更新列t .这个可以在数据库写触发器,或是在程序代码中实现。

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

请登录后发表评论 登录
全部评论

注册时间:2011-02-22

  • 博文量
    25
  • 访问量
    65589