Oracle Bi-Directional Synch using GoldenGate

In this article we will be creating a GoldetGate setup for Bi-Directional Synching.

Source Setup

HOSTNAME => HOST12C2020
DB Name => NCDB
DB Version => 12.2.0
GG Version => 12.3
GG Admin User => ggsource
Schema to replicate => dbsource.*

Target Setup

HOSTNAME => HOST12C2021
DB Name => TCDB          
DB Version =>12.2.0
GG Version => 12.3
GG Admin User => ggtarget
Schema to replicate => dbtarget.*

Step 1.  Check Tnsping for Both database and connectivity from Source to target an vice-versa.

[oracle@HOST12C2020 ~]$ tnsping NCDB
TNS Ping Utility for Linux: Version 12.2.0.1.0 - Production on 21-MAY-2020 00:05:26
Copyright (c) 1997, 2016, Oracle.  All rights reserved.
Used parameter files:

/u01/app/oracle/product/12.2.0.1/db_1/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.188.100)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = NCDB)))

OK (140 msec)
[oracle@HOST12C2020 ~]$ tnsping TCDB
TNS Ping Utility for Linux: Version 12.2.0.1.0 - Production on 21-MAY-2020 00:05:30
Copyright (c) 1997, 2016, Oracle.  All rights reserved.
Used parameter files:

/u01/app/oracle/product/12.2.0.1/db_1/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.188.101)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = TCDB)))

OK (840 msec)

[oracle@HOST12C2020 ~]$
Check Connectivity b/w Source and Target

[oracle@HOST12C2020 ~]$ ping -c 2 HOST12C2020

PING HOST12C2020 (192.168.188.100) 56(84) bytes of data.

64 bytes from HOST12C2020 (192.168.188.100): icmp_seq=1 ttl=64 time=0.020 ms

64 bytes from HOST12C2020 (192.168.188.100): icmp_seq=2 ttl=64 time=0.048 ms
--- HOST12C2020 ping statistics ---

2 packets transmitted, 2 received, 0% packet loss, time 1000ms

rtt min/avg/max/mdev = 0.020/0.034/0.048/0.014 ms

[oracle@HOST12C2020 ~]$
[oracle@HOST12C2020 ~]$ ping -c 2 HOST12C2021

PING HOST12C2021 (192.168.188.101) 56(84) bytes of data.

64 bytes from HOST12C2021 (192.168.188.101): icmp_seq=1 ttl=64 time=0.325 ms

64 bytes from HOST12C2021 (192.168.188.101): icmp_seq=2 ttl=64 time=0.856 ms
--- HOST12C2021 ping statistics ---

2 packets transmitted, 2 received, 0% packet loss, time 1001ms

rtt min/avg/max/mdev = 0.325/0.590/0.856/0.266 ms

[oracle@HOST12C2020 ~]$

Step 2. Configuring Source Database And Target Database Simultaneously.

Check Archivelog on Both side -> This has to be enabled as Classic Capture will use Redo log to process data.

[oracle@HOST12C2020 ~]$ sqlplus "/as sysdba"
SQL*Plus: Release 12.2.0.1.0 Production on Thu May 21 00:09:50 2020
Copyright (c) 1982, 2016, Oracle.  All rights reserved.

Connected to:

Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> archive log list;
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     3
Current log sequence           5

SQL>
SQL> shut immediate;

ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area  876609536 bytes
Fixed Size                  8626672 bytes
Variable Size             599789072 bytes
Database Buffers          264241152 bytes
Redo Buffers                3952640 bytes

Database mounted.
SQL> ALTER DATABASE ARCHIVELOG;

Database altered.

SQL> alter database open;
Database altered.

SQL> select LOG_MODE from v$database;
LOG_MODE

------------

ARCHIVELOG

Step 3. Check Force Logging and Supplemental Log on BOTH  Side.
 

This is required to supply extra information to Oracle process and to provide forcefully logs to Oracle for every operation in database.

SQL> select SUPPLEMENTAL_LOG_DATA_MIN,FORCE_LOGGING from v$database;

SUPPLEME FORCE_LOGGING
-------- ---------------------------------------
NO       NO

SQL> ALTER DATABASE FORCE LOGGING;
Database altered.

SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
Database altered.

SQL> select SUPPLEMENTAL_LOG_DATA_MIN,FORCE_LOGGING from v$database;
SUPPLEME FORCE_LOGGING
-------- ---------------------------------------
YES      YES

Step 4. Check GoldenGate Mandatory Parameter value on BOTH Side

