Migration software
Ora2Pg
Suggested in one of our migration playbooks, mainly for Databases. Here is what it handles, how it treats key settings, and other tools for the same jobs.
What it handles
The limits of the built-in tools that Ora2Pg gets around, as covered in our playbooks.
Oracle to PostgreSQL migration
- Set sequences after the last data loadExport type SEQUENCE_VALUES exports DDL to set the last values of sequences. Run it after writes stop on Oracle.
- Use the C collation on key columns if you validate with AWS DMSExport type TEST_COUNT compares row counts per table, TEST compares objects between Oracle and PostgreSQL, and TEST_DATA runs a data validation check on rows on both sides.
- AWS DMS validation needs a primary key on every table, and fails on text keys unless their collation (sort order setting) is C.TEST_COUNT compares row counts, TEST compares objects, and TEST_DATA checks rows on both sides, whichever tool loaded the data.
- AWS DMS and an Ora2Pg COPY load copy rows, not current sequence values, so the first new inserts fail with duplicate key errors. Google Database Migration Service copies sequence values, but Oracle keeps advancing them until writes stop.Export type SEQUENCE_VALUES writes the statements that set each sequence to its last Oracle value. Run it after writes stop.
How it handles key settings
From the settings checklists in our playbooks: what Ora2Pg carries across.
| Setting | How it handles it | Playbook |
|---|---|---|
| Outage or change data capture | No CDC. You stop writes, export with COPY and load the files, so the application is down for the whole copy. | Oracle to PostgreSQL migration |
| Which converter is the source of truth | Generates DDL for any PostgreSQL target, and its assessment report classes each schema by how much manual rewriting it needs. | Oracle to PostgreSQL migration |
| Tables without a primary key | TEST_COUNT compares row counts and TEST_DATA compares rows on both sides, whichever tool loaded the data. How TEST_DATA handles tables without a key: not stated in Ora2Pg's documentation: test it in your pilot. | Oracle to PostgreSQL migration |
| Data types, empty strings and dates | Type mapping directives in its configuration make the same choices repeatable each time you regenerate the DDL. | Oracle to PostgreSQL migration |
| Indexes, foreign keys and triggers during the load | Microsoft's Ora2Pg guidance recommends exporting with COPY rather than INSERT, and loading without foreign keys, constraints and indexes. | Oracle to PostgreSQL migration |
| Sequence values | Export type SEQUENCE_VALUES writes the statements that set each sequence to its last Oracle value. Run it after writes stop. | Oracle to PostgreSQL migration |
| Users, roles and permissions | Export type GRANT exports roles and privileges from Oracle for review. | Oracle to PostgreSQL migration |
| How you prove the data matches | TEST compares objects, TEST_COUNT compares row counts and TEST_DATA compares rows, whichever tool loaded the data. | Oracle to PostgreSQL migration |
Tools for the same jobs
Other tools our playbooks suggest for the same migrations.
We write these notes from the vendor documentation cited in each playbook. Tools cannot pay to be included or to change them. Check the vendor's current documentation and terms before you buy.