Oracle GoldenGate系统之----双向同步数据表_ITPUB博客

标签： | 发表时间：2020-06-05 16:57 | 作者：

出处：http://blog.itpub.net

双向同步与单向同步类似，但需要着重注意两个问题：防止数据循环和防止数据冲突。
1、防止数据循环
在EXTRACT进程中忽略REPLICAT的事务，一般排除提取用户的操作，并且与需要同步的用户分开
如USERID SCOTT,PASSWORD TIGER
TRANLOGOPTIONS EXCLUDEUSER SCOTT
2、防止数据冲突
一般要从应用层面解决，避免操作相同的数据。

以下是配置步骤和参数。假设是A、B两个库之间的同步配置。
从A到B
-- 管理进程配置
GGSCI (dgrac) 1> edit param mgr

PORT 7801
DYNAMICPORTLIST 7802-7820
AUTOSTART ER *
AUTORESTART ER *,RETRIES 3,WAITMINUTES 2,RESETMINUTES 60
STARTUPVALIDATIONDELAY 5
PURGEOLDEXTRACTS ./dirdat/*,USECHECKPOINTS,MINKEEPDAYS 7

--EXTRACT配置
GGSCI (dgrac) 2> add extract exts1,tranlog,begin now
EXTRACT added.

GGSCI (dgrac) 3> edit param exts1
extract exts1
setenv(NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)
userid scott@wailon,passwordtiger
tranlogoptions excludeuser scott
exttrail /u01/app/ogg/dirdat/l1
table lrj.s1;

GGSCI (dgrac) 4> add exttrail /u01/app/ogg/dirdat/l1,extract exts1
EXTTRAIL added.

-- PUMP配置
GGSCI (dgrac) 5> add extract pumps1 ,exttrailsource /u01/app/ogg/dirdat/l1,begin now
EXTRACT added.

GGSCI (dgrac) 6> edit param pumps1
extract pumps1
setenv(NLS_LANG="AMERICAN_AMERICA.ZHS16GBK")
userid scott @wailon,password tiger
rmthost 192.168.56.101,mgrport 7801
rmttrail /u01/app/ogg/dirdat/s1
PASSTHRU
table lrj.s1;

GGSCI (dgrac) 7> add rmttrail /u01/app/ogg/dirdat/s1,extract pumps1
RMTTRAIL added.

--REPLICAT配置

GGSCI (dgrac) 8> edit param reps1
replicat reps1
setenv(NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)
userid scott @wailon, password tiger
assumetargetdefs
reperror default,discard
discardfile /u01/app/ogg/dirout/reps1.dsc,append,megabytes 100
map lrj.s1,target lrj.s1,&
colmap(usedefaults,"ENAME" = "USERNAME"),&
-- 解决冲突，更新记录不存在时插入，但只插入更新的列，其他列为空，还是要人工处理
RESOLVECONFLICT (UPDATEROWMISSING, (DEFAULT, OVERWRITE));
HANDLECOLLISIONS

-- 启动所有进程
GGSCI (dgrac) 9> start mgr
MGR is already running.

GGSCI (dgrac) 10> info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER     RUNNING
EXTRACT     RUNNING     EXTS1       00:00:00      00:00:09
EXTRACT     RUNNING     PUMPS1      00:00:00      00:00:07
REPLICAT    RUNNING     REPS1       00:00:00      00:00:05

从B到A

--管理进程配置
GGSCI (dg) 1> edit param mgr

PORT 7801
DYNAMICPORTLIST 7802-7820
AUTOSTART ER *
AUTORESTART ER *,RETRIES 3,WAITMINUTES 2,RESETMINUTES 60
STARTUPVALIDATIONDELAY 5
PURGEOLDEXTRACTS ./dirdat/*,USECHECKPOINTS,MINKEEPDAYS 7

-- EXTRACT配置
GGSCI (dg) 2> add extract exts1,tranlog,begin now
EXTRACT added.

GGSCI (dg) 3> edit param exts1
extract exts1
setenv(NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)
userid scott@ogg,passwordtiger
tranlogoptions excludeuser scott
exttrail /u01/app/ogg/dirdat/l1
table lrj.s1;

GGSCI (dg) 4> add exttrail /u01/app/ogg/dirdat/l1,extract exts1
EXTTRAIL added.

--PUMP配置
GGSCI (dg) 5> add extract pumps1 ,exttrailsource /u01/app/ogg/dirdat/l1,begin now
EXTRACT added.

GGSCI (dg) 6> edit param pumps1
extract pumps1
setenv(NLS_LANG="AMERICAN_AMERICA.ZHS16GBK")
userid scott@ ogg,password tiger
rmthost 192.168.56.131,mgrport 7801
rmttrail /u01/app/ogg/dirdat/s1
PASSTHRU
table lrj.s1;

GGSCI (dg) 7> add rmttrail /u01/app/ogg/dirdat/s1,extract pumps1
RMTTRAIL added.

-- REPLICAT配置

GGSCI (dg) 8> edit param reps1
replicat reps1
setenv(NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)
userid scott @ogg, password tiger
assumetargetdefs
reperror default,discard
discardfile /u01/app/ogg/dirout/reps1.dsc,append,megabytes 100
map lrj.s1,target lrj.s1,&
colmap(usedefaults,"ENAME" = "USERNAME"),&
RESOLVECONFLICT (UPDATEROWMISSING, (DEFAULT, OVERWRITE));
HANDLECOLLISIONS

-- 启动所有进程
GGSCI (dg) 9> start mgr
MGR is already running.

GGSCI (dg) 10> info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER     RUNNING
EXTRACT     RUNNING     EXTS1       00:00:00      00:00:09
EXTRACT     RUNNING     PUMPS1      00:00:00      00:00:07
REPLICAT    RUNNING     REPS1       00:00:00      00:00:05

数据测试
B端插入：
02:44:09 LRJ@ogg>select* from s1
02:44:12 2 /

     EMPNO USERNAME   JOB              MGR HIREDATE            SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ------------ ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80          1000        100         20
      7499 ALLEN      SALESMAN        7698 20-FEB-81          1800        400         30
      7521 WARD       SALESMAN        7698 22-FEB-81          1450        600         30
      7566 JONES      MANAGER         7839 02-APR-81          3175        100         20
      7654 MARTIN     SALESMAN        7698 28-SEP-81          1450       1500         30
      7698 BLAKE      MANAGER         7839 01-MAY-81          3050        100         30
      7782 CLARK      MANAGER         7839 09-JUN-81          2650        100         10
      7788 SCOTT      ANALYST         7566 19-APR-87          3200        100         20
      7839 KING       PRESIDENT            17-NOV-81          5200        100         10
      7844 TURNER     SALESMAN        7698 08-SEP-81          1700        100         30
      7876 ADAMS      CLERK           7788 23-MAY-87          1300        100         20
      7900 JAMES      CLERK           7698 03-DEC-81          1150        100         30
      7902 FORD       ANALYST         7566 03-DEC-81          3200        100         20
      7934 MILLER     CLERK           7782 23-JAN-82          1500        100         10
      2000 yojan

15 rows selected.

02:45:41 LRJ@ogg>insertinto s1(empno,username,sal) values(4000,'GZITECH',2000);

1 row created.

02:45:46 LRJ@ogg>commit;

Commit complete.

A端查看：
02:45:50 LRJ@ogg>select* from s1;

16 rows selected.

A端插入：
09:34:18 LRJ@wailon>insertinto s1(empno,ename,job) values(3000,'WAILON','CLERK');

1 row created.

09:34:40 LRJ@wailon>commit;

Commit complete.

B端查看：
09:34:42 LRJ@wailon>select* from s1;

17 rows selected.

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