Hunting Replication Lag: A Queue-by-Queue Method for Diagnosing Oracle GoldenGate

Key Takeaways

• GoldenGate lag is not one number but the sum of waits in a chain of queues, and the fix depends entirely on which queue is growing.
• Heartbeat tables measure true end-to-end lag at the target, while process-level LAG output only says how stale the record a process is touching is.
• Apply parallelism helps only when the apply stage is the bottleneck and the workload is not serialized by dependencies, barriers or hot rows.
• A missing or unusable index on the target turns replicated updates into scans, and on hot tables extra appliers stop helping once each hot-row chain is saturated.
• Once an upstream bottleneck clears, the backlog moves downstream, so drain time is set by the next-slowest stage rather than by the original fault.

Executive Summary

When replication falls behind, the reflex is to raise Replicat parallelism, which works only when apply is the binding constraint. This article treats an Oracle GoldenGate pipeline as a series of queues, from redo generation through Extract, trail, distribution and Replicat to target commit, and shows how to find the queue that is actually growing. A small model injects three faults: a burst of large transactions, an unusable index on a hot target table, and a network throttle. Adding appliers leaves peak lag unchanged for the barrier and network faults, and on hot rows it stops helping once each per-key chain is saturated. The method: measure every hop, fix the growing queue, and expect the backlog to move downstream.

Introduction

At a hospitality software company, I build GoldenGate Integrated Extract/Replicat and Microservices pipelines for CDB-to-PDB and CDB-to-CDB replication on Oracle 19c, including bi-directional active-active topologies. I tune GoldenGate parameter files to reduce replication lag and troubleshoot trail-file corruption, checkpoint, and network issues. The common reflex to lag is adding appliers; often lag stays put.

"Lag" names a symptom, not a component. By Little's law, the number of items in a stable system equals arrival rate times time spent in it [1], so a lag figure only tells you how much work is queued somewhere. Kingman's heavy-traffic result adds that waiting explodes as a stage nears full utilization [2]. Lag is dominated by whichever stage is closest to saturation; the job is to find it.

Background

Work piles up in six places: source redo generation, Extract capture, trail write, the distribution path from the Distribution Service to the Receiver Service [3], Replicat apply, and target commit. Oracle recommends Parallel Replicat unless a feature requires another mode [4]: mappers read the trail, a master calculates dependencies from primary, unique and foreign key columns, and appliers execute batches of independent transactions [5], [6]. That is why parallelism has a ceiling: transactions touching the same row apply in order, whatever the applicant count.

Process lag is not end-to-end lag

Replicat lag from LAG or SEND ... GETLAG is the processing time minus the record's trail timestamp; INFO is less precise because it uses the last checkpointed record [7], [8]. A Replicat sitting at end-of-file has nothing new to measure, so its figure can look healthy while a congested path holds fresh data upstream.

Heartbeat tables measure the target's real position. ADD HEARTBEAT TABLE creates heartbeat and history tables, the GG_LAG and GG_LAG_HISTORY views, and a job writing a heartbeat every 60 seconds by default [9]. Each heartbeat is stamped by Extract, the routing path and Replicat, so lag decomposes hop by hop; with several pumps in series, though, the single routing timestamp is overwritten and becomes an aggregate [10].

-- End-to-end lag per path, run on the target as the GoldenGate admin user
SELECT remote_database,
       incoming_path,
       incoming_lag,
       incoming_heartbeat_age,
       outgoing_lag
FROM   ggadmin.gg_lag
ORDER  BY incoming_lag DESC;

Read INCOMING_HEARTBEAT_AGE alongside INCOMING_LAG [7]. If the age keeps climbing while lag looks stable, heartbeats have stopped arriving, and the real lag is at least the age.

Lag in active-active topologies

With bi-directional replication and conflict detection and resolution, each direction is its own pipeline, so read OUTGOING_LAG alongside INCOMING_LAG at every site [7]. Lag sets the width of the window in which both sites can update the same row before either sees the other's change. A wider window means more conflicts for the resolution logic to settle. A backlog that moves downstream does not close that window; it shifts it, so conflict volume can stay high through the drain, after the original fault is fixed. Here lag is a correctness signal, not just a freshness metric.

Why the reflex fails

Three common causes sit outside the appliers' reach:

