Loading....

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:

export ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1
export ORACLE_SID=testdb1
$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.
Please follow and like us:

Last Update: May 4, 2017  

April 12, 2013 34 Gavin Soorma
Total 0 Votes:
0

Tell us how can we improve this post?

+ = Verify Human or Spambot ?

Add A Knowledge Base Question !

You will receive an email when your question will be answered.

+ = Verify Human or Spambot ?

, , , ,

Leave a Comment

Your email address will not be published. Required fields are marked *

*
*

Social media & sharing icons powered by UltimatelySocial
Back To Top

Add A Knowledge Base Question !

You will receive an email when your question will be answered.

+ = Verify Human or Spambot ?