Oracle Golden Gate Bidirectional Replication

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

Prerequisites

  • 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 source

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

We will create 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 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 Bidirectional Replication

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

Let us insert some rows in table on source

On proddb:
==========
INSERT INTO bidir VALUES ('Scott', '[email protected]', 'USA');
INSERT INTO bidir VALUES ('James', '[email protected]', 'UK');
commit;

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

Was this article helpful?

Related Articles

Leave a Comment