Tuesday, 9 September 2025

Oracle to MySQL Unidirectional Replication

 

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>

No comments:

Post a Comment

Oracle to MySQL Unidirectional Replication

  Topic : Oracle to MySQL Unidirectional Replication   O racle Goldengate facilitates heterogeneous data replication, including repli...