Tutorial: Oracle to PostgreSQL with ora2pg¶

Comparative positioning note

This document is written from the perspective of Microsoft Azure, Cloud Scale Analytics, and CSA Loom. Any description of third-party or competing products, services, pricing, or capabilities is derived from publicly available documentation and sources believed accurate at the time of writing, and is provided for general comparison only. We do not claim expertise in, or authority over, any non-Microsoft product or service; the respective vendor's official documentation is the authoritative source for their offerings, which may change over time. Nothing here is intended to disparage any vendor — where a competing product has genuine advantages, we aim to note them honestly. Verify all third-party details against the vendor's current official documentation before making decisions.

Step-by-step walkthrough: install ora2pg, analyze an Oracle schema, convert to PostgreSQL, deploy to Azure Database for PostgreSQL Flexible Server, and validate the migration.

!!! info "Prerequisites" - Oracle Database 11g or later with read access - Azure Database for PostgreSQL Flexible Server provisioned - Linux workstation or VM (Ubuntu 20.04+ recommended) for ora2pg - Oracle Instant Client installed on the migration workstation - Perl DBI and DBD::Oracle modules - Network connectivity to both Oracle source and Azure PostgreSQL target

**Time estimate:** 3-4 hours for a small database (< 100 tables, < 10 GB)

Step 1: Set up the migration environment¶

1.1 Install Oracle Instant Client¶

# Download Oracle Instant Client for Linux
# https://www.oracle.com/database/technologies/instant-client/linux-x86-64-downloads.html

# Install basic + SDK packages
sudo mkdir -p /opt/oracle
cd /opt/oracle
unzip instantclient-basic-linux.x64-19.22.0.0.0dbru.zip
unzip instantclient-sdk-linux.x64-19.22.0.0.0dbru.zip

# Configure environment
echo 'export ORACLE_HOME=/opt/oracle/instantclient_19_22' >> ~/.bashrc
echo 'export LD_LIBRARY_PATH=$ORACLE_HOME:$LD_LIBRARY_PATH' >> ~/.bashrc
echo 'export PATH=$ORACLE_HOME:$PATH' >> ~/.bashrc
source ~/.bashrc

# Verify
sqlplus -V

1.2 Install Perl Oracle driver¶

# Install Perl and required modules
sudo apt-get update
sudo apt-get install -y perl libdbi-perl cpanminus libaio1

# Install DBD::Oracle
sudo cpanm DBD::Oracle

# Install DBD::Pg (for direct PostgreSQL connection)
sudo apt-get install -y libpq-dev
sudo cpanm DBD::Pg

# Verify
perl -e 'use DBD::Oracle; print "Oracle driver OK\n";'
perl -e 'use DBD::Pg; print "PostgreSQL driver OK\n";'

1.3 Install ora2pg¶

# Install from package manager
sudo apt-get install -y ora2pg

# Or install latest from source
git clone https://github.com/darold/ora2pg.git
cd ora2pg
perl Makefile.PL
make
sudo make install

# Verify
ora2pg --version
# Expected: Ora2Pg v24.x

Step 2: Configure ora2pg¶

2.1 Create project directory¶

mkdir -p /opt/migration/feddb
cd /opt/migration/feddb

# Initialize ora2pg project
ora2pg --project_base /opt/migration --init_project feddb

# This creates:
# /opt/migration/feddb/
# ├── config/
# │   └── ora2pg.conf
# ├── data/
# ├── reports/
# ├── schema/
# │   ├── tables/
# │   ├── views/
# │   ├── sequences/
# │   ├── triggers/
# │   ├── functions/
# │   ├── procedures/
# │   ├── packages/
# │   ├── types/
# │   └── grants/
# └── sources/

2.2 Edit configuration¶

