Active-Active Oracle GoldenGate Across Regions: Decide Who Owns the Data Before You Resolve Conflict

Key Takeaways

• Active-active replication is a data-ownership problem first; conflict handlers only clean up what the ownership model failed to prevent.
• Last-writer-wins converge every replica to the same value, but convergence is not correct: concurrent counter updates are silently discarded and both sites agree on the wrong number.
• Delta resolution preserves every committed increment and decrement, yet it cannot protect an invariant such as "never sell more rooms than exist"; that requires a single owner or an escrowed quota.
• Under clock skew, timestamp resolution quietly becomes "highest clock wins," so time synchronization belongs in the replication runbook.
• Divergence tools such as Veridata detect replicas that disagree, not replicas that agree on a lost update, so business-level reconciliation must complement row comparison.

Executive Summary

Bi-directional Oracle GoldenGate lets two regions write the same tables, and its conflict detection and resolution (CDR) features make that look like configuration. It is not. Each table must first be classified by who may write it: single-owner, escrowed, commutative, last-value, or never multi-master. Only then do redo tags, key partitioning, before-image comparison, and USEMAX or USEDELTA fall into place. A small, reproducible simulation of two sites shows that last-writer-wins loses updates while still converging, that clock skew changes which write wins, and that delta resolution keeps every update but still oversells a shared inventory counter. It closes with monitoring practices and cases where active-active should be declined.

Introduction

At a hospitality software company, I designed bi-directional active-active GoldenGate replication with conflict detection, resolution, and transformation logic for global hospitality properties. GoldenGate faithfully replicates whatever the application does: if two regions decrement the same row, it can pick a winner or add the deltas, but it cannot judge whether the business outcome is acceptable. Gray and colleagues showed that update-anywhere replication is unstable at scale: a ten-fold increase in nodes and traffic yields a thousand-fold increase in deadlocks or reconciliations, and that master-copy schemes mitigate this [1]. Oracle's guidance is the practical version: restrict which columns or records each database may modify [2].

Background

Active-active is two unidirectional pipelines, each Extract feeding the peer's Replicat [2], made safe by three mechanisms.

Loop prevention. Replicat tags the redo it generates with DBOPTIONS SETTAG, and the local Extract ignores that tag with TRANLOGOPTIONS EXCLUDETAG, the method Oracle recommends [2], [3].

Conflict detection. An UPDATEROWEXISTS conflict means a compared column in the target row differs from the trail's before-image [4], [5]. Extract captures those columns with GETBEFORECOLS, Replicat chooses which to compare with COMPARECOLS [5], and those columns must be supplementally logged (ADD TRANDATA ... COLS or ALLCOLS) [6]. Beyond updates, INSERTROWEXISTS covers duplicate keys, and UPDATEROWMISSING and DELETEROWMISSING cover rows already deleted at the target [5]; the classic race is a delete at one site overtaking a late update from the other.

Resolution. Manual CDR offers USEMAX, USEMIN, USEDELTA, OVERWRITE, DISCARD and IGNORE per conflict type [5]. Automatic CDR (Oracle Database 12.2 and GoldenGate 12.3 onward) resolves by hidden latest-timestamp columns, with column groups, numeric delta resolution and delete tombstones [7], [8]. It requires integrated Replicat (or parallel Replicat in integrated mode) [9], and the 21c guide adds MAPINVISIBLECOLUMNS to the Replicat parameter file [10].

Dynamo and Bayou push reconciliation to application logic [11], [12]; CRDT counters converge by construction [13].

Problem Analysis

Active-active fails in three distinct ways.

1. Lost updates. Two sites change the same row within the replication window, and one side's intent disappears.
2. Wrong winner. Timestamp resolution assumes clocks agree. Lamport showed that ordering events across processes requires logical clocks, or physical clocks with bounded synchronization error [14]; Spanner's TrueTime exposes exactly that bound and waits it out at commit [15]. Oracle warns that unsynchronized clocks cause data integrity issues [16].
3. Broken invariants. Even when every update survives, a non-negative counter is not invariant-confluent under concurrent decrements, so it needs coordination [17].

Consider a hypothetical room-inventory row per property, replicated between two regions. A small, reproducible model (exp/z1_exp.py) simulates two sites. Assumptions: 40 rooms per run; Poisson requests at 4 per second for 30 seconds, split evenly between sites, a deliberately compressed sell-out scenario; a site books only if its local copy shows rooms left; end-to-end replication lag (capture, network, apply) is exponential with the stated mean, applied in order; site B's clock runs 0.5 seconds ahead in the skew case; single owner routes B's requests to A, modeled as one replication-lag hop, although a real WAN request costs a full round trip. Rows average 500 seeded runs; the model captures the mechanism, not Oracle itself.

