4. Import and Streaming Incremental Synchronization from Oracle to Klustron
4. Import and Streaming Incremental Synchronization from Oracle to Klustron
01 Environment and Software Requirements
Oracle GoldenGate supports data replication between Oracle databases and Klustron starting from version 11.2.1.0.2.
This document provides a basic configuration plan for data replication, and specific configurations for different operating systems and database versions can be found in the official documentation.
Software requirements:
Oracle GoldenGate for Oracle
Oracle GoldenGate for Postgresql
Software download page: https://www.oracle.com/middleware/technologies/goldengate.html
02 Installation
2.1 Install & Configure Oracle GoldenGate for Oracle
Set up environment variables on the Oracle database server.
ORACLE_HOME=/opt/oracle/product/19c/dbhome_1
ORACLE_SID=kunluntest
LD_LIBRARY_PATH=$ORACLE_HOME/lib
Ensure that the Oracle database is running in archive log mode and has supplemental logging enabled.
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;
Install Oracle GoldenGate for Oracle and set the GoldenGate software directory to the environment variable.
exportPATH=$PATH:/var/kunlun/ggs
exportLD_LIBRARY_PATH=$ORACLE_HOME/lib:/var/kunlun/ggs/lib/LD_LIBRARY_PATH
Configure the base directory for 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
Create the OGG manager parameter file.
GGSCI(SOURCE.KUNLUN.COM)2>editparammgr
addPORT7809 to theparameterfile:
Start the 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 Install & Configure Oracle GoldenGate for Klustron
Extract the GoldenGate software package on the PostgreSQL server and configure the lib path in the environment variable.
mkdir ggs
cd ggs
unzip V34006-01.zip
tar xvf *.tar
[kunlun@centos7b ggs]$ export LD_LIBRARY_PATH=/var/kunlun/ggs/lib
GoldenGate connects to Klustron via ODBC, so ODBC data sources need to be configured.
[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 the configuration file to the environment variable.
[kunlun@centos7b ggs]$ export ODBCINI=/var/kunlun/ggs/odbc.ini
Configure the target 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
Add checkpoint table on the target side:
-- executed in ogg
dblogin sourcedb KUNLUNDB userid abc password abc
add checkpointtable ogg.checkpointtab
03 Prepare Test Tables
Create a table with the same structure in both the Oracle database and the Klustron cluster.
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.
Klustron
Klustron>CREATE TABLE "public"."kunluntest"
(
"col1"integer NOT NULL,
"col2"varchar(20),
CONSTRAINT"PK_Col111" PRIMARY KEY ("col1")
)
3.1 Verify connection from OGG to Oracle database
Run ggsci on the Oracle side.
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 Verify connection from OGG to Klustron
Run ggsci on the Klustron side.
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)
04 Configure Extract process
Configure an Extract process to capture incremental data from table "klustrontest" in Oracle database and write to a trail file.
First, configure MGR parameters:
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;
Start the Extract process.
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
05 Create Definition File
In OGG, data synchronization between heterogeneous databases requires a definition file to be created for the source database.
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
Finally, copy the definition file from the Oracle server /dirdef/KUNLUNTEST.def to the Klustron server's ./dirdef/KUNLUNTEST.def.
06 Configure Replication Process
In the OGG, the data change log between the heterogeneous databases is written to the trail log by the source-side extraction process, and the log can be transmitted to the target-side OGG through a dump process or other means. The replication process applies the logs to Klustron.
The name of the replication process is rpor, and the replication process parameters are configured as follows:
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);
Create and start the replication process.
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
The replication configuration is completed.
07 Testing
Insert data into the source database:
SQL> insert into KUNLUNTEST values (1,'hello world!');
1 row created.
SQL> commit;
Commit complete.
Check the data synchronization result in the target database.
-bash-3.2$ psql KUNLUNTEST
Type "help" for help.
KUNLUNTEST=# select * from kunluntest;
col1 | col2
------+---------
10 | hello world!
(1 rows)
Check the data synchronization result in the target database.
$ 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)
08 Notes
For more detailed configuration instructions and stress testing of OGG on Klustron, please refer to Full Import and Streaming Incremental Synchronization from PostgreSQL to Klustron》
For the data synchronization principle of OGG on Klustron, please refer to Heterogeneous Data Synchronization-Postgresql China Technical Conference 0109v4.pdf - MoTianLun Document (modb.pro)