Oracle GoldenGate HUB Configuration

In this post, we will provide the steps to set up an Oracle GoldenGate hub for Uni-Directional Replication.

In our environment, we have an Oracle Database 21c multi-tenant (container CDB) as both the source and target databases on seperated servers. We also have a GoldenGate hub server with the latest Oracle client installed on it. For this configuration, we will be using "pdb1" on both the source and target databases, and a demo schema named "TEST."

Information:

• Source:
  • Hostname: dbsource
  • IP: 192.168.220.09
  • DB: pdb1
  • Schema: Test
  • Oracle: Oracle Database 21c
• GoldenGate HUB:
  • Hostname: goldengate-server
  • IP: 192.168.220.10
  • DB: proddb
  • Oracle: Oracle Client 21c
• Target:
  • Hostname: dbtarget
  • IP: 192.168.220.11
  • DB: pdb1
  • Schema: Test
  • Oracle: Oracle Database 21c

Steps:

1. Edit .bash profile for client installation.

---

2. Download and install Oracle GoldenGate 21c.

---

3. Download and install Oracle Client 21c.

---

4. Configure tnsnames.ora file with entries for source and target DBs.

---

5. Configure GoldenGate settings and configuration.

---

Download the latest version of Oracle GoldenGate and Oracle Client software.

Verify Certification: Certification Link

---

Create a gg_home directory at /u01/app/oracle/product/21.0.0/

---

Unzip the zip file and install GoldenGate at gg Home:

Install Oracle Golden Gate 21c at gg_home directory

/u01/app/oracle/product/21.0.0/gg_home

---

Client Installation:

Install Oracle Client 21c or a later version at the client home directory

/u01/app/oracle/product/21.0.0/client_1/

---

Update the .bash profile with Oracle settings:

####################################################
# Oracle Settings
TMP=/tmp; export TMP
TMPDIR=$TMP; export TMPDIR

ORACLE_HOSTNAME=goldengate-server; export ORACLE_HOSTNAME
ORACLE_BASE=/u01/app/oracle; export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/product/21.0.0/client_1; export ORACLE_HOME
TNS_ADMIN=$ORACLE_BASE/product/21.0.0/client_1/network/admin; export TNS_ADMIN
OGGHOME=/u01/app/oracle/product/21.0.0/gg_home; export OGGHOME
PATH=/usr/sbin:$PATH; export PATH
PATH=$ORACLE_HOME/bin:$PATH; export PATH

LD_LIBRARY_PATH=$ORACLE_HOME/lib: export LD_LIBRARY_PATH
CLASSPATH=$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib; export CLASSPATH
    

---

Make specific changes in both databases while in mount mode:

SQL> alter system set recyclebin=off scope=spfile;
SQL> alter database add supplemental log data;
SQL> alter database archivelog;
SQL> ALTER DATABASE FORCE LOGGING;
SQL> alter system set ENABLE_GOLDENGATE_REPLICATION=true;

SQL> alter database open;
    

---

Create tablespaces and user script for container databases:

We need to create the required tablespaces and admin user on source and target in CDB as well as in all PDBs.

In CDB:
SQL> create tablespace ggtbs datafile '/u01/app/oracle/oradata/CDB1/ggtbs01.dbf' size 1G autoextend on;
SQL> create temporary tablespace ggtemp tempfile '/u01/app/oracle/oradata/CDB1/ggtemp01.dbf' size 1G autoextend on;

In PDBs:
SQL> alter session set container=pdb1;
SQL> create tablespace ggtbs datafile '/u01/app/oracle/oradata/CDB1/pdb1/ggtbs01.dbf' size 1G autoextend on;
SQL> create temporary tablespace ggtemp tempfile '/u01/app/oracle/oradata/CDB1/pdb1/ggtemp01.dbf' size 1G autoextend on;

