Introduction
In the previous posts, we covered the installation of Oracle GoldenGate (OGG) and the database configuration for both Oracle and MySQL environments. If you haven't gone through those yet, the links are below.
- Oracle GoldenGate 23ai Installation for Oracle
- Oracle GoldenGate 23ai Installation for MySQL
- Prepare Database for GoldenGate Replication
This is the final post in the series. Here we'll configure Extract and Replicat for real-time replication from Oracle to MySQL. We'll cover four parts:
- Extract configuration
- Distribution Path
- Initial load using csvsql
- Replicat configuration
Prerequisites
- Oracle GoldenGate software installed on both the source and target servers.
- Database configuration for GoldenGate completed on the source and target databases.
Environment Used in This Guide
| Server | Source (Oracle) | Target (MySQL) | OGG (Oracle) | OGG (MySQL) |
|---|---|---|---|---|
| Hostname | orcl.oraeasy.com | mysqlOGG.oraeasy.com | ogg.oraeasy.com | mysqlOGG.oraeasy.com |
| OS | OEL 9 | OEL 9 | OEL 9 | OEL 9 |
| DB Name | ORCL | mysqldb | NA | NA |
Note that the same server is used for the MySQL database and its GoldenGate deployment.
Configure the Extract at the Source
The Extract process reads committed changes from the Oracle redo logs and writes them to a trail file. We'll create a normal extract here and handle the initial load separately, rather than letting the wizard do it automatically.
Step 1: Create the Extract
Log in to the source-side GoldenGate console to configure Extract.
Click Home, then click the + sign in the Extract section.
Select the Extract type, give it a name, and click Next.
Provide the database credential details, PDB name, and trail file name, then click Next.
Keep the default options and click Next.
Add the replication schema and table details. We're replicating the Company table from the Test schema here, then click Create and Run.
Monitor progress in Notifications.
The Extract is now created and running.
Configure the Distribution Path
The Distribution Path moves trail files from the source deployment to the target deployment. It requires a matching user on both sides: one registered under distribution services on the target, and one registered under path connections on the source.
Step 1: Create a User on the Target Distribution Service
Click User Administration, then click the + sign.
Provide a username and password, select User Role as Operator, and click Submit.
The user has been created.
Step 2: Create the Same User on the Source Path Connection
Click Path Connection, then click the + sign.
Provide the same username and password, then click Submit.
The user has been created.
Step 3: Create the Distribution Path on the Source
Click Distribution Path, then click the + sign.
Provide the path name and click Next.
Provide the source-side details and click Next.
Provide the target-side details and click Next.
Keep the default options and click Next.
Keep the default options and click Next.
Click Create and Run.
The Distribution Path has been created.
You can see the path running under Receiver Services on the target side.
The receiver service must be running on the target for the Distribution Path to work. You can cross-check its status and start it manually if needed, as shown below.
[oracle@mysqlOGG ~]$ ps -ef | grep recvsrvr
oracle 3853 656 0 12:17 ? 00:00:21 /ogg/ogg23ai_ma/bin/recvsrvr --config /ogg/ogg23ai_sm/var/run/OggMySQL-recvsrvr-config.dat
oracle 6583 6278 1 13:35 pts/2 00:00:00 ./recvsrvr -d /ogg/ogg23ai_deploy
oracle 6600 6278 0 13:36 pts/2 00:00:00 grep --color=auto recvsrvr
[oracle@mysqlOGG ~]$ netstat -tulnp | grep recvsrvr
(Not all processes could be identified, non-owned process info
will not be shown, you would have to be root to see it all.)
tcp 0 0 0.0.0.0:7809 0.0.0.0:* LISTEN 6583/./recvsrvr
tcp6 0 0 :::7805 :::* LISTEN 3853/recvsrvr
tcp6 0 0 :::7809 :::* LISTEN 6583/./recvsrvr
udp 0 0 0.0.0.0:44001 0.0.0.0:* 3853/recvsrvr
udp 0 0 0.0.0.0:7805 0.0.0.0:* 3853/recvsrvr
udp 0 0 0.0.0.0:7809 0.0.0.0:* 6583/./recvsrvr
[oracle@mysqlOGG ~]$
To Start the Receiver Service:
[oracle@mysqlOGG ~]$ cd /ogg/ogg23ai_ma/bin/
[oracle@mysqlOGG bin]$ ls
adminclient cachefiledump chkptdump convchk defgen emsclnt keygen oggca.sh orapki recvsrvr retrace trailscan
adminsrvr checkprm ConfigService convprm distsrvr extract logdump oggerr pmsrvr replicat ServiceManager XAGTask
[oracle@mysqlOGG bin]$ ./recvsrvr -d /ogg/ogg23ai_deploy &
Perform the Initial Load
Before Replicat can pick up ongoing changes, the target needs a starting copy of the data. We'll export the current table contents from Oracle, import them into MySQL, and note the source SCN (System Change Number) at the moment of export, since Replicat will use that SCN as its starting point.
Step 1: Capture the Current SCN and Table Data
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
6564887
SQL> show user
USER is "TEST"
SQL>
SQL> def
DEFINE _DATE = "05-JUN-25" (CHAR)
DEFINE _CONNECT_IDENTIFIER = "ORCLPDB" (CHAR)
DEFINE _USER = "TEST" (CHAR)
DEFINE _PRIVILEGE = "" (CHAR)
DEFINE _SQLPLUS_RELEASE = "1927000000" (CHAR)
DEFINE _EDITOR = "vi" (CHAR)
DEFINE _O_VERSION = "Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.27.0.0.0" (CHAR)
DEFINE _O_RELEASE = "1927000000" (CHAR)
SQL>
SQL> SET COLSEP ','
SQL> SET PAGESIZE 1000
SQL> SET LINESIZE 1000
SQL> SET TRIMSPOOL ON
SQL> SET FEEDBACK OFF
SQL> SET HEADING ON
SQL>
SQL> SPOOL /home/oracle/company.csv
SQL> SELECT * FROM COMPANY;
SQL> SPOOL OFF
Step 2: Transfer the Spool File to the MySQL Server
[oracle@orcl ~]$ scp company.csv mysql@mysqlOGG.oraeasy.com:/var/lib/mysql
mysql@mysqlogg.oraeasy.com's password:
company.csv 100% 872 360.6KB/s 00:00
[oracle@orcl ~]$
Step 3: Install csvsql and Its Dependencies
We'll use the csvsql utility from the csvkit suite to load the CSV directly into MySQL, so install pip, csvkit, and the MySQL connector first.
[root@mysqlOGG ~]# dnf install python3-pip -y
Last metadata expiration check: 1:48:33 ago on Thu 05 Jun 2025 12:44:38 PM IST.
Dependencies resolved.
==============================================================================================================================================================
Package Architecture Version Repository Size
==============================================================================================================================================================
Installing:
python3-pip noarch 21.3.1-1.el9 ol9_appstream 3.0 M
Transaction Summary
==============================================================================================================================================================
Install 1 Package
Total download size: 3.0 M
Installed size: 8.8 M
Downloading Packages:
python3-pip-21.3.1-1.el9.noarch.rpm 1.1 MB/s | 3.0 MB 00:02
--------------------------------------------------------------------------------------------------------------------------------------------------------------
Total 1.1 MB/s | 3.0 MB 00:02
Running transaction check
Transaction check succeeded.
Running transaction test
Transaction test succeeded.
Running transaction
Preparing : 1/1
Installing : python3-pip-21.3.1-1.el9.noarch 1/1
Running scriptlet: python3-pip-21.3.1-1.el9.noarch 1/1
Verifying : python3-pip-21.3.1-1.el9.noarch 1/1
Installed:
python3-pip-21.3.1-1.el9.noarch
Complete!
[root@mysqlOGG ~]#
[root@mysqlOGG ~]# pip install csvkit
Collecting csvkit
Downloading csvkit-2.1.0-py2.py3-none-any.whl (75 kB)
|████████████████████████████████| 75 kB 190 kB/s
Collecting agate-dbf>=0.2.3
Downloading agate_dbf-0.2.3-py2.py3-none-any.whl (3.6 kB)
Collecting openpyxl
Downloading openpyxl-3.1.5-py2.py3-none-any.whl (250 kB)
|████████████████████████████████| 250 kB 4.1 MB/s
Collecting xlrd
Downloading xlrd-2.0.1-py2.py3-none-any.whl (96 kB)
|████████████████████████████████| 96 kB 3.3 MB/s
Collecting agate-sql>=0.7.0
Downloading agate_sql-0.7.2-py2.py3-none-any.whl (7.3 kB)
Collecting sqlalchemy
Downloading sqlalchemy-2.0.41-cp39-cp39-manylinux_2_17_x86_64.manylinux2014_x86_64.whl (3.2 MB)
|████████████████████████████████| 3.2 MB 4.0 MB/s
Collecting agate-excel>=0.4.0
Downloading agate_excel-0.4.1-py2.py3-none-any.whl (7.1 kB)
Collecting agate>=1.12.0
Downloading agate-1.13.0-py2.py3-none-any.whl (95 kB)
|████████████████████████████████| 95 kB 2.0 MB/s
Collecting importlib_metadata
Downloading importlib_metadata-8.7.0-py3-none-any.whl (27 kB)
Collecting pytimeparse>=1.1.5
Downloading pytimeparse-1.1.8-py2.py3-none-any.whl (10.0 kB)
Collecting Babel>=2.0
Downloading babel-2.17.0-py3-none-any.whl (10.2 MB)
|████████████████████████████████| 10.2 MB 935 kB/s
Collecting parsedatetime!=2.5,>=2.1
Downloading parsedatetime-2.6-py3-none-any.whl (42 kB)
|████████████████████████████████| 42 kB 706 kB/s
Collecting python-slugify>=1.2.1
Downloading python_slugify-8.0.4-py2.py3-none-any.whl (10 kB)
Collecting leather>=0.3.2
Downloading leather-0.4.0-py2.py3-none-any.whl (30 kB)
Collecting isodate>=0.5.4
Downloading isodate-0.7.2-py3-none-any.whl (22 kB)
Collecting dbfread>=2.0.5
Downloading dbfread-2.0.7-py2.py3-none-any.whl (20 kB)
Collecting olefile
Downloading olefile-0.47-py2.py3-none-any.whl (114 kB)
|████████████████████████████████| 114 kB 4.4 MB/s
Collecting et-xmlfile
Downloading et_xmlfile-2.0.0-py3-none-any.whl (18 kB)
Collecting typing-extensions>=4.6.0
Downloading typing_extensions-4.14.0-py3-none-any.whl (43 kB)
|████████████████████████████████| 43 kB 1.5 MB/s
Collecting greenlet>=1
Downloading greenlet-3.2.2-cp39-cp39-manylinux_2_24_x86_64.manylinux_2_28_x86_64.whl (579 kB)
|████████████████████████████████| 579 kB 6.7 MB/s
Collecting zipp>=3.20
Downloading zipp-3.22.0-py3-none-any.whl (9.8 kB)
Collecting text-unidecode>=1.3
Downloading text_unidecode-1.3-py2.py3-none-any.whl (78 kB)
|████████████████████████████████| 78 kB 2.5 MB/s
Installing collected packages: text-unidecode, typing-extensions, pytimeparse, python-slugify, parsedatetime, leather, isodate, greenlet, et-xmlfile, Babel, zipp, xlrd, sqlalchemy, openpyxl, olefile, dbfread, agate, importlib-metadata, agate-sql, agate-excel, agate-dbf, csvkit
Successfully installed Babel-2.17.0 agate-1.13.0 agate-dbf-0.2.3 agate-excel-0.4.1 agate-sql-0.7.2 csvkit-2.1.0 dbfread-2.0.7 et-xmlfile-2.0.0 greenlet-3.2.2 importlib-metadata-8.7.0 isodate-0.7.2 leather-0.4.0 olefile-0.47 openpyxl-3.1.5 parsedatetime-2.6 python-slugify-8.0.4 pytimeparse-1.1.8 sqlalchemy-2.0.41 text-unidecode-1.3 typing-extensions-4.14.0 xlrd-2.0.1 zipp-3.22.0
WARNING: Running pip as the 'root' user can result in broken permissions and conflicting behaviour with the system package manager. It is recommended to use a virtual environment instead: https://pip.pypa.io/warnings/venv
[root@mysqlOGG ~]#
[root@mysqlOGG ~]# pip install mysql-connector-python
Collecting mysql-connector-python
Downloading mysql_connector_python-9.3.0-cp39-cp39-manylinux_2_28_x86_64.whl (33.8 MB)
|████████████████████████████████| 33.8 MB 4.5 MB/s
Installing collected packages: mysql-connector-python
Successfully installed mysql-connector-python-9.3.0
WARNING: Running pip as the 'root' user can result in broken permissions and conflicting behaviour with the system package manager. It is recommended to use a virtual environment instead: https://pip.pypa.io/warnings/venv
[root@mysqlOGG ~]#
Step 4: Clean Up the CSV File
The spool output includes a trailing spool off line that needs to be stripped before csvsql can parse the file cleanly.
[mysql@mysqlOGG ~]$ head company.csv
EMP_ID,NAME,COMPANY
104,Vijay ,PATH
105,Vikas ,INFOSYS
101,Yash ,WIPRO
102,Vijay ,AIRTEL
103,Riya ,TCS
SQL> spool off
[mysql@mysqlOGG ~]$ sed -i '/^SQL> spool off/d' final_company.csv
[mysql@mysqlOGG ~]$
[mysql@mysqlOGG ~]$ head final_company.csv
EMP_ID,NAME,COMPANY
104,Vijay ,PATH
105,Vikas ,INFOSYS
101,Yash ,WIPRO
102,Vijay ,AIRTEL
103,Riya ,TCS
Step 5: Import the Data Using csvsql
Here we're using "test" as the connecting user, "company" as the table name, and "mysqldb" as the target database.
[mysql@mysqlOGG ~]$ csvsql --db "mysql+mysqlconnector://test:India#123@localhost:3306/mysqldb" --tables company --insert --no-inference --snifflimit 0 --delimiter ',' company.csv
[mysql@mysqlOGG ~]$
[mysql@mysqlOGG ~]$ mysql -u test -p mysqldb
Enter password:
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 13
Server version: 8.4.5 MySQL Community Server - GPL
Copyright (c) 2000, 2025, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
mysql> select * from company;
+--------+------------------------------+---------+
| EMP_ID | NAME | COMPANY |
+--------+------------------------------+---------+
| 104 | Vijay | PATH |
| 105 | Vikas | INFOSYS |
| 101 | Yash | WIPRO |
| 102 | Vijay | AIRTEL |
| 103 | Riya | TCS |
+--------+------------------------------+---------+
5 rows in set (0.00 sec)
Configure the Replicat
With the initial load in place, Replicat can now apply changes from the trail file onto MySQL, starting from the SCN we captured earlier so nothing is skipped or duplicated.
Step 1: Create the Replicat
Log in to the target-side GoldenGate console to configure Replicat.
Click Home, then click the + sign in the Replicat section.
Provide the Replicat type, name, and description, then click Next.
Provide the trail name, credential details, and checkpoint table, then click Next.
Keep the default options and click Next.
Configure the parameter file and add the replication table details, then click Create.
The Replicat has been created.
Step 2: Start the Replicat Using the Captured SCN
Start Replicat with an explicit starting point rather than the default, using the SCN captured before the initial load so the target picks up exactly where the exported data left off.
Click the three dots, then click Start with Options.
Select After CSN as the start point and provide the SCN in the CSN field, then click Submit.
After a short while, the Replicat lag drops to zero.
Verify Real-Time Replication
GoldenGate configuration between Oracle and MySQL is now complete. We can confirm real-time replication is working by querying the same table on both sides and comparing the results.
At Source (Oracle):
SQL> set lines 333 pages 333
SQL> col name for a20
SQL> col company for a30
SQL> select * from company;
EMP_ID NAME COMPANY
---------- -------------------- ------------------------------
104 Vijay PATH
105 Vikas INFOSYS
106 sachin CISCO
107 Ajay XYZ
101 Yash WIPRO
102 Vijay AIRTEL
103 Riya TCS
7 rows selected.
At Target (MySQL):
mysql> select * from company;
+--------+------------------+---------+
| EMP_ID | NAME | COMPANY |
+--------+------------------+---------+
| 104 | Vijay | PATH |
| 105 | Vikas | INFOSYS |
| 101 | Yash | WIPRO |
| 102 | Vijay | AIRTEL |
| 103 | Riya | TCS |
| 106 | sachin | CISCO |
| 107 | Ajay | XYZ |
+--------+------------------+---------+
7 rows in set (0.09 sec)
Both sides match, confirming that rows added after the initial load are replicating in real time from Oracle to MySQL. That wraps up this series on Oracle GoldenGate 23ai, from installation through a working Oracle-to-MySQL replication setup.
Thank you for reading!
I hope this content has been helpful to you. Your feedback and suggestions are always welcome. Feel free to leave a comment or reach out with any queries.
Abhishek Shrivastava
Oracle DBA with hands-on experience managing production Data Guard, RAC, GoldenGate, and APEX environments. I write practical, tested installation and troubleshooting guides based on real deployment work.
Comments
Post a Comment