A focused toolkit of sanitized configurations, monitoring SQL, and operational runbooks for administering Oracle GoldenGate replication — the parameter files, the database-side health checks, and the lag-detection, restart, and recovery procedures a Senior Oracle DBA uses to keep replication streams in sync.
Where most GoldenGate examples stop at "here's a param file," this repository covers the part that actually matters in production: detecting lag before the business reads stale data, diagnosing an abended Extract or Replicat, and recovering a stream to full synchronization without data loss.
A replication stream is invisible when it's healthy and urgent the moment it isn't — lag climbs, a process abends, and source and target quietly drift apart with no error for the applications reading the target. This repo encodes how to operate GoldenGate so that drift is caught early and recovered cleanly: monitor end-to-end lag, attribute it to the right component, and restart or recover from the checkpoint so no transactions are skipped or duplicated.
Every script and runbook is written to be followed under pressure, by any DBA, without improvisation — and every example is fully sanitized.
| Dimension | Coverage |
|---|---|
| Oracle Database | 11g, 12c, 18c, 19c, 21c (source and target) |
| GoldenGate architecture | Classic (GGSCI) and Microservices (Admin Client) — verbs noted where they differ |
| Capture / apply | Integrated Extract (11.2.0.4+) and Integrated Replicat (12.1+); classic notes inline |
| Topology | Unidirectional (source → target) — reporting, synchronization, migration support |
| Focus | Monitoring, lag detection, troubleshooting, restart, and recovery |
All configs, scripts, sample outputs, and runbooks are fully sanitized and
generic. They contain no real hostnames, IPs, service names, schema
contents, trail paths, credentials, or employer/company data. Process names
(EXTSRC, PMPSRC, REPTGT), the database (ORADEMO), demo schemas
(SALES, RPT), and all values are fictional, written to show what an
operation looks like and how to read it.
Credentials use a credential-store alias (USERIDALIAS) in every example —
never a clear-text password. These are reference materials for portfolio and
educational use; test in a non-production environment and apply under your own
change-control process.
| Script | What it answers | Side |
|---|---|---|
gg_heartbeat_lag.sql |
End-to-end lag per path via the automatic heartbeat (12.2+) | Either |
gg_integrated_capture_status.sql |
Integrated Extract / logmining capture health and SCN gap | Source |
gg_integrated_apply_status.sql |
Integrated Replicat reader/servers and apply errors | Target |
gg_long_running_transactions.sql |
Long open transactions pinning Extract's read position | Source |
gg_supplemental_logging.sql |
Supplemental logging + FORCE LOGGING prerequisites | Source |
gg_archivelog_gap.sql |
Whether archived logs Extract still needs are present | Source |
extract_extsrc.prm— integrated Extract (capture)replicat_reptgt.prm— integrated Replicat (apply) with REPERROR handlingmgr.prm— Manager with AUTORESTART, trail purging, and lag alertsGGSCI Command Reference.md— the day-to-day command cheat sheet
| Runbook | Use it to… |
|---|---|
| Lag Detection Procedure | Detect lag by number and attribute it to capture, apply, or path |
| Extract Troubleshooting | Diagnose/recover capture — missing logs, long transactions, prereqs |
| Replicat Troubleshooting | Diagnose/recover apply — collisions, missing rows, throughput, discards |
| Restart Procedures | Start/stop/restart in the right order, checkpoint-safe |
| Recovery Playbook | Work the harder incidents and resync without data loss |
A sanitized, annotated sample run for the key operations is in
sample_outputs/.
SOURCE (ORADEMO_SRC) TARGET (ORADEMO_TGT)
┌───────────────┐ local trail path/pump remote trail ┌───────────────┐
│ Redo / DB │──► EXTSRC ──► ./dirdat/ea ──► PMPSRC ──► ./dirdat/rt ──► REPTGT ──► │ Target DB │
│ (changes) │ (Extract / (capture) (deliver) (apply) │ (in sync) │
└───────────────┘ capture) └───────────────┘
│ │
└────────────── HEARTBEAT (end-to-end lag, seconds) ◄─────────────────────────────────┘
Capture lag, path/network, and apply lag are diagnosed separately — that's why the Lag Detection Procedure attributes lag before anything is changed.
Prerequisites: GoldenGate installed (classic or microservices); a database
account with SELECT_CATALOG_ROLE for the monitoring SQL; a credential-store
alias for any GGSCI/DBLOGIN action. Connect and check state first:
GGSCI> INFO ALL
SQL> @scripts/gg_heartbeat_lag.sql
Typical operational loop (full detail in the runbooks):
INFO ALL/gg_heartbeat_lag.sql→ is anything behind or abended?LAG …/GETLAGand the attribution table → which component?VIEW REPORT <proc>+ discard file → the root cause.- Fix the cause, then
STARTfrom checkpoint → recover safely. STATS/GETLAG+ sync check → confirm in sync before closing.
Configs describe intent; these show the work. Real GGSCI and SQL*Plus sessions
from a sanitized demo environment (ORADEMO), walking one incident from detection
to verified recovery. Every value is fictional — no hostnames, schemas, trail
paths, or company data. The point isn't the commands; it's the judgment: read
the report before restarting, resume from the checkpoint, and confirm in sync,
not just running.
Problem demonstrated. End-to-end lag is climbing and an application is reading stale data. The one-screen process overview shows whether capture, delivery, or apply is the culprit.
What an experienced DBA concludes. Capture (EXTSRC) and the pump are
RUNNING and current; REPTGT (apply) is ABENDED with eight minutes of lag at
checkpoint. So changes are being captured and shipped but nothing is being
applied — the target is drifting. The next move is VIEW REPORT REPTGT to get the
abend reason, not a blind restart.
Troubleshooting takeaway. Read status and lag together across all processes before acting. A healthy Extract with an abended Replicat means the data is safe in the trail and waiting — the fix is on the apply side, and no captured change has been lost.
Problem demonstrated. An Extract has abended. The temptation is to restart it and hope; the report tells you why so you fix the actual cause.
What an experienced DBA concludes. The report is unambiguous: capture needed
archived log sequence 4587, and it was gone (OGG-00446 / ORA-01291: missing logfile). The log was purged — by FRA pressure or RMAN — before capture read it.
The fix is to restore that exact archived log from backup and restart; Extract
then resumes from its checkpoint with nothing skipped.
Troubleshooting takeaway. The process report is the source of truth for any abend. For a missing-logfile abend, restore the specific sequence from RMAN rather than repositioning the Extract — and then lengthen archive retention beyond your worst-case capture lag so it can't recur.
Problem demonstrated. A Replicat has been restarted after an incident. "Is it actually recovering, or just running?" is the question that matters.
What an experienced DBA concludes. Immediately after restart, GETLAG shows
~489 seconds while the Replicat works through the accumulated trail backlog; a few
minutes later it's down to ~3 seconds — normal near-real-time. STATS … LATEST
confirms inserts, updates, and deletes are applying with zero discards. That
combination — falling lag plus rows actually applying cleanly — is a verified
recovery.
Troubleshooting takeaway. A process in RUNNING state is not proof of
recovery. Watch lag trend down to normal and confirm rows are applying without
discards before you consider the incident resolved.
Problem demonstrated. GGSCI lag is per-process; the business cares about end-to-end lag per replication path — the number you alert on and report.
What an experienced DBA concludes. The automatic heartbeat puts it in one place: ~489 seconds of incoming (apply) lag on the source→target path, against ~4 seconds of outgoing (capture) lag. That instantly attributes the problem to the apply side and agrees with the abended/recovering Replicat seen in GGSCI — two independent signals telling the same story.
Troubleshooting takeaway. Enable the automatic heartbeat (ADD HEARTBEATTABLE) and alert on it. A single end-to-end lag-in-seconds metric per
path is far more actionable for monitoring than reading each process by hand.
Problem demonstrated. Extract lag is rising, but the source isn't busy. Junior diagnosis stalls here; this is the check that explains it.
What an experienced DBA concludes. An INACTIVE BATCH_USER transaction has
been open for 3+ hours without committing, and SEND EXTRACT … SHOWTRANS confirms
it's the oldest transaction Extract is still accounting for. Integrated capture
can't fully advance its read position past the oldest open transaction, so a
single forgotten, uncommitted session quietly drives capture lag and slows
restarts.
Troubleshooting takeaway. When capture lag climbs on an idle system, look for
a long-running open transaction before anything else. Confirm with SHOWTRANS,
resolve the session with the application owner, and fix the batch's commit
handling so it can't recur.
All screenshots are fully sanitized and fictional. Process names (
EXTSRC,PMPSRC,REPTGT), the database (ORADEMO), schemas (SALES,RPT), SCNs, sequences, and every value are illustrative demo data — no production, employer, or confidential information is shown. Each capture mirrors the annotated output insample_outputs/, where every example ends with a "Read:" note explaining the finding and the action.
oracle-goldengate-configs/
├── README.md
├── LICENSE
├── .gitignore
├── banner.png
├── config/ # sanitized Extract / Replicat / Manager param files + command reference
├── scripts/ # 6 read-only DB-side GoldenGate monitoring SQL scripts
├── runbooks/ # lag detection, Extract/Replicat troubleshooting, restart, recovery
├── sample_outputs/ # fictional, sanitized GGSCI / SQL example output
└── screenshots/ # sanitized terminal captures — see "Operational Screenshots"
- Resume from the checkpoint. A normal
STARTskips and duplicates nothing; repositioning (BEGIN,EXTRBA,AFTERCSN,HANDLECOLLISIONS) is a deliberate recovery act, never a reflex. - Read the report before restarting. The abend reason and the discard file name the real cause — fix it, don't paper over it.
- Running is not in sync. Validate source↔target agreement before closing any incident.
- Lag is a silent failure. Monitor end-to-end with the heartbeat and alert on it, so drift surfaces before the business reads stale data.
- Protect the inputs capture needs — archived-log retention and supplemental logging — because most Extract incidents trace back to one of them.
- Bidirectional / conflict-resolution (CDR) examples with loop avoidance.
- Microservices Architecture deployment walkthrough (Service Manager, REST).
- Heartbeat dashboard — a lightweight HTML view over
GG_LAG_HISTORY. - Automated monitor wrapper — a shell/SQL job that alerts on lag and abends.
- Initial-load / instantiation runbook (Data Pump +
AFTERCSN) end to end.
Part of a broader Oracle DBA portfolio (github.com/uusen01):
oracle-rman-scripts · oracle-patching-runbooks · oracle-performance-tuning ·
oracle-cdb-pdb-administration · oracle-health-checks · oracle-dba-runbooks.
Released under the MIT License — free to use, adapt, and share with attribution.