ORACLE GOLDENGATE
Configuration & Architecture Guide
Real-Time Data Replication for Oracle Database 19c
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