All 4,000 runs converged, so a comparison tool would report LWW rows that lost dozens of bookings as healthy; under LWW, lost updates equal oversold rooms because inventory sells out. Skew changed which write survived, not how many were lost. Delta resolution's zero lost updates are held by construction; the real finding is that it still oversold (0.10 and 1.38 rooms per run), because two sites each saw the last room. Single ownership had zero conflicts by construction, since only one site writes, but about half of requests took the extra hop. Conflicts grew with lag, matching Oracle's advice that low latency is itself conflict avoidance [2], and the latency-versus-consistency trade-off PACELC describes [18].

Architecture and Design

Classify every table into one of five ownership classes.

• Single-owner rows. Each row has a home region, for example by property, and only that region writes it; remote requests are routed to the owner. Only this class protects cross-writer invariants such as available inventory, matching Oracle's user-segregation strategy [16].
• Escrowed quantities. Each region holds a quota slice of the inventory, sells locally without coordination, and rebalances slices asynchronously; this is O'Neil's escrow method applied across regions [19], and it coordinates only when a slice runs dry [17].
• Commutative columns. Counters whose correctness is "sum of all changes" with no floor, such as loyalty points earned. Use delta resolution, the CRDT counter idea expressed as a conflict handler [13].
• Last-value columns. Attributes where the latest intent should win, such as a guest's preferred language. Use timestamp resolution, with column groups so unrelated columns do not conflict [7].
• Never multi-master. Globally unique business identifiers, multi-row invariants, and packaged-application data, for which Oracle advises against active-active [2].

Figure 2: Ownership-first design: each table class maps to one replication mechanism, with loop prevention and key discipline applied to all.

Keys need the same discipline: partition sequences (odd and even for two sites, or incremented by the number of sites) or add a location identifier [2]. Every replicated table needs a primary key or a declared KEYCOLS substitute with identical values on all sites, or conflict detection is unreliable [2].

Implementation

For a hypothetical inventory table: tag-based loop prevention, before-images, delta resolution for the counter, and USEMAX on the timestamp otherwise, mirroring the seat-inventory example in Oracle's white paper [16] and the reference syntax [5].

-- Extract (site A)
TRANLOGOPTIONS EXCLUDETAG 0935
TABLE inv.room_inventory, GETBEFORECOLS (ON UPDATE KEYINCLUDING (rooms_left, upd_ts));
 
-- Replicat (site A, applying site B changes)
DBOPTIONS SETTAG 0935
MAP inv.room_inventory, TARGET inv.room_inventory,
COMPARECOLS (ON UPDATE KEYINCLUDING (rooms_left, upd_ts)),
RESOLVECONFLICT (UPDATEROWEXISTS, (delta_rooms, USEDELTA, COLS (rooms_left)),
(DEFAULT, USEMAX (upd_ts))),
RESOLVECONFLICT (INSERTROWEXISTS, (DEFAULT, USEMAX (upd_ts))),
RESOLVECONFLICT (UPDATEROWMISSING, (DEFAULT, DISCARD)),
RESOLVECONFLICT (DELETEROWMISSING, (DEFAULT, DISCARD));

Missing-row conflicts go to the discard file rather than resurrecting deleted rows. Configure resolution identically in both directions, and set updates on every write at both sites, by trigger or application, keeping it fine-grained and monotonic, which the reference says avoids divergence under USEMAX [5].

Transformations need review as inverse pairs. Consider a hypothetical leg that converts property-local times to UTC from A to B but not from B to A: every before-image arriving at A no longer matches its stored value, so every update registers as a conflict. A cheap round-trip test catches this: update a row at A, update another column at B once replicated, and diff both copies; any difference means the pair is asymmetric.

Delta resolution has its own failure mode. If concurrent decrements drive rooms_left below zero, a CHECK (rooms_left >= 0) constraint turns the conflict into a Replicat apply error, handled by REPERROR, whose default is ABEND, or by DISCARD to the discard file [20]. Decide that policy deliberately per table; an unplanned end stops the whole pipeline.

Automatic CDR maintains hidden CDRTS$ROW timestamps and tombstones without application changes [7], but defaults to latest-timestamp. Where clocks cannot be trusted, newer releases add site priority, ACDR SITE_PRIORITY in the Replicat parameter file, which resolves by source rather than time [21].

Performance and Scalability

Before-images cost twice: supplemental logging enlarges redo at the source, and every compared column travels in the trail [6]. KEY is the fastest comparison but cannot detect conflicts on non-key columns; KEYINCLUDING on the resolution columns is the compromise [5]. Applying parallelism matters for correctness, not just throughput: integrated or parallel Replicat applies independent transactions concurrently, shortening lag and therefore the conflict window [9], while hot rows such as a sold-out property's counter still serialize. Routing to an owner trades that window for a WAN round trip on every remote write.

