Skip to main content

Oracle to Klustron (formerly KunlunBase) CDC Synchronization

KlustronAbout 6 min

Oracle to Klustron (formerly KunlunBase) CDC Synchronization

Note:

Unless otherwise specified, version numbers mentioned in this document can be replaced with any officially released version. For a list of all released versions, refer to http://doc.klustron.com/Release_notes.html.

Objective:

This document primarily focuses on the implementation details of using Oracle GoldenGate (OGG) to synchronize Oracle data to Klustron. It covers the installation and deployment of OGG on both the source Oracle database and target Klustron database. Additionally, it explains how to configure OGG to achieve data synchronization from Oracle to Klustron. After configuration, insertion and update synchronization testing has been performed.

01 OGG Deployment Planning and Installation

1.1 OGG Deployment Planning

OGG Deployment Planning
**Source:**Oracle**Target:**Klustron
**Oracle IP:**192.168.56.104**Klustron Computing Node IP:**192.168.56.112
**OGG Software:**Oracle GoldenGate for Oracle**OGG Software:**Oracle GoldenGate for Postgesql
**Install OGG OS User:**oracle**Install OGG OS User:**kunlun
**OGG Download Link:**https://www.oracle.com/middleware/technologies/goldengate-downloads.html

1.2 Source OGG Software Installation [Oracle]

a) Download the OGG installation software to a specified directory, here downloaded to /u01/kunlun/ggs, and then unzip the installation software.

[oracle@db19c ~]$ cd /u01/kunlun/ggs

[oracle@db19c ggs]$ unzip Oracle_GoldenGate_19.1.0.0.4_for_Oracle_on_Linux_86_64.zip

b) Configure the OGG silent installation response file.

[oracle@db19c response]$ vi/u01/kunlun/ggs/fbo_ggs_Linux_x64_shiphome/Disk1/response/oggcore.rsp

 

INSTALL_OPTION=ORA19c

SOFTWARE_LOCATION=/u01/kunlun/ggs

c) Install the OGG software using silent installation.

[oracle@db19cggs]$cd/u01/kunlun/ggs/fbo_ggs_Linux_x64_shiphome/Disk1

[oracle@db19c Disk1]$ ./runInstaller -silent -responseFile/u01/kunlun/ggs/fbo_ggs_Linux_x64_shiphome/Disk1/response/oggcore.rsp

d) Configure the environment variables for the oracle OS user, configure the ~/.bash_profile file.

[oracle@db19c ~]$ vi ~/.bash_profile

export ORACLE_BASE=/u01/app/oracle

export ORACLE_HOME=$ORACLE_BASE/product/19.0.0/db_1

export ORACLE_SID=orcl

export OGG_HOME=/u01/kunlun/ggs

export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:$OGG_HOME:$BASE_PATH

export LD_LIBRARY_PATH=$ORACLE_BASE/product/19.0.0/db_1/lib:$OGG_HOME

e) Log in to the OGG command-line interface and create directories for storing OGG configuration files and TRAIL files.

[oracle@db19c ~]$ cd /u01/kunlun/ggs

[oracle@db19c ggs]$ ./ggsci

GGSCI (db19c) 1> create subdirs

1.3 Target OGG Software Installation [Klustron]

a) Download the OGG installation software to a specified directory, here downloaded to /var/kunlun/ggs, and then unzip the installation software.

[kunlun@kunlun1 soft_u]$ cd /var/kunlun/ggs

[kunlun@kunlun1 ggs]$ unzip p31456619_19100200714_Linux-x86-64_Patch_Oracle_GoldenGate_for_PostgreSQL.zip

[kunlun@kunlun1 ggs]$ ls

[kunlun@kunlun1 ggs]$ tar -xvf ggs_Linux_x64_PostgreSQL_64bit.tar

b) Configure the environment variables for the kunlun OS user, configure the ~/.bash_profile file.

[kunlun@kunlun1 ~]$ vi ~/.bash_profile

export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/var/kunlun/ggs/lib

export ODBCINI=/var/kunlun/ggs/odbc.ini

c) Configure the ODBC file to set up the ODBC connection information for Klustron.

[kunlun@kunlun1 kunlun]$ vi /var/kunlun/ggs/odbc.ini

 

[ODBC Data Sources]