• Large transactions. Parallel Replicat serializes transactions larger than CHUNK_SIZE, unless SPLIT_TRANS_RECS breaks them into pieces [5].
• Dependencies and hot rows. If a fifth of the workload updates a handful of status rows, those transactions form a chain; Oracle's own experiment shows a dependent workload leaving appliers idle [6].
• Missing keys or indexes on the target. Without a primary key, unique key or unique index, Replicat builds its WHERE clause from all columns, essentially a full table scan [11]. Parallel Replicat also requires foreign key columns to be indexed [5].

Architecture and Design

Starting from heartbeat lag and age, this is the map I use:

Implementation

For tables without a usable key, KEYCOLS tells Replicat which columns identify a row, with those columns supplementally logged at the source [11], [20]. The part people skip: KEYCOLS defines identity, not an access path. Without a target index on those columns, the scan remains. When a root-level Integrated Extract captures from a PDB, the source side of each MAP needs a three-part name or SOURCECATALOG, while the target stays two-part because Replicat connects to one PDB [21].

REPLICAT rpar
USERIDALIAS tgt_ogg
MAP_PARALLELISM 3
MIN_APPLY_PARALLELISM 2
MAX_APPLY_PARALLELISM 10
CHUNK_SIZE 50000
SPLIT_TRANS_RECS 10000
MAP pdb_pms.pms.rate_status, TARGET pms.rate_status, KEYCOLS (property_id, rate_code);

Object names are hypothetical [5], [21].

Performance and Scalability

A small discrete-time model (z3_exp.py) makes this concrete; it models the mechanism, not Oracle. Assumptions: 200 five-row transactions per second; capture 1,800 rows/s; network 2,500 rows/s; 400 row-units/s per applier; per-key ordering with a 10,000-transaction lookahead; transactions over 50,000 rows serialized unless split into 10,000-row pieces. Faults run from t=300 s to 900 s: three 100,000-row batch transactions committing at 600 s; an index left unusable by maintenance (or a plan regression) making apply ten times costlier for 20% of transactions, spread over four hot keys; a network throttled to 600 rows/s. A control run keeps the hot keys at normal cost. Lag is the age of the oldest unapplied transaction. Absolute rates are deliberately low; the ratios drive the results: demand to capacity at each stage, and work per second on each hot-key chain.

† Upper bound: the model gives split pieces unique keys.

With large transactions, quadrupling appliers leaves peak lag at 823 s because the barrier runs on one applier while busy time falls to 10%, though 16 appliers do shorten the post-barrier drain (back under 5 s at 1,574 s versus beyond 2,400 s). Splitting cuts peak lag to 178 s, but the model gives pieces unique keys, while Oracle still honors dependencies between them [5], so it overstates that benefit. Capture backlog peaked at 60,000 rows as the redo spike hit Extract first. The same hot keys cause no lag with a healthy index. Without it, 16 appliers beat 4 because aggregate capacity was also short, but 64 match 16 exactly: each hot key's chain needs more than one second of work per second. Under the network throttle, peak lag is identical for 4 and 16 appliers; after recovery, the burst floods a 4-applier Replicat, so more appliers shorten the drain, not the incident.

Security Considerations

Integrated Extract interoperates with TDE [22], but TDE-protected data captured by Extract is written to the trail unencrypted unless trail encryption is enabled; NOENCRYPTTRAIL is the default [23]. Distribution paths can also encrypt trail data in transit [3]; that costs CPU, so capacity tests without it understate production load.

Operational Excellence

Trail-file corruption deserves a runbook. INFO EXTRACT ... SHOWCH displays checkpoints, including the recovery checkpoint at the oldest open transaction [24]. A primary Integrated Extract is repositioned by SCN (ALTER EXTRACT ... SCN) plus a trail rollover, not by EXTSEQNO; EXTSEQNO and EXTRBA reposition trail readers such as pumps and Replicat [24]. In Microservices, the distribution path plays the pump's role and the Distribution Service repositions it [3]. Repositioning skips or replays data, so confirm the target tolerates it first.

Trade-offs and Limitations

The model ignores contention between appliers: sixteen sessions scanning the same table compete for I/O and CPU, so it flatters parallelism. Fixed rates understate the variability effect Kingman describes [2]. Integrated Replicat's serial fallback for DDL and SQLEXEC is not modeled [25]. Heartbeat frequency trades resolution for overhead [9].

Lessons Learned

• Low DB time on the target during a lag window is the fastest way to rule out apply [17].
• Hot-row chains and unusable indexes compound, because the slowest rows are also the most serialized.
• An unusable index after a maintenance window looks like a GoldenGate problem; check index status on replicated targets first.

