Overview
This is a typical engagement for a regional insurer, or any team with a large Oracle estate, that has a renewal date and a migration case built on a licensing saving alone. The open question is how much of the estate converts, and how much of it is PL/SQL that someone will have to rewrite and test.
Challenge
Tool vendors describe conversion as mostly automatic, and a sample schema converts quickly. The real cost sits in packages that hold business rules, jobs that call other systems, database links to other instances, and behavior the application depends on without anyone having written it down, such as empty strings that are NULL in Oracle and not in PostgreSQL. Without an object-level view the plan is a guess, and a large estate is worse because instances depend on each other.
Environment
Oracle databases on-premises and in a cloud account, with PL/SQL packages, scheduled jobs and database links, feeding application services and reporting.
Approach
The engagement starts with an inventory and an assessment using Ora2Pg and the AWS Schema Conversion Tool reports as inputs, not as answers. Every object is classified as converts, needs review or needs rewrite. One representative database is then converted, loaded and run against its real application tests, so the defect categories appear while they are still cheap. Only then is a plan written for the remaining databases.
Solution
- Inventory schemas, objects, PL/SQL, jobs, database links and dependencies between instances
- Run Ora2Pg and the AWS Schema Conversion Tool assessments and classify each object by conversion effort
- Convert and load one representative database and run its application tests against PostgreSQL
- Group the defects by cause: empty-string handling, NUMBER and DATE mapping, packages and package state, autonomous transactions, transaction error behavior
- Compare the change-capture options for a low-downtime cutover: AWS DMS with LogMiner or Binary Reader, and Debezium
- Write the wave plan, the validation approach, and a cutover and rollback design with the point of no return defined
Outcome
The engagement delivers an object-level classification of the estate, a converted and tested sample database, a defect list grouped by cause, a recommended route and wave order, and a stay-or-go view per database. The decision rests on evidence from the team's own code rather than a vendor demo, and the largest risks are named before any commitment is made.
Technologies
PostgreSQLOracle DatabaseOra2PgAWS DMS
Prometheus
Linux
Kubernetes
dbt
Kafka
Docker
NGINX
