Oracle GoldenGate 19c Installation and Configuration on Linux – Complete Step-by-Step Guide

A complete production-ready SOP for installing and configuring Oracle GoldenGate 19c on Linux from scratch. Covers GoldenGate architecture, source and target preparation, Manager configuration, Extract setup, Data Pump configuration, Replicat setup, initial load, DDL replication, monitoring, and full validation — with real commands, expected outputs, and consultant-level notes for both classic and Microservices architecture.

1. Document Info

2. GoldenGate Architecture — Understand Before You Start

📝 What is Oracle GoldenGate? GoldenGate is Oracle’s real-time data replication and integration software. It captures changes from a source database (using Oracle redo logs or other DB transaction logs) and delivers them to a target database with very low latency — typically sub-second. Unlike Data Guard which replicates an entire database, GoldenGate can replicate selected tables, transform data, filter rows, and replicate between different database versions, platforms, and even different database engines.

📝 When is GoldenGate used instead of Data Guard?

• Replication between different Oracle versions (e.g., 11g source to 19c target)
• Replication between different platforms (Oracle to PostgreSQL, Oracle to Kafka)
• Selective table replication (only certain tables, not entire DB)
• Bidirectional replication (active-active)
• Zero-downtime database migrations
• Real-time data warehousing and reporting

Key GoldenGate Components

GoldenGate Data Flow

SOURCE DATABASE                         TARGET DATABASE
─────────────────                       ─────────────────
Oracle Redo Logs
      │
      ▼
  [EXTRACT]  ──── writes ────►  Local Trail Files (/gg/dirdat/lt*)
                                        │
                                        ▼
                                  [DATA PUMP]  ──── TCP/IP ────►  Remote Trail Files (/gg/dirdat/rt*)
                                                                          │
                                                                          ▼
                                                                     [REPLICAT]
                                                                          │
                                                                          ▼
                                                                  TARGET DATABASE

📝 Why have a Data Pump between Extract and Replicat? The Data Pump decouples the source and target. If the target is unavailable (network outage, maintenance), the Extract continues writing to local trail files and the Data Pump queues the data. When the target comes back, the Data Pump sends everything that accumulated. Without a Data Pump (direct delivery mode), Extract would stop or slow down when the target is unavailable.

Classic vs Microservices Architecture

📝 This SOP covers Classic architecture fully. Where Microservices differs significantly, notes are added. For most on-premises Oracle-to-Oracle replication, Classic is still the most widely deployed.

3. Path Conventions and Environment Details

📝 Convention B paths:

• GoldenGate Home: /oracle/GG/19.1 on both servers
• Oracle Home: /oracle/RDBMS/19.31 (source) and /oracle/RDBMS/19.31Standby (target)

📝 Why same port 7809 on both servers? Manager on source and Manager on target both listen on 7809 by default. Since they are on different servers, there is no conflict.

4. Pre-Installation Checks — Both Servers

⚠️ IMPORTANT: Complete all pre-checks on both source and target servers before starting any installation or configuration.

4.1 — Verify Oracle Database Version and Edition

📝 Why? GoldenGate 19c supports specific Oracle database versions. Also, some GoldenGate features (like DDL replication) require Oracle Enterprise Edition. Standard Edition has limitations.

-- On SOURCE database
sqlplus / as sysdba

set linesize 150
set pagesize 50
col banner for a80

SELECT banner FROM v$version;

-- Check if Enterprise Edition
SELECT * FROM v$version WHERE banner LIKE '%Enterprise%';

-- Check database character set
set linesize 150
set pagesize 50
col parameter for a35
col value     for a40

SELECT parameter, value
FROM   nls_database_parameters
WHERE  parameter IN ('NLS_CHARACTERSET','NLS_NCHAR_CHARACTERSET')
ORDER BY parameter;

📎 GoldenGate 19c certification matrix: MOS Doc ID 2193391.1

4.2 — Verify Source Database is in ARCHIVELOG Mode

📝 Why? GoldenGate Extract reads from Oracle redo and archive logs. If the database is not in ARCHIVELOG mode, Extract cannot access committed transactions that have been overwritten in the online redo logs.

-- On SOURCE
sqlplus / as sysdba

set linesize 150
set pagesize 50
col name     for a12
col log_mode for a15

SELECT name, log_mode FROM v$database;

ARCHIVE LOG LIST;

⚠️ IMPORTANT: SOURCE database MUST be in ARCHIVELOG mode. Enable it if not:

SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE ARCHIVELOG;
ALTER DATABASE OPEN;

4.3 — Check Supplemental Logging on Source Database

📝 Why? Supplemental logging adds extra information to Oracle redo records that GoldenGate needs to correctly identify and replicate row changes. Without supplemental logging, GoldenGate cannot determine which row was changed (especially for UPDATE statements on tables without primary keys).

📝 Two levels of supplemental logging:

• Minimal supplemental logging — database level — must always be enabled
• All columns supplemental logging — table or schema level — enables GoldenGate to capture all column values for UPDATE statements (required for most configurations)

-- On SOURCE
-- Check minimal supplemental logging (database level)
set linesize 150
set pagesize 50
col supplemental_log_data_min for a10
col supplemental_log_data_pk  for a10
col supplemental_log_data_all for a10

SELECT supplemental_log_data_min,
       supplemental_log_data_pk,
       supplemental_log_data_ui,
       supplemental_log_data_fk,
       supplemental_log_data_all
FROM   v$database;

What to look for: supplemental_log_data_min must show YES. If it shows NO — enable it now:

-- Enable minimal supplemental logging (database level — required)
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

-- Verify
SELECT supplemental_log_data_min FROM v$database;

4.4 — Verify Network Connectivity Between Source and Target

# From SOURCE — test connectivity to target
ping -c 5 dbserver02

# Test GoldenGate Manager port connectivity (7809)
telnet dbserver02 7809
# OR
nc -zv dbserver02 7809

# From TARGET — test connectivity to source
ping -c 5 dbserver01
nc -zv dbserver01 7809

⚠️ IMPORTANT: Firewall must allow TCP port 7809 (Manager port) between source and target in both directions. Also allow the Data Pump port range (default 7840-7914) from source to target. Check with your network team if ports are blocked.

4.5 — Check Disk Space for GoldenGate Installation and Trail Files

📝 Why? GoldenGate software needs about 2 GB. Trail files can grow significantly depending on transaction volume. Plan trail file storage carefully — if trail files fill up the filesystem, GoldenGate stops replication.

# On BOTH servers
df -hP /u01/app/goldengate

# Minimum space requirements:
# GoldenGate software = 2GB
# Trail files = depends on transaction volume
# Rule of thumb: 3x the peak hourly redo generation rate
# For a 10GB/hour redo database, allocate at least 30GB for trail files

# Check how much redo is being generated on SOURCE
# Run on SOURCE DB:

-- On SOURCE — check redo generation rate
set linesize 150
set pagesize 50
col begin_time for a25
col end_time   for a25
col redo_gb    for 9999.99

SELECT begin_time, end_time,
       blocks * block_size / 1024/1024/1024 redo_gb