Kunlundb1=DataDirect 11.5 KUNLUNDB Wire Protocol

[ODBC]

IANAAppCodePage=106

InstallDir=/var/kunlun/ggs

[kunlundb]

Driver=/var/kunlun/ggs/lib/GGpsql25.so

Description=DataDirect 11.5 KUNLUNDB Wire Protocol

Database=postgres

HostName=192.168.56.112

PortNumber=47001

LogonID=abc

Password=kunlun

d) Log in to the OGG command management interface and create directories for storing OGG configuration files and TRAIL files.

[kunlun@kunlun1 ~]$ cd /var/kunlun/ggs

[kunlun@kunlun1 ggs]$ ./ggsci

GGSCI (kunlun1) 1> create subdirs

02 Source Database OGG Environment Configuration [Oracle]

2.1 Enable archive log on the source database.

[oracle@db19c ~]$ sqlplus / as sysdba

SQL>shutdown immediate;

SQL>startup mount;

SQL>alter database archivelog;

SQL>alter database open;

SQL>archive log list;

2.2 Enable minimum database-level supplemental logging and force logging on the source database.

[oracle@db19c ~]$ sqlplus / as sysdba

SQL>alter database add supplemental log data;

SQL>alter database force logging;

SQL>SELECT supplemental_log_data_min, force_logging FROM v$database;

2.3 Switch logs on the source database to activate supplemental logging.

SQL>alter system switch logfile;

2.4 Set the enable_goldengate_replication parameter to true in the source database Oracle.

SQL> alter system set enable_goldengate_replication = true scope=both;  

SQL> show parameter enable_goldengate_replication

2.5 Create the GoldenGate database user oggkunlun on the source database and grant permissions to the database user.

SQL> create user oggkunlun identified by kunlun;

SQL> GRANT CONNECT TO oggkunlun;

SQL> GRANT CREATE SESSION TO oggkunlun;

SQL> GRANT ALTER SESSION TO oggkunlun;

SQL> GRANT RESOURCE TO oggkunlun;

SQL> GRANT SELECT ANY DICTIONARY TO oggkunlun;

SQL> GRANT SELECT ANY TABLE TO oggkunlun;

SQL> GRANT SELECT ANY TRANSACTION TO oggkunlun;

SQL> GRANT FLASHBACK ANY TABLE TO oggkunlun;

SQL> GRANT ALTER ANY TABLE TO oggkunlun;

SQL> GRANT DBA to oggkunlun;

SQL> exec dbms_goldengate_auth.grant_admin_privilege('oggkunlun');

2.6 Create a test database user kunlun on the source database.

SQL> create user kunlun identified by kunlun;

SQL> grant dba to kunlun;

2.7 Connect to the database using the kunlun user on the source database and create test tables.

SQL> conn kunlun/kunlun

SQL> create table kunluntest (id number, name varchar2(20));

SQL> alter table kunluntest add primary key (id);

2.8 Configure the OGG manager process on the source side and start the manager process.

[oracle@db19c ~]$ cd /u01/kunlun/ggs

[oracle@db19c ggs]$ ./ggsci

GGSCI (db19c) 1> dblogin userid oggkunlun, password kunlun

GGSCI (db19c as oggkunlun@orcl) 12> edit param mgr

 

port 8809

GGSCI (db19c as oggkunlun@orcl) 13> start mgr

GGSCI (db19c as oggkunlun@orcl) 14> info all

GGSCI (db19c as oggkunlun@orcl) 2> view param mgr

2.9 Configure the OGG extract process eora on the source side.

GGSCI (db19c as oggkunlun@orcl) 17> edit param eora

EXTRACT eora

USERID oggkunlun, PASSWORD kunlun

RMTHOST 192.168.56.112, MGRPORT 8809

RMTTRAIL ./dirdat/ep

TABLE kunlun.kunluntest;


GGSCI (db19c as oggkunlun@orcl) 18> view param eora

2.10 Add the extract process eora to the manager process management on the source side.

GGSCI (db19c as oggkunlun@orcl) 19> add extract eora, tranlog, begin now

GGSCI (db19c as oggkunlun@orcl) 20> add exttrail ./dirdat/ep, extract eora, megabytes 5

2.11 Add table-level supplemental logging in OGG on the source side.

GGSCI (db19c as oggkunlun@orcl) 4> add trandata kunlun.*

