Introduction
In the previous posts, we covered the step-by-step installation of Oracle GoldenGate (OGG) for both Oracle and MySQL environments. If you haven't gone through those yet, the links are below.
For this replication setup, we're using an Oracle database as the source and a MySQL database as the target. This article walks through the essential steps to prepare both databases for GoldenGate before we get into creating extract and replicat processes in a later post.
Prerequisites
- Oracle database installed on the source server.
- MySQL database installed on the target server.
- Connectivity between the GoldenGate server and both database servers.
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.
Prepare the Oracle Database (Source)
These steps set up the source-side prerequisites: supplemental logging, a dedicated tablespace, a GoldenGate admin user, and the database connection and TRANDATA registration inside the GoldenGate console.
Step 1: Enable Supplemental Logging and GoldenGate Replication
GoldenGate needs minimal supplemental logging enabled and the ENABLE_GOLDENGATE_REPLICATION parameter set to TRUE. Confirm the database is already in ARCHIVELOG mode before making these changes; if it isn't, switch it to ARCHIVELOG mode first.
SQL> def
DEFINE _DATE = "02-JUN-25" (CHAR)
DEFINE _CONNECT_IDENTIFIER = "orcldc" (CHAR)
DEFINE _USER = "SYS" (CHAR)
DEFINE _PRIVILEGE = "AS SYSDBA" (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> SELECT name,open_mode,database_role, log_mode,supplemental_log_data_min from v$database;
NAME OPEN_MODE DATABASE_ROLE LOG_MODE SUPPLEMENTAL_LOG_DAT
--------- -------------------- ---------------- ------------ --------------------
ORCL READ WRITE PRIMARY ARCHIVELOG NO
SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
Database altered.
SQL> SELECT name,open_mode,database_role, log_mode,supplemental_log_data_min from v$database;
NAME OPEN_MODE DATABASE_ROLE LOG_MODE SUPPLEMENTAL_LOG_DAT
--------- -------------------- ---------------- ------------ --------------------
ORCL READ WRITE PRIMARY ARCHIVELOG YES
SQL>
SQL> show parameter ENABLE_GOLDENGATE_REPLICATION
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
enable_goldengate_replication boolean FALSE
SQL>
SQL> ALTER SYSTEM SET ENABLE_GOLDENGATE_REPLICATION=TRUE SCOPE=BOTH;
System altered.
SQL> show parameter ENABLE_GOLDENGATE_REPLICATION
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
enable_goldengate_replication boolean TRUE
SQL>
Step 2: Create the Tablespace in the CDB and PDB
GoldenGate objects need their own tablespace, created separately at both the CDB and PDB level.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 ORCLPDB READ WRITE NO
SQL>
SQL> select name from v$datafile;
NAME
-----------------------------------------------------------------
/u01/app/oracle/oradata/ORCL/system01.dbf
/u01/app/oracle/oradata/ORCL/sysaux01.dbf
/u01/app/oracle/oradata/ORCL/undotbs01.dbf
/u01/app/oracle/oradata/ORCL/pdbseed/system01.dbf
/u01/app/oracle/oradata/ORCL/pdbseed/sysaux01.dbf
/u01/app/oracle/oradata/ORCL/users01.dbf
/u01/app/oracle/oradata/ORCL/pdbseed/undotbs01.dbf
/u01/app/oracle/oradata/ORCL/orclpdb/system01.dbf
/u01/app/oracle/oradata/ORCL/orclpdb/sysaux01.dbf
/u01/app/oracle/oradata/ORCL/orclpdb/undotbs01.dbf
/u01/app/oracle/oradata/ORCL/orclpdb/users01.dbf
/u01/app/oracle/oradata/ORCL/orclpdb/test01.dbf
/u01/app/oracle/oradata/ORCL/orclpdb/test02.dbf
/u01/app/oracle/oradata/ORCL/orclpdb/users02.dbf
/u01/app/oracle/oradata/ORCL/orclpdb/test03.dbf
15 rows selected.
SQL> create tablespace OGG datafile '/u01/app/oracle/oradata/ORCL/OGGCDB01.dbf' size 1g autoextend on;
Tablespace created.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 ORCLPDB READ WRITE NO
SQL>
SQL> alter session set container=ORCLPDB;
Session altered.
SQL> create tablespace OGG datafile '/u01/app/oracle/oradata/ORCL/orclpdb/OGGPDB01.dbf' size 1g autoextend on;
Tablespace created.
Step 3: Create the GoldenGate User in the CDB and PDB
A common user handles CDB-wide operations, while a local user handles replication inside the PDB. Both need the GoldenGate admin privilege granted through dbms_goldengate_auth.
SQL> alter session set container=cdb$root;
Session altered.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 ORCLPDB READ WRITE NO
SQL> create user c##ogg identified by C##Ogg$123 container=all default tablespace OGG temporary tablespace TEMP;
User created.
SQL> alter user c##ogg quota unlimited on OGG;
User altered.
SQL> grant set container to c##ogg container=all;
Grant succeeded.
SQL> grant alter system to c##ogg container=all;
Grant succeeded.
SQL> grant create session to c##ogg container=all;
Grant succeeded.
SQL> grant alter any table to c##ogg container=all;
Grant succeeded.
SQL> grant connect,resource to c##ogg container=all;
Grant succeeded.
SQL> exec dbms_goldengate_auth.grant_admin_privilege('c##ogg',container=>'all');
PL/SQL procedure successfully completed.
SQL>
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 ORCLPDB READ WRITE NO
SQL>
SQL> alter session set container=ORCLPDB;
Session altered.
SQL> create user ogg identified by OgG##123 container=current default tablespace OGG temporary tablespace TEMP;
User created.
SQL> grant create session to ogg container=current;
Grant succeeded.
SQL> grant alter any table to ogg container=current;
Grant succeeded.
SQL> grant connect,resource to ogg container=current;
Grant succeeded.
SQL> exec dbms_goldengate_auth.grant_admin_privilege('ogg');
PL/SQL procedure successfully completed.
SQL>
Step 4: Log In to the GoldenGate Console and Create the Database Connection
With the Oracle-side user in place, register the database connection inside the GoldenGate deployment console using the TNS alias you configured earlier.
Click DB Connection, then click the + sign.
Provide the credential domain, alias, user ID, and password. Use the TNS alias you already configured, then click Submit.
Click the arrow to establish the connection.
The database connection is now established successfully.
Step 5: Add TRANDATA Information
TRANDATA registration tells the source database which tables to track for change capture. Add this from the TRANDATA information section of the console.
Click the + sign at the TRANDATA information section.
Enter the PDB, schema, and table name, then click Submit.
Search for the TRANDATA information as shown below to verify it.
The source Oracle database is now fully prepared for GoldenGate replication.
Prepare the MySQL Database (Target)
On the target side, we create the working database, enable row-based binary logging for GoldenGate, create the replication user, and register both the connection and checkpoint table in the console.
Step 1: Create the Database
[mysql@mysqlOGG ~]$ mysql --user=root --password
Enter password:
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> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.01 sec)
mysql>
mysql> create database mysqldb;
Query OK, 1 row affected (0.01 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| mysqldb |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
mysql> use mysqldb;
Database changed
mysql>
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| mysqldb |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
mysql> select database();
+------------+
| database() |
+------------+
| mysqldb |
+------------+
1 row in set (0.00 sec)
Step 2: Update /etc/my.cnf for GoldenGate
GoldenGate for MySQL requires ROW-based binary logging with a full row image, and it does not support GTID mode, so that needs to be explicitly disabled. Add a unique server ID and, if this instance will relay changes further downstream, enable log_slave_updates.
[root@mysqlOGG ~]# cat /etc/my.cnf
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/8.4/en/server-configuration-defaults.html
[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove the leading "# " to disable binary logging
# Binary logging captures changes between backups and is enabled by
# default. It's default setting is log_bin=binlog
# disable_log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
user=mysql
log_bin=/u01/mysql/log_bin/myDB
datadir=/u01/mysql/data
tmpdir=/u01/mysql/tmpdir
#datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
#### GG Changes####
# Required for GoldenGate
server-id = 2 # Unique ID in replication topology
binlog_format = ROW # REQUIRED by GoldenGate
binlog_row_image = FULL # RECOMMENDED by GoldenGate
# Disable GTID (GG for MySQL doesn’t support it)
gtid_mode = OFF
enforce_gtid_consistency = OFF
# Optional but useful
log_slave_updates = ON # Only needed if this will relay changes
[root@mysqlOGG ~]#
Restart MySQL Database:
[root@mysqlOGG ~]# systemctl restart mysqld
[root@mysqlOGG ~]#
Step 3: Create the GoldenGate User
Verify the binary logging parameters took effect, then create a dedicated MySQL user for GoldenGate with the privileges it needs across all schemas.
[mysql@mysqlOGG ~]$ mysql --user=root --password
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 9
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> use mysqldb;
Database changed
mysql> SHOW VARIABLES WHERE Variable_name IN (
-> 'server_id', 'log_bin', 'binlog_format', 'binlog_row_image',
-> 'gtid_mode', 'enforce_gtid_consistency', 'log_slave_updates');
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| binlog_format | ROW |
| binlog_row_image | FULL |
| enforce_gtid_consistency | OFF |
| gtid_mode | OFF |
| log_bin | ON |
| log_slave_updates | ON |
| server_id | 2 |
+--------------------------+-------+
7 rows in set (0.01 sec)
mysql>
mysql> SELECT CURRENT_USER();
+----------------+
| CURRENT_USER() |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.01 sec)
mysql> SELECT user, host FROM mysql.user;
+------------------+-----------+
| user | host |
+------------------+-----------+
| mysql.infoschema | localhost |
| mysql.session | localhost |
| mysql.sys | localhost |
| root | localhost |
+------------------+-----------+
4 rows in set (0.00 sec)
mysql> CREATE USER 'mysqlogg'@'%' IDENTIFIED BY 'India#123';
Query OK, 0 rows affected (0.09 sec)
mysql> GRANT ALL PRIVILEGES ON *.* TO 'mysqlogg'@'%' WITH GRANT OPTION;
Query OK, 0 rows affected (0.01 sec)
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.01 sec)
mysql> SELECT user, host FROM mysql.user;
+------------------+-----------+
| user | host |
+------------------+-----------+
| mysqlogg | % |
| mysql.infoschema | localhost |
| mysql.session | localhost |
| mysql.sys | localhost |
| root | localhost |
+------------------+-----------+
5 rows in set (0.00 sec)
mysql>exit
[mysql@mysqlOGG ~]$ mysql --user=mysqlogg --password
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 10
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 CURRENT_USER();
+----------------+
| CURRENT_USER() |
+----------------+
| mysqlogg@% |
+----------------+
1 row in set (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| mysqldb |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
mysql>
Step 4: Create a Test User for Replication
A separate, scoped-down user is useful for verifying that data lands correctly on the target once replication is running, without giving it the broad privileges GoldenGate itself needs.
[mysql@mysqlOGG ~]$ mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 11
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> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| mysqldb |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.06 sec)
mysql>
mysql> CREATE USER 'test'@'%' IDENTIFIED BY 'India#123';
Query OK, 0 rows affected (0.12 sec)
mysql> GRANT ALL PRIVILEGES ON mysqldb.* TO 'test'@'%';
Query OK, 0 rows affected (0.01 sec)
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.01 sec)
mysql> exit
Bye
[mysql@mysqlOGG ~]$
[mysql@mysqlOGG ~]$ mysql -u test -p mysqldb
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 12
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 user();
+----------------+
| user() |
+----------------+
| test@localhost |
+----------------+
1 row in set (0.00 sec)
mysql> select database();
+------------+
| database() |
+------------+
| mysqldb |
+------------+
1 row in set (0.00 sec)
mysql>
Step 5: Log In to the GoldenGate Console and Create the Database Connection
Register the MySQL connection in the GoldenGate console, using the ODBC-backed connection details rather than a TNS alias this time.
Click DB Connection, then click the + sign.
Provide the credential domain and alias, the server hostname and port, and the user ID and password.
Click the arrow to establish the connection.
The MySQL database connection is now established successfully.
Step 6: Add the Checkpoint Table
GoldenGate uses a checkpoint table on the target to track replication progress. Add it through the console before moving on to the extract and replicat configuration.
Click Checkpoint, then click the + sign.
Enter the table name followed by the database name.
The checkpoint table has been added.
Both the Oracle source and MySQL target databases are now prepared for GoldenGate replication. The next post in this series will cover creating the extract and replicat processes to start moving data between them.
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