安装oracle和db2,安装配置Oracle GoldenGate for DB2(单向)

本文的配置只支持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

Copyright (C) 1995, 2012, Oracle and/or its affiliates. All

rights reserved.

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.