Oracle GoldenGate 21c for Oracle on Linux

Implementing Oracle Golden Gate 21c for Oracle Multi-Tenant architecture become challenging as you deal with multiple containers, users and tables. Let's install and setup a single table replication from source to target with Oracle 21c databases. Here are some of the assumptions:

Both source and target are Oracle 21c Multi-Tenant Database
From source, we will replicate PDB.FOX.TAB1 table to target PDB.FOX.TAB1
Both source & target tables will remain empty
We will start GoldenGate Extract, Pump & Replicate
Finally, we will insert rows on the source table and see if it reflects on target

Our table TAB1 is owned by FOX user inside a PDB on source

Pre-Requisites

These pre-requisites are must on Linux server for smooth Golden Gate implementation:

Open port 7809 - 7820 on servers which will be used by GoldenGate
Make sure /etc/hosts file is updated with source & target server details
Source DB must run in archivelog mode
Optionally you can disable firewall on the servers (only if you have key based entry to servers)

systemctl stop firewalld
systemctl disable firewalld

Update Bash Profile

We need to have below three environment variables added to the Oracle user's bash_profile on source & target servers

export GG_HOME=/u01/app/oracle/product/gg
export TNS_ADMIN=/u01/app/oracle/product/21.0/db_home/network/admin
export LD_LIBRARY_PATH=/u01/app/oracle/product/21.0/db_home/lib

Create the GG_HOME directory where we will be installing Golden Gate

mkdir -p /u01/app/oracle/product/gg

Install GoldenGate 21c on Linux

Download Oracle GoldenGate 21c for Oracle on Linux and unzip under /tmp location

On Source & Target
==================
cd /tmp
unzip 213000_fbo_ggs_Linux_x64_Oracle_shiphome.zip

# get inside uzipped folder
cd fbo_ggs_Linux_x64_Oracle_shiphome/Disk1

# install GoldenGate in silent mode
./runInstaller -silent -showProgress -waitforcompletion oracle.install.responseFileVersion=/oracle/install/rspfmt_ogginstall_response_schema_v21_1_0 INSTALL_OPTION=ora21c SOFTWARE_LOCATION=${GG_HOME}

Create Sub-Directories & Start Manager

The Golden Gate sub directories are the default locations where checkpoint file, parameter files and other trail files are kept for replication purpose

On Source & Target
==================
cd $GG_HOME
./ggsci

GGSCI> create subdirs

The manager runs on default port 7809. Update the parameter file and start manager

On Source & Target
==================
GGSCI> edit param mgr

PORT 7809		--> just add one line

GGSCI> start mgr

You can verify status of Golden Gate processes via info command

GGSCI> info all

Prepare Source & Target DB For GoldenGate

In Oracle Multi-Tenant architecture, the Extract runs at CDB$ROOT level so it can read transactions from any PDB. While the Replicat runs at PDB level where you would like to directly apply transactions.

For both Extract and Replicat to work smoothly, we create a common user C##GGADMIN with DBA privileges on both source & target CDBs. Let's enable supplemental log data, force loggin and GoldenGate replication parameters on source

On source CDB$ROOT Container
============================
ALTER DATABASE FORCE LOGGING;
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
ALTER SYSTEM SET ENABLE_GOLDENGATE_REPLICATION=true;

Create a dedicated tablespace for C##GGADMIN user in all containers on source & target

If you don't create dedicated tablespace in all containers, the common user creation will fail

On source & target CDB$ROOT Container
=====================================
ALTER SYSTEM SET db_create_file_dest='/u01';

On all source & target containers (All PDBs)
============================================
CREATE TABLESPACE GG_DATA DATAFILE SIZE 1G;

Create the common user with necessary privileges on source & target CDBs

On source & taget CDB$ROOT container
====================================
CREATE USER C##GGADMIN IDENTIFIED BY passw0rd CONTAINER=all DEFAULT TABLESPACE GG_DATA TEMPORARY TABLESPACE temp;

GRANT RESOURCE, CREATE SESSION, CREATE VIEW, CREATE TABLE, ALTER SYSTEM, ALTER USER, SELECT ANY DICTIONARY, SELECT ANY TRANSACTION to c##ggadmin;

GRANT CONNECT, DBA to c##ggadmin CONTAINER=all; 
ALTER USER c##ggadmin SET CONTAINER_DATA=all CONTAINER=current;
ALTER USER c##ggadmin QUOTA unlimited ON GG_DATA;
EXEC DBMS_GOLDENGATE_AUTH.GRANT_ADMIN_PRIVILEGE('c##ggadmin');

Setup Listener & TNS Entries

When working with multiple Containers, it's good idea to have dedicated TNS entries so it's easy to connect directly to a particular user inside container from Golden Gate processes. Setup Listener on both Source & Target

On Source & Target (Update IPs accordingly)
===========================================
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.7)(PORT = 1521))
    )
  )

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (ORACLE_HOME = /u01/app/oracle/product/21.0/db_home)
      (SID_NAME = CDB)
    )
    (SID_DESC =
      (ORACLE_HOME = /u01/app/oracle/product/21.0/db_home)
      (SID_NAME = PDB)
    )
  )