SQL> alter session set container=pdb2;
SQL> create tablespace ggtbs datafile '/u01/app/oracle/oradata/CDB1/pdb2/ggtbs01.dbf' size 1G autoextend on;
SQL> create temporary tablespace ggtemp tempfile '/u01/app/oracle/oradata/CDB1/pdb2/ggtemp01.dbf' size 1G autoextend on;

Create a Globale User:
SQL> CREATE USER c##ggadmin IDENTIFIED BY ggadmin container=all  default tablespace ggtbs temporary tablespace ggtemp;

Grant privileges:
SQL> GRANT CONNECT, RESOURCE, DBA TO c##ggadmin container=all;
SQL> ALTER USER c##ggadmin QUOTA unlimited ON ggtbs;
SQL> exec dbms_goldengate_auth.grant_admin_privilege('c##ggadmin',container=>'all');
SQL> ALTER USER c##ggadmin SET CONTAINER_DATA=all CONTAINER=current;
    

---

Create subdirectories golden gate server:

mkdir /u01/app/oracle/product/21.0.0/gg_home/dirdat/tr
(we will using this directory for trail files)

cd $GGHome
[oracle@goldengate-server gg_home]$ ./ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 21.3.0.0.0 OGGCORE_21.3.0.0.0_PLATFORMS_210728.1047_FBO
Oracle Linux 7, x64, 64bit (optimized), Oracle Database 21c and lower supported versions on Jul 29 2021 03:59:23
Operating system character set identified as UTF-8.

Copyright (C) 1995, 2021, Oracle and/or its affiliates. All rights reserved.

GGSCI (goldengate-server) 1> create subdirs

Creating subdirectories under current directory ./gg_home

Parameter files                ./gg_home/dirprm: already exists
Report files                   ./gg_home/dirrpt: created
Checkpoint files               ./gg_home/dirchk: created
Process status files           ./gg_home/dirpcs: created
SQL script files               ./gg_home/dirsql: created
Database definitions files     ./gg_home/dirdef: created
Extract data files             ./gg_home/dirdat: created
Temporary files                ./gg_home/dirtmp: created
Stdout files                   ./gg_home/dirout: created
    

---

Run scripts for creating necessary objects for supporting DDL replication on both servers:

@marker_setup.sql
@ddl_setup.sql
@role_setup.sql
@ddl_enable.sql
    

---

Add GoldenGate User to CredentialStore:

We need to add login information in GoldenGate creating a credentialstore and aliases source and target databases:
(Note: Check the tns file at the bottom for database connections).

./ggsci

GGSCI (goldengate-server) 2> add credentialstore
Credential store created.

GGSCI (goldengate-server) 3> alter credentialstore add user c##ggadmin@ggtarget alias ggtarget
Password:
Credential store altered.

GGSCI (goldengate-server) 4> alter credentialstore add user c##ggadmin@ggsource alias ggsource
Password:
Credential store altered.


GGSCI (goldengate-server) 5> info credentialstore

Reading from credential store:

Default domain: OracleGoldenGate

  Alias: ggtarget
  Userid: c##ggadmin@ggtarget

  Alias: ggsource
  Userid: c##ggadmin@ggsource
    

---

Configure the manager and start the service:

GGSCI (goldengate-server) 55> Edit params mgr
port 7809

GGSCI (goldengate-server) 55> start mgr

GGSCI (goldengate-server) 55> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING

    

---

Configure the extract process and start it:

GGSCI (goldengate-server) 1> dblogin useridalias ggsource

GGSCI (goldengate-server) 1> edit params ext1
extract ext1
exttrail /u01/app/oracle/product/21.0.0/gg_home/dirdat/tr
discardfile /u01/app/oracle/product/21.0.0/gg_home/dirrpt/discard01.dsc, append megabytes 50
logallsupcols
DDL include mapped
useridalias ggsource
table test.*;


