KunlunBase指导手册（四）之从Oracle实时同步数

站长

2022年09月17日 12:44 · 阅读数 47

一、环境及软件需求

Oracle GoldenGate从11.2.1.0.2开始支持在Oracle数据库和KunlunBase之间做数据复制。

本文档提供数据复制过程的基本配置方案，涉及不同操作系统及数据库版本具体配置，请参考官方文档。

软件需求：

Oracle GoldenGate for Oracle
Oracle GoldenGate for Postgresql
软件下载页面：https://www.oracle.com/middleware/technologies/goldengate.html

KunlunBase指导手册（四）之从Oracle实时同步数数据同步架构图

二、安装

2.1 安装&配置Oracle GoldenGate for Oracle

Oracle数据库服务器端环境变量。

ORACLE_HOME=/opt/oracle/product/19c/dbhome_1
ORACLE_SID=kunluntest
LD_LIBRARY_PATH=$ORACLE_HOME/lib

Oracle数据库运行在归档模式并且开启附加日志。

alter system set log_archive_dest='LOCATION=USE_DB_RECOVERY_FILE_DEST'scope=both sid='*';
shutdown immediate
startup mount
alter database archivelog;
alter database open;
alterdatabaseaddsupplementallogdata;

安装Oracle GoldenGate for Oracle并设置GoldenGate软件目录到环境变量。

exportPATH=$PATH:/var/kunlun/ggs
exportLD_LIBRARY_PATH=$ORACLE_HOME/lib:/var/kunlun/ggs/lib/LD_LIBRARY_PATH

为Goldengate 配置基本目录。

GGSCI (SOURCE.KUNLUN.COM)1> create subdirs
Creatingsubdirectories under current directory /var/kunlun/ggs
Parameterfiles        /var/kunlun/ggs/dirprm:alreadyexists
Reportfiles          /var/kunlun/ggs/dirrpt:created
Checkpointfiles        /var/kunlun/ggs/dirchk:created
processstatusfiles      /var/kunlun/ggs/dirpcs:created
SQLscriptfiles        /var/kunlun/ggs/dirsql:created
Databasedefinitionsfiles   /var/kunlun/ggs/dirdef:created
Extractdatafiles      /var/kunlun/ggs/dirdat:created
Temporaryfiles        /var/kunlun/ggs/dirtmp:created
Stdoutfiles          /var/kunlun/ggs/dirout:created

创建OGG manager 参数文件。

GGSCI(SOURCE.KUNLUN.COM)2>editparammgr
addPORT7809 to  theparameterfile:

启动 OGG manager。

GGSCI(SOURCE.KUNLUN.COM)3>startmgr
GGSCI(SOURCE.KUNLUN.COM)4>infoall
Program   Status   Group    LagatChkptTimeSinceChkpt
addPORT7809 to  theparameterfile:
MANAGER   RUNNING

2.2 安装&配置Oracle GoldenGate for KunlunBase

在PostgreSQL服务器端解压GoldenGate软件包并发lib路径配置到环境变量中。

mkdir ggs
cd ggs
unzip V34006-01.zip
tar xvf *.tar
[kunlun@centos7b ggs]$ export LD_LIBRARY_PATH=/var/kunlun/ggs/lib

GoldenGate通过ODBC连接 KunlunBase ,因此，需要配置ODBC 数据源。

[ODBC Data Sources]
Kunlundb1=DataDirect 11.5 KUNLUNDB Wire Protocol
[ODBC]
IANAAppCodePage=106
InstallDir=/var/kunlun/ggs
[kunlundb1]
Driver=/var/kunlun/ggs/lib/GGpsql25.so
Description=DataDirect 11.5 KUNLUNDB Wire Protocol
Database=kunlundb
HostName=192.168.0.130
PortNumber=5401
LogonID=abc
Password=abc

将配置文件export到环境变量。

[kunlun@centos7b  ggs]$ export ODBCINI=/var/kunlun/ggs/odbc.ini

配置目标端Goldengate。

[kunlun@TARGET ggs]$ ./ggsci
GGSCI (TARGET.KUNLUN.COM) 1> create subdirs

Creating subdirectories under current directory /var/kunlun/ggs

Parameter files       /var/kunlun/ggs/dirprm: already exists
Report files         /var/kunlun/ggs/dirrpt: created
Checkpoint files       /var/kunlun/ggs/dirchk: created
Process status files     /var/kunlun/ggs/dirpcs: created
SQL script files       /var/kunlun/ggs/dirsql: created
Database definitions files  /var/kunlun/ggs/dirdef: created
Extract data files      /var/kunlun/ggs/dirdat: created
Temporary files       /var/kunlun/ggs/dirtmp: created
Stdout files         /var/kunlun/ggs/dirout: created