cat > /opt/migration/feddb/config/ora2pg.conf << 'CONF'
#---------------------------------------------------------------------
# Oracle source connection
#---------------------------------------------------------------------
ORACLE_HOME     /opt/oracle/instantclient_19_22
ORACLE_DSN      dbi:Oracle:host=oracle-prod.agency.gov;sid=FEDDB;port=1521
ORACLE_USER     migration_reader
ORACLE_PWD      ***

#---------------------------------------------------------------------
# Schema to migrate
#---------------------------------------------------------------------
SCHEMA          APP_SCHEMA
EXPORT_SCHEMA   1

#---------------------------------------------------------------------
# PostgreSQL target connection
#---------------------------------------------------------------------
PG_DSN          dbi:Pg:dbname=feddb;host=pg-flex.postgres.database.azure.com;port=5432;sslmode=require
PG_USER         migration_admin
PG_PWD          ***

#---------------------------------------------------------------------
# Conversion options
#---------------------------------------------------------------------
# Map Oracle schema to PostgreSQL schema
PG_SCHEMA       app_schema

# Data type mapping overrides
DATA_TYPE       DATE:timestamp,LONG:text,LONG RAW:bytea,CLOB:text,NCLOB:text,BLOB:bytea,BFILE:bytea

# Convert Oracle NUMBER to appropriate PostgreSQL types
DEFAULT_NUMERIC numeric
PG_INTEGER_TYPE 1
PG_NUMERIC_TYPE numeric

# PL/SQL conversion
PLSQL_PGSQL     1
NULL_EQUAL_EMPTY 1

#---------------------------------------------------------------------
# Performance options
#---------------------------------------------------------------------
DATA_LIMIT      10000
JOBS            4
ORACLE_COPIES   4
PG_COPIES       4
LONGREADLEN     1048576

#---------------------------------------------------------------------
# Output
#---------------------------------------------------------------------
OUTPUT_DIR      /opt/migration/feddb/schema
FILE_PER_TABLE  1
FILE_PER_FUNCTION 1
CONF

Step 3: Run assessment report¶

3.1 Generate migration report¶

cd /opt/migration/feddb

# Generate assessment report
ora2pg -c config/ora2pg.conf -t SHOW_REPORT --estimate_cost \
    > reports/migration_assessment.txt

# View the report
cat reports/migration_assessment.txt

3.2 Interpreting the assessment¶

The report shows each object type with a migration cost estimate:

-----------------------------------------------
Migration level: B-5
-----------------------------------------------
Migration level: B-5 (moderate complexity)

Object type               Count  Cost   Details
---------------------------------------------------------
TABLE                     45     1.0    All tables convertible
VIEW                      12     1.5    3 views with Oracle-specific syntax
SEQUENCE                  15     0.5    Direct conversion
INDEX                     78     1.0    All standard indexes
CONSTRAINT                120    0.5    All constraints convertible
TRIGGER                   8      3.0    BEFORE triggers need conversion
FUNCTION                  25     2.0    Some DECODE/NVL patterns
PROCEDURE                 35     3.0    Complex PL/SQL in 8 procedures
PACKAGE                   5      5.0    Decompose to schemas + functions
TYPE                      3      2.0    Collection types need arrays
GRANT                     45     0.5    Role mapping needed
---------------------------------------------------------
Total estimated cost: 20.5 person-days
Migration level: B-5 (moderate, 15-30 days)

3.3 Complexity ratings¶

Level	Description	Typical effort
A-1 to A-3	Trivial to easy	1-5 days
B-4 to B-6	Moderate	5-30 days
B-7 to B-9	Difficult	30-60 days
C-10+	Very complex	60+ days

Step 4: Convert schema objects¶

4.1 Convert each object type¶

cd /opt/migration/feddb

