本文的配置只支援source端到target端的同步，不支援雙向同步，也不支援DDL操作和truncate table。

一、環境介紹

source端：

OS：WindowsXP 32bit

Database：DB2 v9.7.100.177 Fix Pack 1

OGG：Oracle GoldenGate V11.2.1.0.1 for DB2 9.7 on Windows 2003, 2008

ip：192.168.3.168

target端：

OS：RHEL Server release 5.8 64bit

Database：DB2 v9.7.0.3 Fix Pack 3

OGG：GG_V11.2.1.0.1 for DB2_9.7 on Linux x86_64

ip：192.168.3.239

二、資料庫準備

source端和target端資料庫為test。由於DB2的使用者機制使用的是作業系統使用者，我這裡不建立新使用者。source端的ogg安裝、Extract使用者都使用當前域使用者liuxiaohui（官方文件建議ogg安裝使用administrator使用者）；target端的ogg安裝、Replicat使用者都使用db2inst1。

1、建立測試表

在source端建立db2inst1.t1表，並插入資料：

D:\>db2 connect to test user db2inst1 using system

資料庫連線資訊

資料庫伺服器 = DB2/NT 9.7.1

SQL 授權標識 = DB2INST1

本地資料庫別名 = TEST

D:\>db2 create table t1(id integer,name varchar(10))

DB20000I SQL 命令成功完成。

D:\>db2 insert into t1 values(1,'a')

DB20000I SQL 命令成功完成。

D:\>db2 insert into t1 values(2,'b')

DB20000I SQL 命令成功完成。

在target端建立相同結構的db2inst1.t1表，不插入資料：

[db2inst1@localhost ~]$ db2 connect to test

Database Connection Information

Database server = DB2/LINUXX8664 9.7.3

SQL authorization ID = DB2INST1

Local database alias = TEST

[db2inst1@localhost ~]$ db2 "create table t1(id integer,name varchar(10))"

DB20000I The SQL command completed successfully.

2、配置資料庫歸檔模式：

C:\Documents and Settings\liuxiaohui>db2 connect to test

資料庫連線資訊

資料庫伺服器 = DB2/NT 9.7.1

SQL 授權標識 = LIUXIAOH...

本地資料庫別名 = TEST

C:\Documents and Settings\liuxiaohui>db2 get db cfg | findstr LOGARCHMETH1

第一個日誌歸檔方法 (LOGARCHMETH1) = DISK:d:\db2\

三、安裝ogg軟體，啟動mgr管理程式

1、source端：

在D：下建立目錄D:\Oracle_GoldenGate

將安裝包解壓至D:\Oracle_GoldenGate目錄下

雙擊執行ggsci ，

GGSCI (liuxiaohui) 1> create subdirs

GGSCI (liuxiaohui) 2> edit params mgr

GGSCI (liuxiaohui) 3> view params mgr

PORT 7809

GGSCI (liuxiaohui) 4> start mgr

Manager started.

Windows下，預設情況下，manager不作為service安裝，可以使用本地或域使用者執行。這樣，使用者log out後，manager就會stop。如果將manager作為service安裝，就可以使它和使用者的連線獨立開執行，可以配置它手動啟動或者隨系統啟動：

在cmd中，切換目錄至D:\Oracle_GoldenGate，執行如下命令：

D:\>cd Oracle_GoldenGate

D:\Oracle_GoldenGate>install addservice

Service 'GGSMGR' created.

Install program terminated normally.

這時，在“服務”中，就多了GGSMGR項。

此時，再開啟manager：

GGSCI (liuxiaohui) 5> start mgr

Starting Manager as service ('GGSMGR')...

Service started.

GGSCI (liuxiaohui) 6> info mgr

Manager is running (IP port liuxiaohui.7809).

2、target端：

[db2inst1@localhost ~]$ cd /opt

[db2inst1@localhost opt]$ mkdir ggs

[db2inst1@localhost opt]$ cd ggs

[db2inst1@localhost ggs]$ tar -xvf /opt/ggs_Linux_x64_db297_64bit.tar

