Active-Active Replication with Conflict Detection and Resolution Part 1

Let us look at a simple example to illustrate GoldenGate’s Active-Active Replication with an introduction to Conflict Detection and Resolution.

Let’s call the two sites we are going to use for Active-Active replication as Site A and Site B.

On Site A we will have the following groups created

• Extract – ext1
• Data Pump – dpump1
• Replicat – rep1

On Site B we will have the following groups created

• Extract – ext2
• Data Pump – dpump2
• Replicat – rep2

On Site A we have the following trails set up

Extract/Dump - lt    • aa – local extract trail which will be written to by ext1
RMttrail - rt             • ab – remote trail which will be processed by data pump extract group dpump1. This will be shipped over the network to Site B 

On Site B we have the following trails set up

Extract/Dump - ac     • ac – local extract trail which will be written to by ext2
RMttrail – ad             • ad – remote trail which will be processed by data pump extract group dpump2. This will be shipped over the network to Site A

Database setup

SQL> Alter user sh identified by sh account unlock;

User altered.

SQL> Conn sh/sh

Connected.

Create the following objects on both databases (Site A and Site B)

SQL> create table inventory
(prod_id number,
prod_category varchar2(20),
qty_in_stock number,
last_dml timestamp default systimestamp);

Table created.

SQL> alter table inventory add constraint pk_inventory primary key (prod_id) ;

Table altered.

SQL> grant all on inventory to GGS_ADMIN;

Grant succeeded.

CREATE OR REPLACE TRIGGER INVENTORY_CDR_TRG

BEFORE UPDATE

ON INVENTORY

REFERENCING NEW AS New OLD AS Old

FOR EACH ROW

BEGIN

IF SYS_CONTEXT ('USERENV','SESSION_USER') != 'GGS_ADMIN'

THEN

:NEW.LAST_DML := SYSTIMESTAMP;

END IF;

END;

/

Trigger created.

Create the extract (EXT1) and data pump (DPUMP1) on Site A

GGSCI (pdemvrhl061) 14> add extract ext1 tranlog begin now
EXTRACT added.

GGSCI (pdemvrhl061) 4> add exttrail /u01/app/GGS_ADMIN/dirdat/aa extract ext1
EXTTRAIL added.

GGSCI (pdemvrhl061) 16> add extract dpump1 exttrailsource /u01/app/GGS_ADMIN/dirdat/aa
EXTRACT added.

GGSCI (pdemvrhl061) 17> add rmttrail /u01/app/GGS_ADMIN/dirdat/ab extract dpump1
RMTTRAIL added.

GGSCI (pdemvrhl061) 14> edit params ext1

EXTRACT ext1
USERID GGS_ADMIN, PASSWORD GGS_ADMIN
EXTTRAIL /u01/app/GGS_ADMIN/dirdat/aa
TRANLOGOPTIONS EXCLUDEUSER GGS_ADMIN
TABLE sh.inventory,
GETBEFORECOLS (
ON UPDATE KEYINCLUDING (prod_category,qty_in_stock, last_dml),                                   # GETBEFORECOLS ( ON UPDATE ALL, ON DELETE ALL )
ON DELETE KEYINCLUDING (prod_category,qty_in_stock, last_dml));

GGSCI (pdemvrhl061) 15> edit params dpump1

EXTRACT dpump1
USERID GGS_ADMIN, PASSWORD GGS_ADMIN
RMTHOST 10.32.206.62, MGRPORT 7809, TCPBUFSIZE 100000
RMTTRAIL /u01/app/GGS_ADMIN/dirdat/ab
PASSTHRU
TABLE sh.inventory;

On site B add replicat (REP1)

GGSCI (pdemvrhl061) 10> edit params ./GLOBAL

            GGSCHEMA ggate

            CHECKPOINTTABLE ggs_admin.checkpoint

GGSCI (pdemvrhl061) 20> add checkpointtable ggs_admin.checkpoint

GGSCI (pdemvrhl061) 21> ADD REPLICAT rep1, EXTTRAIL /u01/app/gg/dirdat/ab, checkpointtable ggs_admin.checkpoint

REPLICAT added.

GGSCI (pdemvrhl062) 10> edit params rep1

REPLICAT rep1
ASSUMETARGETDEFS
USERID GGS_ADMIN, PASSWORD GGS_ADMIN
DISCARDFILE /u01/app/GGS_ADMIN/discard.txt, append,
MAP sh.inventory, TARGET sh.inventory;

Create the extract (EXT2) and data pump (DPUMP2) on Site B

GGSCI (pdemvrhl062) 3> add extract ext2 tranlog begin now
EXTRACT added.

GGSCI (pdemvrhl062) 4> add exttrail /u01/app/GGS_ADMIN/dirdat/ac extract ext2
EXTTRAIL added.

GGSCI (pdemvrhl062) 5> add extract dpump2 exttrailsource /u01/app/GGS_ADMIN/dirdat/ac
EXTRACT added.

GGSCI (pdemvrhl062) 6> add rmttrail /u01/app/GGS_ADMIN/dirdat/ad extract dpump2
RMTTRAIL added.

GGSCI (pdemvrhl062) 31> edit params ext2

EXTRACT ext2
USERID GGS_ADMIN, PASSWORD GGS_ADMIN
EXTTRAIL /u01/app/GGS_ADMIN/dirdat/ac
TRANLOGOPTIONS EXCLUDEUSER GGS_ADMIN
TABLE sh.inventory,
GETBEFORECOLS (
ON UPDATE KEYINCLUDING (prod_category,qty_in_stock, last_dml),
ON DELETE KEYINCLUDING (prod_category,qty_in_stock, last_dml));

