GoldenGate Active-Active Replication with Conflict Detection and Resolution (CDR) part 2

最新推荐文章于 2019-11-19 10:22:57 发布
dbatravel
最新推荐文章于 2019-11-19 10:22:57 发布
阅读量1.3k
点赞数
分类专栏： Oracle OGG
Oracle OGG 专栏收录该内容
11 篇文章 0 订阅
订阅专栏
标题: GoldenGate Active-Active Replication with Conflict Detection and Resolution (CDR) part 2
链接: http://www.traveldba.com/archives/554
ogg active-active 冲突解决方法USEMAX ，这次实验是验证insert操作采用usemax
ogg reolicat 进程配置文件
GGSCI (enmotech) 46> view params rpee
 
REPLICAT rpee
setenv ( NLS_LANG = AMERICAN_AMERICA.ZHS16GBK )
USERID goldengate,PASSWORD goldengate
SQLEXEC "ALTER SESSION SET CONSTRAINTS=DEFERRED"
REPORT AT 01:59
REPORTROLLOVER AT 02:00
--HANDLECOLLISIONS
DISCARDFILE ./dirrpt/rpee.dsc,PURGE
DISCARDROLLOVER AT 02:00
GETTRUNCATES
ASSUMETARGETDEFS
DYNAMICRESOLUTION
 
ALLOWNOOPUPDATES;
 
--This starts the macro
MACRO #exception_handler
BEGIN
, TARGET goldengate.exceptions
, COLMAP ( rep_name = "RPEE"
, table_name = @GETENV ("GGHEADER", "TABLENAME")
, errno = @GETENV ("LASTERR", "DBERRNUM")
, dberrmsg = @GETENV ("LASTERR", "DBERRMSG")
, optype = @GETENV ("LASTERR", "OPTYPE")
, errtype = @GETENV ("LASTERR", "ERRTYPE")
, logrba = @GETENV ("GGHEADER", "LOGRBA")
, logposition = @GETENV ("GGHEADER", "LOGPOSITION")
, committimestamp = @GETENV ("GGHEADER", "COMMITTIMESTAMP"))
, INSERTALLRECORDS
, EXCEPTIONSONLY;
END;
 
--REPERROR (DEFAULT, EXCEPTION)
--REPERROR (DEFAULT2, ABEND)
--REPERROR (-1, EXCEPTION)
map ogg1.tgt, target ogg2.tgt1;
--冲突表
map ogg1.test1, target ogg2.test1;
MAP ogg1.test1 #exception_handler();
--MAP ogg1.test2, TARGET ogg2.test2,
MAP ogg1.test2, TARGET ogg2.test2,
comparecols( on update all, on delete all),
RESOLVECONFLICT (UPDATEROWEXISTS, (DEFAULT, USEMAX (last_mod_time))),
RESOLVECONFLICT (INSERTROWEXISTS, (DEFAULT, USEMAX (last_mod_time))),
RESOLVECONFLICT (DELETEROWEXISTS, (DEFAULT, OVERWRITE)),
RESOLVECONFLICT (UPDATEROWMISSING, (DEFAULT, OVERWRITE)),
RESOLVECONFLICT (DELETEROWMISSING, (DEFAULT, DISCARD));
--RESOLVECONFLICT (UPDATEROWEXISTS,(DEFAULT, IGNORE ));
--异常处理
MAP ogg1.test2, TARGET ogg2.test2_exception,
EXCEPTIONSONLY,
INSERTALLRECORDS
SQLEXEC (id qry, query "select name, phone, address, salary, balance, comment1, last_mod_time from ogg2.test2 where name = :p1", PARAMS(p1 = name )), 
COLMAP ( rep_name = "RPEE" 
,table_name = @GETENV ("GGHEADER", "TABLENAME") 
,errno = @GETENV ("LASTERR", "DBERRNUM") 
,dberrmsg = @GETENV ("LASTERR", "DBERRMSG") 
,optype = @GETENV ("LASTERR", "OPTYPE") 
,errtype = @GETENV ("LASTERR", "ERRTYPE") 
,logrba = @GETENV ("GGHEADER", "LOGRBA")
,logposition = @GETENV ("GGHEADER", "LOGPOSITION") 
,committimestamp = @GETENV ("GGHEADER", "COMMITTIMESTAMP")
,res_date = @DATENOW() 
,name_before = before.name
,phone_before = before.phone 
,address_before = before.address 
,salary_before = before.salary
,balance_before = before.balance 
,comment1_before = before.comment1
, LAST_MOD_TIME = before.last_mod_time
, name_current = qry.name
, phone_current = qry.phone
, address_current = qry.address
, salary_current = qry.salary
, balance_current = qry.balance
, comment1_current = qry.comment1
, last_mod_time_current = qry.last_mod_time
);
map ogg1.test3, target ogg2.test3,COLMAP (USEDEFAULTS);
这里主要的参数是
RESOLVECONFLICT (INSERTROWEXISTS, (DEFAULT, USEMAX (last_mod_time))),
查看两边数据
SQL> col name FOR a8
SQL> col ADDRESS FOR a15
SQL> col COMMENT1 FOR a20
 
