Oracle to PostgreSQL Migration Using Ora2Pg: Step by Step

The short answer

To migrate from Oracle to PostgreSQL with Ora2Pg, install it next to the Oracle client and the DBD::Oracle Perl driver, generate a project with --init_project, point ora2pg.conf at one Oracle schema, and size the job with ora2pg -t SHOW_REPORT --estimate_cost. Then export the schema and the PL/SQL one object type at a time, load the DDL into PostgreSQL, copy the data, and compare the two databases with the TEST, TEST_COUNT and TEST_DATA actions before anyone switches a connection string.

Ora2Pg is free software by Gilles Darold, released under the GNU GPL version 3 or later. The newest release on GitHub is v25.0, published in April 2025. It does the mechanical work well. Its own documentation says plainly that the PL/SQL it converts for functions, procedures, packages and triggers still has to be reviewed. This tutorial covers the tool itself. The Oracle to PostgreSQL migration guide covers the programme around it, and PL/SQL to PL/pgSQL conversion covers the code patterns you will fix by hand.

What you need before you start

Run Ora2Pg on a Linux host that can reach both databases. It works on Windows too, but the parallel options (JOBS and ORACLE_COPIES) are disabled there.

Step 1: Install Ora2Pg and DBD::Oracle

Unpack the Instant Client ZIP archives into one directory. With the ZIP layout, ORACLE_HOME and LD_LIBRARY_PATH both point at that directory. Install the Perl drivers, then build Ora2Pg from the release archive:

# Oracle Instant Client unpacked into /opt/oracle/instantclient
export ORACLE_HOME=/opt/oracle/instantclient
export LD_LIBRARY_PATH=/opt/oracle/instantclient
perl -MCPAN -e 'install DBD::Oracle'
perl -MCPAN -e 'install DBD::Pg'

# Ora2Pg v25.0 source archive from GitHub
tar xzf ora2pg-25.0.tar.gz
cd ora2pg-25.0/
perl Makefile.PL
make && make install
ora2pg --version

The install puts ora2pg in /usr/local/bin/ and a template ora2pg.conf in /etc/ora2pg/. If CPAN cannot build DBD::Oracle, the cause is almost always a missing SDK package or an unset ORACLE_HOME.

Step 2: Generate a project and set ora2pg.conf

A project tree keeps the reports, the converted DDL and the untouched Oracle source apart. One command builds it:

ora2pg --project_base /app/migration --init_project hr_migration

That creates schema/ for converted PostgreSQL code (one subdirectory per object type), sources/ for the original Oracle code, data/, config/ and reports/, plus two scripts: export_schema.sh and import_all.sh. The generated config/ora2pg.conf already switches on sensible project settings, such as one output file per table, index, constraint and function, TRUNCATE_TABLE and DISABLE_TRIGGERS for data loads, and NULL_EQUAL_EMPTY. You still have to tell it where Oracle is. Each line is an upper-case directive, white space, then the value:

ORACLE_HOME      /opt/oracle/instantclient
ORACLE_DSN       dbi:Oracle:host=oradb.example.com;service_name=HRPDB;port=1521
ORACLE_USER      ora2pg_reader
SCHEMA           HR
EXPORT_SCHEMA    1
PG_VERSION       17
PG_DSN           dbi:Pg:dbname=hr;host=pg.example.com;port=5432;sslmode=require
PG_USER          hr_owner
PG_INTEGER_TYPE  1
DEFAULT_NUMERIC  numeric

Leave ORACLE_PWD and PG_PWD out of the file. Ora2Pg reads the Oracle password from the ORA2PG_PASSWD environment variable, or prompts for both passwords when the Term::ReadKey module is installed. The directives that change results most:

Step 3: Test the connection and look around

The documentation's advice is to spend time here, because most installation problems surface on the first connection:

cd /app/migration/hr_migration
ora2pg -t SHOW_VERSION -c config/ora2pg.conf
ora2pg -t SHOW_SCHEMA  -c config/ora2pg.conf
ora2pg -t SHOW_TABLE   -c config/ora2pg.conf
ora2pg -t SHOW_COLUMN  -c config/ora2pg.conf
ora2pg -t SHOW_ENCODING -c config/ora2pg.conf

SHOW_COLUMN prints every column with the PostgreSQL type Ora2Pg will use, and warns when an Oracle name is a PostgreSQL reserved word. Rename those early, before application code is touched. SHOW_ENCODING reports the client encodings Ora2Pg will use against the database's real character set. If an export comes back with nothing but a transaction header and footer, the login lacks dictionary access: grant more, or set USER_GRANTS.

Step 4: Run the migration assessment

The assessment is the reason many teams install Ora2Pg first. It inspects every object and every stored routine and reports what can and cannot convert automatically:

ora2pg -t SHOW_REPORT --estimate_cost --cost_unit_value 10 --dump_as_html \
  -c config/ora2pg.conf > reports/report.html

