Sunday 15 October 2023

Oracle GoldenGate HUB Configuration

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)
    )
  )
No comments:
Subscribe to: Posts (Atom)