Security Considerations

Redo tags are a control plane: a session writing under the Replicat tag is invisible to Extract, so restrict tagging to GoldenGate administrative accounts. Keep Replicat and Extract credentials in the credential store and reference them by alias, never as clear text in parameter files [22]. Discard files and CDR error tables hold full row images, so place them under the same encryption and retention policy as the source tables.

Operational Excellence

Monitoring has three layers:

1. Lag. Heartbeat tables measure end-to-end lag [23]. Because lag is the conflict window, I treat routine work, troubleshooting trail-file corruption, checkpoint and network issues, and tuning parameter files to reduce lag as correctness work, not just freshness work.
2. Conflict telemetry. Automatic CDR records conflicts in DBA_APPLY_ERROR when record_conflicts is enabled [8].
3. Divergence. Veridata hashes rows and rechecks suspects after a configurable delay so in-flight changes are not reported [24]; DBMS_COMPARISON is a native alternative that warns against converging rows still being updated [25].

Trade-offs and Limitations

Delta resolution needs numeric columns [5], [8]. Latest-timestamp resolution needs synchronized clocks and identical time zones [2], [16]. Single ownership needs failover that transfers ownership. CDR does not support LOBs or user-defined types [4]. The simulation omits partitions, multi-row transactions, and triggers; it illustrates mechanisms, not production behavior.

Lessons Learned

• The ownership spreadsheet, not the parameter file, is the design artifact reviewers should sign off.
• Conflicts on a single-owner table are an ownership-violation alarm, not noise to resolve.
• Every conflict handler needs a matching REPERROR and discard policy.
• Transformations in a bi-directional pair must be reviewed as inverse pairs.

Best Practices

• Rehearse ownership transfer for a regional failover before go-live.
• Supplementally log every compared column before enabling CDR [6].
• Reconcile every counter table against business records, such as recomputing inventory from bookings, alongside row comparison.

Future Outlook

Site priority in automatic CDR [21] moves ownership policy into the database; declaring commutativity and invariants per column is the logical next step [13], [17].

Conclusion

Treat every conflict handler as a documented exception to the ownership plan, and verify with reconciliation rather than trust.

References

[1]J. Gray, P. Helland, P. O'Neil, and D. Shasha, "The dangers of replication and a solution," in Proc. ACM SIGMOD Int. Conf. Management of Data, Montreal, QC, Canada, 1996. [Online]. Available: https://www.microsoft.com/en-us/research/publication/the-dangers-of-replication-and-a-solution/

[2]Oracle, "Configuring Oracle GoldenGate for Active-Active Configuration," in Administering Oracle GoldenGate, Release 19c (19.1.0), Oracle Corporation. [Online]. Available: https://docs.oracle.com/en/middleware/goldengate/core/19.1/admin/configuring-oracle-goldengate-active-active-high-availability.html

[3]Oracle, "Excluding Replicat Transactions in Bidirectional Replication," in Oracle GoldenGate Microservices Documentation, Release 21c (21.3), Oracle Corporation. [Online]. Available: https://docs.oracle.com/en/middleware/goldengate/core/21.3/coredoc/replicat-excluding-replicat-transactions-bidirectional-replication.html

[4]Oracle, "Overview of the Oracle GoldenGate CDR Feature," in Oracle GoldenGate Classic Architecture Documentation, Release 21c (21.3), Oracle Corporation. [Online]. Available: https://docs.oracle.com/en/middleware/goldengate/core/21.3/ggcab/overview-oracle-goldengate-cdr-feature.html

[5]Oracle, "TABLE | MAP," in Oracle GoldenGate Parameters and Functions Reference Guide, Release 26ai, Oracle Corporation. [Online]. Available: https://docs.oracle.com/en/database/goldengate/core/26/reference/table-map.html

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

[7]Oracle, "Automatic Conflict Detection and Resolution," in Oracle GoldenGate Microservices Documentation, Release 21c (21.3), Oracle Corporation. [Online]. Available: https://docs.oracle.com/en/middleware/goldengate/core/21.3/coredoc/administer-automatic-cdr.html

[8]Oracle, "DBMS_GOLDENGATE_ADM," in Oracle Database PL/SQL Packages and Types Reference, 19c, Oracle Corporation. [Online]. Available: https://docs.oracle.com/en/database/oracle/oracle-database/19/arpls/DBMS_GOLDENGATE_ADM.html

[9]Oracle, "Requirements for Automatic Conflict Detection and Resolution," in Oracle GoldenGate Microservices Documentation, Release 19c (19.1.0), Oracle Corporation. [Online]. Available: https://docs.oracle.com/en/middleware/goldengate/core/19.1/coredoc/requirement-automatic-conflict-detection-and-resolution.html