Best Practices

• Deploy heartbeat tables on every path before go-live and alert on heartbeat age as well as lag.
• Audit target tables for primary keys and indexed KEYCOLS before the initial load, not during an incident.
• Budget Streams pool memory for root-level Extracts and Integrated Replicats when sizing the SGA.
• Benchmark the distribution path with encryption enabled.

Future Outlook

Auto-tuning between MIN_APPLY_PARALLELISM and MAX_APPLY_PARALLELISM [5] chooses the applier count within bounds you set, but it cannot remove barriers, hot-row chains or upstream throttles.

Conclusion

Measure every hop before touching a knob, and act only on the queue that is growing. Fix that queue, then plan for the backlog, and in active-active the conflict window, to move to the next stage. If added appliers leave lag unchanged, treat that as evidence that the constraint is elsewhere.

References

[1]J. D. C. Little, "A proof for the queuing formula: L = λW," Operations Research, vol. 9, no. 3, pp. 383-387, 1961, doi: 10.1287/opre.9.3.383. [Online]. Available: https://doi.org/10.1287/opre.9.3.383

[2]J. F. C. Kingman, "The single server queue in heavy traffic," Mathematical Proceedings of the Cambridge Philosophical Society, vol. 57, no. 4, pp. 902-904, 1961, doi: 10.1017/S0305004100036094. [Online]. Available: https://doi.org/10.1017/S0305004100036094

[3]Oracle, "Add a Distribution Path," Oracle GoldenGate Microservices Architecture Documentation 26ai, Oracle Corporation. [Online]. Available: https://docs.oracle.com/en/database/goldengate/core/26/coredoc/distribute-add-distribution-path.html

[4]Oracle, "Choosing Capture and Apply Modes," Using Oracle GoldenGate with Oracle Database 21c, Oracle Corporation. [Online]. Available: https://docs.oracle.com/en/middleware/goldengate/core/21.3/oracle-db/choosing-capture-and-apply-modes.html

[5]Oracle, "About Parallel Replicat," Oracle GoldenGate Microservices Architecture Documentation 26ai, Oracle Corporation. [Online]. Available: https://docs.oracle.com/en/database/goldengate/core/26/coredoc/replicat-parallel-replicat.html

[6]V. Kuhr, "Parallel Replicat - Parallelism and Dependency Calculation," Oracle Data Integration Blog, Sep. 29, 2023. [Online]. Available: https://blogs.oracle.com/dataintegration/parallel-replicat-parallelism-and-dependency-calculation

[7]Oracle, "Monitor Lag," Oracle GoldenGate Microservices Architecture Documentation 26ai, Oracle Corporation. [Online]. Available: https://docs.oracle.com/en/database/goldengate/core/26/coredoc/monitor-monitor-lag.html

[8]Oracle, "SEND REPLICAT," Command Line Interface Reference for Oracle GoldenGate 19c, Oracle Corporation. [Online]. Available: https://docs.oracle.com/en/middleware/goldengate/core/19.1/gclir/send-replicat.html

[9]Oracle, "ADD HEARTBEATTABLE," Command Line Interface Reference for Oracle GoldenGate 21c, Oracle Corporation. [Online]. Available: https://docs.oracle.com/en/middleware/goldengate/core/21.3/gclir/add-heartbeattable-1.html

[10]Oracle, "Understanding Heartbeat Table End-To-End Replication Flow," Oracle GoldenGate Classic Architecture Documentation 19c, Oracle Corporation. [Online]. Available: https://docs.oracle.com/en/middleware/goldengate/core/19.1/ggcab/understanding-heartbeat-table-end-end-replication-flow.html

[11]Oracle, "Tuning the Performance of Oracle GoldenGate," Administering Oracle GoldenGate 19c, Oracle Corporation. [Online]. Available: https://docs.oracle.com/en/middleware/goldengate/core/19.1/admin/tuning-performance-oracle-goldengate.html

[12]Oracle, "V$GOLDENGATE_CAPTURE," Oracle Database Reference 19c, Oracle Corporation. [Online]. Available: https://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/V-GOLDENGATE_CAPTURE.html

[13]Oracle, "Optional Parameters for Integrated Modes," Using Oracle GoldenGate for Oracle Database 19c, Oracle Corporation. [Online]. Available: https://docs.oracle.com/en/middleware/goldengate/core/19.1/oracle-db/optional-parameters-integrated-modes.html

