Oracle GoldenGate
Study Notes — Interview Quick Reference
Societe Generale · Consultant Technical Lead · Oracle DBA
? Real-time data replication & integration platform
Oracle GoldenGate captures and replicates transactional data changes (DML + DDL) between databases in real time
using log-based change data capture (CDC). Unlike Data Guard, it works across different database versions, platforms,
and even heterogeneous targets (Oracle → PostgreSQL, Kafka, BigQuery).
✓ Key differentiator: GoldenGate reads the redo/archive logs directly — no triggers, no stamps on source tables.
Near-zero overhead on source DB.
Extract → Trail Files → Data Pump → Replicat
Process Flow
The GoldenGate pipeline has distinct processes on source and target sides:
Process Side Role
Manager Both Port manager. Starts/stops/monitors all GGS processes. Manages trail aging.
Extract Source Reads redo/archive logs. Captures DML & DDL. Writes to local Trail files.
Data Pump Source Secondary Extract. Reads local trail, ships to remote trail. Adds resilience.
Collector Target Receives trail data from Data Pump over TCP. Writes remote trail files.
Replicat Target Reads remote trail. Applies SQL to target DB. Classic or Integrated mode.
Trail Files Both Sequential flat files storing captured change records. Ext: .aa, .ab... or custom prefix.
Integrated vs Classic Extract
Attribute Integrated Extract (12c+) Classic Extract
Uses Oracle LogMiner server inside DB
How it works kernel Reads redo logs directly via OS
RAC support Native — follows redo across all instances Requires additional config
PDB support Full CDB/PDB awareness Limited
DDL handling Robust, kernel-level Can miss complex DDL
Performance Slightly more DB overhead Faster for simple tables
Recommended YES — for all new deployments Legacy only
d
Trail Files
• Binary sequential files written by Extract, read by Data Pump / Replicat.
• Default location: ./dirdat/ — prefix 2 chars e.g. et, rt.
• Each record has: table name, operation type, before/after column images, SCN/CSN, timestamp.
• Size managed by Manager: PURGEOLDEXTRACTS ./dirdat/*, USECHECKPOINTS.
• Trail aging: never purge until all downstream processes have checkpointed past the record.
Checkpoints
• Record of the last position (RBA — Relative Byte Address) successfully processed.
• Stored in checkpoint files and in the GGS checkpoint table on the target DB.
• If Replicat crashes, it restarts from its last checkpoint — no data loss, possible duplicate (handled by
HANDLECOLLISIONS).
• View: GGSCI> info replicat REPNAME, detail — shows current RBA and lag.
CSN / SCN — Commit Sequence Number
• Each captured transaction is tagged with a CSN (Oracle: System Change Number).
• Enables point-in-time restart: {COD('ADD EXTRACT ext1, TRANLOG, BEGIN SCN 99999999')}.
• Critical for initial load + ongoing replication setup (instantiation SCN method).
Command Purpose
info all Show status of all processes
info extract EXT1, detail Detailed Extract status + RBA + lag
info replicat REP1, detail Detailed Replicat status + checkpoint
view report EXT1 View process report log (errors here)
stats replicat REP1, latest DML counts & throughput since last reset
send extract EXT1, status Runtime status without stopping
start / stop extract EXT1 Start or stop Extract
start / stop replicat REP1 Start or stop Replicat
add extract EXT1, integrated tranlog, begin now Add Integrated Extract
add exttrail ./dirdat/et, extract EXT1 Add local trail for Extract
add replicat REP1, exttrail ./dirdat/rt Add Replicat with trail path
delete extract EXT1 ! Delete extract (! skips confirm)
lag extract EXT1 Show current extract lag
lag replicat REP1 Show current replicat lag
on Errors
■ Always check the report file first: GGSCI> view report The error code and context will be in the last 100 lines.
OGG-01224 Missing archived log: Archive purged before Extract read it. Restore archive log or resync
from SCN. Prevent: set MINARCHIVERETENTION on DB or coordinate purge policy with GGS
checkpoint.
OGG-01403 Row not found on target (Replicat): Target data diverged from source. Short term: add
HANDLECOLLISIONS to Replicat param file. Long term: resync the table using initial load and
restart from correct SCN.
OGG-00446 Could not find archived log: Similar to 01224. Also check supplemental logging is enabled:
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
OGG-01028 Replicat abend — duplicate key: HANDLECOLLISIONS or REPERROR (DEFAULT,
DISCARD) to skip and log. Investigate root cause — could be bidirectional conflict.
Extract lag Replicat lag growing: Extract: redo I/O bottleneck, check archive log generation rate, tune
growing FETCHOPTIONS. Replicat: switch to Parallel/Integrated Replicat for parallelism. Check target
DB I/O.
Method 1: SCN-based (recommended for large/busy tables)
1. Note current SCN: SELECT CURRENT_SCN FROM V$DATABASE;
2. Data Pump export at that SCN: expdp ... FLASHBACK_SCN=
3. Import to target DB
4. Start Extract from that SCN: ADD EXTRACT ext1, TRANLOG, BEGIN SCN
5. Start Replicat — GoldenGate auto-applies the delta (changes since SCN)
6. Once lag = 0, application cutover with minimal downtime
Method 2: Direct Load (SPECIALRUN — small tables)
-- Source Extract param (SPECIALRUN mode):
EXTRACT initload
SOURCEISTABLE
EXTFILE ./dirdat/il
TABLE [Link];
-- Target Replicat param:
REPLICAT initload
SPECIALRUN
EXTFILE ./dirdat/il
MAP [Link], TARGET [Link];
— Decision Matrix
Scenario Choose
DR with near-zero data loss, same Oracle version/platform Data Guard
Zero-downtime DB migration or version upgrade GoldenGate
Active-active bidirectional replication GoldenGate
Replicate Oracle → PostgreSQL / Kafka / BigQuery GoldenGate
Selective table/schema replication (subset) GoldenGate
Automated DR failover with broker (FSFO) Data Guard
Offload reporting reads from primary Active Data Guard
Real-time analytics feed from OLTP GoldenGate → Kafka
★ At Societe Generale (financial sector): Data Guard for core DR (Maximum Availability mode), GoldenGate for
zero-downtime upgrades and real-time analytics / reporting feeds. Both likely coexist in their architecture.
Must-Know
GoldenGate needs supplemental logging enabled on the source DB so that all column values (not just changed ones) are
written to the redo log.
-- Minimum supplemental logging (DB level):
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
-- All-column logging for specific table (required if no PK):
ALTER TABLE [Link] ADD SUPPLEMENTAL LOG DATA ALL COLUMNS;
-- Verify:
SELECT supplemental_log_data_min, supplemental_log_data_pk
FROM V$DATABASE;
! Common gotcha: supplemental logging disabled after a DB restore or recreation. Always verify after any DB refresh
— Extract will silently miss column values.
Conflict Resolution
Bidirectional (active-active) replication lets both sites accept writes. Must prevent loopback (Site A replicates to Site B, B
replicates back to A infinitely).
Loop prevention
-- In Replicat param file:
TRANLOGOPTIONS EXCLUDEUSER ggsuser -- ignore changes made by Replicat itself
Conflict detection & resolution
• CDCR: Conflict Detection and Conflict Resolution in GGS param file.
• RESOLVECONFLICT clause: define resolution strategy per operation — e.g. USEMAX (latest timestamp wins),
USEDELTA (sum deltas), DISCARD (log and skip).
• INSERTMISSINGUPDATES: converts UPDATE to INSERT if row missing on target.
• Design schemas to minimise conflict surface — sequence-based PKs from different ranges per site.
ers
Q: What does the Manager process do?
A: Starts/monitors/stops all GGS processes, manages trail file aging, listens on a port (default 7809).
Q: What is a trail file?
A: Binary sequential flat file storing captured change records. Written by Extract, read by Replicat. Each record has
SCN, table, operation, column images.
Q: How does GGS achieve near-zero impact on source?
A: Log-based CDC — reads redo/archive logs, not the tables. No triggers, no additional columns on source.
Q: Integrated vs Classic Extract — which to use?
A: Integrated (12c+) — uses LogMiner server, handles RAC/PDB natively, more robust. Classic only for legacy.
Q: How do you restart after Replicat abend?
A: Fix the cause, then: GGSCI> start replicat REP1 — it resumes from last checkpoint automatically.
Q: How do you add a new table to running replication?
A: Add to Extract/Replicat param files, perform initial load for the table at a known SCN, then GGS picks up ongoing
changes.
Q: What is HANDLECOLLISIONS?
A: Replicat parameter to gracefully handle INSERT when row exists (skip) or UPDATE/DELETE when row missing
(skip). Used during initial sync window only — remove after sync complete.
Q: GoldenGate vs Streams vs Logical Standby?
A: GGS: heterogeneous, bidirectional, fine-grained. Streams: deprecated since 12c. Logical Standby: SQL Apply,
limited data types, same platform only.
Good luck tomorrow! Focus on architecture storytelling, troubleshooting methodology, and always connect answers to financial
sector context (RTO/RPO, GDPR, zero data loss). — Oracle DBA Interview Prep