在單向同步的基礎上配置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.*;

配置Oracle GoldenGate for DB2(雙向)

相關文章