# Convert in recommended order
ora2pg -c config/ora2pg.conf -t SEQUENCE -o schema/sequences/sequences.sql
ora2pg -c config/ora2pg.conf -t TABLE -o schema/tables/tables.sql
ora2pg -c config/ora2pg.conf -t VIEW -o schema/views/views.sql
ora2pg -c config/ora2pg.conf -t FUNCTION -o schema/functions/functions.sql
ora2pg -c config/ora2pg.conf -t PROCEDURE -o schema/procedures/procedures.sql
ora2pg -c config/ora2pg.conf -t PACKAGE -o schema/packages/packages.sql
ora2pg -c config/ora2pg.conf -t TRIGGER -o schema/triggers/triggers.sql
ora2pg -c config/ora2pg.conf -t TYPE -o schema/types/types.sql
ora2pg -c config/ora2pg.conf -t GRANT -o schema/grants/grants.sql

echo "Schema conversion complete. Review output files."

4.2 Review converted SQL¶

# Check for conversion warnings
grep -rn "TODO" schema/
grep -rn "FIXME" schema/
grep -rn "ora2pg" schema/  # ora2pg leaves comments for manual fixes

# Review a converted procedure
cat schema/procedures/procedures.sql | head -100

4.3 Manual fixes for common issues¶

-- Fix 1: Oracle SYSDATE -> PostgreSQL now()
-- ora2pg usually handles this, but verify:
-- Before: WHERE created_date > SYSDATE - 30
-- After:  WHERE created_date > now() - interval '30 days'

-- Fix 2: CONNECT BY -> recursive CTE
-- ora2pg may leave a TODO comment for complex hierarchical queries
-- See schema-migration.md for conversion patterns

-- Fix 3: Package global variables
-- Oracle packages with state variables need redesign
-- Option A: Use a configuration table
-- Option B: Use session variables (SET/SHOW)
-- Option C: Use function parameters instead of global state

-- Fix 4: AUTONOMOUS_TRANSACTION
-- See schema-migration.md for dblink pattern

Step 5: Create target database on Azure PostgreSQL¶

5.1 Provision Flexible Server (if not already done)¶

# Create resource group
az group create --name rg-oracle-migration --location eastus

# Create PostgreSQL Flexible Server
az postgres flexible-server create \
    --resource-group rg-oracle-migration \
    --name pg-flex-feddb \
    --location eastus \
    --admin-user migration_admin \
    --admin-password "***" \
    --sku-name Standard_D4ds_v5 \
    --tier GeneralPurpose \
    --storage-size 256 \
    --version 16 \
    --high-availability ZoneRedundant \
    --public-access None

# Configure private endpoint (if using VNet)
az network private-endpoint create \
    --resource-group rg-oracle-migration \
    --name pe-pg-flex-feddb \
    --vnet-name vnet-prod \
    --subnet snet-data \
    --private-connection-resource-id $(az postgres flexible-server show \
        --resource-group rg-oracle-migration --name pg-flex-feddb --query id -o tsv) \
    --group-id postgresqlServer \
    --connection-name pg-connection

# Enable required extensions
az postgres flexible-server parameter set \
    --resource-group rg-oracle-migration \
    --server-name pg-flex-feddb \
    --name azure.extensions \
    --value "pgcrypto,pg_cron,pgaudit,postgis,uuid-ossp"

5.2 Create database and schema¶

# Connect to PostgreSQL
psql "host=pg-flex-feddb.postgres.database.azure.com \
      dbname=postgres \
      user=migration_admin \
      sslmode=require"

# Create target database
CREATE DATABASE feddb WITH ENCODING 'UTF8' LC_COLLATE 'en_US.UTF-8';

# Connect to new database
\c feddb

# Create schema
CREATE SCHEMA IF NOT EXISTS app_schema;

Step 6: Deploy schema to Azure PostgreSQL¶

6.1 Deploy in order¶

# Deploy schema objects in dependency order
psql "host=pg-flex-feddb.postgres.database.azure.com dbname=feddb user=migration_admin sslmode=require" \
    -f schema/types/types.sql

psql "host=pg-flex-feddb.postgres.database.azure.com dbname=feddb user=migration_admin sslmode=require" \
    -f schema/sequences/sequences.sql

