Prepare Databases for GoldenGate Replication ~ OraEasy

Introduction: In the previous posts, we had covered the step-by-step installation of Oracle GoldenGate (OGG) for both Oracle and MySQL environments. Please visit those, links are given below. Now for replication we are using Oracle database as source and MySQL database as target. In this article, we will go through the essential steps to prepare Oracle (source) and MySQL (target) databases for GoldenGate .

Oracle GoldenGate 23ai Installation for Oracle

Oracle GoldenGate 23ai Installation for MySQL

Prerequisites: Below are the minimum requirements.
- Oracle database should be installed on source server.
- MySQL database should be installed on target server.
- Connectivity between GoldenGate server and database servers.

Environment Used:

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

Please note that we are using the same server for MySQL Database and for its GoldenGate.

Now first setup the Oracle Database for GoldenGate step by step:

1. Now enable SUPPLEMENTAL_LOG_DATA and set ENABLE_GOLDENGATE_REPLICATION to TRUE. Also make sure that Database should be in ARCHIVELOG mode, if not then make it.



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>

2. Now create the tablespace in CDB and PDB.



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>

SQL> create tablespace OGG datafile '/u01/app/oracle/oradata/ORCL/OGGCDB01.dbf' size 1g autoextend on;

Tablespace created.

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 tablespace OGG datafile '/u01/app/oracle/oradata/ORCL/orclpdb/OGGPDB01.dbf' size 1g autoextend on;

Tablespace created.

3. Now create the user for GoldenGate in CDB and PDB.


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>

4. Now login to GoldenGate Console and make database connection.

Click on DB Connection and then click + sign.

Provide the Credential Domain & Alias and user id & its password. Use TNS alias which is configured. Last click on Submit.

Now click on arrow to establish connection.

Now DB connection established successfully.

5. Now add trandata information.

Click on + at TRANDATA information section.

Now give the PDB, Schema and Table name. Then click Submit.

Now search the trandata information like below and verify.

We have successfully configured the source Oracle Database for GoldenGate Replication.

Now we will setup the MySQL Database for GoldenGate step by 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)

2. Now do the required changes in /etc/my.cnf for GoldenGate.


[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 ~]#

3. Now create user for GoldenGate.



[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>

4. Now create user to test replication.



[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>

5. Now login to GoldenGate Console and make database connection.

Click on DB Connection and then click + sign.