Oracle GoldenGate Architecture Deep Dive: Trails, Processes, and CDC Internals

Oracle GoldenGate is the industry standard for heterogeneous, real-time data replication and change data capture (CDC). Despite being in use for decades, its internal mechanics are frequently misunderstood, leading to poorly tuned deployments that bottleneck at the wrong layer. This article dissects the architecture from the redo log all the way to the target apply.

Architecture Overview

A GoldenGate pipeline has three logical tiers:

1. Capture — the Extract process mines redo/archive logs on the source database.
2. Distribution — the Data Pump (a secondary Extract) reads local trail files and transmits them to a remote trail on the target host.
3. Apply — the Replicat process reads the remote trail and applies changes to the target database.

Each tier operates independently, connected only through trail files — sequential, compressed binary files that serve as a persistent, durable queue between processes.

The Extract Process: Log Mining in Detail

Classic Extract vs. Integrated Extract

There are two Extract modes for Oracle sources:

Integrated Extract registers with the Oracle database as a log-mining server. The database’s logmining infrastructure decodes the redo stream and delivers LCRs (Logical Change Records) directly to the Extract, handling redo multiplexing and internal format changes transparently. This is the preferred mode for current Oracle-to-Oracle replication.

-- Register Integrated Extract with the database
DBLOGIN USERID ggadmin@cdb1 PASSWORD xxxxxxxx
REGISTER EXTRACT EXT1 DATABASE CONTAINER (PDB1)

What Extract Actually Reads

Extract reads redo log files in chronological order, tracking position by SCN (System Change Number) and log sequence number. The extract checkpoint stores the current SCN position so that in the event of a restart, Extract resumes exactly where it stopped — no data loss, no duplicates.

Key parameters to understand:

-- In the Extract parameter file
EXTRACT EXT1
USERID ggadmin@cdb1, PASSWORD xxxxxxxx
EXTTRAIL ./dirdat/lt

-- Source table list
TABLE PDB1.SCHEMA1.ORDERS;
TABLE PDB1.SCHEMA1.ORDER_ITEMS;

-- Include row-level supplemental log data for all mapped columns
-- (also enable at the database or table level)

-- Enable supplemental logging at table level (required)
ALTER TABLE schema1.orders ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;

-- Or at database level (simpler, more overhead)
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

SCN-based Positioning and Restartability

GoldenGate checkpoints are written to the trail file and to the checkpoint table (GGSCI> ADD CHECKPOINTTABLE). In integrated mode, the mining server also maintains state in the DBA_APPLY_PROGRESS internals. Always use a checkpoint table — it ensures Replicat can recover after database restart without manual intervention.

Trail Files: The Durable Queue

Trail files are the backbone of GoldenGate’s decoupled architecture. They are written by Extract (local trail) and read by Pump (which writes to remote trail). Replicat reads from the remote trail.

Trail file naming convention: <prefix>SSSSSS where SSSSSS is a six-digit sequence number.

-- GGSCI commands to inspect trail files
INFO EXTTRAIL ./dirdat/lt
INFO RMTTRAIL /u01/ogg/dirdat/rt

-- View trail file content (useful for debugging)
LOGDUMP

Key Trail File Design Decisions

Trail file size: Default is 500 MB. For very high-throughput systems, increasing this reduces the frequency of file rollovers and the administrative overhead of purging old files.

-- In Extract parameter file
EXTTRAIL ./dirdat/lt, MEGABYTES 1024

Purging old trail files: Never let trail files accumulate unboundedly. Implement PURGEOLDEXTRACTS or use the Manager process:

-- In Manager parameter file (mgr.prm)
PORT 7809
PURGEOLDEXTRACTS ./dirdat/lt, USECHECKPOINTS, MINKEEPDAYS 3

Data Pump: Why It Exists

The Data Pump is a secondary Extract that reads the local trail and forwards records to a remote trail. Its purpose is to decouple network transmission from log capture. If the network is down, Extract continues writing to the local trail. Pump retries the transmission independently — this is the key architectural resilience point.

-- Pump parameter file (pump.prm)
EXTRACT PUMP1
PASSTHRU
RMTHOST target-host, MGRPORT 7809, COMPRESS
RMTTRAIL /u01/ogg/dirdat/rt

SOURCEDEFS ./dirsql/source_defs.sql  -- only if schemas differ between source/target

TABLE PDB1.SCHEMA1.ORDERS;
TABLE PDB1.SCHEMA1.ORDER_ITEMS;

PASSTHRU mode skips column mapping, allowing Pump to forward data without interpreting it — maximising throughput for the pump stage.

Replicat: Apply Modes

Replicat reads the remote trail and applies changes to the target. Three apply modes exist:

Integrated Replicat uses the Oracle database’s inbound server (an internal apply coordinator) to parallelise apply while preserving dependency ordering. This is the recommended mode for Oracle-to-Oracle replication at scale.

-- Add Integrated Replicat
ADD REPLICAT REP1, INTEGRATED, EXTTRAIL /u01/ogg/dirdat/rt, CHECKPOINTTABLE ggadmin.checkpoints

-- Replicat parameter file (rep1.prm)
REPLICAT REP1
USERID ggadmin@target_pdb, PASSWORD xxxxxxxx
ASSUMETARGETDEFS   -- only if source and target schemas match exactly

MAP PDB1.SCHEMA1.ORDERS, TARGET SCHEMA1.ORDERS;
MAP PDB1.SCHEMA1.ORDER_ITEMS, TARGET SCHEMA1.ORDER_ITEMS;

Monitoring the Pipeline

Lag: The Critical KPI

GoldenGate lag is the time difference between when a transaction was committed at the source and when it has been applied at the target. Two lag values matter:

• Extract lag: Time between a redo record being written and Extract processing it.
• Replicat lag: Time between Extract writing to the trail and Replicat applying it.

GGSCI> INFO ALL
GGSCI> LAG EXTRACT EXT1
GGSCI> LAG REPLICAT REP1

For operational alerting, query the GoldenGate monitoring views (available in 21c+):

SELECT group_name, lag_at_chkpt, chkpt_updated
FROM GG$EXTRACT_STATS
ORDER BY group_name;

Process-level diagnostics

GGSCI> STATS EXTRACT EXT1 TOTAL
GGSCI> STATS REPLICAT REP1 TOTAL
GGSCI> SEND REPLICAT REP1, REPORT

Common Pitfalls for Architects

• Missing supplemental logging is the most common cause of Extract abend. Validate with DBA_LOG_GROUPS that all mapped tables have appropriate supplemental log groups.
• Checkpoint table omission makes Replicat position recovery unreliable after crashes.
• Trail file accumulation on disk fills up the file system and stops Extract. Always configure PURGEOLDEXTRACTS.
• DDL replication requires additional configuration (DDL INCLUDE MAPPED) and careful testing; schema changes that are not replicated first will cause Replicat to abend on subsequent DML.
• Long-running transactions hold Extract back, increasing lag. Monitor V$TRANSACTION on the source for transactions open longer than expected.