In the earlier post we looked at setting up a simple Active-Active GoldenGate configuration.
Let us build on that by adding in some Conflict Detection and Resolution (CDR) mechanisms and look at some scenarios where conflicts do occur when a particular record in the table involved in active-active replication is being updated at the same time at two different sites and how the conflict is being resolved.
Note that in GoldenGate active-active replication , a conflict is a mismatch between the before image of a record in the trail and the current data in the target table.
In the first instance we will see a case of GoldenGate Conflict Resolution using the Trusted Site Or Trusted Source method where one site is dedicated as the trusted or master site and in a CDR scenario will always prevail over other sites participating in the Active-Active Replication.
So in continuation of the active-active GoldenGate environment we have set up, on Site A we have an extract process EXT1, Data Pump process DPUMP1 and Replicat process REP1 and on Site B we have a corresponding extract process EXT2, Data Pump process DPUMP2 and Replicat process REP2.
To prevent the capture of SQL that is applied by Replicat to other database in the Active-Active configuration we have to use the TRANLOGOPTIONS EXCLUDEUSER option and specify the database user account we have setup for GoldenGate. So in this case we are specifying that all transactions generated by the user GGATE will be excluded from being captured.
The GoldenGate CDR mechanism depends on comparing the data in the trail of a column which is being used for conflict resolution with what is actually stored in the database.
To enable this, we use the GETBEFORECOLS parameter in the Extract to specify columns for which you want before image to be captured and written to the trail upon an update or delete operation.
For updates, the before image of the specified columns is included in the trail whether or not any given column is modified. The KEYINCLUDING clause specifies capture before image of the primary key and also the specified column or columns.
On the Replicat side of things, these two parameters control and enforce CDR – COMPARECOLS and RESOLVECONFLICT.
The COMPARECOLS parameter specifies the columns that Replicat uses to detect and resolve update or delete conflicts.
ON UPDATE and ON DELETE clause specifies whether the before image of the specified columns should be compared for updates or deletes. You can use ON UPDATE only, ON DELETE only, or both. If using both, we need to specify them within the same COMPARECOLS clause.
Use the RESOLVECONFLICT option of the MAP parameter to specify conflict resolution routines for different operations (INSERTS/DELETES/UPDATES) and conflict types. We can use RESOLVECONFLICT multiple times in a MAP statement to specify different resolutions for different conflict situations.
In this case we specify Site A as the Trusted Site or say Master Site in the two site active-active configuration. In a conflict situation, Site A should always prevail over Site B.
Here are the Extract and Replicat parameter files we have used in this example.
The Data Pump and Trail File setup remains same as per the previous example of setting up Active-Active replication as described in the previous post.
Site A EXTRACT ext1 USERID ggate, PASSWORD ggate EXTTRAIL /u01/app/ggate/dirdat/aa TRANLOGOPTIONS EXCLUDEUSER ggate TABLE sh.inventory, GETBEFORECOLS ( ON UPDATE KEYINCLUDING (prod_category,qty_in_stock, last_dml), ON DELETE KEYINCLUDING (prod_category,qty_in_stock, last_dml)); REPLICAT rep1 ASSUMETARGETDEFS USERID ggate, PASSWORD ggate DISCARDFILE /u01/app/ggate/discard.txt, append, MAP sh.inventory, TARGET sh.inventory, COMPARECOLS (ON UPDATE ALL, ON DELETE ALL), RESOLVECONFLICT (UPDATEROWEXISTS, (DEFAULT, IGNORE )); Site B EXTRACT ext2 USERID ggate, PASSWORD ggate EXTTRAIL /u01/app/ggate/dirdat/ac TRANLOGOPTIONS EXCLUDEUSER ggate TABLE sh.inventory, GETBEFORECOLS ( ON UPDATE KEYINCLUDING (prod_category,qty_in_stock, last_dml), ON DELETE KEYINCLUDING (prod_category,qty_in_stock, last_dml)); REPLICAT rep2 ASSUMETARGETDEFS USERID ggate, PASSWORD ggate DISCARDFILE /u01/app/ggate/discard.txt, append, MAP sh.inventory, TARGET sh.inventory;
So the Replicat process on Site B does not have any RESOLVECONFLICT clause because there is no need to check for any conflict of transactions coming from Site A because Site A is the trusted source and always wins. So in a conflict this transaction would be overwritten by the one which originates from Site A
On Site A however, we have the following RESOLVECONFLICT clause in place:
RESOLVECONFLICT (UPDATEROWEXISTS, (DEFAULT, IGNORE ));
The RESOLVECONFLICT parameter instructs the Replicat process running on Site A, REP1, to IGNORE any transaction (in a conflict situation) which originates from Site B.
We can use a simple script and call the script via cron and schedule the same to run at exactly the same time on both servers to simulate a conflicy situation where two users have issued the same update statement at the same time.
For example, these are the contents of the shell script:
$ORACLE_HOME/bin/sqlplus -s sh/sh << EOF update inventory set QTY_IN_STOCK=11 where prod_id=101; commit; EOF On Site A, we issue the following UPDATE statement:
update inventory set QTY_IN_STOCK=11 where prod_id=101; commit;
On Site B, at the same time the following UPDATE statement was issued:
update inventory set QTY_IN_STOCK=9 where prod_id=101; commit;
But after the GoldenGate CDR handles the transactions, this is end result:
SQL> select * from inventory; PROD_ID PROD_CATEGORY QTY_IN_STOCK LAST_DML ---------- -------------------- ------------ ---------------------------- 102 Baseball 105 10-APR-13 03.58.44.422517 PM 101 Football 11 11-APR-13 02.26.01.720284 PM
So we see that the QTY_IN_STOCK value is 11 and not 9 as the transaction from Site A has prevailed and the Update transaction in Site B has been overwritten by the Update transaction sent from Site A.
We can use the REPORTCDR command to see is CDR has actually taken place and in this case we do see that an UPDATEROWEXISTS conflict was detected and resolved by GoldenGate
GGSCI (pdemvrhl061) 2> stats replicat rep1 latest reportcdr Sending STATS request to REPLICAT REP1 ... Start of Statistics at 2013-04-11 14:28:48. Replicating from SH.INVENTORY to SH.INVENTORY: *** Latest statistics since 2013-04-11 14:26:06 *** Total inserts 0.00 Total updates 1.00 Total deletes 0.00 Total discards 0.00 Total operations 1.00 Total CDR conflicts 1.00 CDR resolutions succeeded 1.00 CDR UPDATEROWEXISTS conflicts 1.00 End of Statistics.