ORACLE GOLDENGATE

  

 

 

ORACLE GOLDENGATE

Configuration & Architecture Guide

Real-Time Data Replication for Oracle Database 19c

 1. Overview

Oracle GoldenGate is a real-time, log-based change data capture (CDC) and replication solution that enables high-availability, zero-downtime data integration between Oracle database environments. It works by reading transaction logs on the source database and applying those changes to a target database with minimal latency.

This document covers the complete configuration of a GoldenGate replication pipeline including database preparation, Extract, Data Pump, and Replicat setup for the LOGIUSER schema tables.

 

1.1 Architecture Summary

Component	Process Name	Host	Port
Extract	shipep	ipfix01.example.com	8706 (MGR)
Data Pump	shippump	ipfix01.example.com	8706 (MGR)
Replicat	shipr	ipfix01.example.com	7809 (MGR)
Manager (src)	MANAGER	Source	8706
Manager (tgt)	MANAGER	Target	7809

 

2. Preparing the Database for GoldenGate

Before GoldenGate can capture changes, the Oracle source database must be configured to support log-based replication. The following steps must be completed as SYSDBA.

2.1 Enable Archive Log Mode

GoldenGate requires the database to operate in ARCHIVELOG mode so that completed redo log files are retained for capture.

SQL> SHUTDOWN IMMEDIATE;    

SQL> STARTUP MOUNT;

SQL> ALTER DATABASE ARCHIVELOG;

SQL> ALTER DATABASE FORCE LOGGING;

SQL> ALTER DATABASE OPEN;

 

Set the archive log destination and format:

ALTER SYSTEM SET log_archive_dest_1='LOCATION=/u01/oracle/archivelogs' SCOPE=SPFILE;

ALTER SYSTEM SET log_archive_format='arch_%t_%s_%r.arc' SCOPE=SPFILE;

 

2.2 Enable Supplemental Logging

Supplemental logging ensures that enough column data is recorded in redo logs for GoldenGate to reconstruct DML operations at the target.

SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

SQL> ALTER DATABASE FORCE LOGGING;

 

Verify both settings are active:

SQL> SELECT supplemental_log_data_min, force_logging FROM v$database;

Expected result: YES / YES for both columns.

 

2.3 Enable GoldenGate Replication Parameter

The database parameter ENABLE_GOLDENGATE_REPLICATION must be set to TRUE to activate GoldenGate-specific database services:

SQL> ALTER SYSTEM SET ENABLE_GOLDENGATE_REPLICATION=true SCOPE=BOTH;

 

2.4 Switch Log File

Force a log switch to apply all configuration changes immediately:

SQL> ALTER SYSTEM SWITCH LOGFILE;

 

3. Creating the GoldenGate Database User

A dedicated GoldenGate administrator account (ggadmin) must be created on both source and target databases with the required privileges.

3.1 Create User and Grant Privileges

CREATE USER ggadmin IDENTIFIED BY password DEFAULT TABLESPACE GG_DATA TEMPORARY TABLESPACE temp;

GRANT CREATE SESSION TO ggadmin;

GRANT CREATE VIEW TO ggadmin;

GRANT CONNECT TO ggadmin;

GRANT RESOURCE TO ggadmin;

GRANT ALTER SYSTEM TO ggadmin;

GRANT SELECT ANY DICTIONARY TO ggadmin;

 

3.2 Grant GoldenGate Admin Privilege

EXEC DBMS_GOLDENGATE_AUTH.GRANT_ADMIN_PRIVILEGE('ggadmin');

ALTER USER ggadmin QUOTA UNLIMITED ON GG_DATA;

 

4. GoldenGate Installation Configuration

The GoldenGate software is installed using a silent response file. The key installation parameters used in this configuration are:

Parameter	Value
INSTALL_OPTION	ORA19c
SOFTWARE_LOCATION	/u02/app/oracle/gghome/19c
START_MANAGER	true
MANAGER_PORT	8706
DATABASE_LOCATION	/u02/app/oracle/product/19c/db_1
INVENTORY_LOCATION	/u02/app/oracle/oraInventory
UNIX_GROUP_NAME	oinstall

 

