Oracle GoldenGate 21c: Real-time On-Prem to OCI Hybrid Replication

Overview: Real-Time PeopleSoft Data Replication with Oracle GoldenGate 21c and OCI

In today's fast-paced digital landscape, enterprises demand real-time access to critical business data. For organizations running Oracle PeopleSoft, this often means ensuring that their operational data, residing in on-premises databases, is promptly and accurately mirrored to cloud environments for analytics, reporting, disaster recovery, or cloud-native application integration. Oracle GoldenGate 21c, a powerful real-time data integration and replication platform, combined with Oracle Cloud Infrastructure (OCI), provides an robust solution for this exact challenge.

This article, penned for the discerning technical professional at TechNews Venture, delves into the intricacies of establishing real-time, bidirectional data replication for Oracle PeopleSoft between an on-premises Oracle database and an Oracle Database Cloud Service (DBCS) or Oracle Autonomous Database (ADB) instance within OCI. We'll focus on GoldenGate 21c's enhanced capabilities, including its microservices architecture, and illustrate a step-by-step implementation, complete with actual CLI commands, configuration examples, and best practices. The goal is to demystify the process, enabling our readers to confidently architect and deploy such a critical data pipeline.

The transition of PeopleSoft environments, or parts thereof, to the cloud is a strategic move for many organizations. It offers agility, scalability, reduced operational overhead, and access to advanced cloud services. Real-time replication is fundamental to this strategy, allowing for seamless migration, hybrid deployments, and ensuring business continuity with minimal downtime during cutovers or in disaster recovery scenarios. GoldenGate 21c, with its proven change data capture (CDC) technology, guarantees transactional integrity and high performance, making it the ideal choice for PeopleSoft's complex and high-volume transaction sets.

Why GoldenGate 21c for PeopleSoft?

• Transactional Integrity: GoldenGate ensures that all transactions are replicated in commit order, maintaining data consistency between source and target. This is critical for PeopleSoft's highly interconnected data model.
• Real-time Replication: Minimizes data latency, enabling up-to-the-minute reporting, analytics, and operational decision-making in the cloud.
• Heterogeneous Support: While we focus on Oracle-to-Oracle, GoldenGate's versatility extends to various database and platform combinations.
• Low Impact: The change data capture mechanism has a minimal impact on source database performance, crucial for production PeopleSoft systems.
• Microservices Architecture: GoldenGate 21c leverages a microservices architecture, offering improved manageability, RESTful APIs, and enhanced security compared to classic architecture.
• Disaster Recovery and High Availability: Provides a robust foundation for active-passive or active-active disaster recovery strategies for PeopleSoft.
• Cloud Integration: Seamlessly integrates with OCI services, including Oracle Database Cloud Service and Oracle Autonomous Database, and can leverage OCI GoldenGate for simplified deployment and management in the cloud.

Prerequisites for a Successful Implementation

Before embarking on the replication journey, several foundational components must be in place. These span database configuration, network setup, OCI resource provisioning, and GoldenGate licensing.

1. On-Premises Source Database Requirements (Oracle Database 19c/21c for PeopleSoft)

• Database Version: Ensure your Oracle database is a supported version (e.g., 19c or 21c).
• ARCHIVELOG Mode: The database must be running in ARCHIVELOG mode. This is non-negotiable for GoldenGate's log-based capture.

  
  SQL> SELECT LOG_MODE FROM V$DATABASE;
  LOG_MODE
  ------------
  ARCHIVELOG
          

  If not, enable it:

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

• Force Logging: Enable force logging at the database level to ensure all changes are written to the redo logs, regardless of NOLOGGING settings on tablespaces or objects.

  
  SQL> ALTER DATABASE FORCE LOGGING;
          