ENABLE_GOLDENGATE_REPLICATION

This parameter has to be set on Both side Source and target .This is mandatory parameter.

SQL> show parameter ENABLE_GOLDENGATE_REPLICATION
NAME                                 TYPE                             VALUE
------------------------------------ -------------------------------- ------------------------------

enable_goldengate_replication        boolean                          FALSE

SQL>
SQL> ALTER SYSTEM SET ENABLE_GOLDENGATE_REPLICATION=TRUE SCOPE=BOTH;
System altered.

SQL> show parameter ENABLE_GOLDENGATE_REPLICATION
NAME                                 TYPE                             VALUE
------------------------------------ -------------------------------- ------------------------------

enable_goldengate_replication        boolean                          TRUE

Step 5. Create DB User on Source Side  and Target Side

Now there will be one DB user (SCHEMA) which will be holding tables and objects that we need to replicate on target .

On Source =>

SQL> create user dbsource identified by dbsource  default tablespace source_tbs  quota unlimited on source_tbs;

User created.
SQL> grant connect,resource to dbsource;

Grant succeeded.

On Target =>

SQL>  create user dbtarget identified by dbtarget  default tablespace dbtarget_tbs  quota unlimited on dbtarget_tbs;
User created.

SQL> grant connect,resource to dbtarget;
Grant succeeded.

Step 6. Create and Check Sample Tables

SQL> DESC TEST
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------

 ID                                        NOT NULL NUMBER(38)
NAME                                               VARCHAR2(10)
SQL>

ID column is having PRIMARY KEY.

Step 7. Create Goldengate Admin Account  on BOTH Side

This account will synch data of database users objects from Source System to target System .

ON Source

SQL>  create user ggsource identified by ggsource default tablespace ggsource_tbs quota unlimited on ggsource_tbs;
User created.

SQL> grant create session,connect,resource,alter system to ggsource;
Grant succeeded.

SQL> EXEC DBMS_GOLDENGATE_AUTH.GRANT_ADMIN_PRIVILEGE(grantee=>'ggsource', privilege_type=>'CAPTURE', grant_optional_privileges=>'*');
PL/SQL procedure successfully completed.
SQL>

ON Target

SQL> create user ggtarget identified by ggtarget default tablespace ggtarget_tbs quota unlimited on ggtarget_tbs;
User created.

SQL> grant create session,connect,resource,alter system to ggtarget;

SQL> EXEC DBMS_GOLDENGATE_AUTH.GRANT_ADMIN_PRIVILEGE(grantee=>'ggtarget', privilege_type=>'CAPTURE', grant_optional_privileges=>'*');

Step 8. Run Marker and DDL-enable/setup and Roll-setup script ON BOTH SIDE

These scripts are required to perform DDL replication from Source to target and vice-verse  .

ON Source
[oracle@HOST12C2020 trace]$ cd $GG_HOME

 [oracle@HOST12C2020 gg_home]$ pwd

/u01/app/oracle/gg_home

 [oracle@HOST12C2020 gg_home]$ sqlplus "/as sysdba"
SQL*Plus: Release 12.2.0.1.0 Production on Thu May 21 01:00:59 2020

Copyright (c) 1982, 2016, Oracle.  All rights reserved.

Connected to:

Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
SQL> @marker_setup.sql

SQL> @ddl_setup

SQL> @role_setup.sql

SQL> GRANT GGS_GGSUSER_ROLE TO ggsource;
SQL> @ddl_enable.sql

Trigger altered.

SQL>

ON Target

[oracle@HOST12C2021 gg_home]$ sqlplus "/as sysdba"

SQL*Plus: Release 12.2.0.1.0 Production on Sat May 23 16:00:15 2020

Copyright (c) 1982, 2016, Oracle.  All rights reserved.

SQL> @marker_setup.sql

SQL> @ddl_setup

SQL> @role_setup.sql

SQL> GRANT GGS_GGSUSER_ROLE TO ggsource;
SQL> @ddl_enable.sql

Trigger altered.

SQL>

Step 9. Add trandata on BOTH Side

[oracle@HOST12C2020 gg_home]$ ./ggsci
Oracle GoldenGate Command Interpreter for Oracle

Version 12.3.0.1.4 OGGCORE_12.3.0.1.0_PLATFORMS_180415.0359_FBO

On Source
GGSCI (HOST12C2020) 3> dblogin userid ggsource password ggsource

