Actions

Tags

Bilel.Sfaxi
Feb 20, 2025

Introduction

Database migration from Oracle to PostgreSQL has become increasingly common as organizations seek to reduce licensing costs, embrace open-source solutions, and modernize their database infrastructure. However, such migrations present significant challenges, particularly for business-critical applications that require 24/7 availability. Traditional migration approaches often require extended downtime windows, which can result in substantial business disruption and revenue loss.

Why Migrate from Oracle to PostgreSQL?

Several compelling factors drive organizations to consider this migration:

  • Significant cost savings on licensing and maintenance
  • PostgreSQL's robust feature set and growing enterprise adoption
  • Enhanced flexibility and freedom from vendor lock-in
  • Active open-source community and rapid innovation
  • Modern architecture supporting cloud-native deployments

 

Migration Challenges

The primary challenges in Oracle to PostgreSQL migrations include:

  • Ensuring data consistency and integrity during migration
  • Minimizing application downtime
  • Managing schema and data type differences
  • Handling large data volumes efficiently
  • Maintaining transaction consistency
  • Converting Oracle-specific features and stored procedures

Solution Overview

This guide presents a comprehensive approach to achieving a near-zero downtime migration using two powerful tools:

  • Ora2Pg: An open-source tool specifically designed for Oracle to PostgreSQL migrations, handling the initial schema conversion and data load
  • SharePlex: A robust Change Data Capture (CDC) solution that ensures continuous data synchronization during the migration process

By combining these tools, we create a reliable migration pipeline that:

  1. Minimizes business disruption through near-zero downtime
  2. Ensures data consistency throughout the migration process
  3. Provides fallback options if issues arise
  4. Supports gradual testing and validation
  5. Enables precise control over the migration timeline

This technical guide will walk through the detailed steps, configurations, and best practices needed to successfully execute such a migration, while highlighting potential pitfalls and their solutions.

Prerequisites

Before beginning the migration, ensure you have:

  • Oracle source database
  • PostgreSQL target database
  • SharePlex software installed
  • Ora2Pg tool installed
  • Sufficient network connectivity and authorized flows between source and target systems
  • Appropriate access and permissions on both databases and operating systems

 

High-Level Migration Architecture

The migration will follow these key stages:

  1. Schema conversion from Oracle to PostgreSQL (this is where ora2pg will be used first for the structure of the tables)
  2. SharePlex installation on source and target
  3. Datasource declaration on source and on target
  4. SharePlex daemon startup on source and on target
  5. SharePlex process Post stop (since the target DB doesn’t contain data yet)
  6. Creation and activation of the SharePlex replication configuration file
  7. PostgreSQL initial data load from Oracle based on an SCN (this is where ora2pg will be used second time for the data inside the tables)
  8. Triggers/constraints temporarily deactivation on target
  9. SharePlex process Post reconcile on the given SCN
  10. SharePlex process Post startup
  11. Backlog ingestion
  12. When the Backlog tends to zero, it’s a good moment to switchover the users and the application to the new system, with approval from the Business of course for the right moment. Don’t forget to reactivate the triggers/constraints on the target DB.
  13. Optionally, activate SharePlex in the other way back from PostgreSQL to Oracle in case of failback scenario.

Detailed Migration Process

  1. Preparatory Steps

1.1 Oracle Source Database Preparation

  • Verify Oracle database is in ARCHIVELOG mode, especially for high transactional environments. This is a best practice for production environments.
  • Enable supplemental logging:

Alter Script:

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY, UNIQUE, FOREIGN KEY) COLUMNS;

Check Script:

SELECT LOG_MODE, NAME, SUPPLEMENTAL_LOG_DATA_MIN, SUPPLEMENTAL_LOG_DATA_PK, SUPPLEMENTAL_LOG_DATA_UI, SUPPLEMENTAL_LOG_DATA_FK, SUPPLEMENTAL_LOG_DATA_ALL from v$database;


  • Create a dedicated migration user with necessary permissions:

SQL Script:

CREATE USER migration_user IDENTIFIED BY strong_password;
GRANT CREATE SESSION, SELECT ANY TABLE TO migration_user;

1.2 Ora2Pg Configuration and Initial Load

  • Install Ora2Pg on the migration server, or in the source, or in the target depending on how you cant to manage the network flows.
  • Create the Ora2Pg configuration file (ora2pg.conf):

Configuration file :

