• Arun Kumar

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', 'scott@gmail.com', 'USA');
INSERT INTO bidir VALUES ('James', 'james@gmail.com', 'UK');
commit;

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


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 same on both source and target database. Create Sample Table Create Fox user on both pr

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 Cr

  • Facebook
  • Twitter
  • YouTube
  • Instagram

© 2020 by DBA Genesis