在单向同步的基础上配置Oracle GoldenGate支持双向同步，支持单向truncate操作。
这里，将原来配置好的source端和target端，分别称为：primary system和secondary system.
一、primary system配置
1、在primary system的primary Extract group（我这里是edb2_t1）参数文件中添加如下内容：
--Exclude Replicat transactions
TRANLOGOPTIONS EXCLUDEUSER db2inst1
添加该项的目的是，使Extract识别出Replicat 产生的transactions。由于在双向同步中，primary system和secondary system都同时具有Extract和Replicat group，如果Extract从Replicat产生的transactions中抽取数据，那么将造成死循环。默认情况（或者Extract参数文件中指定GETAPPLOPS和IGNOREREPLICATES时），Extract进程将不从Replicat产生的transactions中抽取数据。但是，仍然必须在Extract参数文件中指定Replicat的用户名，以使Extract识别出Replicat产生的transactions。
因为这样的原因，Extract和Replicat必须使用不同的用户。本人使用同一用户测试时失败！
我这里，primary system端，Extract用户为liuxiaohui，Replicat用户为db2inst1；secondary system端，Extract用户为lxh，Replicat用户为db2inst1.
2、在primary system添加检查表
GGSCI (liuxiaohui) 141> edit params ./GLOBALS
GGSCI (liuxiaohui) 142> view params ./GLOBALS
CHECKPOINTTABLE db2inst1.ggschkpt
GGSCI (liuxiaohui) 143> quit
D:\Oracle_GoldenGate>ggsci
GGSCI (liuxiaohui) 2> add checkpointtable db2inst1.ggschkpt
3、在primary system配置Replicat group
GGSCI (liuxiaohui) 3> add replicat rdb2_t2,exttrail .\dirdat\pa
REPLICAT added.
GGSCI (liuxiaohui) 6> edit params rdb2_t2
GGSCI (liuxiaohui) 6> view params rdb2_t2
--Identify the Replicat group
REPLICAT rdb2_t2
--Specify database login information as needed for the database
TARGETDB test USERID db2inst1,PASSWORD system
--Whether or not Replicat tries to resolve duplicate-record
--and missing-record errors when applying SQL on the target
HANDLECOLLISIONS
--State that source and target definitions are identical
--Directs Oracle GoldenGate not to look up source structures
--from a source-definitions file
ASSUMETARGETDEFS
--Specify a discard file to which Oracle GoldenGate can log records
--that it cannot process
DISCARDFILE .\dirrpt\rdb2_t2.dsc,PURGE
--Specify to processes table truncate operations
IGNORETRUNCATES
--Specify tables for delivery
MAP db2inst1.*,TARGET db2inst1.*;
二、secondary system配置
1、在secondary system配置primary Extract group
GGSCI (localhost.localdomain) 2> add extract edb2_t2 tranlog,begin now
EXTRACT added.
GGSCI (localhost.localdomain) 3> add exttrail ./dirdat/aa,extract edb2_t2
EXTTRAIL added.
GGSCI (localhost.localdomain) 4> edit params edb2_t2
--Identify the Extract group
EXTRACT edb2_t2
--Specify database login information as needed for the database
SOURCEDB test,USERID lxh,PASSWORD system
--Specify the local trail file that this Extract writes to
EXTTRAIL ./dirdat/aa
--Exclude Replicat transactions
TRANLOGOPTIONS EXCLUDEUSER db2inst1
--Specify to processes table truncate operations
IGNORETRUNCATES
--Specify tables to be captured
TABLE db2inst1.*;
2、在secondary system配置pump
GGSCI (localhost.localdomain) 5> add extract pdb2_t2,exttrailsource ./dirdat/aa
EXTRACT added.
GGSCI (localhost.localdomain) 6> add rmttrail .\dirdat\pa,extract pdb2_t2
RMTTRAIL added.
GGSCI (localhost.localdomain) 7> edit params pdb2_t2
GGSCI (localhost.localdomain) 8> view params pdb2_t2
--Identiry the data pump group
EXTRACT pdb2_t2
--Pass data through without mapping,filtering,conversion
PASSTHRU
--Specify the name or IP address of the target system
RMTHOST 192.168.3.168,MGRPORT 7809
--Specify the remote trail on the target system
RMTTRAIL .\dirdat\pa
--Specify tables to be captured
TABLE db2inst1.*;
3、在secondary system添加trandata
GGSCI (localhost.localdomain) 9> dblogin sourcedb test,userid db2inst1,password topnet
2013-01-18 09:14:38 INFO OGG-03036 Database character set identified as UTF-8. Locale: zh_CN.
2013-01-18 09:14:38 INFO OGG-03037 Session character set identified as EUC-CN.
Successfully logged into database.
GGSCI (localhost.localdomain) 11> add trandata db2inst1.*
Logging of supplemental log data (include longvar) is enabled for table DB2INST1.GGSCHKPT
Logging of supplemental log data (include longvar) is enabled for table DB2INST1.T1
Logging of supplemental log data (include longvar) is enabled for table DB2INST1.T2
Logging of supplemental log data (include longvar) is enabled for table DB2INST1.T3
三、测试
当前两端表中数据：
D:\>db2 connect to test
数据库连接信息
数据库服务器 = DB2/NT 9.7.1
SQL 授权标识 = LIUXIAOH...
本地数据库别名 = TEST
D:\>db2 select * from db2inst1.t1
ID NAME
----------- ----------
3 c
1 a
2 b
3 条记录已选择。
D:\>db2 select * from db2inst1.t2
ID NAME
----------- ----------
100 xxx
200 www
400 kkk
3 条记录已选择。
[lxh@localhost ~]$ db2 "select * from db2inst1.t1"
ID NAME
----------- ----------
1 a
2 b
3 c
3 record(s) selected.
[lxh@localhost ~]$ db2 "select * from db2inst1.t2"
ID NAME
----------- ----------
200 www
100 xxx
400 kkk
3 record(s) selected.
1、插入测试：
primary systemàsecondary system
D:\>db2 "insert into db2inst1.t1 values(111,'aaaa')"
DB20000I SQL 命令成功完成。
[lxh@localhost ~]$ db2 "select * from db2inst1.t1"
ID NAME
----------- ----------
1 a
2 b
3 c
111 aaaa
4 record(s) selected.
D:\>db2 "insert into db2inst1.t2 values(001,'a')"
DB20000I SQL 命令成功完成。
[lxh@localhost ~]$ db2 "select * from db2inst1.t2"
ID NAME
----------- ----------
200 www
100 xxx
400 kkk
1 a
4 record(s) selected.
secondary systemàprimary system:
[lxh@localhost ~]$ db2 "insert into db2inst1.t1 values(3333,'zzzz')"
DB20000I The SQL command completed successfully.
D:\>db2 select * from db2inst1.t1
ID NAME
----------- ----------
3 c
111 aaaa
1 a
2 b
3333 zzzz
5 条记录已选择。
[lxh@localhost ~]$ db2 "insert into db2inst1.t2 values(3333,'zzzz')"
DB20000I The SQL command completed successfully.
D:\>db2 select * from db2inst1.t2
ID NAME
----------- ----------
100 xxx
200 www
1 a
400 kkk
3333 zzzz
5 条记录已选择。
2、修改测试：
primary systemàsecondary system:
D:\>db2 update db2inst1.t1 set name='success'
DB20000I SQL 命令成功完成。
[lxh@localhost ~]$ db2 "select * from db2inst1.t1"
ID NAME
----------- ----------
1 success
2 success
3 success
111 success
3333 success
5 record(s) selected.
D:\>db2 update db2inst1.t2 set name='success'
DB20000I SQL 命令成功完成。
[lxh@localhost ~]$ db2 "select * from db2inst1.t2"
ID NAME
----------- ----------
200 success
100 success
400 success
1 success
3333 success
5 record(s) selected.
secondary systemàprimary system:
[lxh@localhost ~]$ db2 "update db2inst1.t1 set name='keep' where id<4"
DB20000I The SQL command completed successfully.
D:\>db2 select * from db2inst1.t1
ID NAME
----------- ----------
3 keep
111 success
1 keep
2 keep
3333 success
5 条记录已选择。
[lxh@localhost ~]$ db2 "update db2inst1.t2 set name='trying' where id>100"
DB20000I The SQL command completed successfully.
D:\>db2 select * from db2inst1.t2
ID NAME
----------- ----------
100 success
200 trying
1 success
400 trying
3333 trying
5 条记录已选择。
3、删除测试
primary systemàsecondary system:
D:\>db2 delete from db2inst1.t1 where name='success'
DB20000I SQL 命令成功完成。
[lxh@localhost ~]$ db2 "select * from db2inst1.t1"
ID NAME
----------- ----------
1 keep
2 keep
3 keep
3 record(s) selected.
D:\>db2 delete from db2inst1.t2 where name='success'
DB20000I SQL 命令成功完成。
[lxh@localhost ~]$ db2 "select * from db2inst1.t2"
ID NAME
----------- ----------
200 trying
400 trying
3333 trying
3 record(s) selected.
secondary systemàprimary system:
[lxh@localhost ~]$ db2 "delete from db2inst1.t1 where id>=2"
DB20000I The SQL command completed successfully.
D:\>db2 select * from db2inst1.t1
ID NAME
----------- ----------
1 keep
1 条记录已选择。
[lxh@localhost ~]$ db2 "delete from db2inst1.t2 where id>=400"
DB20000I The SQL command completed successfully.
D:\>db2 select * from db2inst1.t2
ID NAME
----------- ----------
200 trying
1 条记录已选择。
四、truncate操作说明
官方文档说明如下：
Bi-directional replication of TRUNCATES is not supported, but you can configure these operations to be replicated in one direction, while data is replicated in both directions. To replicate TRUNCATES (if supported by Oracle GoldenGate for the database) in an active-active configuration, the TRUNCATES must originate only from one database, and only from the same database each time.
按照官网的说明，Oracle GoldenGate不支持双向truncate操作，但可以配置单向truncate。
配置方法：
Configure the environment as follows:
1.Configure all database roles so that they cannot execute TRUNCATE from any database other than the one that is designated for this purpose.
2. On the system where TRUNCATE will be permitted, configure the Extract and Replicat parameter files to contain the GETTRUNCATES parameter.
3. On the other system, configure the Extract and Replicat parameter files to contain the IGNORETRUNCATES parameter. No TRUNCATES should be performed on this system by applications that are part of the Oracle GoldenGate configuration.
我这里的配置：
在primary system端，Extract group参数文件（edb2_t1）中配置GETTRUNCATES；Replicat group参数文件（rdb2_t2）中配置IGNORETRUNCATES。
在secondary system端，Extract group参数文件（edb2_t2）中配置IGNORETRUNCATES；Replicat group参数文件（rdb2_t1）中配置GETTRUNCATES。
上面的配置支持primary systemàsecondary system的truncate操作：
D:\>db2 truncate table db2inst1.t1 immediate
DB20000I SQL 命令成功完成。
[lxh@localhost ~]$ db2 "select * from db2inst1.t1"
ID NAME
----------- ----------
0 record(s) selected.
D:\>db2 truncate table db2inst1.t2 immediate
DB20000I SQL 命令成功完成。
[lxh@localhost ~]$ db2 "select * from db2inst1.t2"
ID NAME
----------- ----------
0 record(s) selected.
测试双向：
在将primary system端的Replicat group参数文件（rdb2_t2）和secondary system端的Extract group参数文件（edb2_t2）也配置为GETTRUNCATES后，发现两端都可以执行truncate操作，并同步到另一端。没弄明白官方文档的意思…
secondary systemàprimary system
[lxh@localhost ~]$ db2 "insert into db2inst1.t1 values(911,'qqq')"
DB20000I The SQL command completed successfully.
[lxh@localhost ~]$ db2 "select * from db2inst1.t1"
ID NAME
----------- ----------
911 qqq
1 record(s) selected.
D:\>db2 select * from db2inst1.t1
ID NAME
----------- ----------
911 qqq
1 条记录已选择。
[lxh@localhost ~]$ db2 "truncate table db2inst1.t1 immediate"
DB20000I The SQL command completed successfully.
[lxh@localhost ~]$ db2 "select * from db2inst1.t1"
ID NAME
----------- ----------
0 record(s) selected.
D:\>db2 select * from db2inst1.t1
ID NAME
----------- ----------
0 条记录已选择。
测试成功！最后看一下各参数文件的配置：
primary system:
GGSCI (liuxiaohui) 49> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING EDB2_T1 00:22:04 00:00:04
EXTRACT RUNNING PDB2_T1 00:00:00 00:01:41
REPLICAT RUNNING RDB2_T2 00:00:00 00:00:01
GGSCI (liuxiaohui) 50> view params edb2_t1
--Identify the Extract group
EXTRACT edb2_t1
--Specify database login information as needed for the database
SOURCEDB test USERID liuxiaohui,PASSWORD liu@123456
--Specify the trail file
EXTTRAIL .\dirdat\aa
--Exclude Replicat transactions
TRANLOGOPTIONS EXCLUDEUSER db2inst1
--Specify to processes table truncate operations
GETTRUNCATES
--Specify tables to be captured
TABLE db2inst1.*;
GGSCI (liuxiaohui) 51> view params pdb2_t1
--Identify the data pump group
EXTRACT pdb2_t1
--Pass data through without mapping,filtering,conversion
PASSTHRU
--Specify the name or IP address of the target system
RMTHOST 192.168.3.239,MGRPORT 7809
--Specify the remote trail on the target system
RMTTRAIL ./dirdat/pa
--Specify tables to be captured
TABLE db2inst1.*;
GGSCI (liuxiaohui) 52> view params rdb2_t2
--Identify the Replicat group
REPLICAT rdb2_t2
--Specify database login information as needed for the database
TARGETDB test USERID db2inst1,PASSWORD system
--Whether or not Replicat tries to resolve duplicate-record
--and missing-record errors when applying SQL on the target
HANDLECOLLISIONS
--State that source and target definitions are identical
--Directs Oracle GoldenGate not to look up source structures
--from a source-definitions file
ASSUMETARGETDEFS
--Specify a discard file to which Oracle GoldenGate can log records
--that it cannot process
DISCARDFILE .\dirrpt\rdb2_t2.dsc,PURGE
--Specify to processes table truncate operations
--IGNORETRUNCATES
GETTRUNCATES
--Specify tables for delivery
MAP db2inst1.*,TARGET db2inst1.*;
secondary system:
GGSCI (localhost.localdomain) 25> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING EDB2_T2 00:00:00 00:00:01
EXTRACT RUNNING PDB2_T2 00:00:00 00:01:22
REPLICAT RUNNING RDB2_T1 00:00:00 00:00:02
GGSCI (localhost.localdomain) 26> view params edb2_t2
--Identify the Extract group
EXTRACT edb2_t2
--Specify database login information as needed for the database
SOURCEDB test,USERID lxh,PASSWORD system
--Specify the local trail file that this Extract writes to
EXTTRAIL ./dirdat/aa
--Exclude Replicat transactions
TRANLOGOPTIONS EXCLUDEUSER db2inst1
--Specify to processes table truncate operations
--IGNORETRUNCATES
GETTRUNCATES
--Specify tables to be captured
TABLE db2inst1.*;
GGSCI (localhost.localdomain) 27> view params pdb2_t2
--Identiry the data pump group
EXTRACT pdb2_t2
--Pass data through without mapping,filtering,conversion
PASSTHRU
--Specify the name or IP address of the target system
RMTHOST 192.168.3.168,MGRPORT 7809
--Specify the remote trail on the target system
RMTTRAIL .\dirdat\pa
--Specify tables to be captured
TABLE db2inst1.*;
GGSCI (localhost.localdomain) 28> view params rdb2_t1
--Identify the Replicat group
REPLICAT rdb2_t1
--Specify database login information as needed for the database
TARGETDB test USERID db2inst1,PASSWORD topnet
--Whether or not Replicat tries to resolve duplicate-record
--and missing-record errors when applying SQL on the target
HANDLECOLLISIONS
--State that source and target definitions are identical
--Directs Oracle GoldenGate not to look up source structures
--from a source-definitions file
ASSUMETARGETDEFS
--Specify a discard file to which Oracle GoldenGate can log records
--that it cannot process
DISCARDFILE ./dirrpt/rdb2_t1.dsc,purge
--Specify to processes table truncate operations
GETTRUNCATES
--Specify tables for delivery
MAP db2inst1.*,TARGET db2inst1.*;
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/25744374/viewspace-752890/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/25744374/viewspace-752890/
640
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
