Loading...

中文 日本語
By: Dylan

Need to migrate Oracle to PostgreSQL using Ora2Pg?

Many organizations are migrating from Oracle to PostgreSQL to reduce licensing costs, using more advanced features and higher flexibility. For many teams, moving to PostgreSQL provides a more cost-efficient and future-proof database strategy.

However, migrating from Oracle to PostgreSQL is not always straightforward. Oracle databases often contain complex schemas, stored procedures, triggers, and proprietary SQL features that are not directly compatible with PostgreSQL.

This is where Ora2Pg becomes extremely valuable. Ora2Pg is a popular open-source migration tool designed to simplify Oracle-to-PostgreSQL database conversion. It analyzes Oracle database structures, generates migration reports, converts schemas into PostgreSQL-compatible SQL, and exports data that can be imported into PostgreSQL.

You can just keep on reading; we will demonstrate the detailed steps of Oracle to PostgreSQL migration step by step using Ora2Pg. This guide is tested on a CentOS environment, and it will cover installation, configuration, testing, migration assessment, export, and import steps, etc. 

Part 1. Install Ora2Pg

Follow these structured steps (tested on CentOS 7/8) to build and set up the tool with all required dependencies:

Step 1 – Install Core System & Perl Dependencies

Use YUM to install mandatory packages for ora2pg compilation and operation:

sudo yum install gcc postgresql-devel perl-DBI perl-ExtUtils-MakeMaker perl-devel perl-DBD-Pg -y

Verify installation with perl -MDBI -e ‘print $DBI::VERSION’ to confirm no missing Perl modules.

Step 2 – Install Oracle Instant Client SDK

1. Download Oracle Basic and SDK RPMs (12.2+) from Oracle’s official repository, then install locally:

sudo yum install oracle-instantclient12.2-basic-12.2.0.1.0-1.x86_64.rpm oracle-instantclient12.2-devel-12.2.0.1.0-1.x86_64.rpm -y

2. Configure environment variables in ~/.bash_profile:

export ORACLE_HOME=/usr/lib/oracle/12.2/client64
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
export PATH=$ORACLE_HOME/bin:$PATH

Test connectivity with sqlplus username/password@oracle_host:1521/service_name to confirm the client works.

Step 3 – Build & Install DBD::Oracle Perl Module

This module enables ora2pg to connect to Oracle; install via CPAN for simplicity:

cpan DBD::Oracle

Validate with perl -MDBD::Oracle -e ‘print $DBD::Oracle::VERSION’ (no errors = success).

Step 4 – Build & Install Ora2pg from Source

Download the stable v20.0 release and compile:

wget https://github.com/darold/ora2pg/archive/refs/tags/v20.0.tar.gz
tar -xvf v20.0.tar.gz && cd ora2pg-20.0
perl Makefile.PL
make && sudo make install

Confirm global installation with ora2pg -v (should return “ora2pg v20.0”).

Part 2. Steps to Configure Ora2pg (ora2pg.conf)

Configuring the ora2pg.conf file is the critical step for Oracle migration to PostgreSQL. This file defines how Ora2Pg connects to your Oracle instance, what data to migrate, and how to format output for PostgreSQL compatibility.

Step 1 – Locate the Ora2pg Configuration File

After installation, the default ora2pg.conf is typically stored at:

/usr/local/share/ora2pg/ora2pg.conf.dist

Copy the template to a usable config file (preserve the original):

cp /usr/local/share/ora2pg/ora2pg.conf.dist /etc/ora2pg.conf

Step 2 – Update Core Configuration Parameters

Edit /etc/ora2pg.conf with a text editor (e.g., vim) to match your Oracle/PostgreSQL environment—update these non-negotiable parameters:

# PostgreSQL version (11+ recommended)
PG_VERSION = 14

# Oracle connection string (format: dbi:Oracle:host=ORACLE_HOST;port=1521;sid=ORACLE_SID)
ORACLE_DSN = dbi:Oracle:host=192.168.1.100;port=1521;sid=ORCL

# Oracle credentials (read access minimum)
ORACLE_USER = oracle_migration_user
ORACLE_PWD = your_secure_password