Successfully logged into database.
GGSCI (HOST12C2020 as ggsource@NCDB) 4> add trandata dbsource.*
On Target
GGSCI (HOST12C2021) 1> dblogin userid ggtarget password ggtarget

Successfully logged into database.
GGSCI (HOST12C2021 as ggtarget@TCDB) 3> add trandata dbtarget.*

Step 10. Create Extract and Pump file to Fetch and send data to BOTH SIDE

Source DB

Creating Capture Extract on Source DB On Source

GGSCI (HOST12C2020) 3> dblogin userid ggsource password ggsource

Successfully logged into database.
Creating Extract on Source DB

GGSCI (HOST12C2020) 2> view params ext1

EXTRACT ext1
USERID ggsource@NCDB, PASSWORD ggsource
EXTTRAIL /u01/app/oracle/gg_home/dirdat/aa
TRANLOGOPTIONS EXCLUDEUSER ggsource
TABLE dbsource.*;

GGSCI (HOST12C2020) 3>

GGSCI (HOST12C2020) 3> add extract ext1 tranlog begin now

EXTRACT added.

GGSCI (HOST12C2020) 4> add exttrail /u01/app/oracle/gg_home/dirdat/aa extract ext1

EXTTRAIL added.

GGSCI (HOST12C2020) 5> info ext1

EXTRACT    EXT1      Initialized   2020-05-23 12:29   Status STOPPED

Checkpoint Lag       00:00:00 (updated 00:01:29 ago)

Log Read Checkpoint  Oracle Redo Logs

                     2020-05-23 12:29:33  Seqno 0, RBA 0 SCN 0.0 (0)

Creating Extract (pump) on Source DB

GGSCI (HOST12C2020) 6> edit params dpump1

EXTRACT dpump1
USERID ggsource@NCDB, PASSWORD gsource
RMTHOST HOST12C2021, MGRPORT 7809
RMTTRAIL /u01/app/oracle/gg_home/dirdat/ab
TABLE dbsource.*;

GGSCI (HOST12C2020) 7> add extract dpump1 exttrailsource /u01/app/oracle/gg_home/dirdat/aa

EXTRACT added.

GGSCI (HOST12C2020) 8> add rmttrail /u01/app/oracle/gg_home/dirdat/ab extract dpump1

RMTTRAIL added.

GGSCI (HOST12C2020) 9> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt
MANAGER     RUNNING
EXTRACT     STOPPED     DPUMP1      00:00:00      00:03:35
EXTRACT     STOPPED     EXT1        00:00:00      00:08:57

On Target

Creating Capture Extract on Target DB

[oracle@HOST12C2021 gg_home]$ ./ggsci

GGSCI (HOST12C2021) 1> dblogin userid ggtarget password ggtarget

Successfully logged into database.
GGSCI (HOST12C2021 as ggtarget@TCDB) 4> view params ext2

EXTRACT ext2
USERID ggtarget@TCDB, PASSWORD ggtarget
EXTTRAIL /u01/app/oracle/gg_home/dirdat/bb
TRANLOGOPTIONS EXCLUDEUSER ggtarget
TABLE dbtarget.*;

GGSCI (HOST12C2021 as ggtarget@TCDB) 5>

GGSCI (HOST12C2021 as ggtarget@TCDB) 5> add extract ext2 tranlog begin now
EXTRACT added.

GGSCI (HOST12C2021 as ggtarget@TCDB) 6> add exttrail /u01/app/oracle/gg_home/dirdat/bb extract ext2
EXTTRAIL added.

GGSCI (HOST12C2021 as ggtarget@TCDB) 7> info ext2

EXTRACT    EXT2      Initialized   2020-05-23 16:10   Status STOPPED
Checkpoint Lag       00:00:00 (updated 00:00:26 ago)

Log Read Checkpoint  Oracle Redo Logs

                     2020-05-23 16:10:20  Seqno 0, RBA 0 SCN 0.0 (0)

Creating Extract (pump) on Target DB

GGSCI (HOST12C2021 as ggtarget@TCDB) 9> view params dpump2

EXTRACT dpump2
USERID ggtarget@TCDB, PASSWORD ggtarget
RMTHOST HOST12C2020, MGRPORT 7809
RMTTRAIL /u01/app/oracle/gg_home/dirdat/ba
TABLE dbtarget.*;

GGSCI (HOST12C2021 as ggtarget@TCDB) 10> add extract dpump2 exttrailsource /u01/app/oracle/gg_home/dirdat/bb
EXTRACT added.

