GoldenGate Replication Lab: Oracle to Oracle and Oracle to PostgreSQL

I maintained GoldenGate replication across 5 production databases at Kainga Ora. This post explains the pipeline as I actually understand it from running it, not from reading the documentation.

The pipeline: SOURCE to EXTRACT to TRAIL to PUMP to REPLICAT to TARGET

Every GoldenGate setup has the same structure regardless of source and target database types:

SOURCE DB         SOURCE GG SERVER         NETWORK         TARGET GG SERVER    TARGET DB
Oracle (redo) --> EXTRACT process --------> Trail files --> PUMP process -----> REPLICAT --> Oracle or PostgreSQL
                  (reads redo logs)         (local)         (sends over TCP)    (remote trail) (applies SQL)

Each component has one job. If you understand what each one does, you can diagnose any GoldenGate problem.

EXTRACT reads the Oracle redo logs and writes change records to trail files. It does not touch the target at all. It only reads from the source.

Trail files are fixed-size binary files (default 500MB) that store the captured change records. They are the buffer between EXTRACT and REPLICAT. They also give you the ability to replay changes if something goes wrong on the target.

PUMP is an optional but almost always used EXTRACT process that reads the local trail files and sends them to the remote trail location (the target server or AWS). It is called a passive EXTRACT internally. You use PUMP to separate the network transfer from the redo log reading.

REPLICAT reads the remote trail files and applies the changes to the target database as SQL statements. It handles conflict resolution, mapping, and filtering.

Setting up EXTRACT

Log into the GoldenGate command interface (GGSCI) on the source server:

cd /opt/oracle/goldengate
./ggsci

First configure the globals file and database login:

-- In GGSCI
DBLOGIN USERID gg_user PASSWORD your_password

-- Create the EXTRACT
ADD EXTRACT ext_prod, TRANLOG, BEGIN NOW

-- Add local trail files (prefix is 2 chars, max 4GB each file)
ADD EXTTRAIL ./dirdat/lt, EXTRACT ext_prod, MEGABYTES 500

-- Create the parameter file
EDIT PARAMS ext_prod

The EXTRACT parameter file (dirprm/ext_prod.prm):

EXTRACT ext_prod

-- Database connection
USERID gg_user@ORCL, PASSWORD your_password

-- Local trail file output
EXTTRAIL ./dirdat/lt

-- What to replicate
-- Option 1: specific tables
TABLE YOUR_SCHEMA.ACCOUNTS;
TABLE YOUR_SCHEMA.TRANSACTIONS;
TABLE YOUR_SCHEMA.CUSTOMERS;

-- Option 2: all tables in a schema (be careful with this in production)
-- TABLE YOUR_SCHEMA.*;

-- Handle LOBs
FETCHOPTIONS MISSINGROW ALLOW

-- Sequence support
SEQUENCE YOUR_SCHEMA.*;

-- Supplemental logging (if not set at DB level)
LOGALLSUPCOLS
UPDATERECORDFORMAT COMPACT

Start and verify:

-- In GGSCI
START EXTRACT ext_prod
INFO EXTRACT ext_prod
STATUS EXTRACT ext_prod

Setting up PUMP

PUMP runs on the same server as EXTRACT. It reads the local trail and sends to the target:

-- In GGSCI on source server
ADD EXTRACT pump_prod, EXTTRAILSOURCE ./dirdat/lt

-- Remote trail on the target server
ADD RMTTRAIL ./dirdat/rt, EXTRACT pump_prod, MEGABYTES 500

-- Create pump parameter file
EDIT PARAMS pump_prod

PUMP parameter file (dirprm/pump_prod.prm):

EXTRACT pump_prod

PASSTHRU
RMTHOST target-server-ip, MGRPORT 7809
RMTTRAIL ./dirdat/rt

-- Send all tables through
TABLE YOUR_SCHEMA.*;
SEQUENCE YOUR_SCHEMA.*;

The PASSTHRU directive tells PUMP not to do any transformation, just transfer. Without it PUMP would try to interpret the records.

Start PUMP:

START EXTRACT pump_prod
INFO EXTRACT pump_prod

Setting up REPLICAT on the target

For Oracle-to-Oracle replication:

-- In GGSCI on target server
DBLOGIN USERID gg_user PASSWORD your_password

ADD REPLICAT rep_prod, EXTTRAIL ./dirdat/rt, BEGIN NOW

EDIT PARAMS rep_prod

REPLICAT parameter file for Oracle target:

REPLICAT rep_prod

USERID gg_user@TARGET_DB, PASSWORD your_password

-- Apply mode: integrated replicat (recommended for Oracle 12c+)
-- This uses Oracle's internal apply infrastructure
DBOPTIONS INTEGRATEDPARAMS(parallelism 4)

-- Map source schema to target schema (can be different names)
MAP YOUR_SCHEMA.ACCOUNTS,    TARGET YOUR_SCHEMA.ACCOUNTS;
MAP YOUR_SCHEMA.TRANSACTIONS, TARGET YOUR_SCHEMA.TRANSACTIONS;
MAP YOUR_SCHEMA.CUSTOMERS,   TARGET YOUR_SCHEMA.CUSTOMERS;

-- Error handling
REPERROR (DEFAULT, ABEND)   -- stop on errors by default
REPERROR (1403, DISCARD)    -- discard "no data found" errors (harmless)