5. Table-Level Supplemental Logging

5.1 Configure Credential Store

Create a GoldenGate credential store and add the database user alias:

GGSCI> ADD CREDENTIALSTORE

GGSCI> ALTER CREDENTIALSTORE ADD USER ggadmin ALIAS gguser

GGSCI> DBLOGIN USERIDALIAS gguser

 

5.2 Add TRANDATA for Replicated Tables

Enable table-level supplemental logging for each table in the LOGIUSER schema:

GGSCI> ADD TRANDATA LOGIUSER.DRIVERS

GGSCI> ADD TRANDATA LOGIUSER.SHIPMENTS

GGSCI> ADD TRANDATA LOGIUSER.VEHICLES

 

Note: Warning OGG-01896 (identity column not supported) was raised for all three tables. This means Extract will skip identity-column changes but all other DML operations will still be captured.

 

Key columns identified by GoldenGate:

•       LOGIUSER.DRIVERS — key column: DRIVER_ID

•       LOGIUSER.SHIPMENTS — key column: SHIPMENT_ID

•       LOGIUSER.VEHICLES — key column: VEHICLE_ID

 

6. Configuring Extract (Primary Capture)

The primary Extract process (shipep) runs in integrated capture mode, reading logical change records directly from the Oracle logmining server.

6.1 Register Extract with Mining Database

GGSCI> DBLOGIN USERIDALIAS gguser

GGSCI> REGISTER EXTRACT shipep DATABASE

 

6.2 Extract Parameter File (shipep)

Edit the parameter file with the following configuration:

EXTRACT shipep

USERIDALIAS gguser

DDL INCLUDE MAPPED

EXTTRAIL /u02/app/oracle/gghome/19c/dirdat/lt

SEQUENCE LOGIUSER.SHIPMENTS_seq;

TABLE LOGIUSER.*;

 

6.3 Add Extract Process Group

ADD EXTRACT shipep, INTEGRATED TRANLOG, BEGIN NOW

ADD EXTTRAIL /u02/app/oracle/gghome/19c/dirdat/lt, EXTRACT shipep

 

7. Configuring the Data Pump

The Data Pump (shippump) reads the local trail file and forwards changes to the target system over TCP/IP. It operates in pass-through mode with no data transformation.

7.1 Data Pump Parameter File (shippump)

EXTRACT shippump

USERIDALIAS gguser

RMTHOST ipfix01.example.com, MGRPORT 7809

RMTTRAIL /u02/app/oracle/gghome2/19c/dirdat/rt

SEQUENCE LOGIUSER.SHIPMENTS_seq;

TABLE LOGIUSER.*;

 

7.2 Add Data Pump Process Group

ADD EXTRACT shippump, EXTTRAILSOURCE /u02/app/oracle/gghome/19c/dirdat/lt

ADD RMTTRAIL /u02/app/oracle/gghome2/19c/dirdat/rt, EXTRACT shippump

 

8. Configuring Replicat (Target Apply)

Before configuring Replicat, the following prerequisites must be completed on the target system:

1.    Prepare the target Oracle Database for GoldenGate.

2.    Establish GoldenGate credentials on the target.

3.    Choose the appropriate capture and apply modes.

4.    Configure the Manager process on the target system.

 

8.1 Target Credential Store

GGSCI> ADD CREDENTIALSTORE

GGSCI> ALTER CREDENTIALSTORE ADD USER ggadmin2 ALIAS gguser2

GGSCI> DBLOGIN USERIDALIAS gguser2

 

8.2 Create Checkpoint Tables

Checkpoint tables track Replicat's position in the trail and enable recovery after failure:

DBLOGIN USERIDALIAS gguser2

ADD CHECKPOINTTABLE GGADMIN2.DRIVERS

