- Introduction:
Oracle GoldenGate 23ai Installation for Oracle
Oracle GoldenGate 23ai Installation for MySQL
- Oracle database should be installed on source server.
- MySQL database should be installed on target server.
- Connectivity between GoldenGate server and database servers.
| 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.
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>
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.
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>
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.
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.
[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)
[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 ~]#
[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>
[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.
Provide the Credential Domain & Alias,Server hostname & Port and user id & its password.
Now click on arrow to establish connection.
Now DB connection established successfully.
Click on Checkpoint and then click +
Give the table name followed by the database name
Now checkpoint table has been added.
Thanks for visiting!!