FROM   v$archived_log
WHERE  completion_time > SYSDATE - 1
ORDER BY sequence#;

4.6 — Check OS User and Required Permissions

# Verify oracle user exists on both servers
id oracle

# Check oracle user's Oracle environment
su - oracle -c "echo \$ORACLE_HOME"
su - oracle -c "echo \$ORACLE_SID"

# Verify oracle user can connect to database
su - oracle
sqlplus / as sysdba
SELECT status FROM v$instance;
EXIT;

5. Source Database Preparation

⚠️ IMPORTANT: All steps in this section are performed on the SOURCE database server unless stated otherwise.

5.1 — Create GoldenGate Admin User on Source Database

📝 Why? GoldenGate needs a dedicated database user (typically called ggadmin or c##ggadmin for CDB) with specific privileges to connect to the database, read the redo logs, and access the data dictionary. Never use SYS or SYSTEM for GoldenGate — use a dedicated user.

-- On SOURCE database
sqlplus / as sysdba

-- For NON-CDB database (traditional)
CREATE USER ggadmin IDENTIFIED BY GGadmin_123
    DEFAULT TABLESPACE users
    TEMPORARY TABLESPACE temp
    PROFILE DEFAULT;

-- Grant required privileges
GRANT CREATE SESSION     TO ggadmin;
GRANT ALTER SESSION      TO ggadmin;
GRANT SELECT ANY TABLE   TO ggadmin;
GRANT SELECT ANY DICTIONARY TO ggadmin;
GRANT FLASHBACK ANY TABLE TO ggadmin;
GRANT CREATE TABLE       TO ggadmin;
GRANT INSERT ANY TABLE   TO ggadmin;
GRANT UPDATE ANY TABLE   TO ggadmin;
GRANT DELETE ANY TABLE   TO ggadmin;
GRANT DROP ANY TABLE     TO ggadmin;
GRANT EXECUTE ON DBMS_FLASHBACK TO ggadmin;

-- Grant specific GoldenGate required privileges
GRANT SELECT ON DBA_CLUSTERS     TO ggadmin;
GRANT SELECT ON DBA_OBJECTS      TO ggadmin;
GRANT SELECT ON DBA_SEQUENCES    TO ggadmin;
GRANT SELECT ON DBA_TABLES       TO ggadmin;
GRANT SELECT ON DBA_USERS        TO ggadmin;
GRANT SELECT ON V_$DATABASE      TO ggadmin;
GRANT SELECT ON V_$ARCHIVED_LOG  TO ggadmin;
GRANT SELECT ON V_$LOG           TO ggadmin;
GRANT SELECT ON V_$LOGFILE       TO ggadmin;
GRANT SELECT ON V_$NLS_PARAMETERS TO ggadmin;
GRANT SELECT ON V_$PARAMETER     TO ggadmin;

-- Quota on USERS tablespace for checkpoint table
ALTER USER ggadmin QUOTA UNLIMITED ON USERS;

-- Verify user created
SELECT username, account_status, default_tablespace
FROM   dba_users
WHERE  username = 'GGADMIN';

📝 For CDB database (Container Database) — use common user:

-- CDB requires C## prefix for common users
CREATE USER c##ggadmin IDENTIFIED BY GGadmin_123
    DEFAULT TABLESPACE users
    TEMPORARY TABLESPACE temp
    CONTAINER=ALL;

GRANT CREATE SESSION     TO c##ggadmin CONTAINER=ALL;
GRANT ALTER SESSION      TO c##ggadmin CONTAINER=ALL;
GRANT SELECT ANY TABLE   TO c##ggadmin CONTAINER=ALL;
GRANT SELECT ANY DICTIONARY TO c##ggadmin CONTAINER=ALL;
-- ... (same grants as above with CONTAINER=ALL)

5.2 — Enable GoldenGate Replication in Database Parameter

📝 Why? The ENABLE_GOLDENGATE_REPLICATION parameter must be set to TRUE in Oracle 11.2.0.4+ databases. It enables internal Oracle optimizations for GoldenGate and allows GoldenGate to mine the redo logs correctly.

-- On SOURCE
sqlplus / as sysdba

-- Check current value
SHOW PARAMETER enable_goldengate_replication;

-- Enable it
ALTER SYSTEM SET ENABLE_GOLDENGATE_REPLICATION=TRUE SCOPE=BOTH;

-- Verify
SHOW PARAMETER enable_goldengate_replication;

5.3 — Enable Supplemental Logging for Replicated Tables

📝 Why table-level supplemental logging? Database-level minimal supplemental logging (done in Section 4.3) only logs the minimal information needed for Oracle internal operations. For GoldenGate to correctly replicate updates and deletes, it needs all primary key columns (and ideally all columns) to be logged. This must be enabled at the table level.

-- On SOURCE
-- Option 1: Enable for specific tables only (recommended for targeted replication)
ALTER TABLE hr.employees ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;
ALTER TABLE hr.departments ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;

-- Option 2: Enable for all tables in a schema (easier for full schema replication)
-- Run this for each schema being replicated
EXEC DBMS_CAPTURE_ADM.PREPARE_TABLE_INSTANTIATION(table_name=>'HR.EMPLOYEES');

-- Option 3: Enable all-column supplemental logging for entire database
-- Use only if replicating entire database — impacts redo volume
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;

-- Verify supplemental logging for specific tables
set linesize 200
set pagesize 100
col owner           for a20
col log_group_name  for a30
col table_name      for a30
col log_group_type  for a30

SELECT owner, log_group_name, table_name, log_group_type
FROM   dba_log_groups
WHERE  owner = 'HR'
ORDER BY table_name;

5.4 — Create Checkpoint Table on Source Database

📝 Why? The GoldenGate checkpoint table stores the Extract’s read position in the redo logs. If Extract stops and restarts, it reads the checkpoint table to know where it left off — ensuring no transactions are missed or duplicated.

-- Connect to source as ggadmin
sqlplus ggadmin/GGadmin_123@SOURCEDB

-- Create checkpoint table
-- This table is managed entirely by GoldenGate — do not manually insert/delete
-- The table name here must match what you specify in the GLOBALS file
EXEC DBMS_APPLICATION_INFO.SET_MODULE('GoldenGate','Checkpoint');

📝 Note: The actual checkpoint table creation is done from GGSCI after GoldenGate is installed (Section 7). The database user just needs the privilege to create tables in its default tablespace (granted in Section 5.1).

6. Target Database Preparation

⚠️ IMPORTANT: All steps in this section are performed on the TARGET database server.

6.1 — Create GoldenGate Admin User on Target Database

-- On TARGET database
sqlplus / as sysdba

-- Create GoldenGate user on target
CREATE USER ggadmin IDENTIFIED BY GGadmin_123
    DEFAULT TABLESPACE users
    TEMPORARY TABLESPACE temp
    PROFILE DEFAULT;

-- Target ggadmin needs DML privileges on replicated tables
GRANT CREATE SESSION     TO ggadmin;
GRANT ALTER SESSION      TO ggadmin;
GRANT CREATE TABLE       TO ggadmin;
GRANT INSERT ANY TABLE   TO ggadmin;
GRANT UPDATE ANY TABLE   TO ggadmin;
GRANT DELETE ANY TABLE   TO ggadmin;
GRANT SELECT ANY TABLE   TO ggadmin;
GRANT SELECT ANY DICTIONARY TO ggadmin;
GRANT FLASHBACK ANY TABLE TO ggadmin;

-- Quota for checkpoint table
ALTER USER ggadmin QUOTA UNLIMITED ON USERS;

-- Verify
SELECT username, account_status FROM dba_users WHERE username = 'GGADMIN';

6.2 — Enable GoldenGate Replication on Target Database

-- On TARGET database
sqlplus / as sysdba

ALTER SYSTEM SET ENABLE_GOLDENGATE_REPLICATION=TRUE SCOPE=BOTH;
SHOW PARAMETER enable_goldengate_replication;

6.3 — Create Target Schema and Tables

📝 Why? GoldenGate Replicat does not create tables — it only applies DML. The target schema and tables must exist before Replicat starts. They must have the same structure as the source tables.

-- On TARGET database
-- Create the schema/user that will hold replicated data
sqlplus / as sysdba

CREATE USER hr IDENTIFIED BY Hr_123
    DEFAULT TABLESPACE users
    TEMPORARY TABLESPACE temp;

GRANT CREATE SESSION, RESOURCE TO hr;
ALTER USER hr QUOTA UNLIMITED ON USERS;

-- Create tables matching source structure exactly
-- Example for HR.EMPLOYEES table
sqlplus hr/Hr_123@TARGETDB

CREATE TABLE hr.employees (
    employee_id    NUMBER(6)     NOT NULL,
    first_name     VARCHAR2(20),
    last_name      VARCHAR2(25)  NOT NULL,
    email          VARCHAR2(25)  NOT NULL,
    phone_number   VARCHAR2(20),
    hire_date      DATE          NOT NULL,
    job_id         VARCHAR2(10)  NOT NULL,
    salary         NUMBER(8,2),
    commission_pct NUMBER(2,2),
    manager_id     NUMBER(6),
    department_id  NUMBER(4),
    CONSTRAINT emp_emp_id_pk PRIMARY KEY (employee_id)
);

📝 Tip: Use Oracle Data Pump (expdp/impdp) with CONTENT=METADATA_ONLY to export the DDL from source and import it on target. This is faster and more reliable than manually recreating tables when you have many tables.

# On SOURCE — export schema structure only (no data)
expdp system/Oracle_123@SOURCEDB \
    SCHEMAS=HR \
    CONTENT=METADATA_ONLY \
    DUMPFILE=hr_structure.dmp \
    LOGFILE=hr_structure_exp.log \
    DIRECTORY=DATA_PUMP_DIR

# Copy dump file to target
scp /oracle/admin/SOURCEDB/dpdump/hr_structure.dmp \
    oracle@dbserver02:/oracle/admin/TARGETDB/dpdump/

# On TARGET — import structure only
impdp system/Oracle_123@TARGETDB \
    SCHEMAS=HR \
    CONTENT=METADATA_ONLY \
    DUMPFILE=hr_structure.dmp \
    LOGFILE=hr_structure_imp.log \
    DIRECTORY=DATA_PUMP_DIR \
    TABLE_EXISTS_ACTION=REPLACE

6.4 — Create Checkpoint Table on Target Database

📝 Why? The Replicat process uses a checkpoint table on the TARGET database to track which trail file records it has applied. If Replicat stops and restarts, it reads this checkpoint to resume from exactly where it left off — preventing duplicate applies.

📝 This is done from GGSCI after GoldenGate is installed — referenced here so you know why the ggadmin user needs CREATE TABLE privilege on target.

7. GoldenGate Software Installation

⚠️ IMPORTANT: Install GoldenGate on BOTH source and target servers. The installation steps are identical on both — only the configuration after installation differs.

7.1 — Download and Stage GoldenGate Software

# Download GoldenGate 19c for Oracle on Linux x86-64 from MOS
# MOS → Patches & Updates → search "GoldenGate 19.1" for Linux

# Verify checksum
sha256sum /stage/goldengate/191004_fbo_ggs_Linux_x64_Oracle_shiphome.zip

# Stage on both servers
ls -lh /stage/goldengate/191004_fbo_ggs_Linux_x64_Oracle_shiphome.zip

# Copy to target server if needed
scp /stage/goldengate/191004_fbo_ggs_Linux_x64_Oracle_shiphome.zip \
    oracle@dbserver02:/stage/goldengate/

7.2 — Create GoldenGate Directory Structure

# On BOTH servers as oracle user (or root — then chown to oracle)
su - oracle

# Create GoldenGate home directory
mkdir -p /u01/app/goldengate/19.1

# Convention B
# mkdir -p /oracle/GG/19.1

# Set ownership
chown -R oracle:oinstall /u01/app/goldengate
chmod -R 775 /u01/app/goldengate

# Verify
ls -ld /u01/app/goldengate/19.1

7.3 — Install GoldenGate Software (Silent Installation)

📝 Why silent? Same reasons as Oracle DB silent install — no GUI required, repeatable, scriptable.

# On BOTH servers as oracle user
su - oracle

# Unzip the GoldenGate installer
cd /stage/goldengate
unzip -q 191004_fbo_ggs_Linux_x64_Oracle_shiphome.zip

# Navigate to installer directory
cd /stage/goldengate/fbo_ggs_Linux_x64_shiphome/Disk1

# Create silent response file
cat > /tmp/gg_install.rsp << 'EOF'
oracle.install.responseFileVersion=/oracle/install/rspfmt_ogginstall_response_schema_v19_1_0
INSTALL_OPTION=ORA19c
SOFTWARE_LOCATION=/u01/app/goldengate/19.1
START_MANAGER=false
MANAGER_PORT=7809
DATABASE_LOCATION=/u01/app/oracle/product/19.3.0/dbhome_1
INVENTORY_LOCATION=/u01/app/oraInventory
UNIX_GROUP_NAME=oinstall
EOF

# Run silent installation
./runInstaller -silent \
    -responseFile /tmp/gg_install.rsp \
    -ignoreSysPrereqs \
    -showProgress

# Monitor installation log
tail -100f /tmp/OraInstall*/installActions*.log

📝 For Convention B — change SOFTWARE_LOCATION:

SOFTWARE_LOCATION=/oracle/GG/19.1
DATABASE_LOCATION=/oracle/RDBMS/19.31

Expected completion message:

The installation of Oracle GoldenGate for Oracle 19c was successful.

7.4 — Set GoldenGate Environment Variables

# Add to oracle user's ~/.bash_profile on BOTH servers
su - oracle
vi ~/.bash_profile

Add at the end:

bash

# -------------------------------------------------------
# GoldenGate Environment Variables
# -------------------------------------------------------

# GoldenGate Home directory
export GG_HOME=/u01/app/goldengate/19.1
# Convention B: export GG_HOME=/oracle/GG/19.1

# Add GoldenGate to PATH
export PATH=$GG_HOME:$PATH

# LD_LIBRARY_PATH — GoldenGate needs Oracle libraries
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$GG_HOME:$LD_LIBRARY_PATH

# Convenient alias to launch GGSCI from anywhere
alias ggsci='cd $GG_HOME && ./ggsci'
alias ggstart='cd $GG_HOME && ./ggsci <<EOF\nSTART MANAGER\nEXIT\nEOF'

# Apply profile
. ~/.bash_profile

# Verify
echo $GG_HOME
ls $GG_HOME/ggsci

7.5 — Create GoldenGate Subdirectories

📝 Why? GoldenGate needs specific subdirectories for parameter files, trail files, report files, checkpoint files, and log files. The CREATE SUBDIRS command in GGSCI creates all of them automatically.

# On BOTH servers
su - oracle
cd $GG_HOME

# Launch GGSCI
./ggsci

-- Inside GGSCI on BOTH servers
GGSCI> CREATE SUBDIRS

Expected output:

Creating subdirectories under current directory /u01/app/goldengate/19.1

Parameter files:   /u01/app/goldengate/19.1/dirprm: created
Report files:      /u01/app/goldengate/19.1/dirrpt: created
Checkpoint files:  /u01/app/goldengate/19.1/dirchk: created
Process status:    /u01/app/goldengate/19.1/dirpcs: created
SQL script files:  /u01/app/goldengate/19.1/dirsql: created
Database definitions: /u01/app/goldengate/19.1/dirdef: created
Extract data files: /u01/app/goldengate/19.1/dirdat: created
Temporary files:   /u01/app/goldengate/19.1/dirtmp: created
Credential store:  /u01/app/goldengate/19.1/dircrd: created
Masterkey wallet:  /u01/app/goldengate/19.1/dirwlt: created
Dump files:        /u01/app/goldengate/19.1/dirdmp: created

GGSCI> EXIT

7.6 — Create GLOBALS File on Both Servers

📝 Why? The GLOBALS file contains GoldenGate-wide settings that apply to all processes on this server. The most important setting is CHECKPOINTTABLE — the name of the checkpoint table in the database that all Extract/Replicat processes will use to store their positions.

# On SOURCE server
su - oracle
cd $GG_HOME

# Create GLOBALS file
# The GLOBALS file must be named exactly "GLOBALS" (no extension) in GG_HOME
vi $GG_HOME/GLOBALS

Add to GLOBALS file on SOURCE:

-- GoldenGate GLOBALS file
-- This file is read at GGSCI startup — applies to all processes

-- Checkpoint table — GoldenGate creates and manages this table
-- Must match the user.table format — ggadmin owns it
CHECKPOINTTABLE ggadmin.ggchkpt

-- GoldenGate Manager port
-- Must be consistent across all config files
GGSCHEMA ggadmin

# On TARGET server
vi $GG_HOME/GLOBALS

Add to GLOBALS file on TARGET:

-- GoldenGate GLOBALS file — TARGET SERVER
CHECKPOINTTABLE ggadmin.ggchkpt
GGSCHEMA ggadmin

7.7 — Create GoldenGate Credential Store and Add Database Login

📝 Why use credential store? Instead of putting database passwords in plain text in parameter files, GoldenGate’s credential store (wallet) stores encrypted credentials. You reference credentials by an alias. This is much more secure and is the recommended approach.

# On SOURCE server — launch GGSCI
su - oracle
cd $GG_HOME
./ggsci

-- Create credential store
GGSCI> CREATE CREDENTIALSTORE

-- Add source database credentials
-- Alias SOURCEDB will be used in parameter files instead of password
GGSCI> ADD CREDENTIALSTORE
GGSCI> ALTER CREDENTIALSTORE ADD USER ggadmin PASSWORD GGadmin_123 ALIAS SOURCEDB

-- Verify credential was added
GGSCI> INFO CREDENTIALSTORE

# On TARGET server — launch GGSCI
su - oracle
cd $GG_HOME
./ggsci

GGSCI> CREATE CREDENTIALSTORE
GGSCI> ALTER CREDENTIALSTORE ADD USER ggadmin PASSWORD GGadmin_123 ALIAS TARGETDB
GGSCI> INFO CREDENTIALSTORE

8. Manager Configuration

📝 What is Manager? Manager is the GoldenGate controller process. It must be running before any Extract, Data Pump, or Replicat can be started. Manager monitors all other GG processes and restarts them if they stop (based on AUTORESTART configuration). Think of Manager as the GoldenGate supervisor.

8.1 — Create Manager Parameter File on Source

# On SOURCE server
su - oracle
cd $GG_HOME

# Edit Manager parameter file
# The Manager parameter file is always named mgr.prm
vi $GG_HOME/dirprm/mgr.prm

Add the following to mgr.prm on SOURCE:

-- -------------------------------------------------------
-- GoldenGate Manager Parameter File — SOURCE SERVER
-- File: $GG_HOME/dirprm/mgr.prm
-- -------------------------------------------------------

-- Manager listens on this port
-- Data Pump on source connects to Manager on target via this port
PORT 7809

-- Dynamic port range for Collector processes
-- When Data Pump sends data, Manager on target allocates a port from this range
-- Must be open in firewall from source to target
DYNAMICPORTLIST 7840-7914

-- Purge old trail files automatically
-- MINKEEPDAYS = keep trail files for at least 3 days
-- MAXKEEPDAYS = delete trail files after 7 days
-- This prevents trail files from filling up the filesystem
PURGEOLDEXTRACTS ./dirdat/*, USECHECKPOINTS, MINKEEPDAYS 3, MAXKEEPDAYS 7

-- Auto-restart processes if they abort
-- WAITMINUTES = wait 2 minutes before restarting
-- RESETMINUTES = reset the restart count after 60 minutes of stable operation
AUTORESTART EXTRACT *, WAITMINUTES 2, RESETMINUTES 60
AUTORESTART REPLICAT *, WAITMINUTES 2, RESETMINUTES 60

-- Write Manager status messages to report file
LAGREPORTMINUTES 30
LAGREPORTONLYIFBEHIND
LAGINFOMINUTES 10
LAGCRITICALMINUTES 30

8.2 — Create Manager Parameter File on Target

# On TARGET server
su - oracle
vi $GG_HOME/dirprm/mgr.prm

Add the following to mgr.prm on TARGET:

-- -------------------------------------------------------
-- GoldenGate Manager Parameter File — TARGET SERVER
-- File: $GG_HOME/dirprm/mgr.prm
-- -------------------------------------------------------

PORT 7809

DYNAMICPORTLIST 7840-7914

-- Purge remote trail files on target
PURGEOLDEXTRACTS ./dirdat/*, USECHECKPOINTS, MINKEEPDAYS 3, MAXKEEPDAYS 7

AUTORESTART REPLICAT *, WAITMINUTES 2, RESETMINUTES 60

LAGREPORTMINUTES 30
LAGREPORTONLYIFBEHIND
LAGINFOMINUTES 10
LAGCRITICALMINUTES 30

8.3 — Start Manager on Both Servers

# On SOURCE server — start Manager
su - oracle
cd $GG_HOME
./ggsci

-- Add Manager to GoldenGate registry
GGSCI> ADD MANAGER

-- Start Manager
GGSCI> START MANAGER

-- Verify Manager is running
GGSCI> INFO MANAGER

Expected output:

Manager is running (IP port dbserver01.7809, Process ID 12345).

# On TARGET server — start Manager
su - oracle
cd $GG_HOME
./ggsci

GGSCI> ADD MANAGER
GGSCI> START MANAGER
GGSCI> INFO MANAGER

8.4 — Create Checkpoint Table on Both Databases

📝 Why do this now? Manager must be running before you can create the checkpoint table. GGSCI connects to the database (using the credentials we set up) and creates the table automatically.

# On SOURCE server — create checkpoint table in source DB
su - oracle
cd $GG_HOME
./ggsci

-- Login to source database from GGSCI
GGSCI> DBLOGIN USERIDALIAS SOURCEDB

-- Create checkpoint table (name must match GLOBALS file)
GGSCI> ADD CHECKPOINTTABLE ggadmin.ggchkpt

-- Verify
GGSCI> INFO CHECKPOINTTABLE ggadmin.ggchkpt

# On TARGET server — create checkpoint table in target DB
su - oracle
cd $GG_HOME
./ggsci

GGSCI> DBLOGIN USERIDALIAS TARGETDB
GGSCI> ADD CHECKPOINTTABLE ggadmin.ggchkpt
GGSCI> INFO CHECKPOINTTABLE ggadmin.ggchkpt
GGSCI> EXIT

9. Extract Configuration (Source Server)

📝 What does Extract do? Extract is the GoldenGate capture process. It attaches to the Oracle redo log mining infrastructure (LogMiner or its own internal log reader) and reads every committed transaction from the redo logs. It writes these changes to local trail files in a GoldenGate binary format.

9.1 — Create Extract Parameter File

# On SOURCE server
su - oracle
vi $GG_HOME/dirprm/extora.prm

Add the following content:

-- -------------------------------------------------------
-- GoldenGate Extract Parameter File
-- Process Name: EXTORA
-- File: $GG_HOME/dirprm/extora.prm
-- Purpose: Capture changes from SOURCEDB and write to local trail files
-- -------------------------------------------------------

-- Extract process name (must match what you use in ADD EXTRACT command)
EXTRACT extora

-- Use credential alias instead of plaintext password
USERIDALIAS SOURCEDB

-- Set the Oracle home for this extract
-- Required when running GoldenGate against Oracle database
TRANLOGOPTIONS DBLOGREADER

-- Report file — Extract writes status messages here
REPORT AT 01:00
REPORTCOUNT EVERY 60 MINUTES, RATE

-- Discard file — records that Extract cannot process go here for review
DISCARDFILE ./dirrpt/extora.dsc, APPEND, MEGABYTES 1000

-- Maximum size of each discard file before rollover
DISCARDROLLOVER AT 02:00

-- Write to local trail files
-- lt = local trail prefix, up to 2 characters
-- GoldenGate creates files like lt000000, lt000001 etc.
EXTTRAIL ./dirdat/lt

-- Table list — specify which tables to capture
-- Use wildcards for entire schemas or list individual tables
-- Format: schema.table
TABLE hr.employees;
TABLE hr.departments;
TABLE hr.jobs;
TABLE hr.locations;
TABLE hr.countries;
TABLE hr.regions;

-- To replicate an entire schema use wildcard:
-- TABLE hr.*;

-- To replicate multiple schemas:
-- TABLE hr.*;
-- TABLE sales.*;

9.2 — Register Extract with Database (Required for 12c+)

📝 Why register? In Oracle 12c and above, GoldenGate Extract must be registered with the database’s LogMiner infrastructure. This tells Oracle that GoldenGate is mining the redo logs and Oracle should retain archivelogs until GoldenGate has processed them (prevents Extract from losing data if archivelogs are deleted before Extract reads them).

su - oracle
cd $GG_HOME
./ggsci

-- Login to source database
GGSCI> DBLOGIN USERIDALIAS SOURCEDB

-- Register the Extract with the database
-- This creates a LogMiner registration
GGSCI> REGISTER EXTRACT extora DATABASE

-- Verify registration
GGSCI> INFO EXTRACT extora, SHOWCH

9.3 — Add Extract Process

📝 Why TRANLOG? TRANLOG tells GoldenGate this Extract captures directly from Oracle transaction logs (redo logs). The alternative SOURCEISTABLE is used for initial loads.

📝 Why BEGIN NOW? This tells Extract to start capturing from the current time — not from the beginning of the redo logs. For initial setup, BEGIN NOW means we will do an initial load separately to synchronize existing data, then the Extract captures only new changes going forward.

# On SOURCE server — launch GGSCI
./ggsci

-- Login to database
GGSCI> DBLOGIN USERIDALIAS SOURCEDB

-- Add Extract process
-- TRANLOG = capture from transaction logs
-- BEGIN NOW = start capturing from current SCN (not historical)
GGSCI> ADD EXTRACT extora, TRANLOG, BEGIN NOW

-- Add local trail file
-- EXTTRAIL = local trail, lt = prefix, MEGABYTES 500 = rotate when 500MB
GGSCI> ADD EXTTRAIL ./dirdat/lt, EXTRACT extora, MEGABYTES 500

-- Verify Extract was added
GGSCI> INFO EXTRACT extora

10. Data Pump Configuration (Source Server)

📝 What is Data Pump in GoldenGate? GoldenGate’s Data Pump (not to be confused with Oracle Data Pump expdp/impdp) is a secondary Extract process that reads the local trail files written by the primary Extract and sends them over the network to the target server. It writes to remote trail files on the target.

10.1 — Create Data Pump Parameter File

# On SOURCE server
su - oracle
vi $GG_HOME/dirprm/pmpgora.prm

Add the following content:

-- -------------------------------------------------------
-- GoldenGate Data Pump Parameter File
-- Process Name: PMPGORA
-- File: $GG_HOME/dirprm/pmpgora.prm
-- Purpose: Read local trail files and send to target server
-- -------------------------------------------------------

-- This is a PUMP (secondary Extract) process
EXTRACT pmpgora

-- Use credential alias for source database
-- Data Pump needs DB connection for DDL and metadata operations
USERIDALIAS SOURCEDB

-- Pass-through mode — Data Pump just forwards data without transformation
-- If you need transformation, remove PASSTHRU and add mapping rules
PASSTHRU

-- Read from local trail files (written by primary Extract)
RMTHOST dbserver02.company.com, MGRPORT 7809, COMPRESS

-- Write to remote trail files on target server
-- rt = remote trail prefix
RMTTRAIL ./dirdat/rt, MEGABYTES 500

-- Discard file
DISCARDFILE ./dirrpt/pmpgora.dsc, APPEND, MEGABYTES 500

-- Table list must match or be a subset of Extract's table list
TABLE hr.employees;
TABLE hr.departments;
TABLE hr.jobs;
TABLE hr.locations;
TABLE hr.countries;
TABLE hr.regions;

📝 What does COMPRESS do? Compresses the redo data before sending over the network. Reduces bandwidth usage significantly — typically 60-70% compression on text-heavy data. Has minor CPU cost on source. Recommended for WAN connections.

📝 What is PASSTHRU? Tells the Data Pump to simply forward records as-is without any processing or transformation. Remove this if you want the Data Pump to perform filtering or column mapping.

10.2 — Add Data Pump Process

# On SOURCE server — launch GGSCI
./ggsci

-- Login to database
GGSCI> DBLOGIN USERIDALIAS SOURCEDB

-- Add Data Pump process
-- EXTTRAILSOURCE = reads from local trail files (not directly from redo)
GGSCI> ADD EXTRACT pmpgora, EXTTRAILSOURCE ./dirdat/lt

-- Add remote trail file on target
GGSCI> ADD RMTTRAIL ./dirdat/rt, EXTRACT pmpgora, MEGABYTES 500

-- Verify Data Pump was added
GGSCI> INFO EXTRACT pmpgora

11. Replicat Configuration (Target Server)

📝 What does Replicat do? Replicat reads the remote trail files on the target server and applies each captured change to the target database using SQL DML statements (INSERT, UPDATE, DELETE). It maintains transaction order and atomicity.

11.1 — Create Replicat Parameter File

# On TARGET server
su - oracle
vi $GG_HOME/dirprm/repgora.prm

Add the following content:

-- -------------------------------------------------------
-- GoldenGate Replicat Parameter File
-- Process Name: REPGORA
-- File: $GG_HOME/dirprm/repgora.prm
-- Purpose: Apply changes from remote trail files to TARGETDB
-- -------------------------------------------------------

-- Replicat process name
REPLICAT repgora

-- Use credential alias for target database
USERIDALIAS TARGETDB

-- Assume target rows already exist for UPDATE/DELETE without checking source
-- Improves performance — disable if you need strict row checking
ASSUMETARGETDEFS

-- Handle errors — discard records that fail to apply (log them, do not stop)
-- Change to ABEND if you want Replicat to stop on any error
REPERROR DEFAULT, DISCARD

-- Discard file — failed records written here
DISCARDFILE ./dirrpt/repgora.dsc, APPEND, MEGABYTES 1000

-- Report status every 60 minutes
REPORT AT 01:00
REPORTCOUNT EVERY 60 MINUTES, RATE

-- Map source tables to target tables
-- Format: MAP source_schema.table, TARGET target_schema.table
-- Use identical names here since source and target schema names are same
MAP hr.employees,   TARGET hr.employees;
MAP hr.departments, TARGET hr.departments;
MAP hr.jobs,        TARGET hr.jobs;
MAP hr.locations,   TARGET hr.locations;
MAP hr.countries,   TARGET hr.countries;
MAP hr.regions,     TARGET hr.regions;

-- To map entire schema (wildcard):
-- MAP hr.*, TARGET hr.*;

-- Example of column transformation (rename column):
-- MAP hr.employees, TARGET hr.employees,
--   COLMAP (
--     employee_id = employee_id,
--     full_name   = CONCAT(first_name, ' ', last_name)
--   );

📝 What is ASSUMETARGETDEFS? This tells Replicat that source and target table definitions are identical — same column names, same data types, same order. This avoids the overhead of comparing definitions on every record. Remove this parameter and create a defgen definitions file if source and target structures differ.

11.2 — Add Replicat Process

# On TARGET server — launch GGSCI
./ggsci

-- Login to target database
GGSCI> DBLOGIN USERIDALIAS TARGETDB

-- Add Replicat process
-- EXTTRAIL = read from remote trail files (written by Data Pump)
-- CHECKPOINTTABLE = use checkpoint table to track position
GGSCI> ADD REPLICAT repgora, EXTTRAIL ./dirdat/rt, CHECKPOINTTABLE ggadmin.ggchkpt

-- Verify Replicat was added
GGSCI> INFO REPLICAT repgora

12. Initial Load — Synchronize Existing Data

📝 Why initial load? The Extract we configured in Section 9 captures only NEW changes going forward from the point it was added. But the source tables already have existing data that needs to be copied to the target. The initial load copies all existing data from source to target so that Replicat starts with a consistent baseline.

📝 Initial load must happen AFTER Extract is added and running so that any changes during the initial load are captured by Extract and delivered to the target by Replicat after the initial load completes.

12.1 — Method 1: Initial Load Using Oracle Data Pump (Recommended)

📝 Why Data Pump for initial load? It is the fastest method for large databases. It exports data directly from source to target without going through GoldenGate trail files.

# STEP 1 — On SOURCE: Note the current SCN before starting export
sqlplus / as sysdba

-- Note this SCN — you will need it to configure Replicat start position
SELECT current_scn FROM v$database;
-- Example output: 1234567

# STEP 2 — Export data from source using Data Pump
expdp system/Oracle_123@SOURCEDB \
    SCHEMAS=HR \
    CONTENT=DATA_ONLY \
    DUMPFILE=hr_data.dmp \
    LOGFILE=hr_data_exp.log \
    DIRECTORY=DATA_PUMP_DIR \
    FLASHBACK_SCN=1234567

# STEP 3 — Copy dump to target
scp /oracle/admin/SOURCEDB/dpdump/hr_data.dmp \
    oracle@dbserver02:/oracle/admin/TARGETDB/dpdump/

# STEP 4 — Import data into target
impdp system/Oracle_123@TARGETDB \
    SCHEMAS=HR \
    CONTENT=DATA_ONLY \
    DUMPFILE=hr_data.dmp \
    LOGFILE=hr_data_imp.log \
    DIRECTORY=DATA_PUMP_DIR \
    TABLE_EXISTS_ACTION=REPLACE

# STEP 5 — After import completes
# Start Replicat at the SCN noted in Step 1
# This ensures Replicat applies only changes that happened AFTER the export
su - oracle
cd $GG_HOME
./ggsci

GGSCI> DBLOGIN USERIDALIAS TARGETDB

-- Start Replicat from the SCN captured before the export
GGSCI> ADD REPLICAT repgora, EXTTRAIL ./dirdat/rt, \
         CHECKPOINTTABLE ggadmin.ggchkpt, BEGIN 2024-01-15 10:30:00
-- OR use SCN:
GGSCI> ALTER REPLICAT repgora, SCN 1234567

12.2 — Verify Row Counts Match After Initial Load

-- On SOURCE
sqlplus hr/Hr_123@SOURCEDB

set linesize 150
set pagesize 50

SELECT 'EMPLOYEES'   table_name, COUNT(*) row_count FROM hr.employees   UNION ALL
SELECT 'DEPARTMENTS' table_name, COUNT(*) row_count FROM hr.departments UNION ALL
SELECT 'JOBS'        table_name, COUNT(*) row_count FROM hr.jobs        UNION ALL
SELECT 'LOCATIONS'   table_name, COUNT(*) row_count FROM hr.locations
ORDER BY 1;

-- On TARGET — same query
sqlplus hr/Hr_123@TARGETDB

SELECT 'EMPLOYEES'   table_name, COUNT(*) row_count FROM hr.employees   UNION ALL
SELECT 'DEPARTMENTS' table_name, COUNT(*) row_count FROM hr.departments UNION ALL
SELECT 'JOBS'        table_name, COUNT(*) row_count FROM hr.jobs        UNION ALL
SELECT 'LOCATIONS'   table_name, COUNT(*) row_count FROM hr.locations
ORDER BY 1;

⚠️ IMPORTANT: Row counts must match on source and target before starting Replicat. If they do not match, investigate the import log for errors and fix before proceeding.

13. Start GoldenGate Processes and Verify

📝 Startup sequence matters: Always start processes in this order:

1. Manager (already running from Section 8)
2. Extract (on source)
3. Data Pump (on source)
4. Replicat (on target)

13.1 — Start Extract on Source

# On SOURCE server
su - oracle
cd $GG_HOME
./ggsci

-- Verify Manager is running first
GGSCI> INFO MANAGER

-- Start Extract
GGSCI> START EXTRACT extora

-- Verify Extract started
GGSCI> INFO EXTRACT extora

Expected output:

EXTRACT    EXTORA    Last Started 2024-01-15 10:45   Status RUNNING
Checkpoint Lag       00:00:00 (updated 00:00:02 ago)
Log Read Checkpoint  Oracle Redo Logs
                     2024-01-15 10:45:00  Seqno 100, RBA 1234

13.2 — Start Data Pump on Source

-- Still in GGSCI on SOURCE
GGSCI> START EXTRACT pmpgora

-- Verify Data Pump started
GGSCI> INFO EXTRACT pmpgora

Expected output:

EXTRACT    PMPGORA   Last Started 2024-01-15 10:46   Status RUNNING
Checkpoint Lag       00:00:00 (updated 00:00:01 ago)

13.3 — Start Replicat on Target

# On TARGET server
su - oracle
cd $GG_HOME
./ggsci

-- Verify Manager is running
GGSCI> INFO MANAGER

-- Start Replicat
GGSCI> START REPLICAT repgora

-- Verify Replicat started
GGSCI> INFO REPLICAT repgora

Expected output:

REPLICAT   REPGORA   Last Started 2024-01-15 10:47   Status RUNNING
Checkpoint Lag       00:00:00 (updated 00:00:03 ago)

13.4 — View All Processes Status Together

-- On SOURCE — view all processes
GGSCI> INFO ALL

Expected output on SOURCE:

Program     Status      Group       Lag at Chkpt  Time Since Chkpt
MANAGER     RUNNING
EXTRACT     RUNNING     EXTORA      00:00:00      00:00:05
EXTRACT     RUNNING     PMPGORA     00:00:00      00:00:05

-- On TARGET — view all processes
GGSCI> INFO ALL

Expected output on TARGET:

Program     Status      Group       Lag at Chkpt  Time Since Chkpt
MANAGER     RUNNING
REPLICAT    RUNNING     REPGORA     00:00:00      00:00:05

14. End-to-End Replication Test

📝 Why test? This is the ultimate proof that GoldenGate is working end-to-end. Insert a row on source, verify it appears on target within seconds.

14.1 — Insert Test Row on Source

-- On SOURCE database
sqlplus hr/Hr_123@SOURCEDB

-- Insert a test record
INSERT INTO hr.employees VALUES (
    999,
    'GoldenGate',
    'Test',
    'GGTEST',
    '555-0199',
    SYSDATE,
    'IT_PROG',
    5000,
    NULL,
    100,
    60
);
COMMIT;

-- Confirm the row is in source
SELECT employee_id, first_name, last_name
FROM   hr.employees
WHERE  employee_id = 999;

14.2 — Verify Row Appears on Target

-- On TARGET database — wait 5-10 seconds then check
sqlplus hr/Hr_123@TARGETDB

-- Check if row replicated
SELECT employee_id, first_name, last_name
FROM   hr.employees
WHERE  employee_id = 999;

What to look for: The exact same row must appear on target within seconds.

14.3 — Test UPDATE Replication

-- On SOURCE
UPDATE hr.employees
SET    salary = 6000
WHERE  employee_id = 999;
COMMIT;

-- On TARGET — verify update replicated
SELECT employee_id, first_name, salary
FROM   hr.employees
WHERE  employee_id = 999;
-- salary must show 6000

14.4 — Test DELETE Replication

-- On SOURCE
DELETE FROM hr.employees WHERE employee_id = 999;
COMMIT;

-- On TARGET — verify delete replicated
SELECT COUNT(*) FROM hr.employees WHERE employee_id = 999;
-- Must return 0

15. DDL Replication Configuration

📝 What is DDL replication? DDL (Data Definition Language) replication captures and replicates structural changes — CREATE TABLE, ALTER TABLE, DROP TABLE, CREATE INDEX etc. — from source to target. Without DDL replication, if someone adds a column to a table on source, you must manually add it on target too or Replicat will fail.

📝 DDL replication requires Oracle Enterprise Edition and specific GoldenGate setup.

15.1 — Install DDL Replication Objects on Source

# On SOURCE server — run DDL setup scripts as SYSDBA
su - oracle
cd $GG_HOME

sqlplus / as sysdba

-- Run GoldenGate DDL setup script
-- This creates internal GoldenGate objects for DDL capture
@marker_setup.sql
-- When prompted: enter ggadmin as the GoldenGate schema

@ddl_setup.sql
-- When prompted: enter ggadmin and confirm

@role_setup.sql
-- Creates GGS_GGSUSER_ROLE

-- Grant DDL role to ggadmin
GRANT GGS_GGSUSER_ROLE TO ggadmin;

@ddl_enable.sql
-- Enables DDL triggers for capture

15.2 — Add DDL Parameter to Extract

# On SOURCE — edit Extract parameter file to add DDL capture
vi $GG_HOME/dirprm/extora.prm

Add DDL section after existing content:

-- Add DDL replication
-- Capture all DDL on the replicated schemas
DDL INCLUDE MAPPED

-- Optional: exclude specific DDL operations
-- DDL INCLUDE MAPPED EXCLUDE OBJNAME hr.temp_*

15.3 — Add DDL Parameter to Replicat

bash

# On TARGET — edit Replicat parameter file to add DDL apply
vi $GG_HOME/dirprm/repgora.prm

Add DDL section:

-- Apply DDL received from source
DDL

-- Optional error handling for DDL
DDLERROR DEFAULT IGNORE RETRYOP MAXRETRIES 3 RETRYDELAY 5

15.4 — Restart Extract and Replicat After DDL Changes

# On SOURCE
./ggsci

GGSCI> STOP EXTRACT extora
GGSCI> STOP EXTRACT pmpgora
GGSCI> START EXTRACT extora
GGSCI> START EXTRACT pmpgora
GGSCI> INFO ALL

# On TARGET
./ggsci

GGSCI> STOP REPLICAT repgora
GGSCI> START REPLICAT repgora
GGSCI> INFO ALL

16. Monitoring GoldenGate

📝 Why monitor? GoldenGate processes can lag, stop, or encounter errors silently. Regular monitoring ensures replication is current and any issues are caught early before they become a problem.

16.1 — Check All Process Status

su - oracle
cd $GG_HOME
./ggsci

-- Quick status of all processes
GGSCI> INFO ALL

-- Detailed status of specific process
GGSCI> INFO EXTRACT extora, DETAIL
GGSCI> INFO EXTRACT pmpgora, DETAIL
GGSCI> INFO REPLICAT repgora, DETAIL

16.2 — Check Lag

📝 What is lag? Lag is the time difference between when a transaction was committed on the source and when it was applied on the target. A lag of 0 means the target is fully current. Lag of 5 minutes means the target is 5 minutes behind the source.

-- Check lag on Extract (how far behind source DB)
GGSCI> LAG EXTRACT extora

-- Check lag on Replicat (how far behind received trail files)
GGSCI> LAG REPLICAT repgora

-- Check lag with statistics
GGSCI> STATS EXTRACT extora, TOTAL
GGSCI> STATS REPLICAT repgora, TOTAL

16.3 — View Process Report Files

📝 Why? Report files contain detailed information about what each GoldenGate process is doing, any warnings, and statistics. They are the first place to look when troubleshooting.

-- View Extract report file
GGSCI> VIEW REPORT extora

-- View Data Pump report file
GGSCI> VIEW REPORT pmpgora

-- View Replicat report file
GGSCI> VIEW REPORT repgora

16.4 — View Discard Files

📝 Why? Discard files contain records that GoldenGate could not process or apply. These indicate data integrity issues that must be investigated.

# Check if discard files have content
ls -lh $GG_HOME/dirrpt/*.dsc

# View discard file contents
cat $GG_HOME/dirrpt/repgora.dsc

# Check Extract discard file
cat $GG_HOME/dirrpt/extora.dsc

16.5 — Check GoldenGate Trail Files

-- List all trail files and their status
GGSCI> INFO EXTTRAIL ./dirdat/lt*
GGSCI> INFO RMTTRAIL ./dirdat/rt*

-- Check trail file details
GGSCI> INFO EXTTRAIL ./dirdat/lt, DETAIL

# Check trail file sizes on filesystem
ls -lh $GG_HOME/dirdat/lt*   # Source local trails
ls -lh $GG_HOME/dirdat/rt*   # Target remote trails

# Check total size
du -sh $GG_HOME/dirdat/

16.6 — Monitor via SQL — GoldenGate Statistics

-- On SOURCE — check GoldenGate heartbeat table if configured
-- Check GoldenGate supplemental logging is still active
SELECT supplemental_log_data_min FROM v$database;

-- Check LogMiner registration for Extract
SELECT capture_name, status, captured_scn, applied_scn
FROM   dba_capture
ORDER BY capture_name;

-- Check archive destination errors
SELECT dest_id, status, error
FROM   v$archive_dest
WHERE  status != 'INACTIVE';

17. Post-Installation Checks

⚠️ IMPORTANT: Complete all post-installation checks before handing over the GoldenGate configuration to the application team or declaring the setup complete.

17.1 — Verify All Processes Are Running

# On SOURCE
su - oracle
cd $GG_HOME
./ggsci

GGSCI> INFO ALL

All processes must show RUNNING. Any ABENDED process means there is an error.

17.2 — Verify Lag is Zero or Near Zero

GGSCI> LAG EXTRACT extora
GGSCI> LAG REPLICAT repgora

17.3 — Verify No Records in Discard Files

# Discard files should be empty or not exist
ls -lh $GG_HOME/dirrpt/*.dsc
wc -l $GG_HOME/dirrpt/repgora.dsc

17.4 — Verify Checkpoint Table Has Entries

-- On SOURCE database
sqlplus ggadmin/GGadmin_123@SOURCEDB

set linesize 200
set pagesize 100
col group_name  for a15
col log_cmplt   for a25
col log_read    for a25

SELECT group_name, log_cmplt, log_read
FROM   ggadmin.ggchkpt
ORDER BY group_name;

-- On TARGET database
sqlplus ggadmin/GGadmin_123@TARGETDB

SELECT group_name, log_cmplt, log_read
FROM   ggadmin.ggchkpt
ORDER BY group_name;

17.5 — Verify Supplemental Logging is Active

-- On SOURCE
sqlplus / as sysdba

-- Database level
SELECT supplemental_log_data_min FROM v$database;

-- Table level — verify for all replicated tables
set linesize 200
set pagesize 100
col owner          for a15
col log_group_name for a30
col table_name     for a25
col log_group_type for a30

SELECT owner, log_group_name, table_name, log_group_type
FROM   dba_log_groups
WHERE  owner = 'HR'
ORDER BY table_name;

17.6 — Check GoldenGate Alert Logs

# GoldenGate writes errors to ggserr.log (main error log)
# This is one of the first places to check for any issues
tail -200 $GG_HOME/ggserr.log

# Check for errors or warnings
grep -E "ERROR|WARNING|ABEND" $GG_HOME/ggserr.log | tail -50

17.7 — Verify ENABLE_GOLDENGATE_REPLICATION on Both DBs

-- On SOURCE and TARGET
sqlplus / as sysdba

SHOW PARAMETER enable_goldengate_replication;
-- Must show: TRUE

17.8 — Final Row Count Comparison

-- On SOURCE
sqlplus hr/Hr_123@SOURCEDB

set linesize 150
set pagesize 50
col table_name for a20
col row_count  for 9999999

SELECT 'EMPLOYEES'   table_name, COUNT(*) row_count FROM hr.employees   UNION ALL
SELECT 'DEPARTMENTS' table_name, COUNT(*) row_count FROM hr.departments UNION ALL
SELECT 'JOBS'        table_name, COUNT(*) row_count FROM hr.jobs        UNION ALL
SELECT 'LOCATIONS'   table_name, COUNT(*) row_count FROM hr.locations   UNION ALL
SELECT 'COUNTRIES'   table_name, COUNT(*) row_count FROM hr.countries   UNION ALL
SELECT 'REGIONS'     table_name, COUNT(*) row_count FROM hr.regions
ORDER BY 1;

-- On TARGET — same query — all counts must match
sqlplus hr/Hr_123@TARGETDB

SELECT 'EMPLOYEES'   table_name, COUNT(*) row_count FROM hr.employees   UNION ALL
SELECT 'DEPARTMENTS' table_name, COUNT(*) row_count FROM hr.departments UNION ALL
SELECT 'JOBS'        table_name, COUNT(*) row_count FROM hr.jobs        UNION ALL
SELECT 'LOCATIONS'   table_name, COUNT(*) row_count FROM hr.locations   UNION ALL
SELECT 'COUNTRIES'   table_name, COUNT(*) row_count FROM hr.countries   UNION ALL
SELECT 'REGIONS'     table_name, COUNT(*) row_count FROM hr.regions
ORDER BY 1;

18. Quick Reference Card

This SOP covers everything you need to install and configure Oracle GoldenGate 19c on Linux from scratch without referring to any other source. Always enable supplemental logging before starting Extract, always use credential store instead of plain text passwords in parameter files, always perform an end-to-end DML test before declaring the configuration ready, and always monitor the ggserr.log and discard files regularly to catch any replication issues early.