Oracle GoldenGate Migration Guide: Oracle to MySQL Data Replication

OGG Overview – Oracle GoldenGate (OGG) is a commercial tool that captures Oracle redo logs for real‑time data replication without extensive schema changes. It consists of Manager, Extract, Data Pump, Trail, Collector, and Replicat processes that work together to move data from a source to a target system.

Migration Plan – The migration includes environment preparation, table‑structure conversion, full‑load and incremental data sync, data validation, and issue handling. The source is Oracle 11.2.0.4 with OGG 12.2.0.2.2, and the target is MySQL 5.7.21 with a matching OGG version.

Environment Information

Software

Source

Target

OGG version

OGG 12.2.0.2.2 For Oracle

OGG 12.2.0.2.2 For MySQL

Database version

Oracle 11.2.0.4

MySQL 5.7.21

OGG_HOME

/home/oracle/ogg

/opt/ogg

IP address

17X.1X.84.124

17X.1X.84.121

Database name

cms

cms

Table Structure Migration – Use the open‑source sqlines tool to convert Oracle DDL to MySQL. OGG does not sync DDL, so complete the schema migration before switching the database.

Data Migration – Configure OGG to run the Extract process on the source, capture redo logs, then start the Pump and Replicat processes on the target. Incremental capture should be enabled before the full load to ensure continuity.

Source OGG Configuration

Enable archive mode and supplemental logging on Oracle:

SQL> SELECT NAME,LOG_MODE,OPEN_MODE,PLATFORM_NAME,FORCE_LOGGING,SUPPLEMENTAL_LOG_DATA_MIN FROM V$DATABASE;

SQL> shutdown immediate;

SQL> startup mount;

SQL> alter database archivelog;

SQL> alter database open;

SQL> archive log list;

SQL> alter database add supplemental log data;

SQL> alter database force logging;

Create OGG user with required privileges:

SQL> CREATE USER ogg IDENTIFIED BY ogg DEFAULT TABLESPACE OGG_DATA;

SQL> grant connect,resource,unlimited tablespace to ogg;

SQL> grant create session,alter session to ogg;

SQL> grant execute on utl_file to ogg;

SQL> grant select any dictionary, select any table to ogg;

SQL> grant alter any table to ogg;

SQL> grant flashback any table to ogg;

SQL> grant sysdba to ogg;

Configure Manager (MGR) process:

shell> cd $OGG_HOME

shell> ggsci

ggsci> edit params mgr

PORT 7809

DYNAMICPORTLIST 8000-8050

Configure Extract, Pump, and Replicat processes with appropriate params files, setting environment variables (NLS_LANG, ORACLE_HOME, ORACLE_SID), trail locations, and checkpoint tables.

Target OGG Configuration

Create MySQL OGG user:

mysql> create user 'ogg'@'%' identified by 'ogg';

mysql> grant all on *.* to 'ogg'@'%';

Create checkpoint database and table:

mysql> create database ogg;

ggsci> edit param ./GLOBALS

checkpointtable ogg.ggs_checkpoint

Full‑Load Synchronization – Add a special Extract process with SOURCEISTABLE and RMTFILE to write data directly to the target directory, then create a one‑time Replicat with SPECIALRUN to apply the full load.

Data Validation – Verify migration by checking OGG discard logs, row counts on source and target tables, or using checksum tools (e.g., pt‑table‑checksum) for row‑level verification.

Migration Issues & Solutions

MySQL column size limits – shrink oversized VARCHAR columns or convert them to TEXT.

InnoDB index prefix limits – ensure indexed VARCHAR columns do not exceed 768 bytes when using utf8mb4.

Foreign‑key constraints – disable with SET GLOBAL foreign_key_checks=off; during load and re‑enable afterward.

Handling tables without primary keys – add a GUID column in Oracle or use OGG’s HANDLECOLLISIONS to manage duplicates.

OGG security rules – add ACCESSRULE entries on both source and target MGR nodes to allow remote connections, e.g., ACCESSRULE, PROG *, IPADDR 10.186.*.*, ALLOW.

LOB extraction – use RMTFILE instead of RMTTASK for tables containing CLOB/BLOB columns.

Reference Materials – Official OGG documentation, Oracle support notes (e.g., ID 1605674.1), and the “Administering Oracle GoldenGate” guide provide detailed parameter descriptions and configuration examples.