• Supplemental Logging: This is crucial. GoldenGate requires additional information in the redo logs to reconstruct DML operations correctly.
  • Database-level Supplemental Logging:

    
    SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
    SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;
    SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (UNIQUE) COLUMNS;
    SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (FOREIGN KEY) COLUMNS; -- Recommended for PeopleSoft
                    

  • Schema/Table-level Supplemental Logging for PeopleSoft: For PeopleSoft, it's often best to enable supplemental logging for all tables within the PeopleSoft schema. This can be done via GGSCI later.
• GoldenGate User: Create a dedicated database user for GoldenGate with necessary privileges.

  
  SQL> CREATE USER c##ggadmin IDENTIFIED BY "YourStrongPassword123" CONTAINER=ALL; -- For CDB
  SQL> GRANT CONNECT, RESOURCE TO c##ggadmin CONTAINER=ALL;
  SQL> GRANT SELECT ANY DICTIONARY TO c##ggadmin CONTAINER=ALL;
  SQL> GRANT ALTER ANY TABLE TO c##ggadmin CONTAINER=ALL;
  SQL> GRANT FLASHBACK ANY TABLE TO c##ggadmin CONTAINER=ALL;
  SQL> GRANT SELECT ON V_$ARCHIVED_LOG TO c##ggadmin CONTAINER=ALL;
  SQL> GRANT SELECT ON V_$DATABASE TO c##ggadmin CONTAINER=ALL;
  SQL> GRANT SELECT ON V_$LOGMNR_CONTENTS TO c##ggadmin CONTAINER=ALL;
  SQL> GRANT SELECT ON V_$TRANSACTION TO c##ggadmin CONTAINER=ALL;
  SQL> GRANT EXECUTE ON DBMS_FLASHBACK TO c##ggadmin CONTAINER=ALL;
  SQL> GRANT EXECUTE ON UTL_FILE TO c##ggadmin CONTAINER=ALL;
  SQL> GRANT UNLIMITED TABLESPACE TO c##ggadmin CONTAINER=ALL;
  SQL> ALTER USER c##ggadmin QUOTA UNLIMITED ON USERS;
          

  For a non-CDB, omit `CONTAINER=ALL`. Adjust `c##ggadmin` to `ggadmin` if not using a common user in a CDB.

2. Network Connectivity

• On-Premises to OCI: Secure and reliable network connectivity is paramount. This typically involves:
  • Site-to-Site VPN: IPSec VPN connection between your on-premises network and an OCI Dynamic Routing Gateway (DRG).
  • FastConnect: Dedicated, private network connection for higher bandwidth and lower latency.
  • Bastion Host (for initial setup/troubleshooting): A jump server in OCI to access private resources, though not ideal for production GoldenGate traffic.
• Firewall Rules: Ensure necessary ports are open bi-directionally.
  • On-premises GoldenGate Manager Port (e.g., 7809)
  • OCI GoldenGate Service Manager Port (e.g., 443 for HTTPS, or 7809-7900 for internal communication, depending on setup)
  • Database Listener Ports (e.g., 1521 for both source and target)
• DNS Resolution: Ensure both on-premises and OCI environments can resolve hostnames correctly.

3. OCI Resource Provisioning

• Virtual Cloud Network (VCN): A VCN with appropriate subnets (private preferred) and security lists/Network Security Groups (NSGs) for your OCI GoldenGate deployment and target database.

  
  # Example: Creating a VCN and Subnet using OCI CLI
  oci network vcn create --compartment-id ocid1.compartment.oc1..aaaaaaaarxxxxxxxxxxxxxxxxxxxxxxxxx --display-name "TechNewsVenture-VCN" --cidr-block 10.0.0.0/16 --dns-label "technewsvcn"
  oci network subnet create --compartment-id ocid1.compartment.oc1..aaaaaaaarxxxxxxxxxxxxxxxxxxxxxxxxx --vcn-id ocid1.vcn.oc1..aaaaaaaarxxxxxxxxxxxxxxxxxxxxxxxxx --display-name "GoldenGate-Subnet" --cidr-block 10.0.1.0/24 --dns-label "ggsubnet" --security-list-ids '["ocid1.securitylist.oc1..aaaaaaaarxxxxxxxxxxxxxxxxxxxxxxxxx"]'
          