ADD CHECKPOINTTABLE GGADMIN2.SHIPMENTS

ADD CHECKPOINTTABLE GGADMIN2.VEHICLES

 

8.3 Replicat Parameter File (shipr)

REPLICAT shipr

USERIDALIAS gguser2

ASSUMETARGETDEFS

MAP LOGIUSER.*, TARGET LOGIUSER2.*;

 

8.4 Add Replicat Process Group

DBLOGIN USERIDALIAS gguser2

ADD REPLICAT shipr, INTEGRATED, EXTTRAIL /u02/app/oracle/gghome2/19c/dirdat/rt

 

9. The Manager Process

The Manager process is the control and coordination hub for Oracle GoldenGate. It must be running on both source and target before any other GoldenGate process can start.

9.1 Responsibilities

•       Starts, stops, and monitors all GoldenGate processes (Extract, Pump, Replicat)

•       Listens on a designated TCP port and authorizes incoming Data Pump connections

•       Manages trail file aging and purging to prevent disk exhaustion

•       Auto-restarts failed processes based on configured rules

•       Provides reporting via the GGSCI info all command

 

9.2 Manager on the Target Side

On the target system, the Manager has additional responsibilities specific to receiving replication:

•       Accepts incoming TCP connections from the source Data Pump on port 7809

•       Directs incoming trail data to the remote trail location (dirdat/rt)

•       Supervises the Replicat process and coordinates checkpoint updates

•       Purges consumed remote trail segments to reclaim disk space

 

9.3 Verified Running Status

The final info all output confirmed all processes were RUNNING with zero lag:

 

Program	Status	Group	Lag at Chkpt	Time Since Chkpt
MANAGER	RUNNING	-	-	-
EXTRACT	RUNNING	SHIPEP	00:00:00	00:00:07
EXTRACT	RUNNING	SHIPPUMP	00:00:00	00:00:06
MANAGER	RUNNING	-	-	-
REPLICAT	RUNNING	SHIPR	00:00:00	00:00:03

 

10. TNS Configuration for Integrated Processes

Integrated capture and integrated Replicat each require a dedicated SERVER=DEDICATED connection entry in tnsnames.ora. A shared server connection is not supported.

GGSR =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.68.121)(PORT = 1521)))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = GGSR)

    )

  )

 

11. Starting the Replication Pipeline

Start processes in the following order to bring the full pipeline online:

11.1 Source System

5.    START MANAGER

6.    START EXTRACT shipep

7.    START EXTRACT shippump

 

11.2 Target System

8.    START MANAGER

9.    START REPLICAT shipr

 

11.3 Verify Status

GGSCI> INFO ALL

All processes should show RUNNING status with 00:00:00 lag.

 

12. Common Warnings and Notes

Warning / Code	Explanation
OGG-01896	Identity column not supported. The table will be ignored by Extract for identity column changes. All other DML is captured normally.
OGG-15132	Supplemental redo data logging enabled for the table. Informational — configuration was successful.
OGG-15133	TRANDATA for scheduling columns added. Ensures GoldenGate can correctly schedule and sequence operations on the table.
ORA-01031	Insufficient privileges when using LOGIUSER2 schema for checkpoint tables. Resolved by using GGADMIN2 schema instead.

 

 

End of Document — Oracle GoldenGate Configuration Guide

ORACLE DATA GUARD (Broker Configuration Guide)

ORACLE DATA GUARD

Broker Configuration Guide

Step-by-Step Setup & Switchover Procedures

Primary: ORAP  |  Standby: ORAS

1. Pre-Configuration Checks

 

Before configuring the DataGuard Broker, verify the database role and switchover status on both the primary and standby databases.

 

1.1  Query Database Status

Run the following SQL query on both PRIMARY and STANDBY:

 

-- Run on PRIMARY and STANDBY

SELECT DB_UNIQUE_NAME, DATABASE_ROLE, SWITCHOVER_STATUS, OPEN_MODE

