一、環境及軟體需求

Oracle GoldenGate從11.2.1.0.2開始支援在Oracle資料庫和KunlunDB之間做資料複製。

本文件提供資料複製過程的基本配置方案，涉及不同作業系統及資料庫版本具體配置，請參考官方文件。

軟體需求：

Oracle GoldenGate for Oracle
Oracle GoldenGate for Postgresql
軟體下載頁面：

KunlunDB 快速入門 4.0（從Oracle實時同步資料到kunlunDB）

資料同步架構圖

二、安裝

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 KunlunDB

在PostgreSQL伺服器端解壓GoldenGate軟體包併發lib路徑配置到環境變數中。

mkdir ggscd ggsunzip V34006-01.ziptar xvf *.tar[kunlun@centos7b ggs]$ export LD_LIBRARY_PATH=/var/kunlun/ggs/lib

GoldenGate通過ODBC連線 kunlunDB ,因此，需要配置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資料庫和KunlunDB叢集中建立結構相同的一張表。

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.

KunlunDB

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到KunlunDB資料庫的連線

在kunlunDB端執行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。複製程式將日誌應用KunlunDB中去。

複製程式名稱是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 KunlunDB的更詳細的配置說明及壓力測試請參考《 KunlunDB 快速入門（三）資料匯入&同步》

OGG ON KunlunDB資料同步原理請參考《異構資料同步-Postgresql中國技術大會0109v4.pdf - 墨天輪文件 (modb.pro)》（點選下方“閱讀原文”）

END