• OCI GoldenGate Deployment: A provisioned OCI GoldenGate instance in a private subnet.

  
  # Example: Creating an OCI GoldenGate Deployment
  oci goldengate deployment create \
      --compartment-id ocid1.compartment.oc1..aaaaaaaarxxxxxxxxxxxxxxxxxxxxxxxxx \
      --display-name "TechNewsVenture-GG21c" \
      --license-type "BRING_YOUR_OWN_LICENSE" \
      --description "GoldenGate 21c for PeopleSoft replication" \
      --cpu-core-count 2 \
      --is-auto-scaling-enabled false \
      --subnet-id ocid1.subnet.oc1..aaaaaaaarxxxxxxxxxxxxxxxxxxxxxxxxx \
      --storage-units 100 \
      --deployment-type OGG \
      --deployment-url-type PUBLIC_ENDPOINT \
      --admin-username "gguser" \
      --admin-password "YourAdminPassword123#$" \
      --db-details '{
          "databaseType": "ORACLE",
          "connectionString": "jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCPS)(HOST=adb.example.oraclecloud.com)(PORT=1522))(CONNECT_DATA=(SERVICE_NAME=your_adb_service_name_high.adb.oraclecloud.com)))"
      }' \
      --is-public true \
      --network-security-group-ids '["ocid1.networksecuritygroup.oc1..aaaaaaaarxxxxxxxxxxxxxxxxxxxxxxxxx"]'
          

  Note: While the example uses `is-public true` and `PUBLIC_ENDPOINT` for simplicity in CLI demonstration, for production PeopleSoft environments, always prefer `is-public false` and `PRIVATE_ENDPOINT`, coupled with a private endpoint for the OCI GoldenGate deployment and appropriate network connectivity (VPN/FastConnect) to your on-premises network. The `db-details` here might point to an initial target DB connection, but we'll configure connections more flexibly within the GoldenGate console later.

• Target Database: An Oracle Database Cloud Service (DBCS) or Oracle Autonomous Database (ADB) instance. Ensure it's accessible from the OCI GoldenGate subnet and has sufficient resources. Create a target PeopleSoft schema and ensure it's identical in structure to the source.

  
  # Example: Creating a user for the target database
  SQL> CREATE USER pscust IDENTIFIED BY "YourTargetDBPassword123#$" DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP;
  SQL> GRANT CONNECT, RESOURCE TO pscust;
  SQL> GRANT UNLIMITED TABLESPACE TO pscust;
  SQL> GRANT SELECT ON sys.dba_tablespaces TO pscust; -- If needed for specific GG operations
          

• IAM Policies: Appropriate IAM policies to allow the OCI GoldenGate service to interact with other OCI resources (e.g., target database, object storage for trails).

  
  # Example IAM Policy
  Allow group OggAdmins to manage goldengate-family in compartment TechNewsVenture-Compartment
  Allow group OggAdmins to use database-family in compartment TechNewsVenture-Compartment
  Allow group OggAdmins to manage virtual-network-family in compartment TechNewsVenture-Compartment
          

4. GoldenGate 21c Installation (On-Premises)

Download Oracle GoldenGate 21c for Oracle Database from Oracle Support or eDelivery. Install it on a dedicated server or VM within your on-premises network. We assume the installation directory is `/u01/app/oracle/product/ogg21c`.

Step-by-Step Implementation: On-Prem to OCI PeopleSoft Replication

This section details the configuration of GoldenGate components on both the on-premises source and OCI target environments.

Step 1: On-Premises Source GoldenGate Configuration

Navigate to your GoldenGate installation directory and start the GGSCI utility.

cd /u01/app/oracle/product/ogg21c
./ggsci

1.1. Configure the Manager Process

The Manager process controls all other GoldenGate processes. It must be running on both source and target.

GGSCI> EDIT PARAMS MGR

Add the following to the `mgr.prm` file:

