Oracle GoldenGate 21c: Real-time Peoplesoft Replication from On-Premises to OCI
As enterprises increasingly embrace hybrid cloud strategies, migrating critical applications like Oracle Peoplesoft to the cloud has become a priority. While a full migration often involves re-platforming, maintaining real-time data synchronization between on-premises Peoplesoft environments and Oracle Cloud Infrastructure (OCI) offers myriad benefits: disaster recovery, reporting offload, development/testing environments, or a phased migration approach. Oracle GoldenGate 21c stands out as the premier solution for this demanding task, providing high-performance, low-latency data replication.
This article, penned for TechNews Venture, delves deep into the architectural nuances and practical implementation of establishing real-time data replication for Oracle Peoplesoft using GoldenGate 21c, bridging your on-premises data center with OCI. We'll cover everything from initial setup and configuration to security best practices and ongoing management, ensuring you have a comprehensive guide to deploy this robust solution.
Overview: The Power of GoldenGate 21c for Peoplesoft Hybrid Deployments
Oracle GoldenGate is a comprehensive software package for real-time data integration and replication. It captures, routes, and delivers transactional data changes between heterogeneous systems with minimal overhead. GoldenGate 21c, the latest long-term support release, brings significant enhancements in performance, security, and cloud integration, making it an ideal candidate for complex hybrid scenarios involving Peoplesoft.
For Peoplesoft, data integrity and availability are paramount. Peoplesoft applications are known for their intricate data models, extensive use of triggers, and high transaction volumes. GoldenGate's log-based capture mechanism ensures that every committed transaction, regardless of its origin (application, SQL, or batch process), is captured accurately and delivered to the target system. This capability is critical for Peoplesoft, where data consistency across environments is non-negotiable.
Replicating Peoplesoft data from an on-premises Oracle database to a database running on OCI (e.g., Oracle Database Cloud Service, Exadata Cloud Service, or a self-managed Oracle database on OCI Compute) enables:
- Disaster Recovery (DR): Maintain a continuously updated copy of your Peoplesoft production database in OCI, ready for failover.
- Reporting and Analytics Offload: Direct reporting and analytical queries to the OCI replica, reducing the load on your on-premises production system.
- Development and Testing Environments: Quickly spin up fresh, production-like Peoplesoft environments in OCI for development, testing, or patching cycles.
- Cloud Migration Strategy: Facilitate a phased, low-downtime migration of your Peoplesoft application to OCI by keeping the cloud database synchronized until the cutover.
GoldenGate 21c leverages its advanced architecture, including the Integrated Extract and Integrated Replicat, to interact directly with the Oracle database log stream and apply changes efficiently, ensuring high throughput and minimal latency even across network boundaries to OCI.
Prerequisites for On-Premises to OCI Replication
Before embarking on the GoldenGate configuration, several critical prerequisites must be met on both your on-premises environment and within Oracle Cloud Infrastructure.
1. On-Premises Environment Requirements:
- Oracle Database: Oracle Database 12c Release 2 (12.2) or higher for Integrated Extract (recommended). Ensure the database is in
ARCHIVELOGmode. - GoldenGate 21c Software: Download the appropriate GoldenGate 21c media for your on-premises OS (e.g., Linux x64).
- Network Connectivity:
- Stable, high-bandwidth network connectivity between your on-premises data center and OCI (e.g., FastConnect or IPSec VPN).
- Firewall rules allowing outbound TCP traffic from the on-premises GoldenGate Manager port (default 7809) to the OCI GoldenGate Manager port.
- OS User: A dedicated OS user (e.g.,
ogguser) with appropriate permissions to install and run GoldenGate, and access database binaries/libraries. - Database User: A dedicated database user (e.g.,
ggadmin_src) with specific privileges for GoldenGate operations.
2. Oracle Cloud Infrastructure (OCI) Requirements:
- OCI Tenancy and Compartment: An active OCI tenancy with a dedicated compartment for your Peoplesoft target environment.
- Virtual Cloud Network (VCN): A VCN with appropriate subnets (public/private) for your database and GoldenGate Compute instances.
- Security Lists/Network Security Groups (NSG):
- Inbound rules for the OCI GoldenGate Compute instance to allow TCP traffic on the Manager port (e.g., 7809) from your on-premises GoldenGate IP address.
- Inbound rules for the OCI database instance to allow TCP traffic on the database listener port (e.g., 1521) from the OCI GoldenGate Compute instance's IP.
- OCI Compute Instance: A Linux Compute instance (e.g., Oracle Linux 8, VM.Standard2.2 or higher) to host the target GoldenGate 21c instance. This instance will need a public IP address if connecting directly from on-premises over the internet, or a private IP if using FastConnect/VPN and routing through a DRG. For this guide, we'll assume a public IP for the target OCI GG instance for simplicity, but a private IP with secure connectivity is recommended for production.
- Oracle Database on OCI: An Oracle database instance (e.g., DB System - VM DB, Exadata Cloud, or a self-managed database on Compute) configured as the target for Peoplesoft data.
- Database must be in
ARCHIVELOGmode. - A dedicated database user (e.g.,
ggadmin_tgt) with specific privileges. - Peoplesoft schema (e.g.,
SYSADM) created and ready to receive data, potentially with an initial data load.
- Database must be in
- GoldenGate 21c Software: Download the GoldenGate 21c media for Linux x64 onto your OCI Compute instance.
3. Peoplesoft-Specific Considerations:
- Supplemental Logging: Peoplesoft applications often rely on complex update logic. Ensure full supplemental logging is enabled at the database, schema, and table levels to capture all necessary key and column information for accurate replication.
- Data Model Understanding: A thorough understanding of your Peoplesoft application's data model is crucial for defining the appropriate capture and apply rules.
Step-by-Step Implementation
This section details the configuration steps, starting with the on-premises source and then moving to the OCI target. We'll assume Oracle Linux for both GoldenGate installations and Oracle Database 19c for source and target.
Phase 1: On-Premises Source Configuration
1. Database Preparation (Source)
Connect to your on-premises Oracle database as SYSDBA and perform the following:
-- Enable ARCHIVELOG mode (if not already enabled)
-- This usually requires a database restart
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE ARCHIVELOG;
ALTER DATABASE OPEN;
-- Enable database-level supplemental logging
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (UNIQUE) COLUMNS;
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (FOREIGN KEY) COLUMNS;
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS; -- Recommended for Peoplesoft
-- Create GoldenGate user and grant privileges
CREATE USER ggadmin_src IDENTIFIED BY "OraGgP@ssw0rd!" DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP;
GRANT CONNECT, RESOURCE, DBA TO ggadmin_src; -- For simplicity, DBA for initial setup. Refine for production.
GRANT SELECT ANY DICTIONARY TO ggadmin_src;
GRANT UNLIMITED TABLESPACE TO ggadmin_src;
GRANT ALTER ANY TABLE TO ggadmin_src;
GRANT FLASHBACK ANY TABLE TO ggadmin_src;
GRANT SELECT ON V_$ARCHIVED_LOG TO ggadmin_src;
GRANT SELECT ON V_$DATABASE TO ggadmin_src;
GRANT SELECT ON V_$LOGMNR_CONTENTS TO ggadmin_src;
GRANT SELECT ON V_$TRANSACTION TO ggadmin_src;
GRANT EXECUTE ON DBMS_FLASHBACK TO ggadmin_src;
GRANT ALTER SESSION TO ggadmin_src;
GRANT CREATE TABLE TO ggadmin_src;
GRANT CREATE SEQUENCE TO ggadmin_src;
GRANT ALTER SYSTEM TO ggadmin_src;
GRANT SELECT ANY TRANSACTION TO ggadmin_src;
GRANT SELECT ON SYS.GV_$ARCHIVED_LOG TO ggadmin_src;
GRANT LOGMINING TO ggadmin_src;
ALTER USER ggadmin_src QUOTA UNLIMITED ON USERS;
-- Grant select on Peoplesoft schema tables (example for SYSADM)
GRANT SELECT ON SYSADM.PS_EMPLOYEE_TBL TO ggadmin_src;
GRANT SELECT ON SYSADM.PS_JOB TO ggadmin_src;
-- ... repeat for all Peoplesoft tables to be replicated
2. GoldenGate 21c Installation (On-Premises)
Assuming you've downloaded ogg_21c_linux_x64_for_oracle_21.1.0.0.0.zip to /u01/app/oracle/ogg_source:
# Login as ogguser
mkdir -p /u01/app/oracle/ogg_source
cd /u01/app/oracle/ogg_source
unzip ogg_21c_linux_x64_for_oracle_21.1.0.0.0.zip
# Run the installer
./runInstaller -silent -responseFile /path/to/ogg_install.rsp
# Example response file (ogg_install.rsp):
# INSTALL_OPTION=ORA_DB_INTEGRATION
# SOFTWARE_LOCATION=/u01/app/oracle/ogg_source
# DB_ORACLE_HOME=/u01/app/oracle/product/19.0.0/dbhome_1
# DB_ADMIN_GROUP=dba
# START_MANAGER=false
# After installation, set environment variables
export OGG_HOME=/u01/app/oracle/ogg_source
export LD_LIBRARY_PATH=$OGG_HOME/lib:$ORACLE_HOME/lib
export PATH=$OGG_HOME:$PATH:$ORACLE_HOME/bin
# Initialize GoldenGate working directories
cd $OGG_HOME
./ggsci
CREATE SUBDIRS
EXIT
3. GoldenGate Manager Configuration (On-Premises)
Create the mgr.prm file in $OGG_HOME/dirprm:
# $OGG_HOME/dirprm/mgr.prm
PORT 7809
DYNAMICPORTLIST 7810-7820
AUTOSTART EXTRACT *
AUTORESTART EXTRACT *, RETRIES 5, WAITMINUTES 3
PURGEOLDEXTRACTS /u01/app/oracle/ogg_source/dirdat/*, USEMINUTES 1440
LAGCRITICAL 5 MIN
LAGINFO 3 MIN
Start the Manager:
ggsci
START MANAGER
INFO MANAGER
4. GoldenGate Extract and Data Pump Configuration (On-Premises)
First, add the GoldenGate schema to the database (required for Integrated Extract):
ggsci
DBLOGIN USERID ggadmin_src, PASSWORD "OraGgP@ssw0rd!"
ADD SCHEMATRANDATA SYSADM ALL SUPPLEMENTAL LOG DATA (ALL)
Create and configure the Primary Extract (ext_psft):
ggsci
ADD EXTRACT ext_psft, TRANLOG, BEGIN NOW
ADD EXTTRAIL /u01/app/oracle/ogg_source/dirdat/ps, EXTRACT ext_psft, MEGABYTES 500
EDIT PARAMS ext_psft
-- ext_psft.prm contents
EXTRACT ext_psft
SETENV (ORACLE_HOME="/u01/app/oracle/product/19.0.0/dbhome_1")
SETENV (TNS_ADMIN="/u01/app/oracle/product/19.0.0/dbhome_1/network/admin")
DBLOGIN USERID ggadmin_src, PASSWORD "OraGgP@ssw0rd!"
TRANLOGOPTIONS DBLOGREADER
-- Use GETUPDATEBEFORES for Peoplesoft to ensure all necessary columns are logged for updates
-- This is crucial for applications with complex update logic and multi-column keys
GETUPDATEBEFORES
-- NOCOMPRESSUPDATES is often useful for Peoplesoft for better performance on updates
NOCOMPRESSUPDATES
-- Handle errors gracefully
REPERROR (DEFAULT, ABEND)
-- Exclude internal GoldenGate tables
TABLEEXCLUDE SYSADM.GG_HEARTBEAT;
-- Define Peoplesoft tables to be replicated
TABLE SYSADM.PS_EMPLOYEE_TBL;
TABLE SYSADM.PS_JOB;
TABLE SYSADM.PS_PERSONAL_DATA;
TABLE SYSADM.PS_ADDRESSES;
-- ... add all relevant Peoplesoft tables
Create and configure the Data Pump (dp_psft) to send data to OCI:
ggsci
ADD EXTRACT dp_psft, EXTTRAILSOURCE /u01/app/oracle/ogg_source/dirdat/ps
ADD RMTTRAIL /u01/app/oracle/ogg_target/dirdat/rt, EXTRACT dp_psft, MEGABYTES 500
EDIT PARAMS dp_psft
-- dp_psft.prm contents
EXTRACT dp_psft
PASSTHRU
-- RMTHOST is the Public IP of your OCI GoldenGate Compute instance
-- MGRPORT is the Manager port on the OCI GoldenGate instance
RMTHOST 140.80.20.150, MGRPORT 7809, ENCRYPT AES256, ENCRYPTKEY DEFAULT
-- Define tables to be passed through, typically all tables captured by the primary extract
TABLE SYSADM.*;
Start the Extract and Data Pump:
ggsci
START EXTRACT ext_psft
START EXTRACT dp_psft
INFO EXTRACT *
Phase 2: OCI Target Configuration
1. OCI Network and Compute Instance Provisioning
Ensure your OCI VCN, subnets, and security lists are configured. Example OCI CLI command to launch a Compute instance:
oci compute instance launch \
--availability-domain "AD-1" \
--compartment-id ocid1.compartment.oc1..aaaaaaexample \
--shape VM.Standard2.2 \
--display-name "ogg-target-psft" \
--image-id ocid1.image.oc1.phx.aaaaaaexample \
--subnet-id ocid1.subnet.oc1.phx.aaaaaaexample \
--ssh-authorized-keys-file ~/.ssh/id_rsa.pub \
--assign-public-ip true \
--wait-for-state RUNNING
Once the instance is running, get its public IP (e.g., 140.80.20.150) and update your on-premises Data Pump configuration if needed. Ensure security lists allow inbound TCP on port 7809 from your on-premises GoldenGate IP.
2. Database Preparation (OCI Target)
Connect to your OCI Oracle database (e.g., a DB System or self-managed DB) as SYSDBA:
-- Enable database-level supplemental logging (if not already enabled)
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (UNIQUE) COLUMNS;
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (FOREIGN KEY) COLUMNS;
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS; -- Recommended for Peoplesoft
-- Create GoldenGate user and grant privileges
CREATE USER ggadmin_tgt IDENTIFIED BY "OraGgP@ssw0rd!" DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP;
GRANT CONNECT, RESOURCE, DBA TO ggadmin_tgt; -- For simplicity, DBA for initial setup. Refine for production.
GRANT SELECT ANY DICTIONARY TO ggadmin_tgt;
GRANT UNLIMITED TABLESPACE TO ggadmin_tgt;
GRANT ALTER ANY TABLE TO ggadmin_tgt;
GRANT FLASHBACK ANY TABLE TO ggadmin_tgt;
GRANT SELECT ON V_$ARCHIVED_LOG TO ggadmin_tgt;
GRANT SELECT ON V_$DATABASE TO ggadmin_tgt;
GRANT SELECT ON V_$LOGMNR_CONTENTS TO ggadmin_tgt;
GRANT SELECT ON V_$TRANSACTION TO ggadmin_tgt;
GRANT EXECUTE ON DBMS_FLASHBACK TO ggadmin_tgt;
GRANT ALTER SESSION TO ggadmin_tgt;
GRANT CREATE TABLE TO ggadmin_tgt;
GRANT CREATE SEQUENCE TO ggadmin_tgt;
GRANT ALTER SYSTEM TO ggadmin_tgt;
GRANT SELECT ANY TRANSACTION TO ggadmin_tgt;
GRANT LOGMINING TO ggadmin_tgt;
ALTER USER ggadmin_tgt QUOTA UNLIMITED ON USERS;
-- Create GoldenGate Checkpoint Table (required for Replicat)
CREATE TABLE ggadmin_tgt.GG_CHECKPOINTTABLE (
ID NUMBER(10) NOT NULL,
GROUP_NAME VARCHAR2(30) NOT NULL,
SEQNO NUMBER(10) NOT NULL,
RBA NUMBER(10) NOT NULL,
TIMESTAMP DATE NOT NULL,
PRIMARY KEY (ID)
);
-- Note: Replicat will create this automatically if not present and configured.
Ensure your Peoplesoft schema (e.g., SYSADM) exists on the target database and has empty tables for the initial load. If you're performing an initial load via Data Pump, the schema and tables will be created as part of that process.
3. GoldenGate 21c Installation (OCI Compute)
SSH into your OCI Compute instance (e.g., as opc user, then switch to ogguser). Install GoldenGate 21c similarly to the on-premises setup.
# Login as ogguser
mkdir -p /u01/app/oracle/ogg_target
cd /u01/app/oracle/ogg_target
unzip ogg_21c_linux_x64_for_oracle_21.1.0.0.0.zip
# Run the installer
./runInstaller -silent -responseFile /path/to/ogg_install.rsp
# Example response file (ogg_install.rsp):
# INSTALL_OPTION=ORA_DB_INTEGRATION
# SOFTWARE_LOCATION=/u01/app/oracle/ogg_target
# DB_ORACLE_HOME=/u01/app/oracle/product/19.0.0/dbhome_1
# DB_ADMIN_GROUP=dba
# START_MANAGER=false
# After installation, set environment variables
export OGG_HOME=/u01/app/oracle/ogg_target
export LD_LIBRARY_PATH=$OGG_HOME/lib:$ORACLE_HOME/lib
export PATH=$OGG_HOME:$PATH:$ORACLE_HOME/bin
# Initialize GoldenGate working directories
cd $OGG_HOME
./ggsci
CREATE SUBDIRS
EXIT
4. GoldenGate Manager Configuration (OCI Compute)
Create the mgr.prm file in $OGG_HOME/dirprm:
# $OGG_HOME/dirprm/mgr.prm
PORT 7809
DYNAMICPORTLIST 7810-7820
AUTOSTART REPLICAT *
AUTORESTART REPLICAT *, RETRIES 5, WAITMINUTES 3
PURGEOLDEXTRACTS /u01/app/oracle/ogg_target/dirdat/*, USEMINUTES 1440
LAGCRITICAL 5 MIN
LAGINFO 3 MIN
Start the Manager:
ggsci
START MANAGER
INFO MANAGER
5. GoldenGate Replicat Configuration (OCI Compute)
Add the GoldenGate schema to the target database:
ggsci
DBLOGIN USERID ggadmin_tgt, PASSWORD "OraGgP@ssw0rd!"
ADD SCHEMATRANDATA SYSADM ALL SUPPLEMENTAL LOG DATA (ALL)
Create and configure the Replicat (rep_psft):
ggsci
ADD REPLICAT rep_psft, INTEGRATED, EXTTRAIL /u01/app/oracle/ogg_target/dirdat/rt, CHECKPOINTTABLE ggadmin_tgt.GG_CHECKPOINTTABLE
EDIT PARAMS rep_psft
-- rep_psft.prm contents
REPLICAT rep_psft
SETENV (ORACLE_HOME="/u01/app/oracle/product/19.0.0/dbhome_1")
SETENV (TNS_ADMIN="/u01/app/oracle/product/19.0.0/dbhome_1/network/admin")
DBLOGIN USERID ggadmin_tgt, PASSWORD "OraGgP@ssw0rd!"
-- For Peoplesoft, HANDLECOLLISIONS can be useful during initial synchronization or specific scenarios
-- However, for ongoing replication, it should ideally be avoided as it masks real data issues.
-- HANDLECOLLISIONS
-- BATCHSQL improves performance by batching SQL statements
BATCHSQL
-- REPERROR (DEFAULT, ABEND) for strict error handling
REPERROR (DEFAULT, DISCARD, EXCEPTIONTABLE ggadmin_tgt.rep_psft_DISCARDS)
-- MAP source tables to target tables (assuming same schema and table names)
MAP SYSADM.*, TARGET SYSADM.*;
Start the Replicat:
ggsci
START REPLICAT rep_psft
INFO REPLICAT *
Phase 3: Initial Data Load
Before starting the Replicat for continuous synchronization, an initial load of the Peoplesoft data from on-premises to OCI is required. For large Peoplesoft databases, Oracle Data Pump is generally the most efficient method.
- Export from On-Premises: Use Data Pump Export to export the Peoplesoft schema (e.g.,
SYSADM) from your on-premises source database.expdp ggadmin_src/"OraGgP@ssw0rd!"@onprem_pdb \ SCHEMAS=SYSADM \ DUMPFILE=psft_initial_load_%U.dmp \ LOGFILE=psft_initial_load.log \ DIRECTORY=DATA_PUMP_DIR \ PARALLEL=4 - Transfer to OCI: Securely transfer the Data Pump dump files to your OCI Compute instance or DB System. OCI Object Storage is a good interim location.
- Import to OCI Target: Use Data Pump Import to load the data into the OCI target database.
impdp ggadmin_tgt/"OraGgP@ssw0rd!"@ocidb_pdb \ SCHEMAS=SYSADM \ DUMPFILE=psft_initial_load_%U.dmp \ LOGFILE=psft_initial_load_imp.log \ DIRECTORY=DATA_PUMP_DIR \ PARALLEL=4 - Synchronize GoldenGate: After the initial load, ensure your GoldenGate Extract and Data Pump are running. Start the Replicat from the point in time *after* the initial load completed. This is often done by setting `AFTERCSN` or `AFTERSCN` in the Replicat parameters and then altering its checkpoint.
-- Example: Find the SCN after your impdp completed -- ALTER REPLICAT rep_psft, SCN 123456789; -- Use the SCN after initial load -- START REPLICAT rep_psftAlternatively, if you started Extract/Data Pump *before* the initial load and let them capture transactions during the Data Pump process, you can use GoldenGate's internal synchronization features, but this is more complex and less common for large initial loads.
Security Considerations
Security is paramount when replicating sensitive Peoplesoft data across hybrid environments. Neglecting any aspect can lead to data breaches or service disruptions.
- Network Security:
- OCI Network Security Groups (NSG) / Security Lists: Restrict inbound and outbound traffic to only the necessary ports and source/destination IP addresses. For GoldenGate, ensure only the on-premises GoldenGate IP can connect to the OCI GoldenGate Manager port (7809). For databases, restrict access to port 1521 to only the GoldenGate Compute instance's IP.
- VPN/FastConnect: For production environments, always use OCI FastConnect or an IPSec VPN tunnel for secure, private, and high-performance connectivity between your on-premises data center and OCI, rather than relying solely on public internet.
- Firewall Rules: Configure on-premises firewalls to permit outbound connections from the GoldenGate server to the OCI GoldenGate server's public/private IP on the Manager port.
- GoldenGate Security:
- Encryption in Transit: Always enable encryption for