Oracle Golden Gate Bidirectional Replication

Golden gate bidirectional replication is two-way unidirectional replication. Let us set up bidirectional replication for a single table from source to target.

Prerequisites
Create Sample Table
Setup PROD to DEV Replication
Setup DEV to PROD Replication
Test Bidirectional Replication

Make sure supplemental logging is enabled on both source and target databases

Create Sample Table

Let us create a new table in fox user on the source

On proddb:
==========
Conn fox/fox
CREATE TABLE bidir
(
pname VARCHAR2(32) PRIMARY KEY,
pemail VARCHAR2(64),
paddr VARCHAR2(128)
);

We will create the same table on target with a different name

On devdb:
=========
Conn tom/tom
CREATE TABLE bidir_d
(
pname VARCHAR2(32) PRIMARY KEY,
pemail VARCHAR2(64),
paddr VARCHAR2(128)
);

On the source, add supplemental logging for the source table

On proddb:
==========
GGSCI (ggprod) 2> add trandata fox.bidir

On target also, add supplemental logging for the target table

On proddb:
==========
GGSCI (ggprod) 2> add trandata tom.bidir_d

Setup PROD to DEV Replication

First, we will be configuring uni-directional replication from PROD to DEV.

Add extract on proddb with below details

On proddb:
==========
GGSCI> ADD EXTRACT foxex, INTEGRATED TRANLOG, BEGIN NOW
GGSCI> register extract foxex database  only needed in integrated capture mode
GGSCI> add exttrail /u01/app/oracle/product/gg/dirdat/bi_dir/lp, extract foxex
GGSCI> edit param foxex
EXTRACT foxex
USERID ogg, PASSWORD ogg
EXTTRAIL /u01/app/oracle/product/gg/dirdat/bi_dir/lp
TRANLOGOPTIONS EXCLUDEUSER OGG
TABLE FOX.BIDIR;

Lp -> local trail file on prod

Add data pump on proddb

On proddb:
==========
GGSCI> Add extract foxdp, EXTTRAILSOURCE /u01/app/oracle/product/gg/dirdat/bi_dir/lp
GGSCI> Add rmttrail /u01/app/oracle/product/gg/dirdat/bi_dir/rd, extract foxdp
GGSCI> edit param foxdp
EXTRACT foxdp
USERID ogg, PASSWORD ogg
RMTHOST ggdev, MGRPORT 7809
RMTTRAIL /u01/app/oracle/product/gg/dirdat/bi_dir/rd
TABLE FOX.BIDIR;

Add replicat on target devdb

On GGDEV:
=========
GGSCI> dblogin userid ogg, password ogg
GGSCI> add replicat tomrep, integrated exttrail /u01/app/oracle/product/gg/dirdat/bi_
dir/rd
GGSCI> edit param tomrep
REPLICAT tomrep
USERID ogg, PASSWORD ogg
ASSUMETARGETDEFS
MAP FOX.BIDIR TARGET TOM.BIDIR_D;

Setup DEV to PROD Replication

Now we will be configuring uni-directional replication from DEV to PROD.

Add extract on devdb with below details

On devdb:
==========
GGSCI> ADD EXTRACT tomex, INTEGRATED TRANLOG, BEGIN NOW
GGSCI> register extract tomex database  only needed in integrated capture mode
GGSCI> add exttrail /u01/app/oracle/product/gg/dirdat/bi_dir/ld, extract tomex
GGSCI> edit param tomex
EXTRACT tomex
USERID ogg, PASSWORD ogg
EXTTRAIL /u01/app/oracle/product/gg/dirdat/bi_dir/ld
TRANLOGOPTIONS EXCLUDEUSER OGG
TABLE TOM.BIDIR_D

Add data pump on devdb

On devdb:
==========
GGSCI> Add extract tomdp, EXTTRAILSOURCE /u01/app/oracle/product/gg/dirdat/bi_dir/ld
GGSCI> Add rmttrail /u01/app/oracle/product/gg/dirdat/bi_dir/rp, extract tomdp
GGSCI> edit param tomdp
EXTRACT tomdp
USERID ogg, PASSWORD ogg
RMTHOST ggprod, MGRPORT 7809
RMTTRAIL /u01/app/oracle/product/gg/dirdat/bi_dir/rp
TABLE TOM.BIDIR_D;

Add replicat on target proddb

On proddb:
==========
GGSCI> dblogin userid ogg, password ogg
GGSCI> add replicat foxrep, integrated exttrail /u01/app/oracle/product/gg/dirdat/bi_
dir/rp
GGSCI> edit param foxrep
REPLICAT foxrep
USERID ogg, PASSWORD ogg
ASSUMETARGETDEFS
MAP TOM.BIDIR_D TARGET FOX.BIDIR;

Test Golden Gate Bidirectional Replication

Start all the extract, pump, and replicat processes on both systems and test your replication.

Let us insert some rows in the table on the source

On proddb:
==========
INSERT INTO bidir VALUES ('Scott', 'scott@gmail.com', 'USA');
INSERT INTO bidir VALUES ('James', 'james@gmail.com', 'UK');
commit;

Now try to delete the James record on devdb database, commit transactions and check on proddb if record is deleted or not.

Heading 2