FROM V\$DATABASE;

 

ℹ️  Confirm DATABASE_ROLE shows PRIMARY on the primary node and PHYSICAL STANDBY on the standby node before proceeding.

 

2. Enable DataGuard Broker

 

Enable the DataGuard Broker on both the PRIMARY and STANDBY databases by setting the DG_BROKER_START parameter.

 

2.1  Set DG_BROKER_START on Both Nodes

-- Execute on PRIMARY AND STANDBY

ALTER SYSTEM SET DG_BROKER_START=TRUE SCOPE=BOTH;

 

⚠️  Run this command on BOTH servers. Skipping either node will prevent the broker from starting correctly.

 

3. Clear Archive Log Destination on Standby

 

On the standby server (ORAS), clear the LOG_ARCHIVE_DEST_2 parameter. The broker will manage log shipping after configuration.

 

3.1  Clear LOG_ARCHIVE_DEST_2  (Run on STANDBY — ORAS)

-- Run on STANDBY (oras) only

ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='';

 

4. Create DataGuard Broker Configuration

 

Connect to DGMGRL on the PRIMARY server and create the broker configuration.

 

4.1  Connect to DGMGRL on Primary

dgmgrl sys/password as sysdba

 

4.2  Check Existing Configuration

DGMGRL> show configuration;

 

4.3  Create the Broker Configuration

Create a new configuration named 'ora' with the primary database 'orap':

DGMGRL> create configuration 'ora'

           as primary database is 'orap'

           connect identifier is orap;

 

4.4  Add the Standby Database

Add the standby database 'oras' as a Physical Standby:

DGMGRL> add database 'oras'

           as connect identifier is 'oras'

           maintained as physical;

 

4.5  Verify Configuration

DGMGRL> show configuration;

 

5. Set Static Connect Identifiers

 

Set the static connect identifiers for both the primary and standby databases to allow the broker to restart instances if needed.

 

5.1  Primary Database (ORAP) — HOST: WIN19PRIMARY, PORT: 1522

DGMGRL> edit database orap set property

  staticconnectidentifier=

  '(DESCRIPTION=

     (ADDRESS=(PROTOCOL=TCP)(PORT=1522)(HOST=WIN19PRIMARY))

     (CONNECT_DATA=

       (SERVICE_NAME=ora)(INSTANCE_NAME=ora)(SERVER=DEDICATED)

     )

   )';

 

5.2  Standby Database (ORAS) — HOST: WIN19STANDBY, PORT: 1522

DGMGRL> edit database oras set property

  staticconnectidentifier=

  '(DESCRIPTION=

     (ADDRESS=(PROTOCOL=TCP)(PORT=1522)(HOST=WIN19STANDBY))

     (CONNECT_DATA=

       (SERVICE_NAME=ora)(INSTANCE_NAME=ora)(SERVER=DEDICATED)

     )

   )';

 

6. Set Lag Threshold Properties

 

Configure the ApplyLagThreshold and TransportLagThreshold to 0 on both databases to enable alerting on any lag.

 

6.1  Primary Database Properties

DGMGRL> edit database orap set property ApplyLagThreshold=0;

DGMGRL> edit database orap set property TransportLagThreshold=0;

 

6.2  Standby Database Properties

DGMGRL> edit database oras set property ApplyLagThreshold=0;

DGMGRL> edit database oras set property TransportLagThreshold=0;

 

7. Enable Configuration & Verify

 

7.1  Enable the Broker Configuration

DGMGRL> enable configuration;

 

7.2  Verify Configuration Status

DGMGRL> show configuration;

 

ℹ️  The configuration status should show SUCCESS for both databases. If any errors appear, resolve them before proceeding to switchover.

 

8. Perform Switchover

 

Perform a controlled switchover from the primary database (ORAP) to the standby database (ORAS).

 

⚠️  Ensure all application connections are quiesced before performing a switchover in a production environment.

 

8.1  Initiate Switchover to ORAS