此時執行GGSCI可能會提示lidb2.so.1共享庫相關錯誤：

“./ggsci: error while loading shared libraries: libdb2.so.1: cannot open shared object file: No such file or directory”

需要先設定共享庫路徑：

[db2inst1@localhost ggs]$ export LD_LIBRARY_PATH=/opt/ibm/db2/V9.7/lib64

[db2inst1@localhost ggs]$ echo $LD_LIBRARY_PATH

/opt/ibm/db2/V9.7/lib64

[db2inst1@localhost ggs]$ ./ggsci

GGSCI (localhost.localdomain) 1> create subdirs

GGSCI (localhost.localdomain) 2> edit params mgr

GGSCI (localhost.localdomain) 3> view params mgr

PORT 7809

GGSCI (localhost.localdomain) 4> start mgr

Manager started.

GGSCI (localhost.localdomain) 5> info mgr

Manager is running (IP port localhost.localdomain.7809).

四、初始化載入資料

在異構平臺（例如oracle-mysql），這個功能顯得非常有用。而在DB2-DB2或oracle-oracle的資料複製條件下，可以使用資料庫本身的匯入匯出和遷移工具。

1、source端新增extract程式

GGSCI (liuxiaohui) 7> add extract einig1,sourceistable

EXTRACT added.

//sourceistable代表直接從表中讀取資料

//einig1代表extract initial load group 1縮寫

GGSCI (liuxiaohui) 8> edit params einig1

GGSCI (liuxiaohui) 9> view params einig1

extract einig1

sourcedb test userid liuxiaohui,password liu@123456

rmthost 192.168.3.239,mgrport 7809

rmttask replicat,group rinig1

table db2inst1.t1;

2、target端新增replicat程式

GGSCI (localhost.localdomain) 1> add replicat rinig1,specialrun

REPLICAT added.

//specialrun代表只執行一次

GGSCI (localhost.localdomain) 6> edit params rinig1

//rinig1代表replicat initial load group 1縮寫

//rinig1的名字必須同source端定義的group名字相同

GGSCI (localhost.localdomain) 7> view params rinig1

replicat rinig1

assumetargetdefs

targetdb test userid db2inst1,password topnet

discardfile ./dirrpt/rinig1.dsc,purge

map db2inst1.*,target db2inst1.*;

3、source端啟動extract程式，檢視日誌輸出

GGSCI (liuxiaohui) 12> start extract einig1

Sending START request to MANAGER ('GGSMGR') ...

EXTRACT EINIG1 starting

GGSCI (liuxiaohui) 13> view report einig1

2013-01-15 16:03:04 INFO OGG-01017 Wildcard resolution set to IMMEDIATE because SOURC

EISTABLE is used.

***********************************************************************

Oracle GoldenGate Capture for DB2

Version 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230

Windows (optimized), DB2 9.7 on Apr 23 2012 07:49:42

Starting at 2013-01-15 16:03:04

***********************************************************************

Operating System Version:

Microsoft Windows XP Professional, on x86

Version 5.1 (Build 2600: Service Pack 3)

Process id: 4708

Description:

***********************************************************************

** Running with the following parameters **

***********************************************************************

2013-01-15 16:03:04 INFO OGG-03035 Operating system character set identified as GBK.

Locale: zh_Hans_CN, LC_ALL:.

extract einig1

sourcedb test userid liuxiaohui,password **********

2013-01-15 16:03:04 INFO OGG-03036 Database character set identified as UTF-8. Locale

: zh_Hans_CN.

2013-01-15 16:03:04 INFO OGG-03037 Session character set identified as windows-936.

rmthost 192.168.3.239,mgrport 7809

rmttask replicat,group rinig1

table db2inst1.t1;

Using the following key columns for source table DB2INST1.T1: ID, NAME.

2013-01-15 16:03:04 INFO OGG-01815 Virtual Memory Facilities for: COM

anon alloc: MapViewOfFile anon free: UnmapViewOfFile

file alloc: MapViewOfFile file free: UnmapViewOfFile

