Oracle Golden Gate Bidirectional Replication
Achieve real-time data synchronization in both directions.
Golden gate bidirectional replication is two-way unidirectional replication. Let us set up bidirectional replication for a single table from source to target.
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.