The report lists each object type with a count, an invalid count, an estimated cost in migration units and a comment on how it will be handled. The documentation says a unit represents about five minutes of work for a PostgreSQL expert, and suggests a value of ten for a first migration, which is why the command above passes --cost_unit_value 10. It also scores the database with a migration level and a technical level:

• A: a migration that might run automatically. B: code rewriting is needed, within the person-day limit set by --human_days_limit. C: code rewriting above that limit.
• 1 (trivial, no stored functions or triggers) up to 5 (stored functions or triggers that need rewriting).

Read the total as a ranking signal, not a plan. The author calls the scoring method a work in progress, and it counts only database objects: application SQL, testing, data movement and the cutover sit outside it. That is why it is most useful across many schemas. The ora2pg_scanner script takes a CSV of connections (type, schema, DSN, user, password), writes one assessment line per schema and an HTML report per database, and its -t flag checks every connection before the real run. The Oracle migration cost factors post covers what the report leaves out.

Step 5: Export the schema and the PL/SQL

The quickest route is the generated script. export_schema.sh writes the table, column and HTML assessment reports into reports/, then runs ora2pg -p -t TYPE once for each object type, putting converted code in schema/ and the original Oracle code in sources/ so reviewers can compare them side by side. The -p flag switches on PL/SQL to PL/pgSQL conversion. At the end it prints the command to export the data.

./export_schema.sh

To rerun a single type after changing a directive, call it directly:

ora2pg -p -t PACKAGE -o package.sql -b schema/packages -c config/ora2pg.conf
ora2pg -p -t PROCEDURE -o procedure.sql -b schema/procedures -c config/ora2pg.conf

By default Ora2Pg exports only valid PL/SQL. If the report shows invalid objects, ask Oracle to recompile them with COMPILE_SCHEMA, or export them anyway with EXPORT_INVALID and treat them as suspect.

Step 6: Load the schema into PostgreSQL

You can feed each file to psql in dependency order, or let import_all.sh do it. The script needs a database name and an owner. It can create both, then asks before importing each object type:

./import_all.sh -d hr -o hr_owner -h pg.example.com -U postgres -s -x

-s imports the schema only, and -x defers indexes and constraints until after the data, which is the faster order for large tables. Add -D for a dry run that prints the commands, or -y to answer yes to every prompt once the run is routine. Expect the first load to fail in places, usually in views and routines that call Oracle built-ins. Fix the files under schema/, not the Oracle source, and keep them in version control. They are the real output of the project.

Ora2Pg can also spread a large DDL file over several connections. The LOAD type does that for index files, for example: ora2pg -t LOAD -c config/ora2pg.conf -i schema/tables/INDEXES_table.sql -j 4.

Step 7: Move the data

Export with COPY for speed. With PG_DSN and DBD::Pg in place, Ora2Pg loads PostgreSQL directly instead of writing files:

ora2pg -t COPY -o data.sql -b data -c config/ora2pg.conf -j 4 -J 4 -P 2 --scn current

• -j sets parallel processes writing to PostgreSQL, -J parallel connections reading from Oracle, and -P the number of tables processed at once.
• --scn current reads every table AS OF the same System Change Number, so the copy is consistent even while Oracle takes writes. It needs a login that can read v$database. You can also pass a specific SCN or a timestamp expression.
• DATA_LIMIT sets how many rows are held in memory per batch (10,000 by default). If a load dies with a warning about a child process, the documentation's first suggestion is to lower it.
• BLOBs load as bytea by default. --blob_to_lo stores them as large objects, and --lo_import handles values over 1 GB in a second pass.

Ora2Pg is a bulk copier. Its documentation says it has no feature to apply only the changes made after the first import. For a short outage window, use --cdc_ready: it records the SCN used for each table in TABLES_SCN.log, one tablename:SCN line each, so a change-data-capture tool can start replicating from exactly that point. If PostgreSQL identity columns came out of the TABLE export, import the generated AUTOINCREMENT file after the data, or the sequences stay at their start values.

Step 8: Test the migration

Three actions compare Oracle with PostgreSQL. All need PG_DSN.

ora2pg -t TEST -c config/ora2pg.conf > reports/migration_diff.txt
ora2pg -t TEST_COUNT -c config/ora2pg.conf --count_rows
ora2pg -t TEST_DATA -c config/ora2pg.conf -P 4

TEST compares counts of indexes, unique and primary keys, check and not-null constraints, defaults, identity columns, foreign keys, tables, triggers, views, materialized views, sequences, types and functions, then sequence values and row counts, with an error section for each mismatch. TEST_COUNT and TEST_VIEW compare row counts for tables and views. TEST_DATA compares the rows themselves, with limits worth knowing:

• It checks the first 10,000 rows of each table unless you set DATA_VALIDATION_ROWS; zero means every row.
• The table needs a primary key or unique index, and the key column cannot be a LOB.
• Rows are sorted by that key on both sides. If the PostgreSQL key columns do not use the C collation, sort order can differ from Oracle and the check fails.
• It stops on a table after ten errors by default (DATA_VALIDATION_ERROR) and writes details to data_validation.log.
• Run it before anything modifies the copied data.