# Target Oracle schema to migrate
SCHEMA = HR

# Enable full schema export
EXPORT_SCHEMA = 1

# Export user grants/privileges
USER_GRANTS = 1

# Set transaction mode to read-only (avoids Oracle lock issues)
TRANSACTION = READONLY

Step 3 – Validate the Configuration

Test for syntax errors and connectivity with a quick ora2pg check:

ora2pg -t SHOW_VERSION -c /etc/ora2pg.conf

A successful output will display the ora2pg version and confirm Oracle client connectivity

Part 3. Test Ora2Pg Connectivity

Before getting down to any migration scripts or reports, it is suggested to have a test for Ora2Pg connectivity to avoid failed migration, broken exports, and waste time later in the process.

Follow these simple tests to validate your setup:

Step 1 – Test Basic Ora2pg Installation & Config

First, confirm ora2pg recognizes your configuration file and runs without core errors:

ora2pg -t SHOW_VERSION -c /etc/ora2pg.conf

A successful result will print the ora2pg version (e.g., “ora2pg v20.0”) and confirm the Oracle DBD module is loaded. If you see “DBD::Oracle not found”, re-install the DBD::Oracle Perl module (Step 3 of Ora2pg Installation).

Step 2 – Test Oracle Database Connectivity

Run a targeted test to validate ora2pg can reach your Oracle instance:

ora2pg -t TEST_CONNECTION -c /etc/ora2pg.conf

If successful, you’ll see “Oracle connection OK”—common errors to troubleshoot here include:

  • “Invalid Oracle DSN”: Double-check the ORACLE_DSN string in ora2pg.conf (verify host, port, SID/service name)
  • “Authentication failed”: Confirm ORACLE_USER and ORACLE_PWD are correct, and the user has read access to the target schema
  • “LD_LIBRARY_PATH error”: Recheck Oracle Instant Client environment variables in ~/.bash_profile

Step 3 – Confirm Oracle Schema Scan

Finally, run a quick scan of your target Oracle schema to ensure ora2pg can read its objects:

ora2pg -t SHOW_SCHEMA -c /etc/ora2pg.conf

This command will list all tables, views, and sequences in your target SCHEMA (e.g., HR)—a complete list confirms ora2pg has full read access and is ready for migration assessment.

Part 4. Ora2Pg Migration Assessment

Oracle has many proprietary features (synonyms, packages like DBMS_*, special PL/SQL logic, nested transactions) that Ora2Pg cannot automatically convert. You should assess the migration risk and business downtime to determine whether to proceed.

Here is how to assess migration with Ora2Pg

Step 1 – Generate a Full Migration Assessment Report

Run this command to scan your Oracle schema and produce a detailed, actionable report.

ora2pg -t SHOW_REPORT --estimate_cost -c /etc/ora2pg.conf > oracle_to_postgres_assessment.txt

The –estimate_cost flag calculates a migration difficulty score (1-10) and breaks down effort by object type (tables, PL/SQL, partitions, grants, etc.).

Step 2 – Interpret the Assessment Report

Review the oracle_to_postgres_assessment.txt file to categorize objects by convertibility:

  1. Fully Auto-Convertible: Tables, basic indexes, sequences, and primary/foreign key constraints (no manual edits needed).
  2. Partially Convertible: Views, range/list partitions, and simple stored procedures (minor Postgres syntax tweaks required).
  3. Non-Convertible: Oracle-specific packages (e.g., DBMS_UTILITY), synonyms, or advanced PL/SQL (require full manual porting to PL/pgSQL).

Step 3 – Prioritize Migration Work

Use the report’s difficulty score to allocate resources:

  1. Score 1-3 (Low): Almost full auto-conversion—focus on validating generated scripts only.
  2. Score 4-7 (Moderate): Plan for PL/SQL → PL/pgSQL adjustments and partition syntax fixes.
  3. Score 8-10 (High): Rewrite custom Oracle packages/functions for Postgres compatibility before migration.

Part 4. Perform Oracle to PostgreSQL Migration using Ora2Pg

After validating connectivity and completing a migration assessment, you can migrate Oracle to PostgreSQL with the following steps.

