安装配置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.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/25744374/viewspace-752885/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/25744374/viewspace-752885/