target directories:

D:\Oracle_GoldenGate\dirtmp.

CACHEMGR virtual memory values (may have been adjusted)

CACHESIZE: 1G

CACHEPAGEOUTSIZE (normal): 4M

PROCESS VM AVAIL FROM OS (min): 1.50G

CACHESIZEMAX (strict force to disk): 1.37G

Database Version:

DB2/NT

Version 09.07.0001

ODBC Version 03.01.0000

Driver Information:

DB2CLI.DLL

Version 09.07.0001

ODBC Version 03.51

Database Language and Character Set:

Application Codepage = 1386

Database Codepage = 1208

Warning: Your Application codepage setting does not match database codepage setting.

Please refer to user manual for more information.

Processing table DB2INST1.T1

***********************************************************************

* ** Run Time Statistics ** *

***********************************************************************

Report at 2013-01-15 16:03:10 (activity since 2013-01-15 16:03:04)

Output to rinig1:

From Table DB2INST1.T1:

# inserts: 2

# updates: 0

# deletes: 0

# discards: 0

4、target端驗證

[db2inst1@localhost ggs]$ db2 connect to test

Database Connection Information

Database server = DB2/LINUXX8664 9.7.3

SQL authorization ID = DB2INST1

Local database alias = TEST

[db2inst1@localhost ggs]$ db2 "select * from db2inst1.t1"

ID NAME

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

1 a

2 b

2 record(s) selected.

五、配置實時同步

1、在source端配置extract程式

程式的名字不能超過8個字元

GGSCI (liuxiaohui) 14> edit params edb2_t1

GGSCI (liuxiaohui) 15> view params edb2_t1

extract edb2_t1

sourcedb test userid liuxiaohui,password liu@123456

exttrail .\dirdat\aa

table db2inst1.*;

2、新增trandata

GGSCI (liuxiaohui) 16> dblogin sourcedb test,userid liuxiaohui,password liu@123456

2013-01-15 16:23:05 INFO OGG-03036 Database character set identified as UTF-8. Locale

: zh_Hans_CN.

2013-01-15 16:23:05 INFO OGG-03037 Session character set identified as windows-936.

Successfully logged into database.

GGSCI (liuxiaohui) 17> add trandata db2inst1.*

Logging of supplemental log data (include longvar) is enabled for table DB2INST1.T1

3、新增extract程式，新增trail檔案

trail檔名字首不能超過2個字元

GGSCI (liuxiaohui) 18> add extract edb2_t1,tranlog,begin now

EXTRACT added.

GGSCI (liuxiaohui) 19> add exttrail .\dirdat\aa,extract edb2_t1,megabytes 100

EXTTRAIL added.

GGSCI (liuxiaohui) 20> start extract edb2_t1

Sending START request to MANAGER ('GGSMGR') ...

EXTRACT EDB2_T1 starting

GGSCI (liuxiaohui) 21> info extract edb2_t1

EXTRACT EDB2_T1 Last Started 2013-01-15 16:28 Status RUNNING

Checkpoint Lag 00:01:23 (updated 00:00:07 ago)

Log Read Checkpoint DB2 Transaction Log

2013-01-15 16:26:50.000000 LSN 229816774

GGSCI (liuxiaohui) 22> info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING

EXTRACT RUNNING EDB2_T1 00:00:00 00:00:05

4、新增配置pump程式

GGSCI (liuxiaohui) 23> edit params pdb2_t1

GGSCI (liuxiaohui) 24> view params pdb2_t1

extract pdb2_t1

passthru

rmthost 192.168.3.239,mgrport 7809

rmttrail ./dirdat/pa

table db2inst1.*;

GGSCI (liuxiaohui) 25> add extract pdb2_t1,exttrailsource .\dirdat\aa

EXTRACT added.

GGSCI (liuxiaohui) 26> add rmttrail ./dirdat/pa,extract pdb2_t1,megabytes 100

RMTTRAIL added.

GGSCI (liuxiaohui) 27> start extract pdb2_t1

