Goldengate Bidirectional replication with conflict resolution

Scenario Description

In this Scenario we have tried to perform bidirectional replication. The extract process on Site A ,  e_sitea  will extract the data and send it as a remote trail file to Site B. The replicat process on Site B, r_sitea will read from the trail file and replicate the same in  database on Site B.  User tries to update a column on Site A and at the same instance another user tries to update the same column on Site B with a different value , which  will result in a conflict .We use a counter to detect conflicts,for inserts the counter is set to ‘0’ and for updates the counter is incremented by ‘1’  by a trigger ins_upd_ctr. When an update from one site  is propagated to the other  the counter value from the update is compared to the counter value of the existing row and a conflict is detected, and the resulting conflict is written  in an exception table for resolution

  

SETUP DIAGRAM

Note : The letters in brown indicate database schema

Checkpoint table is mandatory for Bidirectional replication .The global parameter file entry needs to be commented so that a checkpoint table can be added manually for both Site A and Site B

Site A

Checkpoint table

edit param ./GLOBALS

--GGSCHEMA source

--CHECKPOINTTABLE source.checkpoint

GGSCI>dblogin userid ggsiteb, password ggs

GGSCI>add checkpointtable chkpointsiteb

Extract  e_sitea

EXTRACT e_sitea

USERID ggsitea, PASSWORD ggs

RMTHOST target, MGRPORT 8809

RMTTRAIL /opt/oracle/gg/rtrails/sa

GETUPDATEBEFORES

TABLE ADMIN.*;

TRANLOGOPTIONS EXCLUDEUSER ggsiteb

GGSCI>add extract e_sitea, tranlog begin now

GGSCI>add RMTTRAIL /opt/oracle/gg/rtrails/sa, extract e_sitea

Replicat  r_siteb

REPLICAT r_siteb

ASSUMETARGETDEFS

USERID ggsiteb, password ggs

DISCARDFILE /opt/oracle/gg/r_siteb.dsc, PURGE, MEGABYTES 10

REPERROR 9999, EXCEPTION

MAP ADMIN.ACCOUNT, TARGET ADMIN.ACCOUNT, &

SQLEXEC (ID CHECK, ON UPDATE, BEFOREFILTER, &

QUERY "SELECT CTR FROM ADMIN.ACCOUNT WHERE ID = :P1", &

PARAMS (P1 = ID)), &

FILTER (ON UPDATE, BEFORE.CTR = CHECK.CTR, &

RAISEERROR 9999);

INSERTALLRECORDS

MAP ADMIN.ACCOUNT, TARGET ADMIN.ACCOUNT_EXPT, EXCEPTIONSONLY, &

COLMAP (USEDEFAULTS, ERRTYPE= "Conflict Detected - Site A prevailed");

GGSCI>add replicat r_siteb, exttrail /opt/oracle/gg/rtrails/sb, checkpointtable chkpointsiteb

Site B

Checkpoint Table

edit param ./GLOBALS

--GGSCHEMA source

--CHECKPOINTTABLE source.checkpoint

GGSCI>dblogin userid ggsiteb, password ggs

GGSCI>add checkpointtable chkpointsitea

Extract e_siteb

EXTRACT e_siteb

USERID ggsiteb, PASSWORD ggs

RMTHOST source, MGRPORT 8809

RMTTRAIL /opt/oracle/gg/rtrails/sb

GETUPDATEBEFORES

TABLE BGADMIN.*;

TRANLOGOPTIONS EXCLUDEUSER ggsitea

GGSCI>add extract e_siteb, tranlog begin now

GGSCI>add RMTTRAIL /opt/oracle/gg/rtrails/sb, extract e_siteb

Replicat r_sitea

REPLICAT r_sitea

ASSUMETARGETDEFS

USERID ggsitea, password ggs

DISCARDFILE /opt/oracle/gg/r_sitea.dsc, PURGE, MEGABYTES 10

REPERROR 9999, EXCEPTION

MAP BGADMIN.ACCOUNT, TARGET BGADMIN.ACCOUNT, &

SQLEXEC (ID CHECK, ON UPDATE, BEFOREFILTER, &

QUERY "SELECT CTR FROM BGADMIN.ACCOUNT WHERE ID = :P1", &

PARAMS (P1 = ID)), &

FILTER (ON UPDATE, BEFORE.CTR = CHECK.CTR, &

RAISEERROR 9999);

INSERTALLRECORDS

MAP BGADMIN.ACCOUNT, TARGET BGADMIN.ACCOUNT_EXPT, EXCEPTIONSONLY, &

COLMAP (USEDEFAULTS, ERRTYPE= "Conflict Detected - Site B prevailed");

GGSCI>add replicat r_sitea,exttrail /opt/oracle/gg/rtrails/sa, checkpointtable chkpointsitea

Trigger ins_upd_ctr

create or replace TRIGGER ins_upd_ctr

  BEFORE INSERT or UPDATE ON account

  FOR EACH ROW

  DECLARE

BEGIN

IF INSERTING THEN

:new.ctr := nvl(:old.ctr,0);

ELSIF UPDATING THEN

    :new.ctr := :old.ctr + 1;

END IF;

END;