DGMGRL> switchover to 'oras';

 

8.2  Verify Roles in SQL*Plus After Switchover

-- Run in SQL*Plus on both nodes

SELECT DB_UNIQUE_NAME, DATABASE_ROLE, SWITCHOVER_STATUS

FROM V\$DATABASE;

 

9. Data Replication Verification

 

After switchover, verify that data replication is working correctly by creating a test table and inserting rows on the new primary.

 

9.1  Create Employee Test Table

DROP TABLE employee;

COMMIT;

 

CREATE TABLE employee (

    emp_id          INT,

    emp_name        VARCHAR2(12),

    date_of_joining TIMESTAMP DEFAULT systimestamp

);

 

9.2  Insert Initial Records

INSERT INTO employee (emp_id, emp_name) VALUES (1, 'Rock');

INSERT INTO employee (emp_id, emp_name) VALUES (2, 'Water');

COMMIT;

 

ALTER SYSTEM CHECKPOINT;

ALTER SYSTEM SWITCH LOGFILE;

ALTER SYSTEM ARCHIVE LOG CURRENT;

 

COLUMN DATE_OF_JOINING FORMAT A30;

SELECT * FROM employee;

 

9.3  Insert Second Batch & Verify

INSERT INTO employee (emp_id, emp_name) VALUES (3, 'Air');

COMMIT;

 

ALTER SYSTEM CHECKPOINT;

ALTER SYSTEM SWITCH LOGFILE;

ALTER SYSTEM ARCHIVE LOG CURRENT;

 

COLUMN DATE_OF_JOINING FORMAT A30;

SELECT * FROM employee;

 

9.4  Insert Third Batch & Verify

INSERT INTO employee (emp_id, emp_name) VALUES (4, 'Stone');

COMMIT;

 

ALTER SYSTEM CHECKPOINT;

ALTER SYSTEM SWITCH LOGFILE;

ALTER SYSTEM ARCHIVE LOG CURRENT;

 

COLUMN DATE_OF_JOINING FORMAT A30;

SELECT * FROM employee;

 

10. Final Status Check

 

Run the following query to get a consolidated view of the database role, switchover status, and host information:

 

10.1  Comprehensive Status Query

COLUMN DB_UNIQUE_NAME    FORMAT A10;

COLUMN DATABASE_ROLE     FORMAT A20;

COLUMN SWITCHOVER_STATUS FORMAT A20;

COLUMN HOST_NAME         FORMAT A15;

COLUMN MACHINE           FORMAT A15;

 

host cls

 

SELECT host_name, db_unique_name, database_role, switchover_status

FROM v\$database, v\$instance;

 

11. Reference Commands

 

ℹ️  The following command is provided for reference only. Use it to start managed recovery on the standby if needed outside of broker management.

 

11.1  Start Managed Standby Recovery (Reference Only)

-- Reference ONLY

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE

    DISCONNECT FROM SESSION;

 

Summary — Configuration Steps

 

Step	Action	Description
1	Pre-Check Database Status	Query V$DATABASE for role, switchover status, and open mode on both nodes.
2	Enable DG Broker	Set DG_BROKER_START=TRUE SCOPE=BOTH on PRIMARY and STANDBY.
3	Clear Archive Dest	Set LOG_ARCHIVE_DEST_2='' on the standby server (ORAS).
4	Create Configuration	Use DGMGRL to create the 'ora' configuration with primary 'orap' and add standby 'oras'.
5	Set Static Connect IDs	Configure staticconnectidentifier property for both ORAP and ORAS.
6	Set Lag Thresholds	Set ApplyLagThreshold and TransportLagThreshold to 0 on both databases.
7	Enable & Verify	Run 'enable configuration' and confirm SUCCESS status.
8	Switchover	Execute switchover to 'oras' and verify database roles in SQL*Plus.
9	Test Replication	Insert rows into the employee table and verify data on standby.
10	Final Status Check	Query v$database and v$instance for host name, role, and switchover status.