Database-level checks prove the copy, not the application. Run the application's own test suite and the business flows that touch converted routines, and compare totals that finance or operations already reconcile.

What Ora2Pg does not convert

The assessment report states most of these limits itself. Plan an owner for each one:

• PL/SQL semantics. Headers, parameters and much syntax convert, but exception handling, cursor logic, dynamic SQL, package state and transaction control need review. COMMIT and ROLLBACK inside routines are left in place by default so you have to look at them.
• Some index types. Cluster, domain, bitmap join and index-organised table indexes are not exported, and reverse-key indexes are skipped. Bitmap indexes become b-tree indexes.
• Object types with methods. Type bodies with member methods are not exported, and type inheritance is not supported.
• Clusters and jobs. Oracle clusters are not supported. Scheduler jobs are not exported; the report suggests an external cron.
• Materialized view refresh. They arrive as snapshot materialized views that refresh only in full.
• Updatable views and external tables. Updatable views need INSTEAD OF triggers. External tables become ordinary tables unless you set EXTERNAL_TO_FDW.
• Application SQL. Queries built in Java, .NET or report tools are outside the database scan. The QUERY type converts queries you collect into a file, so the collecting is your job.
• Ongoing changes. No built-in CDC, as covered in Step 7.

The Oracle to PostgreSQL migration challenges post covers the data-level traps, such as empty strings, DATE values with times and case folding, that no conversion tool flags for you.

Cutover and rollback

1. Freeze DDL on Oracle and rerun SHOW_REPORT to confirm nothing new has appeared since the export.
2. Stop writes, or let your CDC tool catch up from the SCNs in TABLES_SCN.log.
3. Run TEST and TEST_COUNT one last time and keep the output with the change record.
4. Check every sequence and identity column against the highest loaded key.
5. Switch connection strings and run smoke tests on the critical paths.

Ora2Pg moves data in one direction only. Keep the Oracle database intact and read-only, agree a rollback deadline before cutover, and decide in advance how writes made on PostgreSQL would reach Oracle if you went back: a reverse replication task in a CDC tool, or a replay from application logs. Retire Oracle only after the deadline passes and a final backup is taken.

Where OSSeva fits

OSSeva for PostgreSQL includes Oracle-to-Postgres migration design in OSSeva Assure: schema conversion, query compatibility analysis and the application-layer migration strategy, with Ora2Pg and orafce as tools your team keeps. From the day of cutover the same contract supports the cluster you landed on, with signed builds and, on OSSeva Operate, 24/7 replication and failover monitoring and a 15-minute P1 response. OSSeva Operate patches Critical CVEs (CVSS ≥ 9.0) within 48 hours and High within 7 days. It runs on bare metal, VMs, any Kubernetes or any cloud account, and is priced per cluster; book a discovery call for a quote. See Oracle to PostgreSQL with OSSeva, or compare support costs with your own numbers.

Frequently asked questions

How do you migrate Oracle to PostgreSQL using Ora2Pg?

Install Ora2Pg with the Oracle Instant Client and DBD::Oracle, create a project with --init_project, set the Oracle DSN and schema in ora2pg.conf, run SHOW_REPORT --estimate_cost, export each object type with -p, load the DDL with psql or import_all.sh, copy the data with -t COPY, and validate with TEST, TEST_COUNT and TEST_DATA.

What are the Ora2Pg migration steps in order?

Install, configure, test the connection, assess, export schema and code, load the schema without indexes, load the data, create indexes and constraints, then test. The generated export_schema.sh and import_all.sh scripts follow that order.

Is Ora2Pg free?

Yes. It is open source under the GNU GPL version 3 or later. You need Oracle client libraries to connect, which come from Oracle's download site.

How accurate is the Ora2Pg migration cost estimate?

Treat it as a relative measure. Each unit stands for about five minutes of expert work by default, the author calls the method a work in progress, and it ignores application code and testing. It is best for deciding which schema to migrate first.

Does Ora2Pg convert PL/SQL packages?

It converts them to PL/pgSQL and, by default, emulates each package as a PostgreSQL schema. Every converted routine still needs review, especially package variables, exception handling and transaction control.

Can Ora2Pg migrate data with minimal downtime?

Not on its own. It copies data in bulk and has no change-apply mode. Use --scn for a consistent copy and --cdc_ready to record per-table SCNs, then let a CDC tool replicate changes from those points until cutover.

Can Ora2Pg migrate SQL Server or MySQL as well?

Yes. Its feature list includes MySQL, MariaDB and Microsoft SQL Server migration, using the -m and -M options with the DBD::mysql or DBD::ODBC drivers. For SQL Server, also see SQL Server to PostgreSQL migration using pgloader.

Does Ora2Pg work with Aurora or RDS for PostgreSQL as the target?

The output is SQL, so it loads into any PostgreSQL that accepts the objects. Managed services limit superuser rights and extensions, so check that what you rely on is available. On AWS, the native route is covered in AWS Oracle to PostgreSQL migration.