create the Manager parameter file and start the manager:
GGSCI (TARGET.KUNLUN.COM) 2> edit param mgr
 PORT 7809
 GGSCI(TARGET.KUNLUN.COM) 3> start mgr
Manager started.

GGSCI (TARGET.KUNLUN.COM) 4> info all
Program   Status   Group   Lag at Chkpt Time Since Chkpt
MANAGER   RUNNING

目标端新增checkpoint表。

-- ogg中执行

dblogin sourcedb KUNLUNDB userid abc password abc
add checkpointtable ogg.checkpointtab

三、准备测试表

分别在Oracle数据库和KunlunBase集群中建立结构相同的一张表。

Oracle DB

SQL> connect kunlun/kunlun
Connected.
SQL> create table kunluntest (col1 number, col2varchar2(20));
Table created.
SQL> alter table kunluntest add primary key (col1);
Table altered.

KunlunBase

KunlunDB>CREATE TABLE "public"."kunluntest"
(
 "col1"integer NOT NULL,
 "col2"varchar(20),
 CONSTRAINT"PK_Col111" PRIMARY KEY ("col1")
)

3.1 验证到OGG到Oracle数据库的连接

在Oracle端执行ggsci。

GGSCI (SOURCE.KUNLUN.COM) 8> dblogin userid kunlun,password kunlun
Successfully logged into database.
GGSCI (SOURCE.KUNLUN.COM) 9> list tables *
KUNLUN.KUNLUNTEST
Found 1 tables matching list criteria.

GGSCI (SOURCE.KUNLUN.COM) 10> capture tabledefKUNLUN.KUNLUNTEST
Table definitions for KUNLUN.KUNLUNTEST:
COL1             NUMBER NOT NULL PK
COL2             VARCHAR (20)

3.2 验证到OGG到KunlunBase数据库的连接

在KunlunBase端执行ggsci。

GGSCI (TARGET.KUNLUN.COM) 4> dblogin sourcedb kunlundbuserid abc
Password:
2013-04-06 16:51:18 INFO OGG-03036 Database character setidentified as UTF-8.
Locale: en_US.
2013-04-06 16:51:18 INFO OGG-03037 Session character setidentified as UTF-8.
Successfully logged into database.
GGSCI (TARGET.KUNLUN.COM) 5> list tables *
public.kunluntest

Found 1 tables matching list criteria.
GGSCI (TARGET.KUNLUN.COM) 3> capture tabledef"public"."kunluntest"
Table definitions for public.kunluntest:
col1                                                   NUMBER(10) NOT NULL PK
col2                                                              VARCHAR (20)

四、配置抽取进程

配置一个抽取进程，抽取表Oracle数据库中的表kunluntest的数据增量到trail文件中。

首先配置MGR 参数：

GGSCI (SOURCE.ORACLE.COM) 4> edit param epor

with these parameters:

EXTRACT epor
USERID kunlun, PASSWORD kunlun
RMTHOST 192.168.0.130, MGRPORT 7809
RMTTRAIL ./dirdat/ep
TABLE kunlun.kunluntest;

启动抽取进程。

GGSCI (SOURCE.ORACLE.COM) 5> add extract epor, tranlog,begin now
EXTRACT added.
GGSCI (SOURCE.ORACLE.COM) 6> add exttrail ./dirdat/ep,extract epos, megabytes 5
EXTTRAIL added.

GGSCI (SOURCE.ORACLE.COM) 7> start epor
Sending START request to MANAGER ...
EXTRACT EPOR starting

GGSCI (SOURCE.ORACLE.COM) 8> info all

Program   Status   Group   Lag at Chkpt Time Since Chkpt
MANAGER   RUNNING
EXTRACT  RUNNING   EPOR    00:00:00  00:00:05

五、创建定义文件

在OGG中，异构数据库之间的数据同步需要为源端创建定义文件。

GGSCI (SOURCE.KUNLUN.COM) 10> edit param defgen
DEFSFILE ./dirdef/KUNLUNTEST.def
USERID kunlun, password kunlun
TABLE KUNLUN.KUNLUNTEST;
[kunlun@SOURCE ggs]$ ./defgen paramfile ./dirprm/defgen.prm

***********************************************************************
    Oracle GoldenGateTable Definition Generator for Oracle
 Version 11.2.1.0.314400833 OGGCORE_11.2.1.0.3_PLATFORMS_120823.1258
Copyright (C) 1995, 2012, Oracle and/or its affiliates. Allrights reserved.

          Starting at2022-03-15 18:32:10
***********************************************************************

Operating System Version:
Linux
Node: SOURCE.KUNLUN.COM
Machine: x86_64
             softlimit  hard limit