psql "host=pg-flex-feddb.postgres.database.azure.com dbname=feddb user=migration_admin sslmode=require" \
    -f schema/tables/tables.sql

psql "host=pg-flex-feddb.postgres.database.azure.com dbname=feddb user=migration_admin sslmode=require" \
    -f schema/views/views.sql

psql "host=pg-flex-feddb.postgres.database.azure.com dbname=feddb user=migration_admin sslmode=require" \
    -f schema/functions/functions.sql

psql "host=pg-flex-feddb.postgres.database.azure.com dbname=feddb user=migration_admin sslmode=require" \
    -f schema/procedures/procedures.sql

psql "host=pg-flex-feddb.postgres.database.azure.com dbname=feddb user=migration_admin sslmode=require" \
    -f schema/packages/packages.sql

psql "host=pg-flex-feddb.postgres.database.azure.com dbname=feddb user=migration_admin sslmode=require" \
    -f schema/triggers/triggers.sql

6.2 Verify schema deployment¶

-- Check object counts
SELECT
    CASE c.relkind
        WHEN 'r' THEN 'TABLE'
        WHEN 'v' THEN 'VIEW'
        WHEN 'S' THEN 'SEQUENCE'
        WHEN 'i' THEN 'INDEX'
    END AS object_type,
    COUNT(*) AS count
FROM pg_class c
JOIN pg_namespace n ON c.relnamespace = n.oid
WHERE n.nspname = 'app_schema'
  AND c.relkind IN ('r', 'v', 'S', 'i')
GROUP BY c.relkind
ORDER BY object_type;

-- Check functions and procedures
SELECT routine_name, routine_type
FROM information_schema.routines
WHERE routine_schema = 'app_schema'
ORDER BY routine_type, routine_name;

-- Check triggers
SELECT trigger_name, event_object_table, action_timing, event_manipulation
FROM information_schema.triggers
WHERE trigger_schema = 'app_schema'
ORDER BY event_object_table, trigger_name;

Step 7: Migrate data¶

7.1 Migrate data with ora2pg¶

# Migrate data using COPY mode (fastest)
ora2pg -c config/ora2pg.conf -t COPY -j 4 -o data/data_load.sql

# Monitor progress
# ora2pg shows per-table progress:
# [1/45] Exporting table EMPLOYEES (25,000 rows)...
# [2/45] Exporting table DEPARTMENTS (50 rows)...
# ...

# For very large tables, use direct PostgreSQL COPY
ora2pg -c config/ora2pg.conf -t COPY -j 4 --pg_dsn "dbi:Pg:dbname=feddb;host=pg-flex-feddb.postgres.database.azure.com;port=5432;sslmode=require"

7.2 Alternative: CSV export + COPY import¶

