一、环境及软件需求

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

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

软件需求：

Oracle GoldenGate for Oracle
Oracle GoldenGate for Postgresql
软件下载页面：

数据同步架构图

二、安装

2.1 安装&配置Oracle GoldenGate for Oracle

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

ORACLE_HOME=/opt/oracle/product/19c/dbhome_1ORACLE_SID=kunluntestLD_LIBRARY_PATH=$ORACLE_HOME/lib

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

alter system set log_archive_dest='LOCATION=USE_DB_RECOVERY_FILE_DEST'scope=both sid='*';shutdown immediatestartup mountalter database archivelog;alter database open;alterdatabaseaddsupplementallogdata;

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

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

为Goldengate 配置基本目录。

GGSCI (SOURCE.KUNLUN.COM)1> create subdirsCreatingsubdirectories under current directory /var/kunlun/ggsParameterfiles        /var/kunlun/ggs/dirprm:alreadyexistsReportfiles          /var/kunlun/ggs/dirrpt:createdCheckpointfiles        /var/kunlun/ggs/dirchk:createdprocessstatusfiles      /var/kunlun/ggs/dirpcs:createdSQLscriptfiles        /var/kunlun/ggs/dirsql:createdDatabasedefinitionsfiles   /var/kunlun/ggs/dirdef:createdExtractdatafiles      /var/kunlun/ggs/dirdat:createdTemporaryfiles        /var/kunlun/ggs/dirtmp:createdStdoutfiles          /var/kunlun/ggs/dirout:created

创建OGG manager 参数文件。

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

启动 OGG manager。

GGSCI(SOURCE.KUNLUN.COM)3>startmgrGGSCI(SOURCE.KUNLUN.COM)4>infoallProgram   Status   Group    LagatChkptTimeSinceChkptaddPORT7809 to  theparameterfile:MANAGER   RUNNING

2.2 安装&配置Oracle GoldenGate for KunlunBase

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

mkdir ggscd ggsunzip V34006-01.ziptar 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=106InstallDir=/var/kunlun/ggs[kunlundb1]Driver=/var/kunlun/ggs/lib/GGpsql25.soDescription=DataDirect 11.5 KUNLUNDB Wire ProtocolDatabase=kunlundbHostName=192.168.0.130PortNumber=5401LogonID=abcPassword=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/dirrp
t: created

Checkpoint files       /
var/kunlun/ggs/dirchk: created

Process status 
files     /var/kunlun/ggs/dirpc
s: created

SQL script 
files       /var/kunlun/ggs/dirsq
l: created

Database definitions files  /
var/kunlun/ggs/dirdef: created

Extract data 
files      /var/kunlun/ggs/dirda
t: created

Temporary files       /
var/kunlun/ggs/dirtmp: created

Stdout 
files         /var/kunlun/ggs/dirou
t: 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/kunlunConnected.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(
COL
1=col
1,
COL
2=col
2);

创建并启动复制进程。


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..

130 -p 

5401 -Uabc kunluntest

Type 

"help" 

for 
help.





KUNLUNTEST=

# select * from kunluntest;

 
col1 
| col2


------+---------


  
10 
|
 hello world!





(
1 rows)

八、说明

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

OGG ON KunlunBase数据同步原理请参考《异构数据同步-Postgresql中国技术大会0109v4.pdf - 墨天轮文档 (modb.pro)》（点击下方“阅读原文”）

点击阅读原文

END

ITPub博客

KunlunBase 快速入门 4.0（从Oracle实时同步数据到KunlunBase）