Step 1 – Export Oracle Data/Schema to Postgres-Ready SQL

Choose an export mode that matches your assessment (e.g., schema-only for high-complexity PL/SQL, full export for low-risk databases)

Full Export (Schema + data + Grants):

ora2pg -c /etc/ora2pg.conf -o oracle_to_postgres_migration.sql -d
  • -c: Path to your configured ora2pg.conf
  • -o: Name of the output Postgres-compatible SQL file
  • -d: Enable data export (omit for schema-only exports)

Custom Export Options (For Targeted Migrations)

✵ Schema-only export (no data, ideal for testing structure first):

ora2pg -c /etc/ora2pg.conf -o oracle_schema_only.sql -t SCHEMA

✵ Data-only export (for existing Postgres schemas):

ora2pg -c /etc/ora2pg.conf -o oracle_data_only.sql -t DATA

✵ Grants-only export (user permissions):

ora2pg -c /etc/ora2pg.conf -o oracle_grants_only.sql -t GRANTS

Step 2 – Validate the Generated SQL Script

Never import an unvalidated script—fix Oracle-specific syntax ora2pg may have missed (per your assessment):

1. Scan for unconvertible Oracle keywords (e.g., SYNONYM, DBMS_*, NESTED TABLE):

grep -i "SYNONYM\|DBMS_" oracle_to_postgres_migration.sql

2. Fix identified issues (e.g., replace synonyms with Postgres views, rewrite PL/SQL to PL/pgSQL)

3. Dry-run the script in Postgres to catch syntax errors:

psql -h your_postgres_host -U postgres_user -d target_db -f oracle_to_postgres_migration.sql --dry-run

Step 3 – Import the SQL Script to PostgreSQL

Use a dedicated Postgres migration user (with CREATE/INSERT privileges) to load the validated script:

psql -h your_postgres_host -p 5432 -U postgres_migration_user -d target_postgres_db -f oracle_to_postgres_migration.sql > migration_import_log.txt 2>&1
  • -h: Postgres server host (use localhost for local migrations)
  • -p: Default Postgres port (5432; adjust if custom)
  • -U: Postgres user with sufficient privileges
  • -d: Target Postgres database name
  • > migration_import_log.txt 2>&1: Captures all logs (critical for debugging failed imports)

This is for importing script and log all output/errors for troubleshooting

Step 4 – Verify Migration Completeness

1. Cross-check Postgres against your Oracle source to confirm no data/objects are missing:

List imported tables (match count with Oracle):

SELECT table_name FROM information_schema.tables WHERE table_schema = 'hr';

2. Validate row counts for critical tables (e.g., employees):

-- Postgres count
SELECT COUNT(*) FROM hr.employees;

-- Oracle count (cross-reference)
SELECT COUNT(*) FROM hr.employees;

3. Test key constraints (primary/foreign keys) to ensure data integrity:

SELECT employee_id, COUNT(*) FROM hr.employees GROUP BY employee_id HAVING COUNT(*) > 1;

This is to check for duplicate primary keys, and it should return 0 rows

