Oracle Golden Gate to PostgreSQL Migration

Architecture Decision Record: Oracle to PostgreSQL Migration Strategy

Context

Current Environment:
- Two Oracle database instances in Datacenter A
- Two Oracle database instances in Datacenter B
- Oracle GoldenGate performing homogeneous replication between instances
- Business application using a service name to connect to one Oracle instance at a time
- TLS not currently enabled between application and Oracle databases
- AWS Direct Connect already established between on-premises datacenters and AWS
- Some data is encrypted using Oracle Transparent Data Encryption (TDE)
- Oracle service provides transparent connection routing to active instances
Target Environment:
- AWS PostgreSQL as the destination database
- Need for ongoing replication using CDC (Change Data Capture)
- Desire to enable TLS specifically for the DMS migration path

Decision Drivers

Minimize disruption to existing business applications
Ensure data consistency during and after migration
Enable secure data transfer using TLS for the migration path
Leverage existing Oracle GoldenGate infrastructure where beneficial
Ensure compatibility between source Oracle systems and target PostgreSQL

Options Analysis

Option 1: Direct AWS DMS with Oracle TLS Source Endpoint

Description: Configure AWS DMS to connect directly to one Oracle instance per datacenter with TLS enabled specifically for these connections
Advantages:
- Simpler architecture with direct DMS-to-Oracle connection
- Can enable TLS specifically for DMS connections
- Leverages AWS DMS's built-in CDC capabilities
Disadvantages:
- Need to reconfigure if the active Oracle instance changes
- Requires opening firewall rules from AWS to on-premises
- Additional load on production databases

Option 2: AWS DMS + Oracle GoldenGate as CDC Source

Description: Use existing GoldenGate infrastructure to consolidate changes to a dedicated Oracle instance, which becomes the source for AWS DMS
Advantages:
- Leverages existing GoldenGate replication
- Isolates migration traffic from production databases
- Single entry point for AWS DMS regardless of which instance is active
Disadvantages:
- Additional Oracle instance required
- More complex architecture
- Potential for slightly increased latency

Option 3: Oracle GoldenGate + AWS DMS with Initial Load Optimization

Description: Use AWS DMS for initial load with Oracle GoldenGate handling ongoing CDC to PostgreSQL
Advantages:
- Utilizes GoldenGate's proven replication capabilities
- Can optimize initial load through DMS parallel processing
- Potentially better control over transformation logic
Disadvantages:
- Requires GoldenGate license for heterogeneous replication
- More complex setup and maintenance
- Requires expertise in both GoldenGate and DMS

Option 4: AWS SCT + DMS with GoldenGate-Isolated Source

Description: Use AWS Schema Conversion Tool (SCT) for schema migration, DMS for data migration, with a GoldenGate-replicated Oracle instance as the source
Advantages:
- Comprehensive schema conversion capabilities
- Isolates production databases from migration workload
- Can implement TLS just on the dedicated migration path
Disadvantages:
- More components to manage
- Additional Oracle instance required
- Requires expertise across multiple tools

Decision

Recommended Approach: Option 2 - AWS DMS + Oracle GoldenGate as CDC Source

This approach provides the best balance of leveraging existing infrastructure, minimizing impact on production systems, and providing a clean migration path with TLS security.

Key benefits for this specific environment:

Leverages existing Direct Connect for secure connectivity between AWS and on-premises
Enables TLS specifically for the migration path without disrupting existing application connections
Isolates migration workload from production databases
Creates a single, stable source endpoint for AWS DMS regardless of which production instance is active

Network Architecture Diagrams

Current Application Access (Non-TLS)

┌─────────────────┐                                 ┌─────────────────────────┐
│                 │                                 │    Datacenter A         │
│    Business     │                                 │  ┌─────────────────┐    │
│    Application  │◄───Oracle Service Name──────────┼─►│  Oracle DB A1   │    │
│                 │  (load-balanced, non-TLS)       │  └─────────────────┘    │
└─────────────────┘                                 │                         │
                                                    │  ┌─────────────────┐    │
                                                    │  │  Oracle DB A2   │    │
                                                    │  └─────────────────┘    │
                                                    └─────────────────────────┘
                                                                │
                                                                │ GoldenGate
                                                                │ Replication
                                                                ▼
                                                    ┌─────────────────────────┐
                                                    │    Datacenter B         │
                                                    │  ┌─────────────────┐    │
                                                    │  │  Oracle DB B1   │    │
                                                    │  └─────────────────┘    │
                                                    │                         │
                                                    │  ┌─────────────────┐    │
                                                    │  │  Oracle DB B2   │    │
                                                    │  └─────────────────┘    │
                                                    └─────────────────────────┘

Oracle Service and Connection Proxy Architecture