[14]Oracle, "STREAMS_POOL_SIZE," Oracle AI Database Reference 26ai, Oracle Corporation. [Online]. Available: https://docs.oracle.com/en/database/oracle/oracle-database/26/refrn/STREAMS_POOL_SIZE.html

[15]V. Kuhr, "GoldenGate: How to manage the Streams Pool in the Oracle Database?," Oracle Data Integration Blog, Apr. 29, 2024. [Online]. Available: https://blogs.oracle.com/dataintegration/goldengate-how-to-manage-the-streams-pool-in-the-oracle-database

[16]M. Papio, "Oracle GoldenGate Networking Guidelines: MTU Checks, Bandwidth Benchmarking, BDP Tuning, and SDU Optimization," Oracle Data Integration Blog, Jun. 30, 2025. [Online]. Available: https://blogs.oracle.com/dataintegration/oracle-goldengate-networking-guidelines-mtu-bandwidth-benchmarking-bdp-tuning-and-sdu-configuration

[17]S. Bhattacharya, "Parallel Replicat Performance Tuning in Oracle GoldenGate: A Step-by-Step Guide to Fix Replicat Lag," Oracle Data Integration Blog, Jun. 9, 2025. [Online]. Available: https://blogs.oracle.com/dataintegration/parallel-replicat-performance-tuning-in-oracle-goldengate-a-stepbystep-guide-to-fix-replicat-lag

[18]Oracle, "Analyzing Sampled Data," Oracle Database Performance Tuning Guide 19c, Oracle Corporation. [Online]. Available: https://docs.oracle.com/en/database/oracle/oracle-database/19/tgdba/analyzing-sampled-data.html

[19]Oracle, "Oracle Database Performance Method," Oracle Database 2 Day + Performance Tuning Guide 19c, Oracle Corporation. [Online]. Available: https://docs.oracle.com/en/database/oracle/oracle-database/19/tdppt/oracle-database-performance-method.html

[20]Oracle, "ADD TRANDATA," Command Line Interface Reference for Oracle GoldenGate 19c, Oracle Corporation. [Online]. Available: https://docs.oracle.com/en/middleware/goldengate/core/19.1/gclir/add-trandata.html

[21]Oracle, "Configuring Oracle GoldenGate in a Multitenant Container Database," Using Oracle GoldenGate for Oracle Database 19c, Oracle Corporation. [Online]. Available: https://docs.oracle.com/en/middleware/goldengate/core/19.1/oracle-db/configuring-oracle-goldengate-multitenant-container-database-1.html

[22]Oracle, "About Integrated Extract," Oracle GoldenGate Classic Architecture Documentation 19c, Oracle Corporation. [Online]. Available: https://docs.oracle.com/en/middleware/goldengate/core/19.1/ggcab/integrated-extract.html

[23]Oracle, "ENCRYPTTRAIL | NOENCRYPTTRAIL," Reference for Oracle GoldenGate 23ai, Oracle Corporation. [Online]. Available: https://docs.oracle.com/en/middleware/goldengate/core/23/reference/encrypttrail-noencrypttrail.html

[24]Oracle, "Performing Administrative Operations," Administering Oracle GoldenGate Classic Architecture 21c, Oracle Corporation. [Online]. Available: https://docs.oracle.com/en/middleware/goldengate/core/21.3/admin/performing-administrative-operations.html

[25]Oracle, "How Integrated Replicat Works," Oracle GoldenGate Microservices Architecture Documentation 26ai, Oracle Corporation. [Online]. Available: https://docs.oracle.com/en/database/goldengate/core/26/coredoc/replicat-integrated-replicat.html

About the Author

Zalak Shroff is a database engineer and IT manager with more than ten years of experience running Oracle databases from 10g through 19c, including RAC, Data Guard, ASM, RMAN, and GoldenGate. She currently works on Oracle 19c multitenant platforms on OCI and AWS for hospitality software, where she migrates standalone databases into pluggable databases and builds GoldenGate replication, including bi-directional active-active topologies with conflict detection and resolution. Earlier, in telecom, she automated RMAN backup and recovery, tuned performance with AWR and ASH, and implemented TDE, VPD and role-based access controls for SOX and GDPR compliance. She holds an M.S. in Computer Science and is a named inventor on a patent for a network application that notifies users of an impending coverage lapse.