Loading....

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
Please follow and like us:

Last Update: May 1, 2017  

April 16, 2013 147 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 ?