Introduction
We are going to configure a Bidirectional Golden Gate setup between two Oracle database environments. We will use Oracle Golden Gate 19c classic version. In the previous article, we covered installing the GoldenGate software and preparing both databases — see Bidirectional GoldenGate: Installation and Preparation if you haven't set that up yet. This article configures Extract and Replicat in both directions to complete the bidirectional replication.
Scenario
Suppose a company has two branches — one in Delhi and one in Noida — running similar business applications but on separate database instances. Since both databases handle read/write operations independently, a Bidirectional GoldenGate setup keeps them in sync with each other.
Environment Used in this Guide
| Server | Source | Target |
|---|---|---|
| Hostname | delhi.oraeasy.com | noida.oraeasy.com |
| OS | OEL 8.5 | OEL 8.5 |
| Database Version | 19.28 | 19.28 |
| DB/PDB Name | dlcdb/dldb | ndcdb/nddb |
| Golden Gate Version | 19.1.0.0.4 | 19.1.0.0.4 |
| Golden Gate Home | /u01/app/gghome | /u01/app/gghome1 |
Configuration from Delhi (Source) to Noida (Target)
Step 1: Configure the Extract on the Source
ADD SCHEMATRANDATA tells GoldenGate which schema to track for supplemental logging at the row level, which the Extract needs before it can capture changes. The EXCLUDEUSER option in the Extract parameter file is the key setting for bidirectional setups — without it, changes applied by the Replicat's own user would get re-captured and sent right back to where they came from, creating a replication loop.
GGSCI (delhi.oraeasy.com) 1> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
GGSCI (delhi.oraeasy.com) 2> info mgr
Manager is running (IP port TCP:delhi.oraeasy.com.7809, Process ID 5208).
GGSCI (delhi.oraeasy.com) 3> info CREDENTIALSTORE
Reading from credential store:
Default domain: OracleGoldenGate
Alias: ogg
Userid: c##ogg@DLCDB
Alias: ggadmin
Userid: GGADMIN@DLDB
GGSCI (delhi.oraeasy.com) 3> dblogin USERIDALIAS ogg
Successfully logged into database CDB$ROOT.
GGSCI (delhi.oraeasy.com as c##ogg@dlcdb/CDB$ROOT) 6> ADD SCHEMATRANDATA DLDB.CORPDATA
2025-11-30 14:22:06 INFO OGG-01788 SCHEMATRANDATA has been added on schema "CORPDATA".
2025-11-30 14:22:06 INFO OGG-01976 SCHEMATRANDATA for scheduling columns has been added on schema "CORPDATA".
2025-11-30 14:22:06 INFO OGG-10154 Schema level PREPARECSN set to mode NOWAIT on schema "CORPDATA".
2025-11-30 14:22:09 INFO OGG-10471 ***** Oracle Goldengate support information on table CORPDATA.CORP_ACCOUNTS *****
Oracle Goldengate support native capture on table CORPDATA.CORP_ACCOUNTS.
Oracle Goldengate marked following column as key columns on table CORPDATA.CORP_ACCOUNTS: ACCOUNT_ID.
GGSCI (delhi.oraeasy.com as c##ogg@dlcdb/CDB$ROOT) 7> edit param E_DL
EXTRACT E_DL
USERIDALIAS ogg
TranLogOptions INTEGRATEDPARAMS(MAX_SGA_SIZE 1024) Adjust this size as per available memory:
TRANLOGOPTIONS EXCLUDEUSER dldb.ggadmin It will avoid the reverse replication of same transaction from target to source:
EXTTRAIL /u01/app/gghome/dirdat/ed
DDL INCLUDE ALL
DDLOPTIONS REPORT
TABLE DLDB.CORPDATA.*;
GGSCI (delhi.oraeasy.com as c##ogg@dlcdb/CDB$ROOT) 8> view param E_DL
EXTRACT E_DL
USERIDALIAS ogg
TranLogOptions INTEGRATEDPARAMS(MAX_SGA_SIZE 1024)
TRANLOGOPTIONS EXCLUDEUSER dldb.ggadmin
EXTTRAIL /u01/app/gghome/dirdat/ed
DDL INCLUDE ALL
DDLOPTIONS REPORT
TABLE DLDB.CORPDATA.*;
GGSCI (delhi.oraeasy.com as c##ogg@dlcdb/CDB$ROOT) 9> ADD extract E_DL, integrated tranlog, begin now
EXTRACT (Integrated) added.
GGSCI (delhi.oraeasy.com as c##ogg@dlcdb/CDB$ROOT) 10> ADD EXTTRAIL /u01/app/gghome/dirdat/ed, EXTRACT E_DL, MEGABYTES 5
EXTTRAIL added.
GGSCI (delhi.oraeasy.com as c##ogg@dlcdb/CDB$ROOT) 22> REGISTER EXTRACT E_DL DATABASE CONTAINER (DLDB)
2025-11-30 14:34:36 INFO OGG-02003 Extract E_DL successfully registered with database at SCN 2647290.
GGSCI (delhi.oraeasy.com as c##ogg@dlcdb/CDB$ROOT) 23> start E_DL
Sending START request to MANAGER ...
EXTRACT E_DL starting
GGSCI (delhi.oraeasy.com as c##ogg@dlcdb/CDB$ROOT) 24> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING E_DL 00:00:00 00:10:38
GGSCI (delhi.oraeasy.com as c##ogg@dlcdb/CDB$ROOT) 32> !
info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING E_DL 00:00:03 00:00:06
GGSCI (delhi.oraeasy.com as c##ogg@dlcdb/CDB$ROOT) 33> lag E_DL
Sending GETLAG request to EXTRACT E_DL ...
Last record lag 3 seconds.
At EOF, no more records to process
The zero-second lag reported by lag E_DL confirms E_DL has fully caught up to the current redo stream and is ready to feed the pump process next.
Step 2: Configure the Pump Process to Transfer the Trail File to the Target
The pump extract (P_DL) doesn't do any data transformation — PASSTHRU tells it to move records exactly as captured, straight to the remote trail on Noida, which is all a pump needs to do.
GGSCI (delhi.oraeasy.com as c##ogg@dlcdb/CDB$ROOT) 34> edit param P_DL
EXTRACT P_DL
USERIDALIAS ogg
RMTHOST noida.oraeasy.com, MGRPORT 7809
RMTTRAIL /u01/app/gghome1/dirdat/de
DDL INCLUDE ALL
PASSTHRU
TABLE DLDB.CORPDATA.*;
GGSCI (delhi.oraeasy.com as c##ogg@dlcdb/CDB$ROOT) 35> view param P_DL
EXTRACT P_DL
USERIDALIAS ogg
RMTHOST noida.oraeasy.com, MGRPORT 7809
RMTTRAIL /u01/app/gghome1/dirdat/de
DDL INCLUDE ALL
PASSTHRU
TABLE DLDB.CORPDATA.*;
GGSCI (delhi.oraeasy.com as c##ogg@dlcdb/CDB$ROOT) 36> add extract P_DL exttrailsource /u01/app/gghome/dirdat/ed
EXTRACT added.
GGSCI (delhi.oraeasy.com as c##ogg@dlcdb/CDB$ROOT) 37> add rmttrail /u01/app/gghome1/dirdat/de extract P_DL
RMTTRAIL added.
GGSCI (delhi.oraeasy.com as c##ogg@dlcdb/CDB$ROOT) 38> start extract P_DL
Sending START request to MANAGER ...
EXTRACT P_DL starting
GGSCI (delhi.oraeasy.com as c##ogg@dlcdb/CDB$ROOT) 39> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING E_DL 00:00:03 00:00:09
EXTRACT RUNNING P_DL 00:00:00 00:02:13
GGSCI (delhi.oraeasy.com as c##ogg@dlcdb/CDB$ROOT) 40> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING E_DL 00:00:03 00:00:09
EXTRACT RUNNING P_DL 00:00:00 00:00:13
Note that RMTTRAIL points at /u01/app/gghome1/dirdat/de on Noida — that path only needs to exist logically within GoldenGate's own trail management, since the pump creates it remotely via the Manager listener on port 7809.
Step 3: Perform the Initial Load from Source to Target
Before starting the Replicat, the target needs a starting copy of the data that matches a known point in time. This uses Data Pump's network import with flashback_scn set to the SCN captured at the source, so the import is transactionally consistent with the exact point where the Extract began capturing.
Verify the table data at source. Also note the current SCN:
SQL> def
DEFINE _DATE = "30-NOV-25" (CHAR)
DEFINE _CONNECT_IDENTIFIER = "dlcdb" (CHAR)
DEFINE _USER = "SYS" (CHAR)
DEFINE _PRIVILEGE = "AS SYSDBA" (CHAR)
DEFINE _SQLPLUS_RELEASE = "1928000000" (CHAR)
DEFINE _EDITOR = "vi" (CHAR)
DEFINE _O_VERSION = "Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.28.0.0.0" (CHAR)
DEFINE _O_RELEASE = "1928000000" (CHAR)
SQL>
SQL> sho pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 DLDB READ WRITE NO
SQL>
SQL> alter session set container=DLDB;
Session altered.
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
2593022
SQL> select * from corpdata.corp_accounts;
ACCOUNT_ID ACCOUNT_NAME INDUSTRY COUNTRY ANNUAL_REVENUE CREDI STATUS UPDATED_A
---------- -------------------- -------------------- --------------- -------------- ----- -------------------- ---------
1001 Alpha Tech Pvt Ltd IT Services India 45000000 A ACTIVE 29-NOV-25
1002 Global Infoworks Ltd Software USA 120000000 AAA ACTIVE 29-NOV-25
1003 Zenith Logistics Transport UK 75000000 BBB HOLD 29-NOV-25
1004 BlueOcean Pharma Pharmaceuticals Singapore 90000000 A ACTIVE 29-NOV-25
1005 Nova Retail Corp Retail India 30000000 BB SUSPENDED 29-NOV-25
5 rows selected.
On target create the directory for import. Also create a Public database link to
connect source:
[oracle@noida gghome1]$ mkdir -p /u01/app/oracle/imp
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 NDDB READ WRITE NO
SQL> alter session set container=NDDB;
Session altered.
SQL> create or replace directory imp as '/u01/app/oracle/imp';
Directory created.
SQL> grant read, write on directory imp to public;
Grant succeeded.
SQL> create database link import connect to system identified by YourP@ssw0rd using 'dldb';
Database link created.
SQL> select sysdate from dual@import;
SYSDATE
---------
30-NOV-25
SQL> exit
Set the environment for PDB and do the import using DB link with flashback_scn:
[oracle@noida admin]$ export ORACLE_PDB_SID=NDDB
[oracle@noida admin]$
[oracle@noida admin]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Sun Nov 30 15:32:08 2025
Version 19.28.0.0.0
Copyright (c) 1982, 2025, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.28.0.0.0
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
3 NDDB READ WRITE NO
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.28.0.0.0
[oracle@noida admin]$ impdp schemas=CORPDATA directory=imp logfile=import.log network_link=import flashback_scn=2593022
Import: Release 19.0.0.0.0 - Production on Sun Nov 30 15:41:27 2025
Version 19.28.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Username: / as sysdba
Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
FLASHBACK automatically enabled to preserve database integrity.
Starting "SYS"."SYS_IMPORT_SCHEMA_01": /******** AS SYSDBA schemas=CORPDATA directory=imp logfile=import.log network_link=import flashback_scn=2593022
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
. . imported "CORPDATA"."CORP_ACCOUNTS" 5 rows
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
Job "SYS"."SYS_IMPORT_SCHEMA_01" successfully completed at Sun Nov 30 15:43:11 2025 elapsed 0 00:01:32
Verify the data at target:
[oracle@noida admin]$
[oracle@noida admin]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Sun Nov 30 15:44:48 2025
Version 19.28.0.0.0
Copyright (c) 1982, 2025, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.28.0.0.0
SQL> select count(*) from CORPDATA.CORP_ACCOUNTS;
COUNT(*)
----------
5
SQL> select * from corpdata.corp_accounts;
ACCOUNT_ID ACCOUNT_NAME INDUSTRY COUNTRY ANNUAL_REVENUE CREDI STATUS UPDATED_A
---------- -------------------- -------------------- --------------- -------------- ----- -------------------- ---------
1001 Alpha Tech Pvt Ltd IT Services India 45000000 A ACTIVE 29-NOV-25
1002 Global Infoworks Ltd Software USA 120000000 AAA ACTIVE 29-NOV-25
1003 Zenith Logistics Transport UK 75000000 BBB HOLD 29-NOV-25
1004 BlueOcean Pharma Pharmaceuticals Singapore 90000000 A ACTIVE 29-NOV-25
1005 Nova Retail Corp Retail India 30000000 BB SUSPENDED 29-NOV-25
Matching row counts (5) on both sides confirm the initial load transferred cleanly. The recorded SCN, 2593022, is what the Replicat in the next step will use to know where in the change stream to start applying from.
Step 4: Configure the Replicat on the Target
DDLERROR DEFAULT IGNORE tells the Replicat to skip DDL errors rather than abend on them, which keeps ordinary DML flowing even if a DDL statement can't apply cleanly for some reason. Starting with AFTERCSN at the SCN recorded during the initial load ensures no transactions are missed or double-applied.
GGSCI (noida.oraeasy.com) 1> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
GGSCI (noida.oraeasy.com) 2> edit param R_DL
REPLICAT R_DL
ASSUMETARGETDEFS
DDLOPTIONS REPORT
USERIDALIAS ggadmin
DDL INCLUDE ALL
DDLERROR DEFAULT IGNORE
DiscardFile dirrpt/R_DL.dsc, Append
DiscardRollover at 00:00
MAP DLDB.CORPDATA.*, TARGET NDDB.CORPDATA.*;
GGSCI (noida.oraeasy.com) 3>
GGSCI (noida.oraeasy.com) 3> view param R_DL
REPLICAT R_DL
ASSUMETARGETDEFS
DDLOPTIONS REPORT
USERIDALIAS ggadmin
DDL INCLUDE ALL
DDLERROR DEFAULT IGNORE
DiscardFile dirrpt/R_DL.dsc, Append
DiscardRollover at 00:00
MAP DLDB.CORPDATA.*, TARGET NDDB.CORPDATA.*;
GGSCI (noida.oraeasy.com) 4> info CREDENTIALSTORE
Reading from credential store:
Default domain: OracleGoldenGate
Alias: ogg
Userid: c##ogg@ndcdb
Alias: ggadmin
Userid: GGADMIN@NDDB
GGSCI (noida.oraeasy.com) 5> dblogin useridalias ggadmin
Successfully logged into database NDDB.
GGSCI (noida.oraeasy.com as GGADMIN@ndcdb/NDDB) 6> add checkpointtable CORPDATA.CHECKPOINT
Logon catalog name NDDB will be used for table specification NDDB.CORPDATA.CHECKPOINT.
Successfully created checkpoint table NDDB.CORPDATA.CHECKPOINT.
GGSCI (noida.oraeasy.com as GGADMIN@ndcdb/NDDB) 8> add replicat R_DL, exttrail /u01/app/gghome1/dirdat/de, checkpointtable CORPDATA.CHECKPOINT
REPLICAT added.
Start the Replicate with AFTERCSN clause (SCN which taken at Source):
GGSCI (noida.oraeasy.com as GGADMIN@ndcdb/NDDB) 9> start R_DL AFTERCSN 2593022
Sending START request to MANAGER ...
REPLICAT R_DL starting
GGSCI (noida.oraeasy.com as GGADMIN@ndcdb/NDDB) 10> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
REPLICAT RUNNING R_DL 00:00:00 00:42:37
GGSCI (noida.oraeasy.com as GGADMIN@ndcdb/NDDB) 11> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
REPLICAT RUNNING R_DL 00:00:00 00:00:00
Step 5: Test Delhi-to-Noida Replication with DDL and DML
With E_DL, P_DL, and R_DL all running, an insert, an update, and a column-width change made on Delhi should all appear on Noida without manual intervention. This exercises both the DML path and the DDL replication configured in the Extract parameter file earlier.
At source (Delhi):
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
3 DLDB READ WRITE NO
SQL>
SQL> set lines 333 pages 333
SQL> col ACCOUNT_NAME for a20
SQL> col INDUSTRY for a20
SQL> col COUNTRY for a15
SQL> select * from corpdata.corp_accounts;
ACCOUNT_ID ACCOUNT_NAME INDUSTRY COUNTRY ANNUAL_REVENUE CREDI STATUS UPDATED_A
---------- -------------------- -------------------- --------------- -------------- ----- -------------------- ---------
1001 Alpha Tech Pvt Ltd IT Services India 45000000 A ACTIVE 29-NOV-25
1002 Global Infoworks Ltd Software USA 120000000 AAA ACTIVE 29-NOV-25
1003 Zenith Logistics Transport UK 75000000 BBB HOLD 29-NOV-25
1004 BlueOcean Pharma Pharmaceuticals Singapore 90000000 A ACTIVE 29-NOV-25
1005 Nova Retail Corp Retail India 30000000 BB SUSPENDED 29-NOV-25
SQL> INSERT INTO CORPDATA.CORP_ACCOUNTS
(ACCOUNT_ID, ACCOUNT_NAME, INDUSTRY, COUNTRY, ANNUAL_REVENUE,
CREDIT_RATING, STATUS, UPDATED_AT)
VALUES
(2001, 'Prime Solutions Ltd', 'Consulting', 'India', 15000000,
'A', 'ACTIVE', SYSDATE);
1 row created.
SQL> commit ;
Commit complete.
SQL> select * from corpdata.corp_accounts;
ACCOUNT_ID ACCOUNT_NAME INDUSTRY COUNTRY ANNUAL_REVENUE CREDI STATUS UPDATED_A
---------- -------------------- -------------------- --------------- -------------- ----- -------------------- ---------
1001 Alpha Tech Pvt Ltd IT Services India 45000000 A ACTIVE 29-NOV-25
1002 Global Infoworks Ltd Software USA 120000000 AAA ACTIVE 29-NOV-25
1003 Zenith Logistics Transport UK 75000000 BBB HOLD 29-NOV-25
1004 BlueOcean Pharma Pharmaceuticals Singapore 90000000 A ACTIVE 29-NOV-25
1005 Nova Retail Corp Retail India 30000000 BB SUSPENDED 29-NOV-25
2001 Prime Solutions Ltd Consulting India 15000000 A ACTIVE 30-NOV-25
6 rows selected.
SQL> UPDATE CORPDATA.CORP_ACCOUNTS SET ANNUAL_REVENUE = 18000000,
STATUS = 'HOLD', UPDATED_AT = SYSDATE WHERE ACCOUNT_ID = 2001;
1 row updated.
SQL> commit;
Commit complete.
SQL> select * from corpdata.corp_accounts;
ACCOUNT_ID ACCOUNT_NAME INDUSTRY COUNTRY ANNUAL_REVENUE CREDI STATUS UPDATED_A
---------- -------------------- -------------------- --------------- -------------- ----- -------------------- ---------
1001 Alpha Tech Pvt Ltd IT Services India 45000000 A ACTIVE 29-NOV-25
1002 Global Infoworks Ltd Software USA 120000000 AAA ACTIVE 29-NOV-25
1003 Zenith Logistics Transport UK 75000000 BBB HOLD 29-NOV-25
1004 BlueOcean Pharma Pharmaceuticals Singapore 90000000 A ACTIVE 29-NOV-25
1005 Nova Retail Corp Retail India 30000000 BB SUSPENDED 29-NOV-25
2001 Prime Solutions Ltd Consulting India 18000000 A HOLD 30-NOV-25
6 rows selected.
SQL> desc CORPDATA.CORP_ACCOUNTS
Name Null? Type
----------------------------------------- -------- ----------------------------
ACCOUNT_ID NOT NULL NUMBER
ACCOUNT_NAME VARCHAR2(100)
INDUSTRY VARCHAR2(50)
COUNTRY VARCHAR2(50)
ANNUAL_REVENUE NUMBER(15,2)
CREDIT_RATING VARCHAR2(5)
STATUS VARCHAR2(20)
UPDATED_AT DATE
SQL> ALTER TABLE CORPDATA.CORP_ACCOUNTS MODIFY (ACCOUNT_NAME VARCHAR2(200));
Table altered.
SQL> desc CORPDATA.CORP_ACCOUNTS
Name Null? Type
----------------------------------------- -------- ----------------------------
ACCOUNT_ID NOT NULL NUMBER
ACCOUNT_NAME VARCHAR2(200)
INDUSTRY VARCHAR2(50)
COUNTRY VARCHAR2(50)
ANNUAL_REVENUE NUMBER(15,2)
CREDIT_RATING VARCHAR2(5)
STATUS VARCHAR2(20)
UPDATED_AT DATE
GGSCI (delhi.oraeasy.com as c##ogg@dlcdb/CDB$ROOT) 56> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING E_DL 00:00:00 00:00:06
EXTRACT RUNNING P_DL 00:00:00 00:00:03
At target (Noida):
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
3 NDDB READ WRITE NO
SQL> select * from corpdata.corp_accounts;
ACCOUNT_ID ACCOUNT_NAME INDUSTRY COUNTRY ANNUAL_REVENUE CREDI STATUS UPDATED_A
---------- -------------------- -------------------- --------------- -------------- ----- -------------------- ---------
1001 Alpha Tech Pvt Ltd IT Services India 45000000 A ACTIVE 29-NOV-25
1002 Global Infoworks Ltd Software USA 120000000 AAA ACTIVE 29-NOV-25
1003 Zenith Logistics Transport UK 75000000 BBB HOLD 29-NOV-25
1004 BlueOcean Pharma Pharmaceuticals Singapore 90000000 A ACTIVE 29-NOV-25
1005 Nova Retail Corp Retail India 30000000 BB SUSPENDED 29-NOV-25
2001 Prime Solutions Ltd Consulting India 18000000 A HOLD 30-NOV-25
6 rows selected.
SQL> desc CORPDATA.CORP_ACCOUNTS
Name Null? Type
----------------------------------------- -------- ----------------------------
ACCOUNT_ID NOT NULL NUMBER
ACCOUNT_NAME VARCHAR2(200)
INDUSTRY VARCHAR2(50)
COUNTRY VARCHAR2(50)
ANNUAL_REVENUE NUMBER(15,2)
CREDIT_RATING VARCHAR2(5)
STATUS VARCHAR2(20)
UPDATED_AT DATE
GGSCI (noida.oraeasy.com as GGADMIN@ndcdb/NDDB) 15> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
REPLICAT RUNNING R_DL 00:00:01 00:00:00
The matching row, matching updated value, and matching VARCHAR2(200) column width on Noida all confirm the DML and the DDL both replicated correctly from Delhi.
Configuring the Reverse Direction: Noida (Source) to Delhi (Target)
With one direction verified, the same pattern repeats in reverse to complete the bidirectional loop — Noida becomes the source, Delhi becomes the target.
Step 1: Configure the Extract on the Source
This mirrors Step 1 from the Delhi-to-Noida direction, but note the checkpoint tables created earlier for the first direction's Replicat now show up in the schema trandata output too, since ADD SCHEMATRANDATA is scoped to the whole schema, not just one table.
GGSCI (noida.oraeasy.com) 1> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
REPLICAT RUNNING R_DL 00:00:01 00:00:00
GGSCI (noida.oraeasy.com) 4> info CREDENTIALSTORE
Reading from credential store:
Default domain: OracleGoldenGate
Alias: ogg
Userid: c##ogg@ndcdb
Alias: ggadmin
Userid: GGADMIN@NDDB
GGSCI (noida.oraeasy.com) 6> dblogin USERIDALIAS ogg
Successfully logged into database CDB$ROOT.
GGSCI (noida.oraeasy.com as c##ogg@ndcdb/CDB$ROOT) 7>
GGSCI (noida.oraeasy.com as c##ogg@ndcdb/CDB$ROOT) 7> ADD SCHEMATRANDATA NDDB.CORPDATA
2025-12-08 10:54:53 INFO OGG-01788 SCHEMATRANDATA has been added on schema "CORPDATA".
2025-12-08 10:54:54 INFO OGG-01976 SCHEMATRANDATA for scheduling columns has been added on schema "CORPDATA".
2025-12-08 10:54:54 INFO OGG-10154 Schema level PREPARECSN set to mode NOWAIT on schema "CORPDATA".
2025-12-08 10:55:02 INFO OGG-10471 ***** Oracle Goldengate support information on table CORPDATA.CHECKPOINT *****
Oracle Goldengate support native capture on table CORPDATA.CHECKPOINT.
Oracle Goldengate marked following column as key columns on table CORPDATA.CHECKPOINT: GROUP_NAME, GROUP_KEY.
2025-12-08 10:55:02 INFO OGG-10471 ***** Oracle Goldengate support information on table CORPDATA.CHECKPOINT_LOX *****
Oracle Goldengate support native capture on table CORPDATA.CHECKPOINT_LOX.
Oracle Goldengate marked following column as key columns on table CORPDATA.CHECKPOINT_LOX: GROUP_NAME, GROUP_KEY, LOG_CMPLT_CSN, LOG_CMPLT_XIDS_SEQ.
2025-12-08 10:55:02 INFO OGG-10471 ***** Oracle Goldengate support information on table CORPDATA.CORP_ACCOUNTS *****
Oracle Goldengate support native capture on table CORPDATA.CORP_ACCOUNTS.
Oracle Goldengate marked following column as key columns on table CORPDATA.CORP_ACCOUNTS: ACCOUNT_ID.
GGSCI (noida.oraeasy.com as c##ogg@ndcdb/CDB$ROOT) 8>
GGSCI (noida.oraeasy.com as c##ogg@ndcdb/CDB$ROOT) 8> edit param E_ND
EXTRACT E_ND
USERIDALIAS ogg
TranLogOptions INTEGRATEDPARAMS(MAX_SGA_SIZE 1024) Adjust this size as per available memory:
TRANLOGOPTIONS EXCLUDEUSER nddb.ggadmin It will avoid the reverse replication of same transaction from target to source:
EXTTRAIL /u01/app/gghome1/dirdat/en
DDL INCLUDE ALL
DDLOPTIONS REPORT
TABLE NDDB.CORPDATA.*;
GGSCI (noida.oraeasy.com as c##ogg@ndcdb/CDB$ROOT) 9> view param E_ND
EXTRACT E_ND
USERIDALIAS ogg
TranLogOptions INTEGRATEDPARAMS(MAX_SGA_SIZE 1024)
TRANLOGOPTIONS EXCLUDEUSER nddb.ggadmin
EXTTRAIL /u01/app/gghome1/dirdat/en
DDL INCLUDE ALL
DDLOPTIONS REPORT
TABLE NDDB.CORPDATA.*;
GGSCI (noida.oraeasy.com as c##ogg@ndcdb/CDB$ROOT) 10> ADD extract E_ND, integrated tranlog, begin now
EXTRACT (Integrated) added.
GGSCI (noida.oraeasy.com as c##ogg@ndcdb/CDB$ROOT) 11> ADD EXTTRAIL /u01/app/gghome1/dirdat/en, EXTRACT E_ND, MEGABYTES 5
EXTTRAIL added.
GGSCI (noida.oraeasy.com as c##ogg@ndcdb/CDB$ROOT) 12> REGISTER EXTRACT E_ND DATABASE CONTAINER (NDDB)
2025-12-08 10:56:51 INFO OGG-02003 Extract E_ND successfully registered with database at SCN 2707237.
GGSCI (noida.oraeasy.com as c##ogg@ndcdb/CDB$ROOT) 15> start E_ND
Sending START request to MANAGER ...
EXTRACT E_ND starting
GGSCI (noida.oraeasy.com as c##ogg@ndcdb/CDB$ROOT) 17> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING E_ND 00:00:00 00:02:14
REPLICAT RUNNING R_DL 00:00:00 00:00:00
GGSCI (noida.oraeasy.com as c##ogg@ndcdb/CDB$ROOT) 28> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING E_ND 00:00:00 00:00:07
REPLICAT RUNNING R_DL 00:00:00 00:00:01
Step 2: Configure the Pump Process to Transfer the Trail File to the Target
Same role as P_DL earlier, just reversed — P_ND ships Noida's captured changes to a remote trail on Delhi.
GGSCI (noida.oraeasy.com as c##ogg@ndcdb/CDB$ROOT) 29> edit param P_ND
EXTRACT P_ND
USERIDALIAS ogg
RMTHOST delhi.oraeasy.com, MGRPORT 7809
RMTTRAIL /u01/app/gghome/dirdat/ne
DDL INCLUDE ALL
PASSTHRU
TABLE NDDB.CORPDATA.*;
GGSCI (noida.oraeasy.com as c##ogg@ndcdb/CDB$ROOT) 30> view param P_ND
EXTRACT P_ND
USERIDALIAS ogg
RMTHOST delhi.oraeasy.com, MGRPORT 7809
RMTTRAIL /u01/app/gghome/dirdat/ne
DDL INCLUDE ALL
PASSTHRU
TABLE NDDB.CORPDATA.*;
GGSCI (noida.oraeasy.com as c##ogg@ndcdb/CDB$ROOT) 31> add extract P_ND exttrailsource /u01/app/gghome1/dirdat/en
EXTRACT added.
GGSCI (noida.oraeasy.com as c##ogg@ndcdb/CDB$ROOT) 32> add rmttrail /u01/app/gghome/dirdat/ne extract P_ND
RMTTRAIL added.
GGSCI (noida.oraeasy.com as c##ogg@ndcdb/CDB$ROOT) 41> start P_ND
Sending START request to MANAGER ...
EXTRACT P_ND starting
GGSCI (noida.oraeasy.com as c##ogg@ndcdb/CDB$ROOT) 42> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING E_ND 00:00:00 00:00:06
EXTRACT RUNNING P_ND 00:00:00 00:03:15
REPLICAT RUNNING R_DL 00:00:00 00:00:06
GGSCI (noida.oraeasy.com as c##ogg@ndcdb/CDB$ROOT) 43> !
info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING E_ND 00:00:02 00:00:09
EXTRACT RUNNING P_ND 00:00:00 00:00:08
REPLICAT RUNNING R_DL 00:00:00 00:00:09
Because this direction replicates into the same table that the first direction already populated, there's no separate initial load step needed here — the data set on both sides is already consistent from the first direction's setup.
Step 3: Configure the Replicat on the Target
The final piece: a Replicat on Delhi that applies changes captured from Noida. This one doesn't need an AFTERCSN clause since there's no initial load gap to skip past.
GGSCI (delhi.oraeasy.com) 1> dblogin useridalias ggadmin
Successfully logged into database DLDB.
GGSCI (delhi.oraeasy.com as GGADMIN@dlcdb/DLDB) 2> edit param R_ND
REPLICAT R_ND
ASSUMETARGETDEFS
DDLOPTIONS REPORT
USERIDALIAS ggadmin
DDL INCLUDE ALL
DDLERROR DEFAULT IGNORE
DiscardFile dirrpt/R_ND.dsc, Append
DiscardRollover at 00:00
MAP NDDB.CORPDATA.*, TARGET DLDB.CORPDATA.*;
GGSCI (delhi.oraeasy.com as GGADMIN@dlcdb/DLDB) 3> view param R_ND
REPLICAT R_ND
ASSUMETARGETDEFS
DDLOPTIONS REPORT
USERIDALIAS ggadmin
DDL INCLUDE ALL
DDLERROR DEFAULT IGNORE
DiscardFile dirrpt/R_ND.dsc, Append
DiscardRollover at 00:00
MAP NDDB.CORPDATA.*, TARGET DLDB.CORPDATA.*;
GGSCI (delhi.oraeasy.com as GGADMIN@dlcdb/DLDB) 4> add checkpointtable CORPDATA.CHECKPOINT
Logon catalog name DLDB will be used for table specification DLDB.CORPDATA.CHECKPOINT.
Successfully created checkpoint table DLDB.CORPDATA.CHECKPOINT.
GGSCI (delhi.oraeasy.com as GGADMIN@dlcdb/DLDB) 5> add replicat R_ND, exttrail /u01/app/gghome/dirdat/ne, checkpointtable CORPDATA.CHECKPOINT
REPLICAT added.
GGSCI (delhi.oraeasy.com as GGADMIN@dlcdb/DLDB) 10> start R_ND
Sending START request to MANAGER ...
REPLICAT R_ND starting
GGSCI (delhi.oraeasy.com as GGADMIN@dlcdb/DLDB) 12> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING E_DL 00:00:04 00:00:03
EXTRACT RUNNING P_DL 00:00:00 00:00:07
REPLICAT RUNNING R_ND 00:00:00 00:02:03
GGSCI (delhi.oraeasy.com as GGADMIN@dlcdb/DLDB) 13> !
info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING E_DL 00:00:02 00:00:01
EXTRACT RUNNING P_DL 00:00:00 00:00:04
REPLICAT RUNNING R_ND 00:00:00 00:00:02
Notice the info all output on Delhi now lists all four processes — E_DL and P_DL for the outbound direction, and R_ND for the inbound direction — confirming both halves of the bidirectional loop are active on this server at once.
Step 4: Test Noida-to-Delhi Replication with DDL and DML
The final check: two inserts, an update, and a column-width change made on Noida should propagate to Delhi, mirroring the test done for the first direction.
At source (Noida):
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
3 NDDB READ WRITE NO
SQL>
SQL> set lines 333 pages 333
SQL> col ACCOUNT_NAME for a20
SQL> col INDUSTRY for a20
SQL> col COUNTRY for a15
SQL> select * from corpdata.corp_accounts;
ACCOUNT_ID ACCOUNT_NAME INDUSTRY COUNTRY ANNUAL_REVENUE CREDI STATUS UPDATED_A
---------- -------------------- -------------------- --------------- -------------- ----- -------------------- ---------
1001 Alpha Tech Pvt Ltd IT Services India 45000000 A ACTIVE 29-NOV-25
1002 Global Infoworks Ltd Software USA 120000000 AAA ACTIVE 29-NOV-25
1003 Zenith Logistics Transport UK 75000000 BBB HOLD 29-NOV-25
1004 BlueOcean Pharma Pharmaceuticals Singapore 90000000 A ACTIVE 29-NOV-25
1005 Nova Retail Corp Retail India 30000000 BB SUSPENDED 29-NOV-25
2001 Prime Solutions Ltd Consulting India 18000000 A HOLD 30-NOV-25
6 rows selected.
SQL> INSERT INTO CORPDATA.CORP_ACCOUNTS
(ACCOUNT_ID, ACCOUNT_NAME, INDUSTRY, COUNTRY, ANNUAL_REVENUE,
CREDIT_RATING, STATUS, UPDATED_AT)
VALUES
(2002, 'Global Tech Pvt Ltd', 'Technology', 'India', 55000000,
'A', 'ACTIVE', SYSDATE);
1 row created.
SQL> INSERT INTO CORPDATA.CORP_ACCOUNTS
(ACCOUNT_ID, ACCOUNT_NAME, INDUSTRY, COUNTRY, ANNUAL_REVENUE,
CREDIT_RATING, STATUS, UPDATED_AT)
VALUES
(2003, 'Global Tech Pvt Ltd', 'Technology', 'India', 55000000,
'A', 'HOLD', SYSDATE);
1 row created.
SQL> UPDATE CORPDATA.CORP_ACCOUNTS
SET ANNUAL_REVENUE = 65000000,
UPDATED_AT = SYSDATE
WHERE ACCOUNT_ID = 2002;
1 row updated.
SQL> commit;
SQL> select * from corpdata.corp_accounts;
ACCOUNT_ID ACCOUNT_NAME INDUSTRY COUNTRY ANNUAL_REVENUE CREDI STATUS UPDATED_A
---------- -------------------- -------------------- --------------- -------------- ----- -------------------- ---------
1001 Alpha Tech Pvt Ltd IT Services India 45000000 A ACTIVE 29-NOV-25
1002 Global Infoworks Ltd Software USA 120000000 AAA ACTIVE 29-NOV-25
1003 Zenith Logistics Transport UK 75000000 BBB HOLD 29-NOV-25
1004 BlueOcean Pharma Pharmaceuticals Singapore 90000000 A ACTIVE 29-NOV-25
1005 Nova Retail Corp Retail India 30000000 BB SUSPENDED 29-NOV-25
2001 Prime Solutions Ltd Consulting India 18000000 A HOLD 30-NOV-25
2002 Global Tech Pvt Ltd Technology India 55000000 A ACTIVE 08-DEC-25
2003 Global Tech Pvt Ltd Technology India 55000000 A HOLD 08-DEC-25
8 rows selected.
SQL> desc corpdata.corp_accounts
Name Null? Type
-------------------------------- -------- -----------------------
ACCOUNT_ID NOT NULL NUMBER
ACCOUNT_NAME VARCHAR2(200)
INDUSTRY VARCHAR2(50)
COUNTRY VARCHAR2(50)
ANNUAL_REVENUE NUMBER(15,2)
CREDIT_RATING VARCHAR2(5)
STATUS VARCHAR2(20)
UPDATED_AT DATE
SQL> ALTER TABLE CORPDATA.CORP_ACCOUNTS
MODIFY (ACCOUNT_NAME VARCHAR2(100));
Table altered.
SQL>
SQL> desc corpdata.corp_accounts
Name Null? Type
-------------------------------- -------- -----------------------
ACCOUNT_ID NOT NULL NUMBER
ACCOUNT_NAME VARCHAR2(100)
INDUSTRY VARCHAR2(50)
COUNTRY VARCHAR2(50)
ANNUAL_REVENUE NUMBER(15,2)
CREDIT_RATING VARCHAR2(5)
STATUS VARCHAR2(20)
UPDATED_AT DATE
GGSCI (noida.oraeasy.com as c##ogg@ndcdb/CDB$ROOT) 45> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING E_ND 00:00:02 00:00:00
EXTRACT RUNNING P_ND 00:00:00 00:00:01
REPLICAT RUNNING R_DL 00:00:00 00:00:03
At target (Delhi):
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
3 DLDB READ WRITE NO
SQL> select * from corpdata.corp_accounts;
ACCOUNT_ID ACCOUNT_NAME INDUSTRY COUNTRY ANNUAL_REVENUE CREDI STATUS UPDATED_A
---------- -------------------- -------------------- --------------- -------------- ----- -------------------- ---------
2002 Global Tech Pvt Ltd Technology India 65000000 A ACTIVE 08-DEC-25
2003 Global Tech Pvt Ltd Technology India 55000000 A HOLD 08-DEC-25
1001 Alpha Tech Pvt Ltd IT Services India 45000000 A ACTIVE 29-NOV-25
1002 Global Infoworks Ltd Software USA 120000000 AAA ACTIVE 29-NOV-25
1003 Zenith Logistics Transport UK 75000000 BBB HOLD 29-NOV-25
1004 BlueOcean Pharma Pharmaceuticals Singapore 90000000 A ACTIVE 29-NOV-25
1005 Nova Retail Corp Retail India 30000000 BB SUSPENDED 29-NOV-25
2001 Prime Solutions Ltd Consulting India 18000000 A HOLD 30-NOV-25
SQL> desc corpdata.corp_accounts
Name Null? Type
-------------------------------- -------- -----------------------
ACCOUNT_ID NOT NULL NUMBER
ACCOUNT_NAME VARCHAR2(100)
INDUSTRY VARCHAR2(50)
COUNTRY VARCHAR2(50)
ANNUAL_REVENUE NUMBER(15,2)
CREDIT_RATING VARCHAR2(5)
STATUS VARCHAR2(20)
UPDATED_AT DATE
GGSCI (delhi.oraeasy.com as GGADMIN@dlcdb/DLDB) 1> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING E_DL 00:00:00 00:00:01
EXTRACT RUNNING P_DL 00:00:01 00:00:00
REPLICAT RUNNING R_ND 00:00:00 00:00:02
Both new rows, the updated revenue figure, and the VARCHAR2(100) column width all landed correctly on Delhi, confirming replication now flows both ways. With EXCLUDEUSER filtering out each side's own Replicat user, changes propagate exactly once in each direction rather than looping back and forth.
Series Summary
Across these two articles, the bidirectional GoldenGate setup went from a bare install (Part 1) to a fully working two-way replication loop between Delhi and Noida: two Extracts, two pump processes, and two Replicats, each side excluding the other's apply user to prevent loopback. The result is a setup where either site can accept writes and have them reflected on the other, which is what the original scenario — two branches operating independently but needing to stay in sync — called for.
Thank you for reading!
I hope this content has been helpful to you. Your feedback and suggestions are always welcome. Feel free to leave a comment or reach out with any queries.
Abhishek Shrivastava
Oracle DBA with hands-on experience managing production Data Guard, RAC, GoldenGate, and APEX environments. I write practical, tested installation and troubleshooting guides based on real deployment work.
Comments
Post a Comment