ORACLE_HOME /path/to/oracle/home
ORACLE_DSN dbi:Oracle:host=source_host;sid=source_sid
ORACLE_USER migration_user
ORACLE_PWD strong_password
# Export configuration
OUTPUT_DIR /path/to/migration/output
TYPE TABLE
SCHEMA source_schema
# Conversion options
PG_SUPPORTS_JSONB 1
PG_NUMERIC_TYPE 1


  • Generate PostgreSQL schema and data migration scripts:

Bash commands:

ora2pg -c ora2pg.conf -t SCHEMA > create_schema.sql
ora2pg -c ora2pg.conf -t TABLE > create_tables.sql
ora2pg -c ora2pg.conf -t DATA –scn xxxxx > data_migration.sql


The last command will be issued on Step 7 to export the data of the initial load to an “export/dump” file, based on the provided SCN number (xxxxx).

  • Apply generated scripts to PostgreSQL:

Bash commands:

psql -f create_schema.sql target_database
psql -f create_tables.sql target_database
psql -f data_migration.sql target_database


The last command will be issued on Step 7 to load to the data export file from the Oracle DB to the PostgreSQL instance.

 

  1. SharePlex Configuration

2.1 Installation and Initial Configuration

  • Install SharePlex on source and target servers
    • Optionally, you can install SharePlex on a remote box from source and target acting as a central migration box. Network flows should be reviewed for such deployments.
  • Configure DB source and target on each installation (ora_setup and pg_setup)
  • Create a SharePlex configuration file that contains the tables or the schema to replicate. It’s a simple text file that maps source tables/schemas to target ones.

Example of SharePlex configuration:

#Define source Oracle DB
Datasource:o.sidA

#Define the schema and tables mappings
#3 columns: Source, Target and Routing Map
expand scott.prod% not (%temp%) hal.%   sysb@r.pgB

More examples in the SharePlex online documentation are accessible here.

 

2.2 Continuous Data Synchronization

  • Use SharePlex to capture and replicate changes missed from the Oracle Export, during Ora2Pg initial load.
  • Since the SharePlex process “Post” is stopped, the “Post Queue” will buffer all these instructions as a “Backlog” to reconcile (with SCN) and ingest later.
  • Verify data consistency post-Ora2Pg migration

 

  1. Migration Cutover Strategy

3.1 Pre-Cutover Checklist

  • Verify near-zero replication lag (Post Queue Backlog tends to zero)
  • Confirm data consistency between Oracle and PostgreSQL
  • Prepare application connection strings for new database
  • Plan maintenance window

3.2 Cutover Procedure

  1. Pause write operations on source database
  2. Wait for final data sync
  3. Switch application connections to PostgreSQL
  4. Verify application functionality
  5. Keep Oracle database as immediate rollback option by preparing a SharePlex configuration file to replicate the way back from PostgreSQL to Oracle.

 

Potential Pitfalls and Mitigations

Common Migration Challenges

  1. Data Type Conversions
    • Ora2Pg provides advanced type mapping
    • Verify PostgreSQL-compatible type translations
  2. Large Object (LOB) Migrations
    • Ora2Pg supports LOB migrations
    • SharePlex handles ongoing LOB replication
    • Test large object data thoroughly

HINT : Generally, we dedicate inside SharePlex a Named Queue for the LOB that will run in parallel of the transactional activity, to accelerate the replication flow. But, keep in mind that these LOBs should not have constraints with other tables in the transactional activity.

3. Performance Optimization

    • Use Ora2Pg parallel export options
    • Configure SharePlex for efficient change data capture : Many “Capture” and “Post” parameters to enhance the processes, but require more system resources.
    • Use Fast Disks for the export to reduce the time especially for exporting LOBs.
    • Benchmark and tune PostgreSQL before cutover

4. Schema Complexity

    • Sequences
    • Materialized views
    • Specific constraint types
    • Manually review and adjust automatically generated schema
    • Handle complex Oracle-specific features:

 

Monitoring and Validation Tools

  • SharePlex monitoring scripts
  • Ora2Pg migration reports
  • Database-level consistency checks
  • Application-level smoke tests

 

Conclusion

Migrating from Oracle to PostgreSQL using Ora2Pg and SharePlex requires meticulous planning, thorough testing, and a systematic approach. By leveraging Ora2Pg for initial schema and data migration, and SharePlex for continuous replication, you can achieve a smooth, near-zero downtime database migration.

Disclaimer: Always test migration procedures in a staging environment before production implementation.

Related Content