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) 两端开展相同业务,但依据地域或其他条件对数据予以区分,两边不操作同一条数据