Setup TNS entries on both source & target containers

On Source & Target
==================
AKS01_CDB =
 (DESCRIPTION = 
   (ADDRESS_LIST =
     (ADDRESS = (PROTOCOL = TCP)(HOST = 142.132.183.72)(PORT = 1521))
   )
 (CONNECT_DATA =
   (SERVICE_NAME = CDB)
 )
)

AKS01_PDB =
 (DESCRIPTION = 
   (ADDRESS_LIST =
     (ADDRESS = (PROTOCOL = TCP)(HOST = 142.132.183.72)(PORT = 1521))
   )
 (CONNECT_DATA =
   (SERVICE_NAME = PDB)
 )
)

AKS02_CDB =
 (DESCRIPTION = 
   (ADDRESS_LIST =
     (ADDRESS = (PROTOCOL = TCP)(HOST = 91.107.195.151)(PORT = 1521))
   )
 (CONNECT_DATA =
   (SERVICE_NAME = CDB)
 )
)

AKS02_PDB =
 (DESCRIPTION = 
   (ADDRESS_LIST =
     (ADDRESS = (PROTOCOL = TCP)(HOST = 91.107.195.151)(PORT = 1521))
   )
 (CONNECT_DATA =
   (SERVICE_NAME = PDB)
 )
)

Create Test User & Table Inside PDB

Let's create a FOX user on both Source & Target PDBs with TAB1 table without any rows

On Source & Target DBs
======================
alter session set container=pdb; 	-- switch to pdb container

Create user fox identified by fox;
Grant connect, resource to fox;
Alter user fox quota unlimited on system;

CREATE TABLE fox.tab1
(
 c1 NUMBER PRIMARY KEY,
 c2 VARCHAR2(30),
 c3 NUMBER,
 c4 NUMBER
);

Add GoldenGate Extract on Source

Connect to ggsci and login to database using the common user into CDB$ROOT container. On source extract should connect to CDB$ROOT container

cd $GG_HOME
./ggsci

GGSCI> dblogin userid c##ggadmin@AKS01_CDB password passw0rd

Notice the tns entry AKS01_CDB which connects to root container

Enable supplemental logging from Golden Gate at schema level

GGSCI> add schematrandata pdb.fox;

Add Extract, attach to source PDB container and add trail file

GGSCI> add extract ext1, integrated tranlog, begin now
GGSCI> register extract ext1 database container (PDB)
GGSCI> add exttrail ./dirdat/ex, extract ext1

Edit Extract parameters and add following details

GGSCI> edit param ext1

EXTRACT ext1
USERID c##ggadmin@AKS01_CDB, PASSWORD passw0rd
EXTTRAIL ./dirdat/ex
TABLE pdb.fox.tab1;

Note the Table parameter is three part (container).(user).(table) and User id connects to the root container CDB

Start the extract process on source

GGSCI> start ext1

Add GoldenGate Pump on Source

The pump process will read from the extract trail file and send it to remote host

GGSCI> Add extract dp1, EXTTRAILSOURCE ./dirdat/ex
GGSCI> Add rmttrail /u01/app/oracle/product/gg/dirdat/rt, extract dp1

Edit the pump parameters

GGSCI> edit param dp1

EXTRACT dp
USERID c##ggadmin@AKS01_CDB, PASSWORD passw0rd
RMTHOST aks02, MGRPORT 7809
RMTTRAIL /u01/app/oracle/product/gg/dirdat/rt
TABLE pdb.fox.tab1;

Note the pump also connects to root container on source, reads from extract trail and creates a remote trail file on remote host aks02

Start the pump process on source

GGSCI> start dp1

Add GoldenGate Replicate on Target

The replicat process on target runs at PDB level which will apply transactions coming from source directly onto a PDB

cd $GG_HOME
./ggsci

GGSCI> dblogin userid c##ggadmin@AKS02_PDB password passw0rd

Notice the tns entry AKS02_PDB which connects to PDB container

Add replicat which will read from remote trail file sent by pump process and apply to PDB

GGSCI> add replicat rep1, integrated exttrail /u01/app/oracle/product/gg/dirdat/rt

Edit the replicat parameter

GGSCI> edit param rep1

REPLICAT rep1
USERID c##ggadmin@AKS02_PDB, PASSWORD passw0rd
ASSUMETARGETDEFS
MAP PDB.FOX.TAB1 TARGET PDB.FOX.TAB1;

Note the Table parameter is three part (container).(user).(table) and User id connects to the PDB on target. Not the CDB$ROOT container.

Start replicat process on target

start rep1

Verify Replication

Now that all our Golden Gate process are setup on source and target, let's insert few records on source table and verify it they reflect on target target

On Source
=========
sqlplus fox/fox@aks01_pdb

INSERT INTO tab1 VALUES (1,'Alpha',10,100);
INSERT INTO tab1 VALUES (2,'Beta',20,200);
commit;

You should now see the same records inserted into the target table! Enjoy!!!