Sending START request to MANAGER ('GGSMGR') ...

EXTRACT PDB2_T1 starting

GGSCI (liuxiaohui) 28> info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING

EXTRACT RUNNING EDB2_T1 00:00:00 00:00:00

EXTRACT RUNNING PDB2_T1 00:00:00 00:00:58

5、在target端新增檢查表

GGSCI (localhost.localdomain) 1> edit params ./GLOBALS

GGSCI (localhost.localdomain) 2> view params ./GLOBALS

checkpointtable db2inst1.ggschkpt

GGSCI (localhost.localdomain) 5> quit //這裡需要退出一下

[db2inst1@localhost ggs]$ ./ggsci

GGSCI (localhost.localdomain) 1> dblogin sourcedb test,userid db2inst1,password topnet

2013-01-15 16:42:05 INFO OGG-03036 Database character set identified as UTF-8. Locale: zh_CN.

2013-01-15 16:42:05 INFO OGG-03037 Session character set identified as EUC-CN.

Successfully logged into database.

GGSCI (localhost.localdomain) 2> add checkpointtable

No checkpoint table specified, using GLOBALS specification (db2inst1.ggschkpt)...

Successfully created checkpoint table db2inst1.ggschkpt.

6、在target端新增配置replicat程式

GGSCI (localhost.localdomain) 3> edit params rdb2_t1

GGSCI (localhost.localdomain) 4> view params rdb2_t1

replicat rdb2_t1

targetdb test userid db2inst1,password topnet

handlecollisions

assumetargetdefs

discardfile ./dirrpt/rdb2_t1.dsc,purge

map db2inst1.*,target db2inst1.*;

GGSCI (localhost.localdomain) 5> add replicat rdb2_t1,exttrail ./dirdat/pa

REPLICAT added.

GGSCI (localhost.localdomain) 6> start replicat rdb2_t1

Sending START request to MANAGER ...

REPLICAT RDB2_T1 starting

GGSCI (localhost.localdomain) 7> info replicat rdb2_t1

REPLICAT RDB2_T1 Last Started 2013-01-15 16:50 Status RUNNING

Checkpoint Lag 00:00:00 (updated 00:00:07 ago)

Log Read Checkpoint File ./dirdat/pa000000

First Record RBA 0

GGSCI (localhost.localdomain) 8> info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING

REPLICAT RUNNING RDB2_T1 00:00:00 00:00:02

六、測試同步

1、插入測試

D:\Oracle_GoldenGate>db2 select * from db2inst1.t1

ID NAME

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

1 a

2 b

2 條記錄已選擇。

D:\Oracle_GoldenGate>db2 insert into db2inst1.t1 values(3,'c')

DB20000I SQL 命令成功完成。

D:\Oracle_GoldenGate>db2 select * from db2inst1.t1

ID NAME

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

1 a

2 b

3 c

3 條記錄已選擇。

[db2inst1@localhost ggs]$ db2 "select * from db2inst1.t1"

ID NAME

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

1 a

2 b

3 c

3 record(s) selected.

2、修改測試

D:\Oracle_GoldenGate>db2 update db2inst1.t1 set name='E' where id=3

DB20000I SQL 命令成功完成。

D:\Oracle_GoldenGate>db2 select * from db2inst1.t1

ID NAME

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

1 a

2 b

3 E

3 條記錄已選擇。

[db2inst1@localhost ggs]$ db2 "select * from db2inst1.t1"

ID NAME

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

1 a

2 b

3 E

3 record(s) selected.

3、刪除測試

D:\Oracle_GoldenGate>db2 delete from db2inst1.t1 where id=3

DB20000I SQL 命令成功完成。

D:\Oracle_GoldenGate>db2 select * from db2inst1.t1

ID NAME

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

1 a

2 b

2 條記錄已選擇。

[db2inst1@localhost ggs]$ db2 "select * from db2inst1.t1"

ID NAME

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

1 a

2 b

2 record(s) selected.

安裝配置Oracle GoldenGate for DB2(單向)

相關文章