-- Conflict detection and resolution
HANDLECOLLISIONS  -- handles duplicate key errors during initial load overlap

For Oracle-to-PostgreSQL (heterogeneous replication):

REPLICAT rep_pg

-- PostgreSQL connection via ODBC
TARGETDB DSN=pg_target, USERID pg_user, PASSWORD your_password

-- Type mapping (Oracle to PostgreSQL types differ)
MAP YOUR_SCHEMA.ACCOUNTS, TARGET public.accounts,
  COLMAP (
    id       = id,
    balance  = CAST(balance, NUMERIC(18,2)),
    created  = CAST(created_date, TIMESTAMP)
  );

The monitoring commands you will use every day

Once replication is running these are the commands I ran daily:

# Overall status of all processes
./ggsci
INFO ALL

# Detailed lag information
LAG EXTRACT ext_prod
LAG REPLICAT rep_prod

# Statistics since last start
STATS EXTRACT ext_prod, TOTAL
STATS REPLICAT rep_prod, TOTAL

# Check trail file status
INFO EXTTRAIL *
INFO RMTTRAIL *

# View recent errors
VIEW REPORT ext_prod
VIEW REPORT rep_prod

The output of LAG REPLICAT rep_prod shows three numbers:

LAG : 00:00:02 (HH:MM:SS)   -- how far behind target is from source
TIME SINCE CHKPT : 00:00:01 -- time since last checkpoint was written

In production, lag should be under 30 seconds during business hours. Anything over 5 minutes means something is wrong (usually long-running transactions on the source or a resource constraint on the target).

What breaks in production

Long-running transactions on the source will cause EXTRACT to hold open redo logs back to the start of that transaction. If the transaction runs for 6 hours, EXTRACT cannot advance past that point and your archive logs from 6 hours ago need to still be available. Set ARCHIVEDLOGONLY on EXTRACT to give you warning when this happens.

Trail file disk space — trail files pile up if REPLICAT is falling behind. Monitor the disk on both source and target. We had a situation where a batch job created 40 million inserts in 2 hours and the trail files filled a 200GB disk. Set PURGEMARKER to clean up old trail files automatically.

Sequences — sequences are not transactional in Oracle (intentionally) so they do not appear in redo logs the same way DML does. You need to add them to both EXTRACT and REPLICAT explicitly with the SEQUENCE directive. Missed sequences usually show up as ORA-02287 errors on the target.

DDL replication — GoldenGate can replicate DDL (ALTER TABLE, CREATE INDEX etc.) but it requires extra configuration. In most migrations you freeze DDL during the replication period and handle schema changes manually. If you need DDL replication, add DDL INCLUDE MAPPED to your EXTRACT and REPLICAT parameter files.

Abend recovery — when REPLICAT abends (which it will at some point), do not panic. Check the report file, fix the root cause, then restart with START REPLICAT rep_prod, AFTERCSN csn_number to continue from where it stopped. Never restart with BEGIN NOW on a running replication setup unless you want to lose changes.

Useful monitoring queries

Run these on the source Oracle database to understand replication health:

-- Check GoldenGate heartbeat (if configured)
SELECT * FROM GGS_HEARTBEAT ORDER BY EXTRACT_TIMESTAMP DESC;

-- Check for long-running transactions that might be blocking EXTRACT
SELECT s.sid, s.serial#, s.username,
       ROUND(t.used_ublk * 8192 / 1024 / 1024, 2) AS undo_mb,
       ROUND((SYSDATE - t.start_date) * 24 * 60, 1) AS minutes_active,
       s.sql_id
FROM v$transaction t
JOIN v$session s ON t.ses_addr = s.saddr
WHERE (SYSDATE - t.start_date) * 24 * 60 > 30  -- running more than 30 min
ORDER BY minutes_active DESC;

-- Check archive log availability (EXTRACT needs these)
SELECT name, ROUND(blocks * block_size / 1024 / 1024, 1) AS size_mb,
       completion_time, archived, deleted
FROM v$archived_log
WHERE completion_time > SYSDATE - 1
ORDER BY completion_time DESC;

-- Check supplemental logging status per table
SELECT log_group_type, log_group_name, table_name
FROM all_log_groups
WHERE owner = 'YOUR_SCHEMA'
ORDER BY table_name;

The heartbeat table

Set up a heartbeat table to give you real-time replication lag visibility:

-- Create on source
CREATE TABLE YOUR_SCHEMA.GG_HEARTBEAT (
  server_name  VARCHAR2(100),
  capture_time TIMESTAMP DEFAULT SYSTIMESTAMP,
  send_time    TIMESTAMP,
  apply_time   TIMESTAMP
);

-- Insert/update heartbeat every 30 seconds via a scheduled job
INSERT INTO YOUR_SCHEMA.GG_HEARTBEAT (server_name, capture_time)
VALUES ('source-prod', SYSTIMESTAMP);
COMMIT;

Then add the heartbeat table to your REPLICAT mapping with a column map that stamps the apply time. Now you can query the target to see exactly how many seconds behind replication is at any moment.

---

Part of my AWS Golden Jacket journey. The Oracle to AWS migration post covers how this fits into a full cloud migration.

References

• Oracle GoldenGate documentation
• GoldenGate for Oracle to PostgreSQL
• AWS DMS with Oracle source