# For tables too large for ora2pg direct migration
# Step 1: Export from Oracle to CSV
sqlplus -S migration_reader/***@oracle-prod.agency.gov:1521/FEDDB << 'EOF'
SET COLSEP ','
SET PAGESIZE 0
SET LINESIZE 32767
SET FEEDBACK OFF
SET HEADING ON
SET TRIMSPOOL ON
SPOOL /opt/migration/feddb/data/transactions.csv
SELECT employee_id, department_id, salary, hire_date, name
FROM APP_SCHEMA.EMPLOYEES;
SPOOL OFF
EXIT;
EOF

# Step 2: Upload to Azure (if remote)
azcopy copy '/opt/migration/feddb/data/transactions.csv' \
    'https://stmigration.blob.core.windows.net/data/'

# Step 3: Import to PostgreSQL
psql "host=pg-flex-feddb.postgres.database.azure.com dbname=feddb user=migration_admin sslmode=require" \
    -c "\COPY app_schema.employees(employee_id, department_id, salary, hire_date, name) FROM '/opt/migration/feddb/data/transactions.csv' WITH CSV HEADER"

Step 8: Validate the migration¶

8.1 Row count comparison¶

# Create validation script
cat > /opt/migration/feddb/validate_counts.sh << 'SCRIPT'
#!/bin/bash
echo "=== Row Count Validation ==="
echo ""

TABLES="employees departments transactions audit_log documents"

for TABLE in $TABLES; do
    ORA_COUNT=$(sqlplus -S migration_reader/***@oracle-prod:1521/FEDDB << EOF
SET PAGESIZE 0 FEEDBACK OFF
SELECT COUNT(*) FROM APP_SCHEMA.$TABLE;
EXIT;
EOF
)
    PG_COUNT=$(psql -t -A \
        "host=pg-flex-feddb.postgres.database.azure.com dbname=feddb user=migration_admin sslmode=require" \
        -c "SELECT COUNT(*) FROM app_schema.$TABLE;")

    if [ "$ORA_COUNT" = "$PG_COUNT" ]; then
        echo "PASS: $TABLE - Oracle: $ORA_COUNT, PostgreSQL: $PG_COUNT"
    else
        echo "FAIL: $TABLE - Oracle: $ORA_COUNT, PostgreSQL: $PG_COUNT"
    fi
done
SCRIPT

chmod +x /opt/migration/feddb/validate_counts.sh
./validate_counts.sh

8.2 Data integrity checks¶

-- PostgreSQL: Verify data types
SELECT column_name, data_type, character_maximum_length,
       numeric_precision, numeric_scale
FROM information_schema.columns
WHERE table_schema = 'app_schema' AND table_name = 'employees'
ORDER BY ordinal_position;

-- Verify foreign key integrity
SELECT tc.table_name, tc.constraint_name,
       kcu.column_name, ccu.table_name AS foreign_table
FROM information_schema.table_constraints tc
JOIN information_schema.key_column_usage kcu
    ON tc.constraint_name = kcu.constraint_name
JOIN information_schema.constraint_column_usage ccu
    ON tc.constraint_name = ccu.constraint_name
WHERE tc.constraint_type = 'FOREIGN KEY'
  AND tc.table_schema = 'app_schema';

8.3 Functional testing¶

-- Test converted functions
SELECT app_schema.get_employee_salary(1001);

-- Test converted procedures
CALL app_schema.update_salary(1001, 85000.00);

-- Verify trigger behavior
INSERT INTO app_schema.employees (name, department_id, salary)
VALUES ('Test User', 10, 50000);
-- Check that audit trigger fired
SELECT * FROM app_schema.audit_log ORDER BY log_time DESC LIMIT 1;

Step 9: Configure ADF pipeline for CSA-in-a-Box¶

# Create ADF linked service for PostgreSQL
# In Azure Portal: ADF > Manage > Linked Services > New
# Select "Azure Database for PostgreSQL"
# Configure connection to pg-flex-feddb

# Create pipeline to copy data to OneLake
# ADF > Author > New Pipeline > "PG_to_OneLake_Incremental"
# Add Copy Activity:
#   Source: Azure PostgreSQL (query with watermark)
#   Sink: Lakehouse Table (OneLake Delta)
# Schedule: Every 15 minutes for near-real-time

Troubleshooting¶

Issue	Cause	Resolution
`DBD::Oracle install failed`	Missing Oracle client libraries	Ensure `ORACLE_HOME` and `LD_LIBRARY_PATH` are set
`ORA-12154: TNS could not resolve`	Oracle connectivity issue	Verify tnsnames.ora or host:port/sid syntax
`encoding mismatch`	Oracle charset vs PostgreSQL UTF-8	Set `NLS_LANG=AMERICAN_AMERICA.AL32UTF8` in environment
`function body syntax error`	PL/SQL not fully converted	Review ora2pg TODO comments, apply manual fixes
`COPY failed on large table`	Memory or network timeout	Reduce DATA_LIMIT, increase LONGREADLEN
`permission denied on extension`	Extension not allow-listed	Enable in Azure Portal > Server Parameters > azure.extensions

Maintainers: csa-inabox core team Last updated: 2026-04-30