GGSCI (HOST12C2021 as ggtarget@TCDB) 11> add rmttrail /u01/app/oracle/gg_home/dirdat/ba extract dpump2
RMTTRAIL added.

STEP 11. Further Configuration on BOTH sides

Add Checkpoint table and Create REPLICAT on BOTH Side

On Target

GGSCI (HOST12C2021) 3> dblogin userid ggtarget password ggtarget

Successfully logged into database.

GGSCI (HOST12C2021 as ggtarget@TCDB) 4> info mgr
Manager is running (IP port HOST12C2021.7809, Process ID 17365).

GGSCI (HOST12C2021 as ggtarget@TCDB) 5> add checkpointtable  ggtarget.chkpt
Successfully created checkpoint table ggtarget.chkpt.

GGSCI (HOST12C2021 as ggtarget@TCDB) 6> info checkpointtable  ggtarget.chkpt
Checkpoint table ggtarget.chkpt created 2020-05-23 13:58:47.

On Source

[oracle@HOST12C2020 gg_home]$ ./ggsci

GGSCI (HOST12C2020) 1> dblogin userid ggsource password ggsource

Successfully logged into database.

GGSCI (HOST12C2020 as ggsource@NCDB) 2> add checkpointtable ggsource.chkpt
Successfully created checkpoint table ggsource.chkpt.

GGSCI (HOST12C2020 as ggsource@NCDB) 3> info checkpointtable ggsource.chkpt
Checkpoint table ggsource.chkpt created 2020-05-23 16:22:01.

Step 12. Add replicat on BOTH Side

On Target

GGSCI (HOST12C2021 as ggtarget@TCDB) 8> view params rep1

REPLICAT rep1
ASSUMETARGETDEFS
USERID ggtarget@TCDB  PASSWORD ggtarget
MAP dbsource.*, TARGET dbtarget.*;

GGSCI (HOST12C2021 as ggtarget@TCDB) 10> add replicat rep1 exttrail /u01/app/oracle/gg_home/dirdat/ab  checkpointtable  ggtarget.chkpt
REPLICAT added.

[oracle@HOST12C2021 gg_home]$ sqlplus "/as sysdba"
SQL*Plus: Release 12.2.0.1.0 Production on Sat May 23 14:23:53 2020
Connected to:

Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> GRANT insert,update,delete on dbtarget.test TO GGTARGET;
Grant succeeded.

On Source

GGSCI (HOST12C2020 as ggsource@NCDB) 6> view params rep2

REPLICAT rep2
ASSUMETARGETDEFS
USERID ggsource@NCDB  PASSWORD ggsource
MAP dbtarget.*,TARGET dbsource.*;

GGSCI (HOST12C2020 as ggsource@NCDB) 7> add replicat rep2 exttrail /u01/app/oracle/gg_home/dirdat/ba checkpointtable ggsource.chkpt
REPLICAT added.

[oracle@HOST12C2020 gg_home]$  sqlplus "/as sysdba"

SQL>  GRANT insert,update,delete on dbsource.TEST TO GGSOURCE;
Grant succeeded.

Step 13. START all process on BOTH SIDE

On Source

GGSCI (HOST12C2020) 3> info all
Program     Status      Group       Lag at Chkpt  Time Since Chkpt
MANAGER     RUNNING
EXTRACT     RUNNING     DPUMP1      00:00:00      00:00:08
EXTRACT     RUNNING     EXT1        00:00:00      00:00:10
REPLICAT    RUNNING     REP2        00:00:00      00:00:05
GGSCI (HOST12C2020) 4>

On Target

GGSCI (HOST12C2021 as ggtarget@TCDB) 10> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt
MANAGER     RUNNING
EXTRACT     RUNNING     DPUMP2      00:00:00      00:00:00
EXTRACT     RUNNING     EXT2        00:00:00      00:00:04
REPLICAT    RUNNING     REP1        00:00:00      00:00:07

GGSCI (HOST12C2021 as ggtarget@TCDB) 11>

STEP 14. Testing Time

Insert value in Source table

[oracle@HOST12C2020 gg_home]$ sqlplus "dbsource/dbsource"

SQL>  select * from TEST;
        ID NAME
---------- ----------
          2 NEN
         1 PRIME

SQL>  insert into TEST values(3,'TEN');
1 row created.

SQL> commit;
Commit complete.

Verify value in Target table

[oracle@HOST12C2021 gg_home]$  sqlplus "dbtarget/dbtarget"

SQL>  select * from TEST;
        ID NAME
---------- ----------
1 PRIME
3 TEN
2 NEN