GGSCI (pdemvrhl062) 32> edit params dpump2

EXTRACT dpump2
USERID GGS_ADMIN, PASSWORD GGS_ADMIN
RMTHOST 10.32.206.61, MGRPORT 7809, TCPBUFSIZE 100000
RMTTRAIL /u01/app/GGS_ADMIN/dirdat/ad
PASSTHRU
TABLE sh.inventory;

On site A add replicat (REP2)

GGSCI (pdemvrhl061) 10> edit params ./GLOBAL

            GGSCHEMA ggate

            CHECKPOINTTABLE ggs_admin.checkpoint

GGSCI (pdemvrhl061) 20> add checkpointtable ggs_admin.checkpoint

GGSCI (pdemvrhl061) 21> ADD REPLICAT rep2, EXTTRAIL /u01/app/gg/dirdat/ad, checkpointtable ggs_admin.checkpoint

REPLICAT added.

GGSCI (pdemvrhl061) 10> edit params rep2

REPLICAT rep2

ASSUMETARGETDEFS

USERID ggs_admin, PASSWORD ggs_admin

DISCARDFILE /u01/app/gg/discard.txt, append,

MAP sh.inventory, TARGET sh.inventory; 

On both Site A and Site B, add trandata

GGSCI (pdemvrhl061) 17> dblogin userid GGS_ADMIN password GGS_ADMIN
Successfully logged into database.

GGSCI (pdemvrhl061) 12> add trandata sh.inventory cols (prod_category,qty_in_stock, last_dml)

Logging of supplemental redo data enabled for table SH.INVENTORY.

GGSCI (pdemvrhl061) 13> info trandata sh.inventory

Logging of supplemental redo log data is enabled for table SH.INVENTORY.

Columns supplementally logged for table SH.INVENTORY: PROD_ID, PROD_CATEGORY, QTY_IN_STOCK, LAST_DML.

GGSCI (pdemvrhl062) 18> dblogin userid GGS_ADMIN password GGS_ADMIN
Successfully logged into database.

GGSCI (pdemvrhl062) 14> add trandata sh.inventory cols (prod_category,qty_in_stock, last_dml)

Logging of supplemental redo data enabled for table SH.INVENTORY.

GGSCI (pdemvrhl062) 15> info trandata sh.inventory

Logging of supplemental redo log data is enabled for table SH.INVENTORY.

Columns supplementally logged for table SH.INVENTORY: PROD_ID, PROD_CATEGORY, QTY_IN_STOCK, LAST_DML.

Start the Extract and Data Pump process on Site A

GGSCI (pdemvrhl061) 31> start extract ext1

Sending START request to MANAGER …
EXTRACT EXT1 starting

GGSCI (pdemvrhl061) 23> start extract dpump1

Sending START request to MANAGER …
EXTRACT DPUMP1 starting

GGSCI (pdemvrhl061) 32> info extract ext1

EXTRACT EXT1 Last Started 2013-03-22 17:12 Status RUNNING
Checkpoint Lag 00:00:00 (updated 00:00:03 ago)
Log Read Checkpoint Oracle Redo Logs
2013-03-22 17:12:14 Seqno 250, RBA 30170624
SCN 0.6827610 (6827610)

GGSCI (pdemvrhl061) 34> info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING
EXTRACT RUNNING DPUMP1 00:00:00 00:00:07
EXTRACT RUNNING EXT1 00:00:00 00:00:03

Start the Extract and Data Pump process on Site B

GGSCI (pdemvrhl062) 22> start extract ext2

Sending START request to MANAGER …
EXTRACT EXT2 starting

GGSCI (pdemvrhl062) 23> start extract dpump2

Sending START request to MANAGER …
EXTRACT DPUMP2 starting

GGSCI (pdemvrhl062) 24> info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING
EXTRACT RUNNING DPUMP2 00:00:00 00:26:01
EXTRACT RUNNING EXT2 00:00:00 00:00:09

On Site A start the Replicat process REP1

GGSCI (pdemvrhl061) 38> start replicat rep1

Sending START request to MANAGER …
REPLICAT REP1 starting

GGSCI (pdemvrhl061) 39> status replicat rep1
REPLICAT REP1: RUNNING

On Site B start the Replicat process REP2

GGSCI (pdemvrhl062) 26> start replicat rep2

Sending START request to MANAGER …
REPLICAT REP2 starting

GGSCI (pdemvrhl062) 27> status replicat rep2
REPLICAT REP2: RUNNING

INSERT a row from Site A

SQL> select name from v$database;

NAME
———
TESTDB1

SQL> insert into inventory values (100,’TV’,100,sysdate);

1 row created.

SQL> commit;

Commit complete.

Check if row is replicated on Site B

SQL> select name from v$database;

NAME
———
TESTDB2

SQL> select * from inventory;

PROD_ID PROD_CATEGORY QTY_IN_STOCK LAST_DML
———- ——————– ———— ———
100 TV 100 22-MAR-13

From Site B now INSERT another record

SQL> insert into inventory values (101,’DVD’,10,sysdate);

1 row created.

SQL> commit;

Commit complete.

From Site A check if the replication has taken place

SQL> select * from inventory;

PROD_ID PROD_CATEGORY QTY_IN_STOCK LAST_DML
———- ——————– ———— ———
100 TV 100 22-MAR-13
101 DVD 10 22-MAR-13

Note: Simultaneously transaction different session so which is one to perform 1^st DML then commit that’s write on disk.