Active-Active replication allows databases actively receive transactions when their data are synchronized by the data replication. The implementation enables you to provide [3]:
- Active-Active High Availability
- Distributed Data Management
- Optimized Workload and Balanced Use of the Compute Resources
Implementing an effective active-active replication configuration is not easy. It requires thorough considerations. One of the considerations is defining the Conflict Detection and Resolution (CDR) rules. The CDR rule decides what to do when active transactions introduce data conflicts. In this blog, let me use some examples to explain the CDR concept, and show you how to configure CDR using Oracle GoldenGate.
Information: Oracle GoldenGate starts to provides built-in conflict detection and resolution routines in 11.2 to support bi-directional and multi-master configurations. (Source: Oracle GoldenGate 11.2 Release Notes)
1. What is Conflict Resolution and Detection (CDR)?
Let's use the following example from the Oracle GoldenGate Administration Guide [1] [2] to explain what CDR is:
MAP fin.src, TARGET fin.tgt,
COMPARECOLS (ON UPDATE ALLEXCLUDING (comment)),
RESOLVECONFLICT ( UPDATEROWEXISTS, (delta_res_method, USEDELTA, COLS (salary, balance)), (max_res_method, USEMAX (last_mod_time), COLS (address, last_mod_time)), (DEFAULT, IGNORE));
We can divide the CDR configuration into two parts: the conflict detection and the conflict resolution.
1.1 Conflict Detection
Oracle GoldenGate uses key columns to identity the records and then detects conflicts by comparing the data before and after the transaction updates. You need the following configurations for the conflict detection:
- Replicating the full before-image of each record: you need to use ADD TRANDATA to include the information in the redo log and use LOGALLSUPCOLS in the extract to include the data in the GoldenGate trail files.
- Use COMPARECOLS to specify the SQL operation (UPDATE | DELETE) and key columns (ALL | KEY | KEYINCLUDING | ALLEXCLUDING) to detect conflicts.
In the example, Oracle GoldenGate checks the conflicts of all the UPDATE operations on all but the comment column on the fin.src table. Why do I get the " WARNING OGG-02180 Table xxx.xxx.xxx will use legacy trail format to support parameter UPDATEBEFORES." message?
In Oracle GoldenGate 12.3, you would use LOGALLSUPCOLS instead of GETBEFOREUPDATES. You might get the following error message for all of your tables: "WARNING OGG-02180 Table xxx.xxx.xxx will use legacy trail format to support parameter UPDATEBEFORES. "
1.2. Conflict Resolution
Oracle GoldenGate provides REOLVECONFLICT to defines the resolutions for each conflict. The following is the list of conflicts:
- INSERTROWEXISTS: Insert to an existing record.
- UPDATEROWMISSING: Update a non-existing record.
- UPDATEROWEXISTS: Update a record with different value (the before image is different).
- DELETEROWMISSING: Delete a non-existing record.
- DELETEROWEXISTS: Delete a record with different value (the before image is different).
The following is the list of resolution functions:
- USEMAX and USEMAXEQ
- USEMIN and USEMINEQ
- USEDELTA
- DISCARD
- OVERWRITE
- IGNORE
You can refer to the Oracle GoldenGate reference guide for the detailed definitions.
Oracle GoldenGate Conflict Resolution Parameters
In the example, when updating a record with different value on the target database:
- the salary and balance columns will be updated with the delta between the before and after image in the trail.
- the address and the last_mod_time columns will be updated with the max value of last_mod_time.
- the updates will be ignored for all other columns.
Best Practices: Avoid Conflict First and Make Resolution Rule as Simple as Possible
Conflict detection and Resolution operates on each rows of your data. Therefore, it's a significant overhead especially when having millions of row to process. Consequently, you would avoid conflict in the first place by controlling transaction operations in your applications such as using application segregation or using different primary keys in different DBs. Then, you would use the simplest resolution rule such as using a transaction timestamp or relying on trusted sources to overwrite inconsistent data. [3]
2. CDR Design Strategy
n summary, there are two typical strategies when implementing CDR:
- Master-Slave with overwrites: there is a master-slave relationship between the databases. You always use the value on the master to overwrite the value on the slave.
- Master-Master with rules: databases are treated equally. You then use the built-in or CDR functions in Oracle GoldenGate to resolve the conflicts. You might need some additional column(s) such as the data committed timestamp to resolve the conflicts.
3. Example using Oracle GoldenGate Hub for Active-Active Replication
Let's explain how CDR works with the following example.
Oracle GoldenGate provides detailed reports of all the CDR operations. You can find them from the GGCSI command, report file or column-conversion functions.