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:
- Edit .bash profile for client installation.
- Download and install Oracle GoldenGate 21c.
- Download and install Oracle Client 21c.
- Configure tnsnames.ora file with entries for source and target DBs.
- 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_homeClient 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)
)
)
