Loading....

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

• aa – local extract trail which will be written to by ext1
• 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

• ac – local extract trail which will be written to by ext2
• 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

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); 2 3 4 5

Table created.

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

Table altered.

SQL> grant all on inventory to ggate;

Grant succeeded.

CREATE OR REPLACE TRIGGER INVENTORY_CDR_TRG
BEFORE UPDATE
ON SH.INVENTORY
REFERENCING NEW AS New OLD AS Old
FOR EACH ROW
BEGIN
IF SYS_CONTEXT (‘USERENV’, ‘SESSION_USER’) != ‘GGATE’
THEN
:NEW.LAST_DML := SYSTIMESTAMP;
END IF;
END;
/SQL> 2 3 4 5 6 7 8 9 10 11 12

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/ggate/dirdat/aa extract ext1
EXTTRAIL added.

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

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

GGSCI (pdemvrhl061) 14> edit params ext1

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));

GGSCI (pdemvrhl061) 15> edit params dpump1

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

 

On site B add replicat (REP2)

GGSCI (pdemvrhl062) 37> add replicat rep2 exttrail /u01/app/ggate/dirdat/ab
REPLICAT added.

GGSCI (pdemvrhl062) 10> edit params rep2

REPLICAT rep2
ASSUMETARGETDEFS
USERID ggate, PASSWORD ggate
DISCARDFILE /u01/app/ggate/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/ggate/dirdat/ac extract ext2
EXTTRAIL added.

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

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

GGSCI (pdemvrhl062) 31> edit params ext2

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));

GGSCI (pdemvrhl062) 32> edit params dpump2

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

 

On site A add replicat (REP1)

GGSCI (pdemvrhl061) 21> add replicat rep1 exttrail /u01/app/ggate/dirdat/ad
REPLICAT added.

GGSCI (pdemvrhl061) 10> edit params rep1

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

 

On both Site A and Site B, add trandata

GGSCI (pdemvrhl061) 17> dblogin userid ggate password ggate
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 ggate password ggate
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
2 values
3 (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
2 values
3 (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

Please follow and like us:

Last Update: May 4, 2017  

April 12, 2013 204 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 ?