• Arun Kumar

Oracle Golden Gate Bidirectional Replication

Golden gate bidirectional replication is two-way unidirectional replication. Let us set up 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 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 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 transaction and check on proddb if record is deleted or not.


819 views

Recent Posts

See All

Install Oracle 12c Golden Gate on Oracle Linux

In this article we will look at the steps to install oracle 12c Golden Gate on Oracle Enterprise Linux 6.5. The steps involved are: Virtual Machine Setup Install Oracle 12c Database Install Oracle 12c

Single Table Replication – Same Schema

In this article, we will be looking at single table replication using Oracle Golden Gate where the schema name is the same on both source and target database. Create Sample Table Test Replication Stop

Single Table Replication – Different Schema

In this article we will be looking at single table replication using Oracle Golden Gate where the schema name is different on source and target but table structure remains same. Create Sample Table Te