- Active-Active High Availability
- Distributed Data Management
- Optimized Workload and Balanced Use of the Compute Resources
Let's use the following example from the Oracle GoldenGate Administration Guide [1] [2] to explain what CDR is:
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));
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 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. "
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).
- USEMAX and USEMAXEQ
- USEMIN and USEMINEQ
- USEDELTA
- DISCARD
- OVERWRITE
- IGNORE
- 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.
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]
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. (PPT)
Let's look an example processing flow:
- 1: 00:00 pm - The application updates the same row on both DB a and DB b.
- update name=scott at DB a
- update name=adam at DB b
- 1: 00:02 pm - Oracle GoldenGate captured the data from DB a and delivered the data o DB b
- Ca captured data (name=scott) from DB a and delivered the data to DB b via Db
- 1: 00:10 pm - Oracle GoldenGate captured the data from DB b and delivered the data to DB a
- Cb captured data (name=adam) from DB b and delivered the data to DB a via Da
- 1: 00:12 pm - Oracle GoldenGate applied the transactions on both DB a and DB b when the CDR is triggered. Data are in sync after the CDR.
- Db delivered the data (name=scott) to DB b but found out that (name=adam). The CDR is triggered, the (name=scott) is discarded.
- Da delivered data to DB a (name=adam) but found out that (name=scott), The CDR is triggered, the (name=adam) overwrote the existing value.
Of course, the latency will not be this big in your environment. You should typically see the update instantaneously. Here, I use large latency numbers to help you notice that during the latency period, you will have data inconsistency. Therefore, it's important to handle this at the application layer. I have seen approaches like the following used:
- Waiting until passing the latency time to return the transaction result to the user
- Using a load balancer to control the user access to one database per application session.
After you configure the replication correctly, you can enjoy the benefits of CDR and active-active replication.
- Oracle GoldenGate 12.2 Documentation: 10 Configuring Conflict Detection and Resolution
- Oracle GoldenGate 12.2 Reference: 3.171 TABLE | MAP
- Best Practices for Conflict Detection and Resolution in Active-Active Replication Environments Using Oracle GoldenGate, 2012