GGSCI (goldengate-server as c##ggadmin@cdb1) 7> register extract ext1 database

GGSCI (goldengate-server as c##ggadmin@cdb1) 8> add extract ext1, integrated tranlog, begin now
Integrated Extract added.

GGSCI (goldengate-server as c##ggadmin@cdb1) 9>  Add EXTTRAIL /u01/app/oracle/product/21.0.0/gg_home/dirdat/tr, extract ext1
EXTTRAIL added.

-- to view 
ggsci> view params ext1
    

---

Configure the Replicat process:

login to target db
GGSCI (goldengate-server) 1> dblogin useridalias ggtarget
Successfully logged into database.

-- Parameter file for Replicat:
GGSCI (goldengate-server as c##ggadmin@cdb1) 3> edit params rep1

REPLICAT rep1
ASSUMETARGETDEFS
HANDLECOLLISIONS
DISCARDFILE ./dircrd/rep_discard.txt, append, megabytes  10
DDL include ALL
USERIDALLIAS ggtarget
MAP test.*, TARGET test.*;

-- Create checkpoint Table
GGSCI (goldengate-server as c##ggadmin@cdb1) 8> add checkpointtable c##ggadmin.checkpoint
Successfully created checkpoint table c##ggadmin.checkpoint.

-- Add Replicats:
GGSCI (goldengate-server as c##ggadmin@cdb1) 9> ADD REPLICAT rep1 integrated exttrail 
/u01/app/oracle/product/21.0.0/gg_home/dirdat/tr CHECKPOINTTABLE c##ggadmin.checkpoint

Integrated Replicat added.

-- Register Replicats to database:
GGSCI (goldengate-server as c##ggadmin@cdb1) 10> register replicat REP1 database
2023-10-13 17:21:06  INFO    OGG-02528  Replicat group REP1 successfully registered with database as inbound server OGG$REP1.
    

---

Table creation for demo:

CREATE TABLE Test.t1(
    id NUMBER GENERATED BY DEFAULT AS IDENTITY,
    dt VARCHAR2(50) NOT NULL,
    PRIMARY KEY(id)
);
    

---

Add supplemental logging for a table or all schemas:

GGSCI (goldengate-server) 1> add SCHEMATRANDATA test
    

---

Start the Extract and Replicat process:

GGSCI (goldengate-server) 6> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
EXTRACT     STOPPED     EXT1        00:14:23      00:00:14
REPLICAT    STOPPED     REP1        00:00:00      00:00:06

GGSCI (goldengate-server) 7> start ext1

Sending START request to Manager ...
Extract group EXT1 starting.

GGSCI (goldengate-server) 10> start rep1

Sending START request to Manager ...
Replicat group REP1 starting.


GGSCI (goldengate-server) 11> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
EXTRACT     RUNNING     EXT1        00:14:23      00:01:24
REPLICAT    RUNNING     REP1        00:00:00      00:01:15
    

---

Insert some data on source and check the replication:

SQL> insert into t1 values(1,sysdate);
SQL> commit;
SQL> select * from t1;
        ID DT
---------- ---------------------------
         1 13-OCT-23

Check on Target DB:
$  sqlplus test/test@ggtarget

SQL*Plus: Release 21.0.0.0.0 - Production on Sat Oct 14 02:23:02 2023
Version 21.3.0.0.0

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

Last Successful login time: Fri Oct 13 2023 23:48:18 -04:00

Connected to:
Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0

SQL> select * from t1;

        ID DT
---------- ---------------------------
         1 13-OCT-23
         
         
Replicatoin is successfull. You can test it for all DML and DDL operation...
    

---

TNS file use in this demo:

[oracle@goldengate-server gg_home]$ cat /u01/app/oracle/product/21.0.0/client_1/network/admin/tnsnames.ora
GGSOURCE=
   (DESCRIPTION=
        (ENABLE=BROKEN)
        (ADDRESS_LIST=
            (ADDRESS=(PROTOCOL=TCP)(HOST=dbsource)(PORT=1522)))
        (CONNECT_DATA=(SERVICE_NAME=pdb1))
    )

GGTARGET =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = dbtarget)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = pdb1)
    )
  )
    

---

---