AIX6.1上针对于DB2 v9.7数据库本地部署OGG12.3
拥有十几年数据库领域的开发运维经验的打杂IT人
摘要
相对于Oracle GoldenGate 19c/21c版本，12.3是支持IBM DB2 V9的最后一个版本，针对于某些行业老的业务系统或遗留使用的老版本DB2需要迁移或者升级，或许使用GoldenGate 12.3是个不错的选择。
本文主要基于现有AIX下IBM DB2数据库(版本：
v9.7.6
)的环境上安装其对应的Oracle GoldenGate软件(版本为
12.3
)，同时描述了GoldenGate安装后详细配置及对应DB2数据库中的相关配置。最后介绍了怎么从DB2抽取(extract进程)增量数据(基于DB2数据日志)、存量数据(基于现有表中数据获取)的三种方式及投递(replicat进程)数据到DB2数据库中。
环境信息
- 操作系统：AIX 6.1(IBM Power 750)
- 数据库: LUW DB2 v9.7.6
-
OGG软件安装目录：
/home/ogguser/ogg
GoldenGate安装准备
- 确定系统信息 主要确定swap空间、软件安装磁盘空间、操作系统版本。
--查看IBM机器型号
# uname -Mu
IBM,8408-E8D IBM,020695D7T
--查看AIX配置
# prtconf|more
--cpu
# pmcycles -m
CPU 0 runs at 4060 MHz
CPU 1 runs at 4060 MHz
CPU 2 runs at 4060 MHz
CPU 3 runs at 4060 MHz
--物理内存
# lsattr -El mem0
ent_mem_cap I/O memory entitlement in Kbytes False
goodsize 8192 Amount of usable physical memory in Mbytes False
mem_exp_factor Memory expansion factor False
size 8192 Total amount of physical memory in Mbytes False
var_mem_weight Variable memory capacity weight False
--swap
# lsps -a
Page Space Physical Volume Volume Group Size %Used Active Auto Type Chksum
hd6 hdisk0 rootvg 16384MB 1 yes yes lv 0
# df -g
Filesystem GB blocks Free %Used Iused %Iused Mounted on
/dev/hd4 1.00 0.60 40% 11114 8% /
/dev/hd2 5.00 1.84 64% 60467 13% /usr
/dev/hd9var 5.00 4.65 7% 8694 1% /var
/dev/hd3 8.00 0.49 94% 1953 2% /tmp
/dev/hd1 5.00 3.70 26% 242 1% /home
# lsdev -CHc disk
name status location description
hdisk0 Available Virtual SCSI Disk Drive
hdisk1 Available Virtual SCSI Disk Drive
hdisk2 Available Virtual SCSI Disk Drive
# lsvg rootvg
VOLUME GROUP: rootvg VG IDENTIFIER: 00f895d700004c000000016b49431f49
VG STATE: active PP SIZE: 512 megabyte(s)
VG PERMISSION: read/write TOTAL PPs: 198 (101376 megabytes)
MAX LVs: 256 FREE PPs: 96 (49152 megabytes)
LVs: 14 USED PPs: 102 (52224 megabytes)
OPEN LVs: 13 QUORUM: 2 (Enabled)
TOTAL PVs: 2 VG DESCRIPTORS: 3
STALE PVs: 0 STALE PPs: 0
ACTIVE PVs: 2 AUTO ON: yes
MAX PPs per VG: 32512
MAX PPs per PV: 1016 MAX PVs: 32
LTG size (Dynamic): 256 kilobyte(s) AUTO SYNC: no
HOT SPARE: no BB POLICY: relocatable
PV RESTRICTION: none INFINITE RETRY: no
# lsvg -p rootvg
rootvg:
PV_NAME PV STATE TOTAL PPs FREE PPs FREE DISTRIBUTION
hdisk0 active 99 1 00..00..00..00..01
hdisk1 active 99 95 20..16..19..20..20
# lsvg -l rootvg
--操作系统
# oslevel -s
6100-08-03-1339- xlC升级 XLC 是用于为IBM Power®系统创建和维护C应用程序的编译器。GoldenGate 12.3安装需要XL C++ runtime environment (RTE) version 13.1版本及其以上。
--当前xlC版本
# lslpp -l xlC\*
Fileset Level State Description
----------------------------------------------------------------------------
Path: /usr/lib/objrepos
xlC.adt.include 9.0.0.0 COMMITTED C Set ++ Application
Development Toolkit
xlC.aix61.rte 12.1.0.0 COMMITTED IBM XL C++ Runtime for AIX 6.1
and 7.1
xlC.cpp 9.0.0.0 COMMITTED C for AIX Preprocessor
xlC.msg.en_US.cpp 9.0.0.0 COMMITTED C for AIX Preprocessor
Messages--U.S. English
xlC.msg.en_US.rte 12.1.0.0 COMMITTED IBM XL C++ Runtime
Messages--U.S. English
xlC.rte 12.1.0.0 COMMITTED IBM XL C++ Runtime for AIX
xlC.sup.aix50.rte 9.0.0.1 COMMITTED XL C/C++ Runtime for AIX 5.2
--从AIX7.1截至的安装包找到XLC高版本并安装在6.1的环境中，安装后结果如下
# lslpp -l xlC\*
Fileset Level State Description
----------------------------------------------------------------------------
Path: /usr/lib/objrepos
xlC.adt.include 9.0.0.0 COMMITTED C Set ++ Application
Development Toolkit
xlC.aix61.rte 13.1.3.1 COMMITTED IBM XL C++ Runtime for AIX 6.1
and later
xlC.cpp 9.0.0.0 COMMITTED C for AIX Preprocessor
xlC.msg.en_US.cpp 9.0.0.0 COMMITTED C for AIX Preprocessor
Messages--U.S. English
xlC.msg.en_US.rte 13.1.3.1 COMMITTED IBM XL C++ Runtime
Messages--U.S. English
xlC.rte 13.1.3.1 COMMITTED IBM XL C++ Runtime for AIX
xlC.sup.aix50.rte 9.0.0.1 COMMITTED XL C/C++ Runtime for AIX 5.2-
DB2数据库设置 此处以实例
db2ires为例子
--数据库版本
# su - db2ires
$ db2level
DB21085I Instance "db2ires" uses "64" bits and DB2 code release "SQL09076"
with level identifier "08070107".
Informational tokens are "DB2 v9.7.0.6", "s120629", "IP23414", and Fix Pack
Product is installed at "/opt/IBM/db2/V9.7".
--归档设置
$ db2 get db cfg for resdb128|grep LOGARCH
First log archive method (LOGARCHMETH1) = DISK:/dbarc/
Options for logarchmeth1 (LOGARCHOPT1) =
Second log archive method (LOGARCHMETH2) = OFF
Options for logarchmeth2 (LOGARCHOPT2) =
$ ls -l /dbarc/db2ires
total 0
drwxr-x--- 3 db2ires db2ires 256 Jun 17 14:09 RESDB128
drwxr-x--- 3 db2ires db2ires 256 Jun 17 14:08 RESDB386
drwxr-x--- 3 db2ires db2ires 256 Jun 17 14:03 RESDB819
启动DB2的循环日志，此处只设置了
LOGARCHMETH1
，没有使用单独的
overflowlogpath
，注意启用归档时要备份数据库，数据库临时处于不可用状态。详情可以参考
Retaining the Transaction Logs
和
Specifying the Archive Path
GoldenGate安装
- Oracle GoldenGate下载 https://www. oracle.com/technetwork/ middleware/goldengate/downloads/index.html
此处选择
Oracle GoldenGate 12.3.0.1.2 for DB2 9.7 on AIX
-
准备用户和授权
# mkuser ogguser
# passwd ogguser
--设置密码，首次登录请修改
# id ogguser
uid=203(ogguser) gid=1(staff)
--配置DB2环境变量
$ cp .profile .profile.bak
$ cat >> .profile <<EOF
# The following three lines have been added by IBM DB2 instance utilities.
if [ -f /home/db2ires/sqllib/db2profile ]; then
. /home/db2ires/sqllib/db2profile
export DB2INSTANCE=db2ires
--实例db2ires下有3个库，此处只选择其中一个库resdb386，给予ogguser授予dbadmin权限
# su - db2ires
$ db2 connect to resdb386
$ db2 "grant dbadm on database to user ogguser"
$ db2 terminate-
[可选]安装
unzip(依赖软件getext, libiconv, bzip2) 由于从官网下载下来的为zip文件包，此处介绍了怎么在AIX上安装unzip软件。当然，此步骤可选，也有直接选择tar包上传，通过tar命令进行解压。 AIX兼容依赖包下载： ftp://www. oss4aix.org/compatible/ aix61/ unzip软件下载： http://www. perzl.org/aix/index.php? n=Main.Unzip
# rpm -ivh unzip-64bit-6.0-6.aix5.1.ppc.rpm
error: failed dependencies:
libiconv >= 1.15-1 is needed by unzip-64bit-6.0-6
bzip2 >= 1.0.2-4 is needed by unzip-64bit-6.0-6
#ls -lrt *.rpm
-rw-r--r-- 1 root system 169038 Jun 18 15:31 unzip-64bit-6.0-6.aix5.1.ppc.rpm
-rw-r--r-- 1 root system 182389 Jun 18 15:51 bzip2-1.0.5-3.aix5.3.ppc.rpm
-rw-r--r-- 1 root system 1600408 Jun 18 15:51 libiconv-1.16-1.aix5.1.ppc.rpm
-rw-r--r-- 1 root system 5927293 Jun 18 15:55 gettext-0.19.8.1-1.aix5.1.ppc.rpm
# rpm -ivh gettext-0.19.8.1-1.aix5.1.ppc.rpm libiconv-1.16-1.aix5.1.ppc.rpm bzip2-
# rpm -ivh unzip-64bit-6.0-6.aix5.1.ppc.rpm
# which unzip
/usr/bin/unzip- 解压安装OGG
# su - ogguser
$ ls -l
total 281792
-rw-r--r-- 1 ogguser staff 144273469 Jun 18 15:28 123012_ggs_AIX_ppc_db297_64bit.zip
$ unzip 123012_ggs_AIX_ppc_db297_64bit.zip
Archive: 123012_ggs_AIX_ppc_db297_64bit.zip
inflating: ggs_AIX_ppc_db297_64bit.tar
inflating: OGG-12.3.0.1-README.txt
inflating: OGG_WinUnix_Rel_Notes_12.3.0.1.pdf
$ mkdir -p ogg
$ cd ogg
$ tar -xf ../ggs_AIX_ppc_db297_64bit.tar
SQLDataTypes.h ggparam.dat libicudata56.a
UserExitExamples ggsci libicui18n.a
bcpfmt.tpl help.txt libicui18n56.1.a
bcrypt.txt keygen libicui18n56.a
cachefiledump lib libicuuc.a
checkprm libPocoCrypto64.48.a libicuuc56.1.a
chkpt_db2_create.sql libPocoFoundation64.48.a libicuuc56.a
convchk libPocoJSON64.48.a libudt.so
convprm libPocoNet64.48.a libxerces-c-3.1.a
crypto libPocoNetSSL64.48.a logdump
db2cntl.tpl libPocoUtil64.48.a mgr
db_upgrade libPocoXML64.48.a notices.txt
defgen libantlr3c.so oggerr
demo_db2_create.sql libboost_program_options-mt.so pmsrvr
demo_db2_insert.sql libboost_system-mt.so prvtclkm.plb
demo_db2_load.sql libdb-6.1.so replicat
demo_db2_misc.sql libgglog.a retrace
diretc libggnnzitp.a server
dirout libggparam.a sqlldr.tpl
dirwww libggperf.a tcperrs
emsclnt libggrepo.a ucharset.h
extract libggssl.a usrdecs.h
freeBSD.txt libggutil.a zlib.txt
ggMessage.dat libicudata.a
ggcmd libicudata56.1.a-
配置ogguser用户profile 注意：AIX下必须配置
LIBPATH，不然启动ggsci命令时会出现缺少lib库报错。
# su - ogguser
$ cat >> .profile <<EOF
export GG_HOME=/home/ogguser/ogg
export PATH=$GG_HOME:$PATH
export LIBPATH=$GG_HOME:$GG_HOME/lib:$LIBPATH
$ . .profile
$ ggsci -V
Oracle GoldenGate Command Interpreter for DB2
Version 12.3.0.1.2 OGGCORE_12.3.0.1.0_PLATFORMS_171208.0005
AIX 6, ppc, 64bit (optimized), DB2 9.7 on Dec 8 2017 13:15:13
Oracle GoldenGate Command Interpreter for DB2
Version 12.3.0.1.2 OGGCORE_12.3.0.1.0_PLATFORMS_171208.0005
AIX 6, ppc, 64bit (optimized), DB2 9.7 on Dec 8 2017 13:15:13
Operating system character set identified as US-ASCII.
GGSCI (localhost) 1> create subdirs
Creating subdirectories under current directory /home/ogguser
Parameter file /home/ogguser/ogg/dirprm: created.
Report file /home/ogguser/ogg/dirrpt: created.
Checkpoint file /home/ogguser/ogg/dirchk: created.
Process status files /home/ogguser/ogg/dirpcs: created.
SQL script files /home/ogguser/ogg/dirsql: created.
Database definitions files /home/ogguser/ogg/dirdef: created.
Extract data files /home/ogguser/ogg/dirdat: created.
Temporary files /home/ogguser/ogg/dirtmp: created.
Credential store files /home/ogguser/ogg/dircrd: created.
Masterkey wallet files /home/ogguser/ogg/dirwlt: created.
Dump files /home/ogguser/ogg/dirdmp: created.
GGSCI (localhost) 2> show subdirs
Parameter settings:
SET DEBUG OFF
Current directory: /home/ogguser
Editor: vi
...- 配置mgr并启动
$ ggsci
Oracle GoldenGate Command Interpreter for DB2
Version 12.3.0.1.2 OGGCORE_12.3.0.1.0_PLATFORMS_171208.0005
AIX 6, ppc, 64bit (optimized), DB2 9.7 on Dec 8 2017 13:15:13
Operating system character set identified as US-ASCII.
GGSCI (localhost) 1> edit param mgr
port 7809
ACCESSRULE, PROG *, IPADDR *.*.*.*, ALLOW
dynamicportlist 7810-7910
purgeoldextracts ./dirdat/*, usecheckpoints,minkeepdays 14
AUTORESTART ER *, RETRIES 3, WAITMINUTES 2,RESETMINUTES 10
LAGREPORTHOURS 1
LAGINFOMINUTES 30
LAGCRITICALMINUTES 45
GGSCI (localhost) 2> start mgr
Manager started.
GGSCI (localhost) 3> info mgr
Manager is running (IP port localhost.7809, Process ID 12255402).
GGSCI (localhost) 4> view report mgr
$ ps -eaf|grep mgr
ogguser 12255402 1 0 17:54:21 - 0:00 ./mgr PARAMFILE /home/ogguser/ogg/dirprm/mgr.prm REPORTFILE /home/ogguser/ogg/dirrpt/MGR.rpt PROCESSID MGR-
extract配置抓取日志中变化数据 以数据库
resdb386的SYSIBM.SYSTABLES为例，该数据库字符集为GBK编码集(DB2CODEPAGE为 1386 )。其他的db2数据库字符集还有 1208 对应UTF-8编码集, 819 对应ISO8859-1。
$ ggsci
--测试数据库连接
> dblogin sourcedb resdb386 userid ogguser password ogguser
--查看变更抓取属性
> info trandata sysibm.systables
--可以通过ggsci命令行或者DB2命令来启动表抓取属性
GGSCI> ADD TRANDATA <owner>.<table>
DB2 > ALTER TABLE <table_name> DATA CAPTURE CHANGES INCLUDE LONGVAR COLUMNS;
--增量数据抓取(日志中变化数据)
$ cat > dirprm/ext1.prm <<EOF
extract ext1
setenv(DB2CODEPAGE=1386)
sourcedb resdb386 userid ogguser password ogguser
exttrail ./dirdat/e1
table SYSIBM.SYSTABLES;
$ ggsci <<EOF
add ext ext1, tranlog, eof
add exttrail ./dirdat/e1, ext ext1
start ext1
EOF注意：基于LUW DB2跟Oracle等其他数据库日志记录方式的不一致性，这里启动时抽取进程使用eof方式表明从日志的最后一个开始，它不支持从时间方式，如需要指定日志位置可以通过LRI方式。
-
extract配置抓取表中存量数据库 此方式跟数据库日志配置和trandata无关，只需要对表有
selelct权限即可。
$ export DB2CODEPAGE=1208
$ export DB2COUNTRY=86
$ ggsci
--1 存量trail文件落在本地方式
> add ext ie1, sourceistable
> edit param ie1
extract ie1
setenv(DB2CODEPAGE=1386)
sourcedb resdb386 userid ogguser password ogguser
extfile ./dirdat/i1
reportcount every 1 minutes, rate
table SYSIBM.SYSTABLES;
> start ie1
> info ie1, tasks
--查看运行状态
$ ls -l dirdat/i1*
--查看trail文件生产情况
--2 存量trail文件落在远端方式
> add ext ie2, sourceistable
> edit param ie2
extract ie2
sourcedb resdb819 userid ogguser password ogguser
rmthost <remote_ip>, mgrport <remote_port>
rmtfile <remote_ogg_dir>/dirdat/i1
table SYSIBM.SYSTABLES;
> start ie2
--3 存量不落地trail文件，直接传给replicat进程处理
> add ext ie3, sourceistable
> edit param ie3
extract ie3
sourcedb resdb819 userid ogguser password ogguser
rmthost <remote_ip>, mgrport <remote_port>
rmttask replicat, group ir3
table SYSIBM.SYSTABLES;-
replicat配置 以上述的表
SYSIBM.SYSTABLES生成的trail文件dirdat/i1
grant insert,update,delete,select on test.t to ogguser;
$ ggsci
> add rep ir1, exttrail ./dirdat/i1, nodbcheckpoint