PORT 7809
DYNAMICPORTLIST 7810-7820
AUTORESTART EXTRACT *, PUMP *
LAGCRITICAL 10
LAGINFO 5
PURGEOLDEXTRACTS /u01/app/oracle/product/ogg21c/dirdat, USEMINS 1440, MINKEEPHOURS 24
VIEWPARAMS MGR

Start the Manager:

GGSCI> START MGR
GGSCI> INFO MGR

1.2. Enable Supplemental Logging for PeopleSoft Schema

Connect to the source database as the GoldenGate user and enable supplemental logging for the PeopleSoft schema (e.g., `SYSADM`).

GGSCI> DBLOGIN USERID c##ggadmin, PASSWORD YourStrongPassword123
GGSCI> ADD TRANDATA SYSADM.*

Verify supplemental logging:

GGSCI> INFO TRANDATA SYSADM.*

This command adds primary key, unique key, and foreign key supplemental logging for all tables in the `SYSADM` schema, which is crucial for PeopleSoft's relational integrity.

1.3. Configure the Extract Process (Primary Extract)

The primary Extract process captures changes from the source database's redo logs.

GGSCI> ADD EXTRACT extpsft, TRANLOG, BEGIN NOW
GGSCI> EDIT PARAMS extpsft

Add the following to the `extpsft.prm` file:

EXTRACT extpsft
SETENV (ORACLE_HOME="/u01/app/oracle/product/19.0.0/dbhome_1")
SETENV (ORACLE_SID="PEOPLEDB")
DBLOGIN USERID c##ggadmin, PASSWORD YourStrongPassword123
TRANLOGOPTIONS DBLOGREADER
-- For CDB:
-- SOURCEDB PEOPLEDB@//onprem-db.example.com:1521/PEOPLEDB_PDB1, USERID c##ggadmin, PASSWORD YourStrongPassword123
-- For non-CDB:
SOURCEDB PEOPLEDB, USERID c##ggadmin, PASSWORD YourStrongPassword123

EXTTRAIL /u01/app/oracle/product/ogg21c/dirdat/ps
TABLE SYSADM.*;

Register and start the Extract:

GGSCI> REGISTER EXTRACT extpsft DATABASE
GGSCI> START EXTRACT extpsft
GGSCI> INFO EXTRACT extpsft

1.4. Configure the Data Pump Process

The Data Pump is a secondary Extract process that reads the local trail files generated by the primary Extract and sends them to the remote target (OCI GoldenGate).

GGSCI> ADD RMTTRAIL /u01/app/oracle/product/ogg21c/dirdat/rt, EXTRACT extpsft
GGSCI> ADD EXTRACT pumppsft, EXTTRAILSOURCE /u01/app/oracle/product/ogg21c/dirdat/ps
GGSCI> EDIT PARAMS pumppsft

Add the following to the `pumppsft.prm` file:

EXTRACT pumppsft
PASSTHRU
RMTHOST oci-goldengate-deployment.example.com, MGRPORT 7809, ENCRYPT AES256
RMTTRAIL /u01/app/oracle/product/ogg21c/dirdat/rt
TABLE SYSADM.*;

Note on `RMTHOST` and `MGRPORT`: The `RMTHOST` should be the public or private IP/hostname of your OCI GoldenGate deployment. For `MGRPORT`, if using OCI GoldenGate's microservices architecture, the `MGRPORT` is typically not directly exposed for trail file transfer. Instead, the OCI GoldenGate deployment exposes a secure endpoint (HTTPS) for data pump connections. You'll need to configure an "Acceptor" in the OCI GoldenGate deployment to listen for incoming data pump connections on a specific port. Let's adjust this for OCI GoldenGate's microservices approach.

Revised Data Pump configuration for OCI GoldenGate Microservices:

EXTRACT pumppsft
PASSTHRU
-- Connect to the OCI GoldenGate deployment's secure data pump endpoint
RMTHOST oci-goldengate-deployment.example.com, MGRPORT 443, ENCRYPT AES256, USERIDALIAS oci_gg_alias
RMTTRAIL /u01/app/oracle/product/ogg21c/dirdat/rt
TABLE SYSADM.*;

You would create the `oci_gg_alias` using `ADD CREDENTIALSTORE` and `ALTER CREDENTIALSTORE ADD USER ...` on the source GoldenGate, storing the username/password for the OCI GoldenGate deployment's data pump user. We'll set this up on the OCI side shortly.

Start the Data Pump:

GGSCI> START EXTRACT pumppsft
GGSCI> INFO EXTRACT pumppsft

Step 2: OCI GoldenGate Deployment Configuration (Target)

Access your OCI GoldenGate deployment console via its public or private endpoint URL (e.g., `https://oci-goldengate-deployment.example.com`). Log in with the administrator credentials you set during deployment creation.

2.1. Create Database Connections

In the OCI GoldenGate console, navigate to "Configuration" -> "Database Connections".

• Source Database Connection (On-Premises):
  • Click "Create Connection".
  • Connection Type: Oracle Database
  • Connection Name: `onprem_psft_src`
  • Database Type: Oracle
  • Connectivity: Enter the TNS connect string for your on-premises PeopleSoft database (e.g., `(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=onprem-db.example.com)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=PEOPLEDB_PDB1)))`).
  • Database Username: `c##ggadmin`
  • Password: YourStrongPassword123
  • Test the connection and save.
• Target Database Connection (OCI DBCS/ADB):
  • Click "Create Connection".
  • Connection Type: Oracle Database
  • Connection Name: `oci_psft_target`
  • Database Type: Oracle
  • Connectivity: Enter the TNS connect string for your OCI target database (e.g., for ADB: `(DESCRIPTION=(ADDRESS=(PROTOCOL=TCPS)(HOST=adb.example.oraclecloud.com)(PORT=1522))(CONNECT_DATA=(SERVICE_NAME=your_adb_service_name_high.adb.oraclecloud.com)))`). Upload wallet if using TCPS.
  • Database Username: `pcust`
  • Password: YourTargetDBPassword123#$
  • Test the connection and save.

2.2. Configure the Acceptor (Data Pump Listener)

For the on-premises Data Pump to send trails to OCI GoldenGate, you need an Acceptor process. In the OCI GoldenGate console, go to "Administration" -> "Service Manager".

• Under the "Network" section, locate "Acceptors".
• Click "Add Acceptor".
• Port: Choose a port (e.g., 7810). Ensure this port is open in your OCI VCN's security lists/NSGs for inbound traffic from your on-premises network.
• Authentication: Select "Username and Password".
• Username: `datapump_user`
• Password: `YourDataPumpPassword123#$`
• Save the Acceptor.

Now, update the on-premises Data Pump parameter file (`pumppsft.prm`) to use this Acceptor:

EXTRACT pumppsft
PASSTHRU
-- Connect to the OCI GoldenGate deployment's Acceptor endpoint
RMTHOST oci-goldengate-deployment.example.com, RMTPORT 7810, ENCRYPT AES256, COMPRESSION
TABLE SYSADM.*;

Restart the `pumppsft` process on-premises after this change:

GGSCI> STOP EXTRACT pumppsft
GGSCI> START EXTRACT pumppsft

2.3. Configure the Replicat Process

The Replicat process reads the trail files received from the Data Pump and applies the changes to the target PeopleSoft database.

In the OCI GoldenGate console, navigate to "Overview" -> "Replicats".

• Click "Add Replicat".
• Replicat Type: "Classic Replicat" (for initial setup, though Integrated Replicat is often preferred for performance).
• Process Name: `repopsft`
• Trail File Name: `rt` (This matches the `RMTTRAIL` name from the Data Pump).
• Start Replicat: Select "Immediately".
• Click "Add Replicat" and then "Next" to configure parameters.

Edit the Replicat parameters:

REPLICAT repopsft
SETENV (ORACLE_HOME="/usr/lib/oracle/21/client64") -- Example for OCI GoldenGate internal client
SOURCEDB oci_psft_target, USERID pcust, PASSWORD YourTargetDBPassword123#$
-- For PeopleSoft, consider HANDLECOLLISIONS for initial sync or potential conflicts
-- HANDLECOLLISIONS
ASSUMETARGETDEFS
MAP SYSADM.*, TARGET SYSADM.*;

Note on `SOURCEDB`: In OCI GoldenGate, when configuring Replicat, you typically specify the "Target Database" connection you created earlier (e.g., `oci_psft_target`). The `SOURCEDB` parameter in the Replicat PFILE refers to the target database where changes are applied.

Start the Replicat:

Once configured, the Replicat will start automatically if "Start Replicat Immediately" was selected. Otherwise, you can start it from the "Replicats" overview page.

-- (Inside OCI GoldenGate's GGSCI, if you had direct access, but usually done via console)
GGSCI> START REPLICAT repopsft
GGSCI> INFO REPLICAT repopsft

Step 3: Initial Load (Optional but Recommended for Large Datasets)

For large PeopleSoft databases, an initial load is usually performed before starting real-time replication to bring the target database in sync with the source. This can be done using Oracle Data Pump, RMAN, or GoldenGate's initial load utility.

Using Oracle Data Pump:

1. Export `SYSADM` schema from on-premises:

   
   expdp c##ggadmin/YourStrongPassword123@PEOPLEDB_PDB1 DUMPFILE=psft_schema.dmp LOGFILE=exp_psft.log DIRECTORY=DATA_PUMP_DIR SCHEMAS=SYSADM
           

2. Transfer `psft_schema.dmp` to OCI Object Storage.
3. Import `SYSADM` schema into OCI target database:

   
   impdp pcust/YourTargetDBPassword123#$@your_adb_service_name_high.adb.oraclecloud.com DUMPFILE=psft_schema.dmp LOGFILE=imp_psft.log DIRECTORY=DATA_PUMP_DIR SCHEMAS=SYSADM
           

After the initial load, ensure the GoldenGate Extract process starts capturing changes from a point *after* the export was taken (using `BEGIN NOW` or a specific SCN/timestamp) to avoid data loss or duplication.

Step 4: Monitoring and Troubleshooting

Regular monitoring is essential to ensure the replication pipeline is healthy. Both on-premises GGSCI and the OCI GoldenGate console provide robust monitoring capabilities.

On-Premises GGSCI:

GGSCI> INFO ALL
GGSCI> STATUS EXTRACT extpsft
GGSCI> LAG EXTRACT extpsft
GGSCI> VIEW REPORT extpsft

OCI GoldenGate Console:

The OCI GoldenGate console provides a graphical interface to monitor the health, lag, and status of all Extract and Replicat processes. You can view logs, statistics, and manage processes directly from the browser.

Security Considerations

Implementing real-time replication, especially across cloud boundaries, demands stringent security measures.

• Network Security:
  • VCN Security Lists/NSGs: Restrict traffic to and from the OCI GoldenGate deployment and target database to only necessary IP addresses and ports.
  • VPN/FastConnect: Always prefer private, encrypted network connections over public internet for sensitive data.
  • Firewalls: Configure on-premises firewalls to allow GoldenGate traffic only to the OCI GoldenGate endpoint.
• Data Encryption:
  • GoldenGate Encryption: Use `ENCRYPT AES256` in the Data Pump parameter file to encrypt data in transit between on-premises and OCI.
  • TDE (Transparent Data Encryption): Ensure your source and target Oracle databases leverage TDE for data at rest encryption.
  • OCI Object Storage Encryption: If using Object Storage for trail file backups, ensure encryption is enabled.
• Authentication and Authorization:
  • Strong Passwords: Use complex, regularly rotated passwords for all GoldenGate and database users.
  • Dedicated Users: GoldenGate should use dedicated database users with the principle of least privilege.