Address Space Size :  unlimited  unlimited
Heap Size      :  unlimited unlimited
File Size      :  unlimited unlimited
CPU Time       :  unlimited unlimited

Process id: 1546

***********************************************************************
**      Running withthe following parameters         **
***********************************************************************
DEFSFILE ./dirdef/KUNLUNTEST.def
USERID postgres, password ********
TABLE KUNLUN.KUNLUNTEST;
Retrieving definition for KUNLUN.KUNLUNTEST

Definitions generated for 1 table in ./dirdef/KUNLUNTEST.def

Content of the Defgen File:
[oracle@SOURCE ggs]$ more ./dirdef/KUNLUNTEST.def
*+- Defgen version 2.0, Encoding UTF-8
*
\* Definitions created/modified 2022-03-15 18:32
*
\* Field descriptions for each column entry:
*
\*   1  Name
\*   2  Data Type
\*   3  External Length
\*   4  Fetch Offset
\*   5  Scale
\*   6  Level
\*   7  Null
\*   8  Bump if Odd
\*   9  Internal Length
\*  10  Binary Length
\*  11  Table Length
\*  12  Most Significant DT
\*  13  Least Significant DT
\*  14  High Precision
\*  15  Low Precision
\*  16  Elementary Item
\*  17  Occurs
\*  18  Key Column
\*  19  Sub Data Type
*
Database type: ORACLE
Character set ID: UTF-8
National character set ID: UTF-16
Locale: neutral
Case sensitivity: 14 14 14 14 14 14 14 14 14 14 14 14 11 1414 14
*
Definition for table POSTGRES.GGTEST
Record length: 262
Syskey: 0
Columns: 2
COL1  64   50   0 0 0 1 0   50   50  50 0 0 0 0 1  0 1 2
COL2  64  200   56 0 0 1 0  200  200   00 0 0 0 1  0 0 0
End of definition

最后，将定义文件从oracle服务器/dirdef/KUNLUNTEST.def复制到kunlundb 服务器的./dirdef/KUNLUNTEST.def。

六、配置复制进程

在源端的抽取进程将数据的变更日志写入到trail日志中，日志可以通过dump进程或其他方式传输到目标端的OGG。复制进程将日志应用KunlunBase中去。

复制进程名称是rpor, 配置复制进程参数：

GGSCI (TARGET.KUNLUN.COM) 1> edit param rpor

with the parameters:

REPLICAT rpor
SOURCEDEFS ./dirdef/GGTEST.def
SETENV ( PGCLIENTENCODING = "UTF8" )
SETENV (ODBCINI="/var/kunlun/ggs/odbc.ini" )
SETENV (NLS_LANG="AMERICAN_AMERICA.AL32UTF8")
TARGETDB GG_Postgres, USERID kunlun, PASSWORD kunlun
DISCARDFILE ./dirrpt/diskg.dsc, purge
MAP POSTGRES.GGTEST, TARGET public.kunluntest, COLMAP(COL1=col1,COL2=col2);

创建并启动复制进程。

GGSCI (ZKUPCHV119) 2> add replicat rpor, NODBCHECKPOINT,exttrail ./dirdat/ep
REPLICAT added.
GGSCI (edvmr1p0) 3> start rpor

Sending START request to MANAGER ...
REPLICAT REPKG starting

GGSCI (TARGET.KUNLUN.COM) 2> info all
Program   Status   Group   Lag at Chkpt Time Since Chkpt
MANAGER   RUNNING
REPLICAT RUNNING   RPOR    00:00:00  00:00:07

复制配置完成。

七、测试

源库插入数据：

SQL> insert into KUNLUNTEST values (1,'hello world!');

1 row created.

SQL> commit;

Commit complete.

目标库查看数据同步结果

-bash-3.2$ psql KUNLUNTEST
Type "help" for help.

KUNLUNTEST=# select * from kunluntest;
 col1 | col2
------+---------
  10 | hello world!

(1 rows)

目标库查看数据同步结果。

$ psql -h 192.168.0.130 -p 5401 -Uabc kunluntest
Type "help" for help.

KUNLUNTEST=# select * from kunluntest;
 col1 | col2
------+---------
  10 | hello world!

(1 rows)

八、说明

OGG on KKunlunBase的更详细的配置说明及压力测试请参考《KunlunBase 快速入门（三）数据导入&同步》

OGG ON KunlunBase数据同步原理请参考《异构数据同步-Postgresql中国技术大会0109v4.pdf - 墨天轮文档 (modb.pro)》》")

点击阅读原文

KunlunBase项目已开源

【GitHub：】https://github.com/zettadb

【Gitee：】https://gitee.com/zettadb

END

转载自:https://segmentfault.com/a/1190000041595692