System Architecture & Prerequisites

Component	Source System	Target System
Operating System	RHEL 6 x86_64	RHEL 6 x86_64
Hostname	src-oracle-db	dst-mysql-db
Network Address	10.0.0.51	10.0.0.61
Database Release	Oracle 11.2.0.4	MySQL 5.7.23
Character Encoding	ZHS16GBK	UTF-8
Schema/Instance	srcdb	dstschema
Replication User	ggs_ctl / SecurePass1!	ggs_ctl / SecurePass1!
OGG Release	12.3.0.1	12.3.0.1
Target Table	srcdb.EMP_PAYROLL	dstschema.EMP_PAYROLL

Pre-Deployment Configuration

Establish bidirectional host resolution across both nodes:

cat >> /etc/hosts <<eof dst-mysql-db="" eof="" src-oracle-db=""></eof>

Terminate firewall services to guarantee uninterrupted port communication:

systemctl disable firewalld
systemctl stop firewalld

Prepare the destination schema. GoldenGate requires explicit primary keys on synchronized tables to maintain row uniqueness during replication.

CREATE TABLE EMP_PAYROLL (
  emp_id INT NOT NULL,
  emp_name VARCHAR(255),
  PRIMARY KEY (emp_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Verify source database encoding:

SELECT userenv('language') FROM dual;

Software Installation

Source Node (Oracle): Deploy using the response file for silent installation, or utilize the graphical installer. Ensure the installation directory matches the planned extraction path.

mkdir -p /u01/app/goldengate
chown -R oracle:dba /u01/app/goldengate
# Extract installation media, then execute silent install
./runInstaller -silent -responseFile response/oggcore.rsp \
  -oracle_home /u01/app/oracle/product/11.2.0/db_1 \
  -software_location /u01/app/goldengate

Configure environment variables for the database account:

export GG_HOME=/u01/app/goldengate
export LD_LIBRARY_PATH=$GG_HOME:$ORACLE_HOME/lib
export PATH=$PATH:$GG_HOME

Target Node (MySQL): Extract the archive directly to the designated path and apply matching environment variables to the MySQL OS user.

mkdir -p /u01/app/goldengate
tar -xzf ggs_Linux_x64_MySQL_64bit.tar.gz -C /u01/app/goldengate

Oracle Database Preparasion

Activate archive log mode to capture redo streams:

SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER SYSTEM SET log_archive_dest_1='LOCATION=/u01/archive/logs' SCOPE=SPFILE;
ALTER DATABASE ARCHIVELOG;
ALTER DATABASE OPEN;

Enable mandatory supplemental logging to capture transactional metadata:

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;
ALTER DATABASE FORCE LOGGING;

Validate activation:

SELECT force_logging, supplemental_log_data_min FROM v$database;

Replication Account & Schema Initialization

Establish the administrative user on the Oracle instance with granular privileges:

CREATE TABLESPACE ggtbs DATAFILE '/u01/app/oracle/oradata/srcdb/ggtbs01.dbf' SIZE 500M AUTOEXTEND ON;
CREATE USER ggs_ctl IDENTIFIED BY SecurePass1! DEFAULT TABLESPACE ggtbs;
GRANT CREATE SESSION, ALTER SESSION TO ggs_ctl;
GRANT RESOURCE, CONNECT TO ggs_ctl;
GRANT SELECT ANY DICTIONARY, SELECT ANY TABLE TO ggs_ctl;
GRANT FLASHBACK ANY TABLE, ALTER ANY TABLE TO ggs_ctl;
GRANT INSERT ANY TABLE, UPDATE ANY TABLE, DELETE ANY TABLE TO ggs_ctl;
GRANT EXECUTE ON DBMS_FLASHBACK TO ggs_ctl;
GRANT EXECUTE ON UTL_FILE TO ggs_ctl;
ALTER SYSTEM SET enable_goldengate_replication=TRUE;

Insert baseline test records:

CONN ggs_ctl/SecurePass1!
CREATE TABLE EMP_PAYROLL (id NUMBER PRIMARY KEY, name VARCHAR2(100));
INSERT INTO EMP_PAYROLL VALUES (501, 'Elena Rostova');
INSERT INTO EMP_PAYROLL VALUES (502, 'Marcus Cole');
COMMIT;

On the MySQL endpoint, confirm schema existence and assign comprehensive privileges to the replication account.

DDL Support Scripts

Execute the provided marker and DDL setup routines on the Oracle database:

sqlplus / as sysdba
@marker_setup.sql
@ddl_setup.sql
@role_setup.sql
@ddl_enable.sql
# Input 'ggs_ctl' when prompted for the GoldenGate schema

GoldenGate Process Architecture

1. Directory Initialization

Launch the command-line utility on both servers:

./ggsci
CREATE SUBDIRS

Critical directories include dirprm (configurations), dirdat (binary trail files), dirdef (metadata mappings), and dirrpt (execution reports).

2. Manager Configuration

Source Parameters:

EDIT PARAMS MGR
PORT 8888
DYNAMICPORTLIST 8900-8950
AUTORESTART EXTRACT *, RETRIES 5, WAITMINUTES 5
PURGEOLDEXTRACTS ./dirdat/*, USECHECKPOINTS, MINKEEPDAYS 7
LAGREPORTHOURS 1
LAGINFOMINUTES 30
LAGCRITICALMINUTES 45

Target Parameters:

EDIT PARAMS MGR
PORT 8888
ACCESSRULE, PROG *, IPADDR 10.0.0.51, ALLOW
AUTORESTART REPLICAT *, RETRIES 5, WAITMINUTES 5
PURGEOLDEXTRACTS ./dirdat/*, USECHECKPOINTS, MINKEEPDAYS 7

Initialize the daemon on both endpoints: START MGR

3. Extract Process (Capture)

EDIT PARAMS EXT_CAP
EXTRACT EXT_CAP
SETENV (ORACLE_SID = 'srcdb')
SETENV (NLS_LANG = 'AMERICAN_AMERICA.ZHS16GBK')
USERID ggs_ctl, PASSWORD SecurePass1!
EXTTRAIL /u01/app/goldengate/dirdat/ec
TABLE srcdb.EMP_PAYROLL;

Register the capture module and allocate trail storage:

ADD EXTRACT EXT_CAP, TRANLOG, BEGIN NOW
ADD EXTTRAIL /u01/app/goldengate/dirdat/ec, EXTRACT EXT_CAP, MEGABYTES 100
DBLOGIN USERID ggs_ctl, PASSWORD SecurePass1!
ADD TRANDATA srcdb.EMP_PAYROLL

4. Data Pump Process (Forwarding)

EDIT PARAMS PUMP_XFER
EXTRACT PUMP_XFER
PASSTHRU
RMTHOST 10.0.0.61, MGRPORT 8888, COMPRESS
RMTTRAIL /u01/app/goldengate/dirdat/px
TABLE srcdb.EMP_PAYROLL;

Bind the pump to the local trail and define the remote destination:

ADD EXTRACT PUMP_XFER, EXTTRAILSOURCE /u01/app/goldengate/dirdat/ec, BEGIN NOW
ADD RMTTRAIL /u01/app/goldengate/dirdat/px, EXTRACT PUMP_XFER, MEGABYTES 100

5. Metadata Definition Generation (Heterogeneous)

Cross-platform replication requires explicit column mapping. Generate the definition file on the source:

EDIT PARAMS DEFGEN
DEFSFILE ./dirdef/src_metadata.def, PURGE
USERID ggs_ctl, PASSWORD SecurePass1!
TABLE srcdb.EMP_PAYROLL;

Execute the generator and transfer artifacts:

./defgen PARAMFILE dirprm/defgen.prm
scp dirdef/src_metadata.def root@10.0.0.61:/u01/app/goldengate/dirdef/

6. Checkpoint Table Registration

Establish recovery points for transactional continuity:

Source:

EDIT PARAMS ./GLOBALS
CHECKPOINTTABLE ggs_ctl.ggs_checkpoint
ADD CHECKPOINTTABLE ggs_ctl.ggs_checkpoint

Target:

EDIT PARAMS ./GLOBALS
CHECKPOINTTABLE dstschema.ggs_checkpoint
# Inside GGSCI target shell:
DBLOGIN SOURCEDB dstschema, USERID ggs_ctl, PASSWORD SecurePass1!
ADD CHECKPOINTTABLE dstschema.ggs_checkpoint

7. Replicat Process (Apply)

EDIT PARAMS REP_APP
REPLICAT REP_APP
SOURCEDEFS /u01/app/goldengate/dirdef/src_metadata.def
TARGETDB dstschema, USERID ggs_ctl, PASSWORD SecurePass1!
DISCARDFILE /u01/app/goldengate/dirdat/rep_app.dsc, APPEND, MEGABYTES 50
MAP srcdb.EMP_PAYROLL, TARGET dstschema.EMP_PAYROLL;

Bind the apply process to the remote trail:

ADD REPLICAT REP_APP, EXTTRAIL /u01/app/goldengate/dirdat/px, CHECKPOINTTABLE dstschema.ggs_checkpoint

8. Pipeline Activation

Execute the startup sequence:

START MGR
START EXTRACT EXT_CAP
START EXTRACT PUMP_XFER
# Switch to Target Host
START MGR
START REPLICAT REP_APP

Monitor pipeline health using INFO ALL. Verify that latency drops to 00:00:00 and status displays RUNNING.

Administrative Operations

VIEW REPORT EXT_CAP          # Inspect capture execution details
STOP REPLICAT REP_APP        # Terminate apply process gracefully
DELETE EXTRACT PUMP_XFER     # Purge configuration references
INFO ALL                     # List all active components
VIEW PARAMS MGR              # Audit manager directives

Implement persistent startup by wrapping START MGR and process directives in a shell script, then register it with systemd or /etc/rc.local to trigger on boot.

Cross-Platform Synchronization Constraints

• Data Type Translation: Oracle DATE stores timestamps, while MySQL may interpret it as date-only unless explicitly cast to DATETIME. Collation mismatches (case-sensitive Oracle vs case-insensitive MySQL) frequently trigger duplicate key violations on unique indexes if target tables are not explicitly defined with case-sensitive collations.
• Heterogeneous Metadata Requirement: Replicating between distinct database engines mandates the SOURCEDEFS parameter. The generated definition file acts as a translation layer, allowing the MySQL replicat to parse Oracle-formatted binary trail records accurately.