Topic: Oracle to MySQL Unidirectional Replication
Oracle Goldengate
facilitates heterogeneous data replication, including replication from an
Oracle source database to a MySQL target database. This process involves
capturing changes from the Oracle database and applying them to the MySQL
database in real time
Deployment diagram:
Infrastructure Details:
|
Oracle DB Details |
|
|
OS version |
Red Hat Enterprise Linux release 7.6
(Maipo) |
|
DB Version |
19.3.0.0.0 |
|
GG Version |
19.1.0.0.210228 |
|
Host Name |
DB19C.example.com |
|
Oracle DB Details |
|
|
OS version |
Red Hat Enterprise Linux release 7.6
(Maipo) |
|
PG Version |
8.0.43-commercial |
|
GG Version |
19.1.0.0.0 |
|
Port number |
3306 |
|
Cluster location |
/var/lib/mysql |
|
Host Name |
Node01.example.com |
In this
QuickStart, we learn:
·
Prerequisite
Steps
-
Validate the
Oracle GG setup is already there
-
Create a schema
for replication (Oracle)
-
Create a schema
and database for replication (Mysql)
-
Install the
Oracle GoldenGate for MySQL
·
Configure
the Oracle GG replication to MySQL
-
Configure
the manager process for both sides (Oracle and MySQL)
-
Configure
the extract process on the Oracle Side
-
Configure
replication process on the MySQL side
- Validation
INFORMATION: We already configured the GG setup in Oracle. Here, we will configure
the MySQL GG setup and replication
Step-1: Prerequisite Steps
-
Validate the
Oracle GG setup is already there
-
Details of schema
use for replication (Oracle)
-
Details of
database use for replication (MySQL)
-
Install
the Oracle GoldenGate for MySQL
-bash-4.2$
unzip -q GG_19.1.0.0.0_MySQL_Linux_x86-64.zip
-bash-4.2$ tar
-xvf ggs_Linux_x64_MySQL_64bit.tar -C /u01/app/gg/product/19c
Step-2: Configure the Oracle GG replication to MySQL
-
Configure
the manager process (oracle)
-
Configure
the manager process (MySQL)
-
Configure
credentialstore (Oracle)
GGSCI (DB19C.example.com) 4>
add credentialstore
GGSCI (DB19C.example.com) 5>
Alter credentialstore add user C##GGUSER@pdb alias orcldb domain admin
Password:
Credential store altered.
GGSCI (DB19C.example.com) 5>
Alter credentialstore add user C##GGUSER@DBA19C alias DBA19C domain admin
Password:
Credential store altered.
GGSCI (DB19C.example.com) 6> Info credentialstore domain
admin
-
Configure
credentialstore (MySQL)
GGSCI
(Node01.example.com) 6> add credentialstore
Credential store
created.
GGSCI
(Node01.example.com) 7> ALTER CREDENTIALSTORE ADD USER hr ALIAS hr
Password:
Credential store
altered.
GGSCI
(Node01.example.com) 8> info credentialstore
-
In
heterogeneous data replication, we must generate a source definitions file and
copy it onto the MySQL DB server (target Server)
-
[oracle@DB19C OGG-19.1.0.0]$ ./defgen paramfile
/u02/app/gg/product/OGG-19.1.0.0/dirprm/defgen.prm
[oracle@DB19C dirdef]$ scp GGTEST.def mysql@Node01.example.com:/u01/app/gg/product/19c/dirdef
-
configure
a checkpoint in MySQL (Target side)
GGSCI
(Node01.example.com) 2> add checkpointtable hr.ggschkpt
Information:
You need to configure a checkpoint table in GoldenGate to ensure data integrity
and fault tolerance for the Replicat process. A checkpoint table prevents data
loss or duplication in the event of an unexpected shutdown by saving
transaction recovery points directly within the target database, rather than
relying solely on less-reliable checkpoint files. (checkpoint table must
be configured on the target side)
-
Configure
the extract and Extract Pump process on the Oracle Side
GGSCI
(DB19C.example.com as C##GGUSER@DBa19c/CDB$ROOT) 13>add extract EHRINT01,
integrated tranlog, begin now
GGSCI
(DB19C.example.com as C##GGUSER@DBa19c/CDB$ROOT) 14>add exttrail
/u02/app/gg/product/OGG-19.1.0.0/dirdat/ET, extract EHRINT01, megabytes 100
GGSCI
(DB19C.example.com as C##GGUSER@DBa19c/CDB$ROOT) 15>REGISTER EXTRACT
EHRINT01 DATABASE CONTAINER (PDB);
GGSCI
(DB19C.example.com as C##GGUSER@DBa19c/CDB$ROOT) 2> view param EHRINT01
EXTRACT EHRINT01
USERIDALIAS
DBA19C DOMAIN admin
EXTTRAIL
/u02/app/gg/product/OGG-19.1.0.0/dirdat/ET
SOURCECATALOG PDB
TABLE hr.test;
GGSCI
(DB19C.example.com as C##GGUSER@DBa19c/CDB$ROOT) 3>
GGSCI
(DB19C.example.com as C##GGUSER@DBa19c/CDB$ROOT) 3> Add extract EHRDP01,
EXTTRAILSOURCE /u02/app/gg/product/OGG-19.1.0.0/dirdat/ET
GGSCI
(DB19C.example.com as C##GGUSER@DBa19c/CDB$ROOT) 3> Add rmttrail
/u02/app/gg/product/OGG-19.1.0.0/dirdat/ET, extract EHRDP01
GGSCI
(DB19C.example.com as C##GGUSER@DBa19c/CDB$ROOT) 5> view param EHRDP01
EXTRACT EHRDP01
USERIDALIAS
DBA19C DOMAIN admin
RMTHOST
Node01.example.com, MGRPORT 7809
RMTTRAIL
/u01/app/gg/product/19c/dirdat/om
SOURCECATALOG PDB
TABLE hr.test;
GGSCI
(DB19C.example.com as C##GGUSER@DBa19c/CDB$ROOT) 6> start EHRINT01
Sending START
request to MANAGER ...
EXTRACT EHRINT01
starting
GGSCI
(DB19C.example.com as C##GGUSER@DBa19c/CDB$ROOT) 7> start EHRDP01
Sending START
request to MANAGER ...
EXTRACT EHRDP01
starting
GGSCI
(DB19C.example.com as C##GGUSER@DBa19c/CDB$ROOT) 8> info all
Program Status
Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING
EHRINT01 00:00:00 22:00:29
EXTRACT RUNNING
EHRDP01 00:00:00 22:00:34
GGSCI
(DB19C.example.com as C##GGUSER@DBa19c/CDB$ROOT) 9>
-
Configure
replication process on the MySQL side
GGSCI
(Node01.example.com) 13>add replicat myrep1, exttrail
/u01/app/gg/product/19c/dirdat/ET
GGSCI
(Node01.example.com) 15> view param myrep1
REPLICAT myrep1
TARGETDB hr,
USERID hr, PASSWORD ***
SOURCEDEFS
/u01/app/gg/product/19c/dirdef/GGTEST.def
MAP pdb.hr.test
TARGET hr.test;
GGSCI
(Node01.example.com) 16>
GGSCI
(Node01.example.com) 16> start myrep1
GGSCI
(Node01.example.com) 19> info all
Program Status
Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
REPLICAT RUNNING
myrep1 00:00:00 00:00:07
GGSCI
(Node01.example.com) 20>
Step-3: Validation
-
Insert
data into table (Oracle side)
-
Validate
transaction from GG
[oracle@DB19C
OGG-19.1.0.0]$ ./ggsci
Oracle GoldenGate
Command Interpreter for Oracle
Version
19.1.0.0.1 OGGCORE_19.1.0.0.0_PLATFORMS_190524.2201_FBO
Linux, x64, 64bit
(optimized), Oracle 19c on May 25 2019 06:46:13
Operating system
character set identified as UTF-8.
Copyright (C)
1995, 2019, Oracle and/or its affiliates. All rights reserved.
GGSCI
(DB19C.example.com) 1> info all
Program Status
Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING
EHRINT01 00:00:00 00:00:08
EXTRACT RUNNING
EHRDP01 00:00:00 00:00:06
GGSCI
(DB19C.example.com) 2>
GGSCI
(DB19C.example.com) 2> stats EHRINT01
Sending STATS
request to EXTRACT EHRINT01 ...
Start of
Statistics at 2025-08-31 15:35:21.
Output to
/u02/app/gg/product/OGG-19.1.0.0/dirdat/om:
Extracting from
PDB.HR.TEST to PDB.HR.TEST:
*** Total
statistics since 2025-08-31 15:34:06 ***
Total inserts 1.00
Total updates 0.00
Total deletes 0.00
Total upserts 0.00
Total discards 0.00
Total operations 1.00
*** Daily
statistics since 2025-08-31 15:34:06 ***
Total inserts 1.00
Total updates 0.00
Total deletes 0.00
Total upserts 0.00
Total discards 0.00
Total operations 1.00
*** Hourly
statistics since 2025-08-31 15:34:06 ***
Total inserts 1.00
Total updates 0.00
Total deletes 0.00
Total upserts 0.00
Total discards 0.00
Total operations 1.00
*** Latest
statistics since 2025-08-31 15:34:06 ***
Total inserts 1.00
Total updates 0.00
Total deletes 0.00
Total upserts 0.00
Total discards 0.00
Total operations 1.00
End of
Statistics.
GGSCI
(DB19C.example.com) 3> stats EHRDP01
Sending STATS
request to EXTRACT EHRDP01 ...
Start of
Statistics at 2025-08-31 15:35:35.
Output to
/u01/app/gg/product/19c/dirdat/om:
Extracting from
PDB.HR.TEST to PDB.HR.TEST:
*** Total
statistics since 2025-08-31 15:34:07 ***
Total inserts 1.00
Total updates 0.00
Total deletes 0.00
Total upserts 0.00
Total discards 0.00
Total operations 1.00
*** Daily
statistics since 2025-08-31 15:34:07 ***
Total inserts 1.00
Total updates 0.00
Total deletes 0.00
Total upserts 0.00
Total discards 0.00
Total operations 1.00
*** Hourly
statistics since 2025-08-31 15:34:07 ***
Total inserts 1.00
Total updates 0.00
Total deletes 0.00
Total upserts 0.00
Total discards 0.00
Total operations 1.00
*** Latest
statistics since 2025-08-31 15:34:07 ***
Total inserts 1.00
Total updates 0.00
Total deletes 0.00
Total upserts 0.00
Total discards 0.00
Total operations 1.00
End of
Statistics.
GGSCI
(DB19C.example.com) 4>
-
Validate
from target side (MySQL)
-
Validate
from the GG process
-
GGSCI
(Node01.example.com) 18> start myrep1
REPLICAT OMREP1
is already running.
GGSCI
(Node01.example.com) 19> info all
Program Status
Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
REPLICAT RUNNING
myrep1 00:00:00 00:00:07
GGSCI
(Node01.example.com) 20>
GGSCI
(Node01.example.com) 20> stats myrep1
Sending STATS
request to REPLICAT myrep1 ...
Start of
Statistics at 2025-08-31 15:39:31.
Replicating from
PDB.HR.TEST to hr.test:
*** Total
statistics since 2025-08-31 15:34:09 ***
Total inserts 1.00
Total updates 0.00
Total deletes 0.00
Total upserts 0.00
Total discards 0.00
Total operations 1.00
*** Daily
statistics since 2025-08-31 15:34:09 ***
Total inserts 1.00
Total updates 0.00
Total deletes 0.00
Total upserts 0.00
Total discards 0.00
Total operations 1.00
*** Hourly
statistics since 2025-08-31 15:34:09 ***
Total inserts 1.00
Total updates 0.00
Total deletes 0.00
Total upserts 0.00
Total discards 0.00
Total operations 1.00
*** Latest
statistics since 2025-08-31 15:34:09 ***
Total inserts 1.00
Total updates 0.00
Total deletes 0.00
Total upserts 0.00
Total discards 0.00
Total operations 1.00
End of
Statistics.
GGSCI
(Node01.example.com) 21>
