Oracle to PostgreSQL Migration: A Complete Step-by-Step Guide

Prerequisites

• Oracle 11g or higher (LogMiner available from 9i but API differs)
• DBA_LOGMNR_CONTENTS SELECT privilege
• EXECUTE on DBMS_LOGMNR and DBMS_LOGMNR_D
• Supplemental logging enabled on source tables
• Python packages: oracledb, psycopg2-binary, loguru

Step 1: Enable Supplemental Logging

Oracle LogMiner (used for CDC) requires supplemental logging to capture before/after column values. Without it, only the changed columns appear in redo logs — not enough for CDC replication.

-- Enable database-level supplemental logging
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

-- Enable ALL COLUMNS logging for each table (required for UPDATE capture)
ALTER TABLE HR.EMPLOYEES ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;
ALTER TABLE HR.DEPARTMENTS ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;

-- Verify
SELECT LOG_MODE, SUPPLEMENTAL_LOG_DATA_MIN FROM V$DATABASE;

Step 2: Capture Current SCN

Before migrating any data, record the current SCN. This is your CDC resume point after bulk migration.

SELECT CURRENT_SCN, SYSDATE FROM V$DATABASE;
-- Save the SCN output: e.g. 4829103

Step 3: Run Schema Assessment

The assessment report (saved to assessment/) identifies:

• XMLTYPE columns → needs JSONB mapping
• CONNECT BY queries → needs WITH RECURSIVE
• Oracle packages → need flattening
• Circular FK dependencies
• NUMBER precision issues
• ROWID references

Step 4: DDL Conversion

DBMigrateAIPro generates PostgreSQL DDL from Oracle's ALL_TABLES, ALL_COLUMNS, ALL_CONSTRAINTS, ALL_INDEXES. Key type mappings:

Step 5: Bulk Data Migration

DBMigrateAIPro streams Oracle data using cx_Oracle fetchmany and loads into PostgreSQL via COPY. FKs and triggers are deferred during load.

Configuration tips: set batch_size=10000, parallel_workers=8-16 for large tables. Peak throughput: 87,000 rows/sec on modern hardware.

Step 6: PL/SQL Conversion

Key substitutions:

-- Oracle → PostgreSQL
ROWNUM          → LIMIT n
NVL(x, y)       → COALESCE(x, y)
DECODE(x,a,b,c) → CASE WHEN x=a THEN b ELSE c END
SYSDATE         → NOW()
TRUNC(date)     → DATE_TRUNC('day', date)
TO_CHAR(d,'...')→ TO_CHAR(d,'...')  -- mostly compatible
CONNECT BY      → WITH RECURSIVE cte AS (...)
SYS_GUID()      → gen_random_uuid()

Step 7: Validate

Row count comparison + optional SHA256 checksum per column. Report saved to reports/migration_YYYYMMDD_HHMMSS_comparison.html.

Step 8: Start CDC for Cutover

Use the SCN captured in Step 2 to start LogMiner streaming. Both databases stay in sync during cutover testing.

reader = LogMinerReader(
    config={...},
    tables=["HR.EMPLOYEES", "HR.DEPARTMENTS"],
    poll_interval=5.0,
)
for event in reader.stream(start_scn=4829103):
    apply_to_postgres(event)

Step 9: Cutover

1. Redirect app connection string to PostgreSQL
2. Verify application works
3. Stop CDC reader
4. Sync sequences: SELECT setval('hr.employees_id_seq', (SELECT MAX(id) FROM hr.employees))

With CDC keeping both databases in sync during validation, your actual application downtime is reduced to the seconds it takes to update a connection string — making true zero-downtime Oracle-to-PostgreSQL migration achievable for most production workloads.