Oracle goldengate 11g （三）【DML双向复制】

Oracle GoldenGate configuration elements for active-active synchronization

Oracle goldengate 11g （一）【DML单向复制】

http://space.itpub.net/26442936/viewspace-764272

Oracle goldengate 11g （二）【DML and DDL单向复制】

http://space.itpub.net/26442936/viewspace-764335

 

学习【DML双向复制】前，建议把Oracle goldengate 11g （一）【DML单向复制】弄懂，因为【DML双向复制】是在Oracle goldengate 11g （一）【DML单向复制】的基础上完成的

 

 

配置过程，整体还是很简单的。但是注意 参数的解释这个是精华！

 

Prerequisites on both systems

1 、creating a checkpint table

GGSCI (doudou-NAS) 15> view params ./GLOBALS  --A 端

checkpointtable ogg.checkpoint   

GGSCI (localhost.localdomain) 1> view params ./GLOBALS        --B 端

checkpointtable ogg.checkpoint

2 、configure manager processes

GGSCI (doudou-NAS) 28> view params mgr                 --A 端

port 7809    -- 指定管理端口

dynamicportlist 7810-7900  -- 动态端口列表，当指定端口不可用时，管理进程会自动选择一个可用端口。最大可指定256个端口

autostart er  *    --mgr 开启时自动开启所有的extract and replicat process

autorestart er  *,retries 5 ,waitminutes 2  --extract and replicat processes fail mgr automatically start 参数每2分钟重启一次，重启5次后放弃

lagreporthours 1  -- 每1小时检查extract延迟情况

laginfominutes 3  -- 延迟超过3分钟就把信息记录到错误日志里

lagcriticalminutes 5  -- 延迟超过5分钟就把它当做警告记录到错误日志里

purgeoldextracts /opt/ogg/dirdat/e*,usecheckpoints  --purgeoldextracts 参数自动删除OGG已经完成的trail files

purgeoldextracts /opt/ogg/dirdat/r*,usecheckpoints

 

GGSCI (localhost.localdomain) 38> view params mgr      --B 端

port 7809

dynamicportlist 7810-7900

autostart er *

autorestart er * ,waitminutes 2,retries 5

lagreporthours 1

laginfominutes 3

lagcriticalminutes 5

purgeoldextracts /u01/ogg/dirdat/e*,usecheckpoints

purgeoldextracts /u01/ogg/dirdat/r*,usecheckpoints

 

configuration from primary system to secondary system

1、 to configure the primary extract goup            --A 端

add extract edou01,tranlog,begin now  -- 如果想开启intergrated capture把tranlog改为integrated tranlog

add exttrail /opt/ogg/dirdat/e1,extract edou01

GGSCI (doudou-NAS) 41> view params edou01

extract edou01  -- 提取进程组的名字

userid ogg,password oracle  -- 同步用户

exttrail /opt/ogg/dirdat/e1  --trail 文件目录和标识（类似于e1*这样文件）

tranlogoptions excludeuser ogg  -- 避免循环复制的出现

TRANLOGOPTIONS asmuser sys@asm,asmpassword oracle --ASM 存储redo log file 或archive log

table doudou.*;      -- 同步的表或用户下所有的表

2、 to configure the data pump            --A 端

add extract pdou01 ,exttrailsource /opt/ogg/dirdat/e1 ,begin now

add rmttrail /u01/ogg/dirdat/r1 , extract pdou01  --

GGSCI (doudou-NAS) 48> view params pdou01

extract pdou01

userid ogg ,password oracle

rmthost 192.168.1.219 ,mgrport 7809  -- 指定远端IP和MGR端口

rmttrail /u01/ogg/dirdat/r1

passthru    -- 双向复制中数据结构完全相同使用passthru参数调优，不检查表定义

table doudou.*;

3、 to configure the replicat group      --B 端

add replicat rdou01,exttrail /u01/ogg/dirdat/r1 ,begin now

GGSCI (localhost.localdomain) 44> view params rdou01

replicat rdou01

assumetargetdefs    -- 定义目标表和源表使用MAP语法有相同的列结构，当出现热点时，不查看源结构而直接从源定义文件查看

userid ogg ,password oracle

map doudou.*,target doudou.*;

 

configuration from secoundary system to primary system

1 、to configure the primary extract group

add extract edou03,tranlog,begin now

add exttrail /u01/ogg/dirdat/e3,extract edou03

GGSCI (localhost.localdomain) 57> view params edou03

extract edou03

userid ogg,password oracle

exttrail /u01/ogg/dirdat/e3

tranlogoptions excludeuser ogg

table doudou.*;

       2 、to configure the pump

add extract pdou03 ,exttrailsource /u01/ogg/dirdat/e3 ,begin now

add rmttrail /opt/ogg/dirdat/r3 , extract pdou03

GGSCI (localhost.localdomain) 58> view params pdou03

extract pdou03

userid ogg ,password oracle

rmthost 192.168.1.217 ,mgrport 7809

rmttrail /opt/ogg/dirdat/r3

TRANLOGOPTIONS asmuser sys@asm,asmpassword oracle

passthru

table doudou.*;

       3 、to configure the replicat group

add replicat rdou03,exttrail /opt/ogg/dirdat/r3 ,begin now

GGSCI (doudou-NAS) 60> view params rdou03

replicat rdou03

assumetargetdefs

userid ogg ,password oracle

map doudou.*,target doudou.*;

 

查看进程状态

GGSCI (doudou-NAS) 71> info all   --A 端

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING                                          

EXTRACT     RUNNING     EDOU01      00:00:00      00:00:10   

EXTRACT     RUNNING     EDOU02      00:00:00      00:00:10   

EXTRACT     RUNNING     PDOU01      00:00:00      00:00:07   

EXTRACT     RUNNING     PDOU02      00:00:00      00:00:06   

REPLICAT    RUNNING     RDOU03      00:00:00      00:00:07 

 

GGSCI (localhost.localdomain) 68> info all      --B 端

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING                                          

EXTRACT     RUNNING     EDOU03      00:00:00      00:00:02   

EXTRACT     RUNNING     EDOU04      00:00:00      00:00:02   

EXTRACT     RUNNING     PDOU03      00:00:00      00:00:02   

EXTRACT     RUNNING     PDOU04      00:00:00      00:00:02    

REPLICAT    RUNNING     RDOU01      00:00:00      00:00:00  

 

双向复制测试

primary system to secondary system

--A 端

SQL>  select count(*) from bi_doudou;

  COUNT(*)

----------

         0

SQL> insert into bi_doudou values (1,'doudou');

1 row created.

SQL> commit;

Commit complete.

SQL> select count(*) from bi_doudou;

 

  COUNT(*)

----------

         1

--B 端

SQL>  select * from bi_doudou;

 

        ID NAME

---------- ------------------------------

         1 doudou

 

secoundary system to primary system

--B 端

SQL> insert into bi_doudou values (2,'xiaoyu');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from bi_doudou;

        ID NAME

---------- ------------------------------

         1 doudou

         2 xiaoyu

--A 端

SQL> select * from bi_doudou;

        ID NAME

---------- ------------------------------

         1 doudou

         2 xiaoyu

 

 

双向复制避免数据冲突是难点，解决这个难点根本还是调整业务：

a)   只在一段开展业务

b)   两端开展不同业务，涉及不同数据集

c)   两端开展相同业务，但依据地域或其他条件对数据予以区分，两边不操作同一条数据

 

ogg-bi.jpg