03 Target Database OGG Environment Configuration [Klustron]

3.1 Connect to the target database Klustron and create the database user kunlun.

[kunlun@kunlun1 ggs]$ psql -h 192.168.56.112 -p 47001 postgres

postgres=# create user kunlun with password 'kunlun';

postgres=# grant all privileges on database postgres to kunlun;

3.2 Connect to Klustron using the database user kunlun and create test tables.

[kunlun@kunlun1 ggs]$ psql -h 192.168.56.112 -p 47001 -U kunlun postgres

postgres=# CREATE TABLE "public"."kunluntest" ("id" integer NOT NULL, "name" varchar                                                              (20), CONSTRAINT "PK_id_key" PRIMARY KEY ("id"));

postgres=# \dt

3.3 Configure the OGG manager process on the target side and start the manager process.

[kunlun@kunlun1 ~]$ cd /var/kunlun/ggs/

[kunlun@kunlun1 ggs]$ ./ggsci

GGSCI (kunlun1) 1> edit param mgr

8809

 

GGSCI (kunlun1) 2> view param mgr

GGSCI (kunlun1) 2> start mgr

GGSCI (kunlun1) 3> info all

3.4 Configure the OGG replicat process rora on the target side.

GGSCI (kunlun1) 4> edit param rora

REPLICAT rora

SETENV ( PGCLIENTENCODING = "UTF8" )

SETENV (ODBCINI="/var/kunlun/ggs/odbc.ini" )

SETENV (NLS_LANG="AMERICAN_AMERICA.AL32UTF8")

TARGETDB kunlundb, USERID kunlun, PASSWORD kunlun

DISCARDFILE ./dirrpt/diskg.dsc, purge

MAP kunlun.kunluntest, TARGET public.kunluntest;

 

GGSCI (kunlun1) 5> view param rora

3.5 Add the replicat process rora to the manager's management on the target side.

GGSCI (kunlun1) 8> add replicat rora, NODBCHECKPOINT, exttrail ./dirdat/ep

04 Start OGG Source Extract and Target Replicat Processes

4.1 Start the OGG extract process on the source side and check the process status to confirm that all processes are running.

GGSCI (db19c as oggkunlun@orcl) 21> start eora

GGSCI (db19c as oggkunlun@orcl) 22> info all

4.2 Start the OGG replicat process on the target side and check the process status to confirm that all processes are running.

GGSCI (kunlun1) 9> start rora

GGSCI (kunlun1) 10> info all

05 OGG Synchronization Testing

5.1 Perform data insert operations to verify data synchronization:

a) Perform insert operations on the source database.

[oracle@db19c ~]$ sqlplus kunlun/kunlun

SQL> insert into kunluntest values(10001,'kunlundb admin');

SQL> insert into kunluntest values(10002,'kunlundb operator');

SQL> insert into kunluntest values(10003,'kunlundb user');

SQL> insert into kunluntest values(10004,'zhang shan');

SQL> insert into kunluntest values(10005,'li si');

SQL> commit;

SQL>

SQL> select * from kunluntest;

b) Check data synchronization on the target database.

[kunlun@kunlun1 ~]$ psql -h 192.168.56.112 -p 47001 -U kunlun postgres

postgres=# select * from public.kunluntest;

c) Verify that insert operations on the source database have been synchronized to the target database.

5.2 Perform data update operations to verify data synchronization:

a) Perform update operations on the source database.

SQL> update kunluntest set name='zhang shan update' where id=10004;

SQL> update kunluntest set name='li si update' where id=10005;

SQL> commit;

SQL> select * from kunluntest;

b) Check data synchronization on the target database.

postgres=# select * from public.kunluntest;

c) Verify that update operations on the source database have been synchronized to the target database.

5.3 Perform data delete operations to verify data synchronization:

a) Perform delete operations on the source database.

SQL> delete from kunluntest where id=10004;

SQL> delete from kunluntest where id=10005;

SQL> commit;


SQL> select * from kunluntest;

b) Check data synchronization on the target database.

postgres=# select * from public.kunluntest;

c) Verify that delete operations on the source database have been synchronized to the target database.

Complete OGG synchronized data from Oracle to Klustron configuration.

END

Copyright © Klustron
Copyright © 2025 Klustron