┌─────────────────┐                                 ┌─────────────────────────┐
│                 │                                 │      Oracle Service     │
│    Business     │◄───────────────────────────────►│  ┌─────────────────┐   │
│    Application  │                                 │  │ Oracle Net      │   │
│                 │                                 │  │ Listener        │   │
└─────────────────┘                                 │  └───────┬─────────┘   │
                                                    │          │             │
                                                    │          ▼             │
                                                    │  ┌─────────────────┐   │
                                                    │  │ Connection      │   │
                                                    │  │ Manager Proxy   │   │
                                                    │  └───────┬─────────┘   │
                                                    └──────────┼─────────────┘
                                                               │
                         ┌────────────────────────────────────┐│┌─────────────────────────────────┐
                         │                                    ││                                  │
                         ▼                                    ▼▼                                  ▼
              ┌─────────────────────────┐        ┌─────────────────────────┐        ┌─────────────────────────┐
              │    Datacenter A         │        │    Datacenter A         │        │    Datacenter B         │
              │  ┌─────────────────┐    │        │  ┌─────────────────┐    │        │  ┌─────────────────┐    │
              │  │  Oracle DB A1   │    │        │  │  Oracle DB A2   │    │        │  │  Oracle DB B1   │    │
              │  └─────────────────┘    │        │  └─────────────────┘    │        │  └─────────────────┘    │
              └─────────────────────────┘        └─────────────────────────┘        └─────────────────────────┘

Oracle Service Components

Oracle Net Listener:
- Listens for client connection requests
- Receives initial connection through the service name (e.g., PROD_SERVICE)
- Forwards connection to the Connection Manager based on the service configuration
Connection Manager Proxy (CMAN):
- Acts as a proxy between clients and database servers
- Performs load balancing across available database instances
- Provides session multiplexing and connection concentration
- Can implement network access control rules

Service Configuration:

# Example tnsnames.ora configuration
PROD_SERVICE =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = oracle-cman.example.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVICE_NAME = PROD_SERVICE)
    )
  )

Migration-Specific Service Configuration:

Create a dedicated service for AWS DMS connections
Example:

MIGRATION_SERVICE =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCPS)(HOST = migration-oracle.example.com)(PORT = 2484))
    (CONNECT_DATA =
      (SERVICE_NAME = MIGRATION_SERVICE)
    )
    (SECURITY =
      (SSL_SERVER_CERT_DN = "CN=migration-oracle.example.com,O=Your Organization")
    )
  )

Configuration for TLS-Only Migration Service:
- Configure a separate Oracle Net Listener for TLS connections
- Create a dedicated Connection Manager profile for migration traffic
- Ensure the migration service points only to the dedicated Oracle instance
- Apply network access controls to allow only AWS DMS IP ranges

Proposed Migration Architecture with TLS for DMS

┌─────────────────┐                                 ┌─────────────────────────┐
│                 │                                 │     Oracle Service      │
│    Business     │◄───Oracle Service Name──────────┼─►┌─────────────────┐    │
│    Application  │  (load-balanced, non-TLS)       │  │ Connection      │    │
│                 │                                 │  │ Manager Proxy   │    │
└─────────────────┘                                 │  └──────┬──────────┘    │
                                                    └──────────┼───────────────┘
                                                               │
                                  ┌──────────────────────┐     │     ┌──────────────────────┐
                                  │                      │     │     │                      │
                                  ▼                      ▼     ▼     ▼                      ▼
                    ┌─────────────────────────┐   ┌─────────────────────────┐   ┌─────────────────────────┐
                    │    Datacenter A         │   │    Datacenter A         │   │    Datacenter B         │
                    │  ┌─────────────────┐    │   │  ┌─────────────────┐    │   │  ┌─────────────────┐    │
                    │  │  Oracle DB A1   │    │   │  │  Oracle DB A2   │    │   │  │  Oracle DB B1   │    │
                    │  │ (TDE enabled)   │    │   │  │ (TDE enabled)   │    │   │  │ (TDE enabled)   │    │
                    │  └─────────────────┘    │   │  └─────────────────┘    │   │  └─────────────────┘    │
                    └─────────────────────────┘   └─────────────────────────┘   └─────────────────────────┘
                                 │                           │                             │
                                 │                           │                             │
                                 │        GoldenGate         │                             │
                                 └───────────Replication─────┴─────────────────────────────┘
                                                   │
                                                   ▼
┌───────────────────────┐                 ┌─────────────────────────────────┐
│        AWS            │                 │         Dedicated Migration      │
│                       │ AWS Direct      │         Environment              │
│  ┌─────────────────┐  │ Connect         │  ┌───────────────────────────┐  │
│  │                 │  │                 │  │  Oracle TLS Listener      │  │
│  │  DMS            │  │                 │  │  (Port 2484)              │  │
│  │  Replication    │◄─┼─TLS Connection──┼─►│                           │  │
│  │  Instance       │  │ with TDE Wallet │  │  Migration Oracle DB      │  │
│  │                 │  │                 │  │  (TDE Keys Available)     │  │
│  └─────────────────┘  │                 │  └───────────────────────────┘  │
│         │             │                 └─────────────────────────────────┘
│         │             │
│         ▼             │
│  ┌─────────────────┐  │
│  │                 │  │
│  │  PostgreSQL     │  │
│  │  Target with    │  │
│  │  pgcrypto       │  │
│  └─────────────────┘  │
│                       │
└───────────────────────┘

Implementation Plan