[10]Oracle, "Automatic Conflict Detection and Resolution," in Using Oracle GoldenGate for Oracle Database, Release 21c (21.3), Oracle Corporation. [Online]. Available: https://docs.oracle.com/en/middleware/goldengate/core/21.3/oracle-db/automatic-conflict-detection-and-resolution1.html

[11]G. DeCandia et al., "Dynamo: Amazon's highly available key-value store," in Proc. 21st ACM Symp. Operating Systems Principles (SOSP), 2007. [Online]. Available: https://www.allthingsdistributed.com/files/amazon-dynamo-sosp2007.pdf

[12]D. B. Terry, M. M. Theimer, K. Petersen, A. J. Demers, M. J. Spreitzer, and C. H. Hauser, "Managing update conflicts in Bayou, a weakly connected replicated storage system," in Proc. 15th ACM Symp. Operating Systems Principles (SOSP), 1995. [Online]. Available: https://dl.acm.org/doi/10.1145/224056.224070

[13]M. Shapiro, N. Preguica, C. Baquero, and M. Zawirski, "Conflict-free replicated data types," in Stabilization, Safety, and Security of Distributed Systems (SSS 2011), LNCS vol. 6976. Berlin, Germany: Springer, 2011, pp. 386-400. [Online]. Available: https://link.springer.com/chapter/10.1007/978-3-642-24550-3_29

[14]L. Lamport, "Time, clocks, and the ordering of events in a distributed system," Communications of the ACM, vol. 21, no. 7, pp. 558-565, Jul. 1978. [Online]. Available: https://www.microsoft.com/en-us/research/publication/time-clocks-ordering-events-distributed-system/

[15]J. C. Corbett et al., "Spanner: Google's globally-distributed database," in Proc. 10th USENIX Symp. Operating Systems Design and Implementation (OSDI), 2012. [Online]. Available: https://www.usenix.org/system/files/conference/osdi12/osdi12-final-16.pdf

[16]Oracle, "Best Practices for Conflict Detection and Resolution in Active-Active Replication Environments Using Oracle GoldenGate," Oracle White Paper, Dec. 2012. [Online]. Available: https://www.oracle.com/cn/a/tech/docs/technical-resources/wp-oracle-goldengate-activeactive-final2-1.pdf

[17]P. Bailis, A. Fekete, M. J. Franklin, A. Ghodsi, J. M. Hellerstein, and I. Stoica, "Coordination avoidance in database systems," Proc. VLDB Endowment, vol. 8, no. 3, pp. 185-196, 2015. [Online]. Available: https://www.vldb.org/pvldb/vol8/p185-bailis.pdf

[18]D. J. Abadi, "Consistency tradeoffs in modern distributed database system design: CAP is only part of the story," IEEE Computer, vol. 45, no. 2, pp. 37-42, Feb. 2012. [Online]. Available: https://doi.org/10.1109/MC.2012.33

[19]P. E. O'Neil, "The escrow transactional method," ACM Trans. Database Syst., vol. 11, no. 4, pp. 405-430, 1986. [Online]. Available: https://dl.acm.org/doi/10.1145/7239.7265

[20]Oracle, "REPERROR," in Reference for Oracle GoldenGate, Release 19c (19.1.0), Oracle Corporation. [Online]. Available: https://docs.oracle.com/en/middleware/goldengate/core/19.1/reference/reperror.html

[21]Oracle, "Automatic Conflict Detection and Resolution," in Oracle GoldenGate Microservices Documentation, Release 23ai, Oracle Corporation. [Online]. Available: https://docs.oracle.com/en/middleware/goldengate/core/23/coredoc/administer-automatic-cdr.html

[22]Oracle, "Managing Identities in a Credential Store," in Oracle GoldenGate Documentation, Release 26ai, Oracle Corporation. [Online]. Available: https://docs.oracle.com/en/database/goldengate/core/26/coredoc/secure-managing-identities-credential-store.html

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

[24]Oracle, "How Oracle GoldenGate Veridata Works?," in Using Oracle GoldenGate Veridata, Release 26, Oracle Corporation. [Online]. Available: https://docs.oracle.com/en/database/goldengate/core/26/gvdug/how-oracle-goldengate-veridata-works.html

[25]Oracle, "DBMS_COMPARISON," in Oracle Database PL/SQL Packages and Types Reference, 19c, Oracle Corporation. [Online]. Available: https://docs.oracle.com/en/database/oracle/oracle-database/19/arpls/DBMS_COMPARISON.html

About the Author

Zalak Shrof 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, migrating standalone databases to pluggable databases and building 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.