![Oracle GoldenGate 21c: Real-time On-Premises to OCI Replication [w4td]](/_next/image?url=https%3A%2F%2Fimages.unsplash.com%2Fphoto-1504639725590-34d0984388bd%3Fw%3D1200%26q%3D80&w=3840&q=75)
Overview: Bridging the On-Premises and OCI Divide for PeopleSoft with Oracle GoldenGate 21c
In today's dynamic enterprise landscape, the strategic imperative to leverage cloud capabilities while maintaining critical on-premises infrastructure is more pronounced than ever. For organizations running Oracle PeopleSoft, a robust and often heavily customized enterprise resource planning (ERP) suite, this hybrid reality presents unique challenges and opportunities. Real-time data synchronization between an on-premises PeopleSoft environment and Oracle Cloud Infrastructure (OCI) is not merely a convenience; it's a cornerstone for disaster recovery, reporting offloading, cloud migration strategies, and enabling advanced analytics.
Oracle GoldenGate 21c stands out as the premier solution for addressing these complex data integration needs. As a mature and highly performant real-time data replication platform, GoldenGate 21c offers unparalleled reliability, low-latency data movement, and broad platform support. This article delves into the intricacies of establishing a real-time replication pipeline for PeopleSoft data from an on-premises Oracle Database to an Oracle Database Cloud Service (DBCS) instance within OCI, utilizing Oracle GoldenGate 21c. We'll explore the architecture, configuration, and best practices to ensure your PeopleSoft data remains consistent, available, and performant across your hybrid cloud estate.
The primary use cases for such a setup in a PeopleSoft context are manifold:
- Disaster Recovery (DR): Maintaining a hot standby of your PeopleSoft production database in OCI for rapid failover in case of an on-premises outage.
- Reporting and Analytics Offloading: Shifting resource-intensive reporting and analytical queries to an OCI instance, thereby reducing the load on your production PeopleSoft database.
- Cloud Migration Strategy: A phased approach to migrating PeopleSoft to OCI, where data replication serves as a critical step in ensuring business continuity during the transition.
- Data Distribution: Providing up-to-date PeopleSoft data to other applications or data warehouses running in OCI for integration or analytics purposes.
GoldenGate's architecture, comprising Extracts, Data Pumps, and Replicats, ensures transactional integrity and data consistency. Extracts capture changes from the source database's redo logs, Data Pumps securely transmit these changes (via trail files) to the target, and Replicats apply them to the target database. This robust mechanism is ideal for the high-volume, mission-critical data typical of PeopleSoft applications.
Prerequisites for Real-time Replication
Before embarking on the GoldenGate 21c implementation, a thorough preparation of both your on-premises and OCI environments is essential. This ensures a smooth deployment and reliable operation.
On-Premises Environment (Source - PeopleSoft Production)
- Oracle Database:
- Oracle Database 19c Enterprise Edition (or compatible version) hosting your PeopleSoft application, e.g., PDB
PSPRODPDBwithin CDBPSPROD. - Database must be in
ARCHIVELOGmode. This is crucial for GoldenGate's log-based capture. - Sufficient disk space for GoldenGate installation, its working directories, and trail files.
- A dedicated database user (e.g.,
ggadmin) with appropriate privileges for GoldenGate operations.
- Oracle Database 19c Enterprise Edition (or compatible version) hosting your PeopleSoft application, e.g., PDB
- Operating System:
- Linux (e.g., Oracle Linux 8) or Windows Server.
- Dedicated OS user (e.g.,
ogg) for running GoldenGate processes. - Sufficient CPU, memory, and I/O resources for GoldenGate processes.
- Network Connectivity:
- Reliable and secure network connectivity to OCI. This typically involves Oracle Cloud Infrastructure FastConnect for high-bandwidth, low-latency, and private connectivity, or an IPSec VPN Connect for secure internet-based connectivity.
- Firewall rules configured to allow traffic on the GoldenGate Manager port (default 7809) from the OCI target GoldenGate instance, and the Oracle Database Listener port (default 1521) from the on-premises GoldenGate instance.
- Oracle GoldenGate 21c Software:
- Downloaded GoldenGate 21c media for your specific operating system and database version.
OCI Environment (Target - PeopleSoft DR/Reporting)
- Oracle Database Cloud Service (DBCS):
- An Oracle Database 19c Enterprise Edition (or compatible version) DBCS instance provisioned in OCI, e.g., PDB
PSDRPDBwithin CDBPSDR. - This database will host the replicated PeopleSoft schema.
- A dedicated database user (e.g.,
ggadmin) with appropriate privileges for GoldenGate operations, mirroring the source. - Ensure the target PeopleSoft schema and tablespaces exist, or plan for their creation during the initial load phase.
- An Oracle Database 19c Enterprise Edition (or compatible version) DBCS instance provisioned in OCI, e.g., PDB
- OCI Compute Instance for GoldenGate:
- A dedicated OCI Compute instance (e.g., VM.Standard2.2 or similar, running Oracle Linux 8) to host the GoldenGate 21c target processes. While OCI GoldenGate Service is an option, this article focuses on a self-managed GG 21c deployment for granular control.
- Sufficient disk space, CPU, and memory for GoldenGate.
- Dedicated OS user (e.g.,
ogg) for running GoldenGate processes.
- OCI Networking Configuration:
- A Virtual Cloud Network (VCN) with appropriate subnets (e.g., private subnet for DBCS and GG Compute).
- Security Lists or Network Security Groups (NSGs) configured to allow:
- Inbound traffic on port 7809 (GoldenGate Manager) to the OCI GoldenGate Compute instance from the on-premises GoldenGate host.
- Outbound traffic from the OCI GoldenGate Compute instance to the OCI DBCS instance on port 1521 (Database Listener).
- Inbound traffic on port 1521 (Database Listener) to the OCI DBCS from the OCI GoldenGate Compute instance.
- Route tables configured to direct traffic between your on-premises network and the OCI VCN via FastConnect or VPN Connect.
- OCI CLI:
- OCI Command Line Interface (CLI) installed and configured on your local machine for managing OCI resources (e.g., networking rules).
Step-by-Step Implementation: GoldenGate 21c for PeopleSoft Hybrid Replication
1. Prepare Source Oracle Database (On-Premises PeopleSoft)
Log in to your on-premises PeopleSoft database as a privileged user (e.g., 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 supplemental logging at the database level
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY, UNIQUE INDEX) COLUMNS;
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (FOREIGN KEY) COLUMNS; -- Optional but recommended for PeopleSoft
-- Create GoldenGate Administrator User in the PDB
-- Replace 'PSPRODPDB' with your actual PDB name
ALTER SESSION SET CONTAINER = PSPRODPDB;
CREATE USER c##ggadmin IDENTIFIED BY "YourSecurePassword123" CONTAINER = CURRENT;
GRANT CONNECT, RESOURCE, DBA TO c##ggadmin CONTAINER = CURRENT;
GRANT CREATE TABLE, ALTER TABLE, DROP TABLE, SELECT ANY TABLE, INSERT ANY TABLE, UPDATE ANY TABLE, DELETE ANY TABLE TO c##ggadmin CONTAINER = CURRENT;
GRANT SELECT ON V_$DATABASE TO c##ggadmin CONTAINER = CURRENT;
GRANT SELECT ON V_$ARCHIVED_LOG TO c##ggadmin CONTAINER = CURRENT;
GRANT SELECT ON V_$LOGMNR_CONTENTS TO c##ggadmin CONTAINER = CURRENT;
GRANT SELECT ON V_$TRANSACTION TO c##ggadmin CONTAINER = CURRENT;
GRANT EXECUTE ON DBMS_FLASHBACK TO c##ggadmin CONTAINER = CURRENT;
GRANT ALTER SYSTEM TO c##ggadmin CONTAINER = CURRENT;
GRANT MANAGE SCHEDULER TO c##ggadmin CONTAINER = CURRENT;
GRANT SELECT ANY DICTIONARY TO c##ggadmin CONTAINER = CURRENT;
GRANT UNLIMITED TABLESPACE TO c##ggadmin CONTAINER = CURRENT;
-- For PeopleSoft, also grant access to specific PeopleSoft schemas if ggadmin is not the schema owner
-- For example, if PeopleSoft schema is PSUSER
-- GRANT SELECT, INSERT, UPDATE, DELETE ON PSUSER.PS_JOB TO c##ggadmin CONTAINER = CURRENT;
-- GRANT SELECT, INSERT, UPDATE, DELETE ON PSUSER.PS_EMPLID TO c##ggadmin CONTAINER = CURRENT;
-- ... repeat for all relevant PeopleSoft tables ...
-- Or, if ggadmin is intended to manage all PeopleSoft tables, ensure appropriate grants on the schema owner.
-- For simplicity, we assume ggadmin has broad privileges or specific grants on tables to be replicated.
2. Install Oracle GoldenGate 21c (On-Premises)
On your on-premises GoldenGate host (e.g., 192.168.1.101), perform the installation:
- Download the GoldenGate 21c media (e.g.,
fbo_ggs_Linux_x64_21.3.0.0.0.zip). - Unzip the archive to a chosen installation directory, e.g.,
/opt/oracle/ogg21c. - Run the OGG installer:
mkdir -p /opt/oracle/ogg21c cd /path/to/downloaded/zip unzip fbo_ggs_Linux_x64_21.3.0.0.0.zip -d /opt/oracle/ogg21c cd /opt/oracle/ogg21c ./runInstallerFollow the prompts for a typical installation, specifying the Oracle Home for your database client (if needed) and the GoldenGate installation location.
- Set up environment variables (add to
.bash_profileor similar):export OGG_HOME=/opt/oracle/ogg21c export LD_LIBRARY_PATH=$OGG_HOME/lib:$ORACLE_HOME/lib:/lib:/usr/lib export PATH=$OGG_HOME:$PATH - Initialize the GoldenGate working directories:
cd $OGG_HOME ./ggsci GGSCI> CREATE SUBDIRS
3. Configure GoldenGate Source (On-Premises)
From the GoldenGate command line interface (ggsci) on the on-premises host:
GGSCI> DBLOGIN USERID C##GGADMIN@PSPRODPDB, PASSWORD YourSecurePassword123
GGSCI> ADD CHECKPOINTTABLE c##ggadmin.gg_checkpoint
-- Add primary Extract for capturing changes from the source database
-- EXT_PSPROD: Name of the Extract process
-- TRANLOG: Specifies log-based capture
-- ASYNCTRAN: Recommended for performance
-- ETROLLOVERSIZE 100M: Trail file size
GGSCI> ADD EXTRACT EXT_PSPROD, TRANLOG, BEGIN NOW, ASYNCTRAN, ETROLLOVERSIZE 100M
-- Add RMTTRAIL to define the remote trail file location on the target OCI GoldenGate host
-- RMTTRAIL /u01/app/oracle/ogg21c/dirdat/rt, MGRPORT 7809, HOST 10.0.1.11
GGSCI> ADD RMTTRAIL /u01/app/oracle/ogg21c/dirdat/rt, EXTRACT EXT_PSPROD, MEGABYTES 100, MGRPORT 7809, HOST 10.0.1.11
-- Add Data Pump Extract for secure transmission to OCI
-- DP_PSPROD: Name of the Data Pump process
-- EXTTRAILSOURCE /opt/oracle/ogg21c/dirdat/ea: Source trail files from primary Extract
GGSCI> ADD EXTRACT DP_PSPROD, EXTTRAILSOURCE /opt/oracle/ogg21c/dirdat/ea
-- Add RMTTRAIL for the Data Pump, pointing to the same remote trail location
GGSCI> ADD RMTTRAIL /u01/app/oracle/ogg21c/dirdat/rt, EXTRACT DP_PSPROD, MEGABYTES 100, MGRPORT 7809, HOST 10.0.1.11
-- Configure Manager process (manager.prm)
-- Edit /opt/oracle/ogg21c/dirprm/manager.prm
-- (Ensure this is done before starting Manager)
/opt/oracle/ogg21c/dirprm/manager.prm (On-Premises):
PORT 7809
DYNAMICPORTLIST 7810-7820
LAGREPORTMINUTES 15
LAGCRITICALMINUTES 30
AUTOSTART EXTRACT EXT_PSPROD
AUTOSTART EXTRACT DP_PSPROD
AUTORESTART EXTRACT EXT_PSPROD, WAITMINUTES 5, RETRIES 10
AUTORESTART EXTRACT DP_PSPROD, WAITMINUTES 5, RETRIES 10
/opt/oracle/ogg21c/dirprm/ext_psprod.prm (Primary Extract Parameter File):
EXTRACT EXT_PSPROD
SETENV (ORACLE_SID=PSPROD)
SETENV (NLS_LANG="AMERICAN_AMERICA.AL32UTF8")
SOURCEDB C##GGADMIN@PSPRODPDB, PASSWORD YourSecurePassword123
TRANLOGOPTIONS DBLOGREADER -- Recommended for performance with 19c+
EXTTRAIL /opt/oracle/ogg21c/dirdat/ea
TABLE PSUSER.PS_JOB;
TABLE PSUSER.PS_EMPLID;
TABLE PSUSER.PS_LEDGER_TBL;
TABLE PSUSER.PS_ABSENCE_HIST;
-- Add all PeopleSoft tables/schemas you need to replicate.
-- Consider using TABLE PSUSER.*; if replicating entire schema (with caution).
/opt/oracle/ogg21c/dirprm/dp_psprod.prm (Data Pump Parameter File):
EXTRACT DP_PSPROD
PASSTHRU
RMTHOST 10.0.1.11, MGRPORT 7809
RMTTRAIL /u01/app/oracle/ogg21c/dirdat/rt
TABLE PSUSER.PS_JOB;
TABLE PSUSER.PS_EMPLID;
TABLE PSUSER.PS_LEDGER_TBL;
TABLE PSUSER.PS_ABSENCE_HIST;
-- Must match tables specified in the primary extract
4. Prepare Target Oracle Database (OCI DBCS PeopleSoft)
Connect to your OCI DBCS instance (e.g., PDB PSDRPDB within CDB PSDR) as a privileged user and create the GoldenGate administrator user:
-- Connect to the target PDB
ALTER SESSION SET CONTAINER = PSDRPDB;
CREATE USER c##ggadmin IDENTIFIED BY "YourSecurePassword456" CONTAINER = CURRENT;
GRANT CONNECT, RESOURCE, DBA TO c##ggadmin CONTAINER = CURRENT;
GRANT CREATE TABLE, ALTER TABLE, DROP TABLE, SELECT ANY TABLE, INSERT ANY TABLE, UPDATE ANY TABLE, DELETE ANY TABLE TO c##ggadmin CONTAINER = CURRENT;
GRANT SELECT ON V_$DATABASE TO c##ggadmin CONTAINER = CURRENT;
GRANT UNLIMITED TABLESPACE TO c##ggadmin CONTAINER = CURRENT;
-- Ensure the PeopleSoft schema (e.g., PSUSER) exists on the target database
-- and has appropriate tablespaces.
-- If not, you'll need to create it or perform an initial load that includes schema creation.
5. Install Oracle GoldenGate 21c (OCI Compute Instance)
On your OCI Compute instance (e.g., 10.0.1.11), repeat step 2 to install GoldenGate 21c. The installation path and environment variables should be consistent with the on-premises setup, e.g., /u01/app/oracle/ogg21c.
mkdir -p /u01/app/oracle/ogg21c
cd /path/to/downloaded/zip (transfer zip to OCI compute first, e.g., via scp)
unzip fbo_ggs_Linux_x64_21.3.0.0.0.zip -d /u01/app/oracle/ogg21c
cd /u01/app/oracle/ogg21c
./runInstaller
Set up environment variables:
export OGG_HOME=/u01/app/oracle/ogg21c
export LD_LIBRARY_PATH=$OGG_HOME/lib:$ORACLE_HOME/lib:/lib:/usr/lib
export PATH=$OGG_HOME:$PATH
Initialize GoldenGate working directories:
cd $OGG_HOME
./ggsci
GGSCI> CREATE SUBDIRS
6. Configure GoldenGate Target (OCI Compute Instance)
From the ggsci on the OCI Compute instance:
GGSCI> DBLOGIN USERID C##GGADMIN@PSDRPDB, PASSWORD YourSecurePassword456
GGSCI> ADD CHECKPOINTTABLE c##ggadmin.gg_checkpoint
-- Add Replicat process
-- REP_PSDR: Name of the Replicat process
-- EXTTRAIL /u01/app/oracle/ogg21c/dirdat/rt: Reads from the remote trail files
-- ALLOWNONFATALERROR: Useful during initial sync or for handling minor data discrepancies
GGSCI> ADD REPLICAT REP_PSDR, EXTTRAIL /u01/app/oracle/ogg21c/dirdat/rt, CHECKPOINTTABLE c##ggadmin.gg_checkpoint
-- Configure Manager process (manager.prm)
-- Edit /u01/app/oracle/ogg21c/dirprm/manager.prm
/u01/app/oracle/ogg21c/dirprm/manager.prm (OCI Target):
PORT 7809
DYNAMICPORTLIST 7810-7820
LAGREPORTMINUTES 15
LAGCRITICALMINUTES 30
AUTOSTART REPLICAT REP_PSDR
AUTORESTART REPLICAT REP_PSDR, WAITMINUTES 5, RETRIES 10
/u01/app/oracle/ogg21c/dirprm/rep_psdr.prm (Replicat Parameter File):
REPLICAT REP_PSDR
SETENV (ORACLE_SID=PSDR)
SETENV (NLS_LANG="AMERICAN_AMERICA.AL32UTF8")
TARGETDB C##GGADMIN@PSDRPDB, PASSWORD YourSecurePassword456
ASSUMETARGETDEFS
MAP PSUSER.PS_JOB, TARGET PSUSER.PS_JOB;
MAP PSUSER.PS_EMPLID, TARGET PSUSER.PS_EMPLID;
MAP PSUSER.PS_LEDGER_TBL, TARGET PSUSER.PS_LEDGER_TBL;
MAP PSUSER.PS_ABSENCE_HIST, TARGET PSUSER.PS_ABSENCE_HIST;
-- For initial load, you might temporarily use HANDLECOLLISIONS
-- HANDLECOLLISIONS
-- Remove or comment out HANDLECOLLISIONS after initial sync.
-- BATCHSQL: Improves performance for high-volume transactions
BATCHSQL
7. Initial Data Load (Critical for PeopleSoft)
Before starting the GoldenGate processes for continuous replication, you must perform an initial load to synchronize the source and target PeopleSoft databases. Oracle Data Pump is a common and efficient method for this.
On-premises (Source) - Export PeopleSoft Schema:
-- Create a directory object in the source database
CREATE DIRECTORY DATA_PUMP_DIR AS '/u01/app/oracle/dp_export';
GRANT READ, WRITE ON DIRECTORY DATA_PUMP_DIR TO C##GGADMIN;
-- Perform the export
expdp c##ggadmin/YourSecurePassword123@PSPRODPDB \
schemas=PSUSER \
directory=DATA_PUMP_DIR \
dumpfile=psprod_psuser_%U.dmp \
logfile=psprod_psuser_exp.log \
compression=all \
flashback_time=systimestamp
Transfer the dump files (psprod_psuser_*.dmp) to the OCI Compute instance or a shared storage accessible by the OCI DBCS instance (e.g., OCI Object Storage, then mounted to DBCS). Use scp or OCI CLI to move files:
# From on-premises machine
scp /u01/app/oracle/dp_export/psprod_psuser_*.dmp opc@10.0.1.11:/u01/app/oracle/dp_import/
# Or using OCI CLI to Object Storage
oci os object put --namespace MyTenancyNamespace --bucket-name my-data-pump-bucket --file /u01/app/oracle/dp_export/psprod_psuser_01.dmp --name psprod_psuser_01.dmp
OCI (Target) - Import PeopleSoft Schema:
-- Create a directory object in the target database
CREATE DIRECTORY DATA_PUMP_DIR AS '/u01/app/oracle/dp_import';
GRANT READ, WRITE ON DIRECTORY DATA_PUMP_DIR TO C##GGADMIN;
-- Perform the import
impdp c##ggadmin/YourSecurePassword456@PSDRPDB \
schemas=PSUSER \
directory=DATA_PUMP_DIR \
dumpfile=psprod_psuser_%U.dmp \
logfile=psprod_psuser_imp.log \
remap_schema=PSUSER:PSUSER -- If target schema name is same, this is optional but good practice
Once the initial load is complete, ensure the target PeopleSoft schema is consistent with the source. If HANDLECOLLISIONS was used in the Replicat parameter file, remove or comment it out now.
8. Start GoldenGate Processes and Monitor
On-premises GoldenGate host:
GGSCI> START MANAGER
GG