SQL> SELECT * FROM ogg1.test2;
 
NAME     PHONE      ADDRESS             SALARY    BALANCE COMMENT1             LAST_MOD_TIME
-------- ---------- --------------- ---------- ---------- -------------------- ---------------------------------------------------------------------------
Mary     1234567890 Oracle Pkwy            100        400 ccc                  18-OCT-13 09.18.35.000000 PM
Mary1    1234567890 Oracle Pkwy            100        100 ccc                  04-FEB-13 12.00.00.000000 AM
Mary2    1111111    Ralston               1000       1600 bbb                  18-OCT-13 10.02.57.000000 PM
Mary3    1111111    Ralston               1000       1600 bbb                  18-OCT-13 10.04.49.000000 PM
 
SQL> SELECT * FROM ogg2.test2;
 
NAME     PHONE      ADDRESS             SALARY    BALANCE COMMENT1             LAST_MOD_TIME
-------- ---------- --------------- ---------- ---------- -------------------- ---------------------------------------------------------------------------
Mary2    1111111    Ralston               2000       2600 bbb                  18-OCT-13 10.03.04.000000 PM
Mary3    1111111    Ralston               2000       2600 bbb                  18-OCT-13 10.04.40.000000 PM
Mary     1111111    Ralston                300        800 bbb                  18-OCT-13 09.19.06.000000 PM
Mary1    1111111    Ralston                300        600 bbb                  03-FEB-13 12.00.00.000000 AM
 
两边在同一时间插入数据
 
ogg1.test2
<pre lang="sql">
22:15:07 SQL> INSERT INTO ogg1.test2 VALUES ('Mary7','1111111','Ralston',1000,1600,'bbb',sysdate);
 
1 ROW created.
 
22:15:50 SQL> commit;
 
Commit complete.
ogg2.test2
22:15:38 SQL> INSERT INTO ogg2.test2 VALUES ('Mary7','1111111','Ralston',2000,2600,'bbb',sysdate);
 
1 ROW created.
 
22:15:43 SQL> commit;
 
Commit complete.
查看结果
22:16:00 SQL> SELECT * FROM ogg2.test2;
 
NAME     PHONE      ADDRESS             SALARY    BALANCE COMMENT1             LAST_MOD_TIME
-------- ---------- --------------- ---------- ---------- -------------------- ---------------------------------------------------------------------------
Mary2    1111111    Ralston               2000       2600 bbb                  18-OCT-13 10.03.04.000000 PM
Mary3    1111111    Ralston               2000       2600 bbb                  18-OCT-13 10.04.40.000000 PM
Mary4    1111111    Ralston               2000       2600 bbb                  18-OCT-13 10.06.53.000000 PM
Mary5    1111111    Ralston               1000       1600 bbb                  18-OCT-13 10.10.24.000000 PM
Mary     1111111    Ralston                300        800 bbb                  18-OCT-13 09.19.06.000000 PM
Mary6    1111111    Ralston               2000       2600 bbb                  18-OCT-13 10.14.55.000000 PM
Mary7    1111111    Ralston               1000       1600 bbb                  18-OCT-13 10.15.50.000000 PM
Mary1    1111111    Ralston                300        600 bbb                  03-FEB-13 12.00.00.000000 AM
从上面可以看出在insert是采用了usemax(LAST_MOD_TIME)规则
看下ogg的冲突解决记录
GGSCI (enmotech) 57> stats rpee ,table ogg2.test2 ,reportcdr
 
Sending STATS request to REPLICAT RPEE ...
 
Start of Statistics at 2013-10-18 22:17:53.
 
Replicating from OGG1.TEST2 to OGG2.TEST2:
 
*** Total statistics since 2013-10-18 22:10:35 ***
        Total inserts                                      3.00
        Total updates                                      0.00
        Total deletes                                      0.00
        Total discards                                     0.00
        Total operations                                   3.00
        Total CDR conflicts                                3.00
        CDR resolutions succeeded                          3.00
        CDR INSERTROWEXISTS conflicts                      3.00
 
*** Daily statistics since 2013-10-18 22:10:35 ***
        Total inserts                                      3.00
        Total updates                                      0.00
        Total deletes                                      0.00
        Total discards                                     0.00
        Total operations                                   3.00
        Total CDR conflicts                                3.00
        CDR resolutions succeeded                          3.00
        CDR INSERTROWEXISTS conflicts                      3.00
 
*** Hourly statistics since 2013-10-18 22:10:35 ***
        Total inserts                                      3.00
        Total updates                                      0.00
        Total deletes                                      0.00
        Total discards                                     0.00
        Total operations                                   3.00
        Total CDR conflicts                                3.00
        CDR resolutions succeeded                          3.00
        CDR INSERTROWEXISTS conflicts                      3.00
 
*** Latest statistics since 2013-10-18 22:10:35 ***
        Total inserts                                      3.00
        Total updates                                      0.00
        Total deletes                                      0.00
        Total discards                                     0.00
        Total operations                                   3.00
        Total CDR conflicts                                3.00
        CDR resolutions succeeded                          3.00
        CDR INSERTROWEXISTS conflicts                      3.00
 
End of Statistics.