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

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

Oracle GoldenGate configuration elements for active-activesynchronization

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

http://blog.csdn.net/lovehewenyu/article/details/9127405

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

http://blog.csdn.net/lovehewenyu/article/details/9130229

 

学习【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端

checkpointtableogg.checkpoint   

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

checkpointtableogg.checkpoint

2、configure manager processes

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

port 7809    --指定管理端口

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

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

autorestart er  *,retries 5 ,waitminutes 2  --extract and replicat processes fail mgrautomatically 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 * ,waitminutes2,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、 toconfigure 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、 toconfigure the data pump           --A端

add extract pdou01 ,exttrailsource /opt/ogg/dirdat/e1 ,beginnow

add rmttrail /u01/ogg/dirdat/r1 , extract pdou01  --<remote_trail>

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、 toconfigure the replicat group     --B端

add replicat rdou01,exttrail /u01/ogg/dirdat/r1 ,beginnow

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

addextract edou03,tranlog,begin now

addexttrail /u01/ogg/dirdat/e3,extract edou03

GGSCI(localhost.localdomain) 57> view params edou03

extractedou03

useridogg,password oracle

exttrail/u01/ogg/dirdat/e3

tranlogoptionsexcludeuser ogg

tabledoudou.*;

       2、toconfigure the pump

addextract pdou03 ,exttrailsource /u01/ogg/dirdat/e3 ,begin now

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

GGSCI(localhost.localdomain) 58> view params pdou03

extractpdou03

useridogg ,password oracle

rmthost192.168.1.217 ,mgrport 7809

rmttrail/opt/ogg/dirdat/r3

TRANLOGOPTIONSasmuser sys@asm,asmpassword oracle

passthru

tabledoudou.*;

       3、toconfigure the replicat group

addreplicat rdou03,exttrail /opt/ogg/dirdat/r3 ,begin now

GGSCI(doudou-NAS) 60> view params rdou03

replicatrdou03

assumetargetdefs

useridogg ,password oracle

mapdoudou.*,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_doudouvalues (1,'doudou');

1 row created.

SQL> commit;

Commit complete.

SQL> select count(*) frombi_doudou;

 

  COUNT(*)

----------

         1

--B端

SQL>  select * from bi_doudou;

 

        ID NAME

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

         1 doudou

 

secoundary system to primary system

--B端

SQL> insert into bi_doudouvalues (2,'xiaoyu');

1 row created.

SQL> commit;

Commit complete.

SQL> select * frombi_doudou;

        ID NAME

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

         1 doudou

         2 xiaoyu

--A端

SQL> select * frombi_doudou;

        ID NAME

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

         1 doudou

         2 xiaoyu

 

 

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

a)  只在一段开展业务

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

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