1. Prepare the Environment

Create a dedicated Oracle instance to serve as the GoldenGate target and DMS source
Configure this instance with TLS enabled
Ensure network connectivity between AWS and the dedicated Oracle instance
Provision AWS DMS replication instance with appropriate specifications
TDE Considerations: Set up the Oracle wallet with TDE keys from production databases

2. Configure Oracle Connection Manager and Services

Set up Oracle Connection Manager (CMAN) if not already in use
Create a dedicated service for AWS DMS connections with TLS enforcement
Configure network access control rules to restrict access to the migration service
Test connection routing through both standard and TLS-enabled services

3. Configure Oracle GoldenGate

Configure GoldenGate to replicate from all Oracle instances to the dedicated instance
Verify that all necessary tables and schemas are included in the replication
TDE Considerations: Ensure GoldenGate has appropriate wallet access for TDE-encrypted columns
Test GoldenGate replication performance and consistency

3. Configure TLS for the Dedicated Oracle Instance

Generate TLS certificates for the dedicated Oracle instance
Configure Oracle listener for TLS connections
Set up Oracle wallet with certificates
Test TLS connectivity locally to ensure proper setup
Document TLS configuration for future reference

4. Setup AWS Schema Conversion Tool (SCT)

Use AWS SCT to convert Oracle schemas to PostgreSQL format
Review and adjust any incompatible objects or data types
Create the target schema in PostgreSQL

6. Configure AWS DMS

Create source endpoint connecting to the dedicated Oracle instance with TLS enabled:

{  "EndpointIdentifier": "oracle-tde-source",  "EndpointType": "source",  "EngineName": "oracle",  "Username": "dms_user",  "Password": "{{password}}",  "ServerName": "migration-oracle.example.com",  "Port": 2484,  "DatabaseName": "MIGDB",  "SslMode": "verify-full",  "CertificateArn": "arn:aws:dms:region:account:certificate:cert-id",  "ExtraConnectionAttributes": "useLogminerReader=N;useBfile=Y;accessAlternateDirectly=false;useAlternateFolderForOnline=true;oraclePathPrefix=/rdsdbdata/log/;usePathPrefix=/rdsdbdata/log/;replacePathPrefix=true"}

Create target endpoint connecting to AWS PostgreSQL
Create replication task with appropriate settings for full load + CDC
Configure table mappings and transformation rules as needed
TDE-specific configuration: Include Oracle wallet location in the DMS task settings

7. PostgreSQL Encryption Setup

Install and configure pgcrypto extension for column-level encryption
Set up AWS KMS integration for key management
Design encryption strategy for sensitive columns
Create stored procedures for encryption/decryption operations

8. Migration Execution

Perform initial full load while application continues to use Oracle
Validate data in PostgreSQL against Oracle source
TDE Data Validation: Verify that previously encrypted data is correctly processed
Enable ongoing CDC replication
Monitor replication lag and data consistency

9. Application Cutover

Update application configuration to connect to PostgreSQL
Monitor application performance and functionality
Maintain Oracle GoldenGate + DMS replication for a rollback period
Decommission Oracle infrastructure after successful validation

Security Considerations

TLS certificates must be properly configured on the dedicated Oracle instance
AWS DMS replication instance should be in a secure VPC with appropriate security groups
Leverage existing Direct Connect for secure connectivity between AWS and on-premises
Database credentials should be managed through AWS Secrets Manager
Audit logging should be enabled on both source and target databases
Network ACLs and security groups should be configured to allow only necessary traffic

Monitoring and Validation Strategy

Implement checksum validation for critical tables
Monitor DMS task metrics through CloudWatch
Set up alerts for replication lag exceeding thresholds
Regularly validate row counts and sample data between source and target
Perform application-level validation tests to ensure business logic integrity

Network and TLS Configuration Details

TLS Configuration for Oracle Migration Source

Certificate Setup:
- Generate a certificate signing request (CSR) for the dedicated Oracle instance
- Use an internal CA or AWS Certificate Manager Private CA to sign the certificate
- Import the certificate and CA chain into an Oracle wallet

Oracle Listener Configuration:

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = migration-oracle.example.com)(PORT = 1521))
      (ADDRESS = (PROTOCOL = TCPS)(HOST = migration-oracle.example.com)(PORT = 2484))
    )
  )

WALLET_LOCATION = (SOURCE = (METHOD = FILE)(METHOD_DATA = (DIRECTORY = /oracle/wallet)))
SSL_CLIENT_AUTHENTICATION = FALSE

AWS DMS Source Endpoint Configuration:
- Use TCPS protocol in the connection string
- Upload the CA certificate to AWS DMS
- Configure the endpoint with TLS mode enabled
Direct Connect Configuration:
- Ensure security groups in AWS VPC allow inbound connections on port 2484 (TCPS)
- Configure on-premises firewall to allow outbound connections from DMS IP range to Oracle on port 2484
- Use AWS Direct Connect routing policies to prioritize database migration traffic

PreviousNotes NextStep-by-Step CDC Recovery Guide: Oracle to PostgreSQL Migration

Last updated 10 months ago

Was this helpful?