In the earlier post on GoldenGate Conflict Detection and Resolution, we had looked at an example where a conflict caused by an update happening at the same time for the same record from two different sites was handled using the RESOLVECONFLICT with the UPDATEROWEXISTS clause.
Let us look at some more CDR examples involving DELETE and INSERT operations and how GoldenGate detects and handles those type of conflicts.
Here is the replicat parameter files which exist on Site A and Site B.
The extract, data pump and trail file remains the same as used in the Active-Active configuration used in the earlier examples.
Site A
GGSCI (pdemvrhl061) 16> view params rep1 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, USEMIN (last_dml))), RESOLVECONFLICT (DELETEROWEXISTS, (DEFAULT, OVERWRITE)), RESOLVECONFLICT (DELETEROWMISSING, (DEFAULT, DISCARD));
Site B
GGSCI (pdemvrhl062) 19> view params rep2 REPLICAT rep2 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, USEMIN (last_dml))) , RESOLVECONFLICT (DELETEROWEXISTS, (DEFAULT, OVERWRITE)), RESOLVECONFLICT (DELETEROWMISSING, (DEFAULT, DISCARD));
The DELETEROWMISSING keyword indicates that we use a DISCARD resolution if the row does not exist during a DELETE operation – we discard the trail record.
The DELETEROWEXOSTS keyword means that if a row exists during a DELETE operation, use OVERWRITE to resolve the conflict situation – apply the delete in the trail record.
Let us look at an example:
On both Site A as well as Site B, we issue this DELETE statement at the same time.
delete from inventory
where prod_id=101;
We see that the row with PROD_ID=101 has been deleted from the table in both databases.
SQL> select * from inventory;
PROD_ID PROD_CATEGORY QTY_IN_STOCK LAST_DML
---------- -------------------- ------------ -----------------------------
102 Baseball 105 10-APR-13 04.44.01.500114 PM
If we open the discard file which we have specified, we can see that GoldenGate has recorded that a conflict arose because of the delete statement and it was resolved by discarding the record in the trail file.
Operation failed at seqno 0 rba 4412 Discarding record on CDR DISCARD resolution Record not found Aborted delete from SH.INVENTORY to SH.INVENTORY (target format)... * PROD_ID = 101 PROD_CATEGORY = Football QTY_IN_STOCK = 1 LAST_DML = 2013-04-14 15:28:03.331467000
We can find further evidence that a CDR has taken place by running the REPORTCDR command – we can see a conflict pertaining to DELETEROWMISSING was detected and resolved.
GGSCI (pdemvrhl061) 1> stats replicat rep1 latest reportcdr
Sending STATS request to REPLICAT REP1 ...
Start of Statistics at 2013-04-15 15:09:13.
Replicating from SH.INVENTORY to SH.INVENTORY:
*** Latest statistics since 2013-04-15 15:03:42 ***
Total inserts 0.00
Total updates 0.00
Total deletes 1.00
Total discards 0.00
Total operations 1.00
Total CDR conflicts 1.00
CDR resolutions succeeded 1.00
CDR DELETEROWMISSING conflicts 1.00
Let us now look a conflict involving an INSERT statement being issued from two sites at the same time – in this case both sites are trying to insert a row with the same PROD_ID but with different values for the column QTY_IN_STOCK.
This is the parameter file which will exist on both Site A and Site B and basically comtinuing from the above example involving the DELETE condition CDR.
GGSCI (pdemvrhl061) 1> view params rep1 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, USEMIN (last_dml))), RESOLVECONFLICT (INSERTROWEXISTS, (DEFAULT, USEMAX (last_dml))), RESOLVECONFLICT (DELETEROWEXISTS, (DEFAULT, OVERWRITE)), RESOLVECONFLICT (DELETEROWMISSING, (DEFAULT, DISCARD));
For the INSERTROWEXISTS condition we are going to resolve the conflict using the USEMAX method. This means use the column LAST_DML as the resolution column to resove a conflict.
If a row exists during an INSERT operation, then compare the value in the trail file with that stored in the database. If the value in the trail file is greater then apply the trail file record. If the database value is higher ignore the record.
Let us now insert a record from each site into the INVENTORY table with PROD_ID=103.
On Site A, we have the value 100 for QTY_IN_STOCK and on Site B we are inserting the same record but with a different value of 101 for QTY_IN_STOCK.
SITE A insert into inventory values (103,'Baseball',100,systimestamp); commit; Site B insert into inventory values (103,'Baseball',101,systimestamp); commit;
Site B wins the conflict and the value for QTY_IN_STOCK inserted into the table is 101 and not6 100.
SQL> select * from inventory;
PROD_ID PROD_CATEGORY QTY_IN_STOCK LAST_DML
---------- -------------------- ------------ -------------------------------
103 Baseball 101 15-APR-13 04.03.02.143242 PM
101 Football 1 15-APR-13 03.18.01.000000 PM
102 Baseball 105 10-APR-13 04.44.01.500114 PM
Let us see why this has happened.
This is because the resolution column LAST_DML value (16:03:02.143242000) in Site B is greated than Site A (16:03:01.882289000) and we can see this by examining the trail files on both sites.
Trail file on SITE A (sent from SITE B) Name: SH.INVENTORY After Image: Partition 4 G s 0000 0007 0000 0003 3130 3300 0100 0c00 0000 0842 | ........103........B 6173 6562 616c 6c00 0200 0700 0000 0331 3031 0003 | aseball........101.. 001f 0000 3230 3133 2d30 342d 3135 3a31 363a 3033 | ....2013-04-15:16:03 3a30 322e 3134 3332 3432 3030 30 | :02.143242000 Trail file on Site B (sent from Site A) 2013/04/15 16:03:00.959.629 Insert Len 73 RBA 4978 Name: SH.INVENTORY After Image: Partition 4 G s 0000 0007 0000 0003 3130 3300 0100 0c00 0000 0842 | ........103........B 6173 6562 616c 6c00 0200 0700 0000 0331 3030 0003 | aseball........100.. 001f 0000 3230 3133 2d30 342d 3135 3a31 363a 3033 | ....2013-04-15:16:03 3a30 312e 3838 3232 3839 3030 30 | :01.882289000
The REPORTCDR command also shows that an INSERTROWEXISTS conflict was detected and resolved.
GGSCI (pdemvrhl061) 1> stats replicat rep1 latest reportcdr
Sending STATS request to REPLICAT REP1 ...
Start of Statistics at 2013-04-15 15:20:55.
Replicating from SH.INVENTORY to SH.INVENTORY:
*** Latest statistics since 2013-04-15 15:18:06 ***
Total inserts 1.00
Total updates 0.00
Total deletes 0.00
Total discards 0.00
Total operations 1.00
Total CDR conflicts 1.00
CDR resolutions succeeded 1.00
CDR INSERTROWEXISTS conflicts 1.00
