ITPub博客

首页 > 数据库 > Oracle > Oracle GoldenGate系统之----双向同步数据表

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

原创 Oracle 作者:wailon 时间:2013-12-07 00:15:23 0 删除 编辑
双向同步与单向同步类似,但需要着重注意两个问题:防止数据循环和防止数据冲突。
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,password tiger
 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,password tiger
 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>insert into 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;

     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
      4000 GZITECH                                            2000

16 rows selected.

A端插入:
09:34:18 LRJ@wailon>insert into 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;

     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
      3000 WAILON     CLERK
      4000 GZITECH                                            2000

17 rows selected.

 

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

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

注册时间:2013-11-08

  • 博文量
    51
  • 访问量
    291509