Note:

  • For large datasets (100GB+): Use ora2pg –parallel 4 to split exports into chunks, and disable Postgres indexes/constraints during import (re-enable post-import for faster load times)
  • Production migrations: Run during low-traffic maintenance windows, and take full backups of Oracle (pre-export) and Postgres (post-import)
  • Incremental sync: For active Oracle databases, use ora2pg -t INCREMENTAL to sync new/updated data after the initial full export

    Part 5. Common Ora2pg Migration Issues & Troubleshooting

    Even with careful setup, you may face typical errors during an Oracle to Postgres migration with ora2pg. Below are the most common problems and straightforward fixes to keep your process on track.

    1. Missing Perl Modules (DBD::Oracle / DBI Errors)

    This error appears when required Perl libraries are missing or broken. Reinstall DBD::Oracle and DBI modules, then confirm they load correctly with version-check commands.

    2. Oracle Instant Client Path Issues

    Ora2pg cannot find Oracle client files due to wrong environment variables. Check and fix ORACLE_HOME and LD_LIBRARY_PATH, then reload your system profile.

    3. Oracle Connection or Authentication Failures

    Wrong credentials, invalid DSN format, or network blocks cause connection failures. Verify username, password, host, port, and SID/service name in ora2pg.conf.

    4. PostgreSQL SQL Script Import Errors

    These are caused by Oracle-only syntax that ora2pg can’t automatically convert. Use your migration assessment to find unsupported objects and rewrite them manually.

    5. Grants & Privileges Export Failures

    The Oracle migration user doesn’t have enough read permissions. Grant proper read access on the target schema and re-run the export.

    6. Slow Export on Large Datasets

    Ora2pg runs slowly by default on big tables because of single-thread processing. Use the –parallel flag to enable multi-threaded export and speed up the process.

    7. Partition or Advanced Object Conversion Issues

    Some Oracle partitions and custom objects don’t map directly to Postgres. Adjust settings in ora2pg.conf and manually correct unsupported structures.

    Alternative to Ora2Pg for More Secure and Zero-Downtime Migration

    While Ora2Pg is a viable open-source tool for small-scale, low-complexity Oracle-to-PostgreSQL migrations, it faces critical limitations for enterprise-level scenarios, especially with large datasets (100GB+), mission-critical production environments.

    For organizations seeking a faster, more reliable, and zero-downtime migration solution, Info2Soft’s i2Stream, which a leading heterogeneous database replication product, addresses ora2pg’s core pain points and delivers enterprise-grade capabilities for Oracle-to-Postgres migration.

    Key Advantages of i2Stream over Ora2Pg:

    • Zero Downtime Migration: i2Stream enables real-time data replication during the migration process. Data is continuously synchronized from Oracle to PostgreSQL while applications remain online, allowing organizations to complete migrations without disrupting business operations or customer access.
    • Effortless Large Dataset Handling: i2Stream uses intelligent parallel synchronization to automatically split and transfer large datasets efficiently. It can maintain PostgreSQL indexes and constraints during synchronization, reducing the need for additional tuning or post-migration validation.
    • Real-Time Incremental Sync: i2Stream captures Oracle redo logs and replicates changes to PostgreSQL in near real time. This allows continuous incremental synchronization throughout the migration lifecycle, helping ensure data consistency between the source and target databases.
    • Enterprise-Grade Automation: Ora2Pg requires manual conversion of PL/SQL code and proprietary syntax. but i2Stream adapts Oracle data structures and logic to PostgreSQL. This reduces the amount of manual code rewriting required when migrating specific objects.
    • Automatic Data Validation and Monitor: Automate data validation through MD5 checksum comparisons, with visual drift analysis and one-click repair. With a unified dashboard, administrators can track all throughput, latency, status, in real time.
    FREE Trial for 60-Day
    Secure Download

    Conclusion

    Oracle to PostgreSQL migration using Ora2Pg is a good choice in a small-scale, low-complexity scenario. Following the step-by-step process in this guide, you will have a smooth basic migration from Oracle to PostgreSQL databases.

    However, for some enterprise-grade use cases, including mandatory downtime, large datasets, Info2Soft’s i2Stream is the better choice. Its enterprise-grade heterogeneous replication capabilities deliver zero-downtime migration, real-time sync, automated data validation, solving all of ora2pg’s core pain points for a low-risk, efficient transition.

    In addition, i2Stream also has broad compatibility with many other databases, and you can migrate data between Oracle, MySQL, MS SQL Server, etc.

     

    {{ author_info.name }}
    {{author_info.introduction || "No brief introduction for now"}}

    More Related Articles

    {{item.title}}
    {{item.excerpt}}
    Read Article
    Table of Contents:
    Stay Updated on Latest Tips
    Subscribe to our newsletter for the latest insights, news, exclusive content. You can unsubscribe at any time.
    Subscribe
    Ready to Enhance Business Data Security?
    Start a 60-day free trial or view demo to see how Info2Soft protects enterprise data.
    Free Trial Contact Sales
    Table of Contents:
    {{ country.name }}
    Please fill out the form and submit it, our customer service representative will contact you soon.
    By submitting this form, I confirm that I have read and agree to the Privacy Notice.
    {{ isSubmitting ? 'Submitting...' : 'Submit' }}