Oracle to Aurora PostgreSQL — the heterogeneous migration, done properly.

IWhat actually changes when you go heterogeneous

A homogeneous Oracle→Oracle move (to Amazon RDS for Oracle) copies bytes — same engine, same SQL, same PL/SQL, no rewrite. Oracle→Aurora PostgreSQL is the other kind of migration: the engine changes, so the schema, the procedural code, and the application’s assumptions about the database all have to be translated. That distinction governs everything below.

If you are still weighing lift-and-shift to RDS for Oracle against this re-platform, start with the broader decision page — Oracle to AWS lays out both paths and when each wins. This page assumes the decision is made: the database carries real Oracle licensing cost, it is tractable enough to convert, and your team will own PostgreSQL going forward. From here, the goal is to do the conversion well.

The target is Amazon Aurora PostgreSQL — a PostgreSQL-compatible engine with cloud-native storage that auto-scales to 128 TiB, replicates six ways across three Availability Zones, supports up to fifteen low-latency read replicas, and (the entire point) carries no database license. You pay for compute, storage, and I/O. Aurora is wire-compatible with community PostgreSQL, so the conversion target is "idiomatic PostgreSQL," and everything PostgreSQL-native — drivers, extensions, the planner — applies.

The honest mental model: roughly 80% of the calendar on a serious Oracle→Aurora project is not moving data. It is converting and rewriting the procedural code, changing the parts of the application that spoke Oracle, validating that behavior matches, and tuning a different query planner. DMS makes the data movement almost boring. The engineering lives in the schema and the application. The next sections follow that reality in order.

IIAWS SCT: what auto-converts, and the assessment that becomes your plan

The Schema Conversion Tool does two jobs. It converts the schema and code it can, and — far more valuable on day one — it produces an Assessment Report that tells you object-by-object what it cannot, rated by difficulty. That report is the only honest way to size the project before you commit budget.

You point SCT at the source Oracle database and the target Aurora PostgreSQL endpoint and let it analyze the whole schema: tables, indexes, constraints, views, sequences, and the procedural code (packages, procedures, functions, triggers). It converts what it can and flags the rest, classifying each remaining action item as simple, medium, or complex. The headline number — percent of objects auto-converted — typically lands between 70% and 95%, and where you fall in that band is almost entirely a function of how much PL/SQL you carry and how Oracle-specific it is.

The structural layer converts cleanly almost every time. Datatype mapping is mostly mechanical: Oracle NUMBER lands on PostgreSQL numeric / bigint / integer depending on precision and scale, VARCHAR2 becomes varchar, DATE becomes timestamp (Oracle DATE carries a time component — a classic silent trap if you assume it is date-only), CLOB becomes text, BLOB becomes bytea, and RAW/ROWID need explicit decisions. SCT does the mapping and flags the NUMBER columns whose precision a human should confirm. Tables, indexes, views, and check constraints come across with little drama. The hours go somewhere specific and predictable, and naming them up front is what keeps a project from stalling mid-stream.

• Packages — the biggest structural gap. PostgreSQL has no native package. SCT maps each Oracle package to a PostgreSQL schema holding the individual functions and procedures, but package-level shared state (package variables that persist for a session), initialization blocks, and overloaded signatures need manual redesign — often into per-session GUC settings, a temp table, or explicit parameters.
• Autonomous transactions. Oracle’s PRAGMA AUTONOMOUS_TRANSACTION (commit inside a nested transaction independent of the caller — the classic audit/logging pattern) has no direct PostgreSQL equivalent. It is redesigned, commonly via the dblink extension making a loopback connection, or by refactoring the pattern away (e.g., queue the log row and write it outside the transaction).
• Hierarchical queries. Oracle CONNECT BY / START WITH / PRIOR rewrites to PostgreSQL WITH RECURSIVE common table expressions. SCT converts many cases automatically; deep or cyclic hierarchies, plus LEVEL / SYS_CONNECT_BY_PATH / CONNECT_BY_ROOT, often need a hand-written recursive CTE.
• Sequences and identity. seq.NEXTVAL / seq.CURRVAL map to nextval('seq') / currval('seq'), but the idioms differ: PostgreSQL favors GENERATED ... AS IDENTITY or serial-backed defaults, and any trigger that populated a key from a sequence on insert is usually replaced by a column default. Caching, increment, and CURRVAL-without-a-prior-NEXTVAL semantics all need checking.
• Bulk operations and collections. BULK COLLECT, FORALL, and PL/SQL associative arrays / nested tables have no one-to-one mapping. In heavy ETL packages these are common rewrites — often re-expressed as set-based SQL (which PostgreSQL prefers anyway) or array operations.
• Built-in and semantic differences. DECODE → CASE, NVL → COALESCE, NVL2, ROWNUM → LIMIT / row_number(), SYSDATE/SYSTIMESTAMP semantics, the (+) outer-join operator, MERGE quirks, and Oracle treating an empty string as NULL while PostgreSQL does not — each is small, but collectively they are the long tail of medium action items.
• Triggers, materialized views, and partitioning. Trigger syntax and firing semantics differ; Oracle materialized-view fast-refresh has no exact analog (PostgreSQL refreshes are full or concurrent); and Oracle partitioning strategies map to PostgreSQL declarative partitioning but not always one-for-one.

For the genuinely hard objects, SCT can deploy an extension pack and, for some constructs, emulate Oracle behavior on the PostgreSQL side rather than rewriting it by hand. Treat that as a pragmatic accelerator and as technical debt — useful for the long tail, not a license to skip idiomatic rewrites of code that runs hot. The disciplined sequence is: take the auto-converted objects, manually rewrite the medium/complex action items into clean PL/pgSQL, and reserve emulation for the cases a proper rewrite cannot yet justify.

The most valuable artifact of this phase is not the converted schema — it is the number plus the action-item list. "This database is 88% auto-convertible with 240 complex items, concentrated in three ETL packages and the audit framework" is a project you can staff and schedule. A 95%-convertible reporting mart is a quick win; a 72%-convertible core-transaction schema with six-figure PL/SQL line counts is a real engineering program. SCT lets you make that call with evidence instead of optimism.

IIIAWS DMS: full load, then CDC to a near-zero-downtime cutover

With the schema built on Aurora, the Database Migration Service moves the data and — the part that matters — keeps it moving. DMS change-data-capture is what turns a dreaded weekend cutover into a controlled switch measured in minutes.

A DMS task runs in up to three modes. Full load bulk-copies existing rows from Oracle into Aurora. CDC then reads Oracle’s redo logs and continuously applies every subsequent insert, update, and delete to Aurora. Run "full load plus CDC" and DMS first copies the data, then settles into a steady state where Aurora trails live Oracle by seconds. The source stays fully online throughout — users never know a migration is underway. You can let that replication run for days or weeks while you validate, test the application against Aurora in staging, and tune performance.

Operationally there are real knobs, and a seasoned migration team turns them deliberately. The replication instance is sized to the change rate; high-volume systems are parallelized across multiple tasks split by table or schema. Large-object (LOB) columns need explicit handling — full LOB mode is complete but slow, limited LOB mode is fast but truncates beyond a set size — so you choose per workload. For best throughput on the initial load you typically create secondary indexes, foreign keys, and triggers after the full load rather than before, then enable them for the CDC phase. And CDC requires Oracle supplemental logging enabled on the source so the redo stream carries enough column data to replay changes.

Be clear about DMS’s boundary: it replicates row changes, not logic. It does not migrate procedural code (that is SCT’s job and your rewrites), it does not convert schema, and it will not invent referential integrity you did not define. DMS moves and syncs the data; SCT and your engineers move the behavior. Keeping that line crisp is what prevents the "we thought DMS handled the triggers" surprise late in a project.

IVThe application data-access layer: where the hidden work hides

SCT converts the database and DMS moves the data, but neither touches your application. A surprising amount of Oracle-specific behavior lives in the app tier — the SQL your code emits, the driver it loads, and the assumptions it makes — and scoping this honestly is the difference between a 4-month project and a 9-month one.

Start with the connectivity layer. Oracle clients use the oracle JDBC driver / OCI / SQL*Net and an Oracle connection string and SID/service name; Aurora PostgreSQL uses the PostgreSQL JDBC (or libpq, psycopg, node-postgres, etc.) driver and a standard PostgreSQL DSN. ORMs and query builders need their dialect switched (Hibernate dialect, Django/Rails/Sequelize adapter, SQLAlchemy URL). This is mechanical but touches configuration, secrets, and every service that holds a connection — and connection-pool behavior differs enough that you tune pool sizes for PostgreSQL rather than assume the Oracle settings carry over.

Then the SQL the application emits directly — the inline queries, the report builders, the stored-SQL fragments that never went through SCT because they live in application code, not the database. These carry the same dialect gaps SCT flagged in the schema: ROWNUM and Oracle pagination become LIMIT/OFFSET or keyset pagination; (+) outer joins become ANSI joins; NVL/DECODE/SYSDATE need translating; sequence access changes; and — the subtle one that bites in production — Oracle treats '' (empty string) as NULL while PostgreSQL treats it as a zero-length string, so WHERE col = '' and IS NULL checks behave differently. Case-sensitivity of unquoted identifiers and date/number formatting via NLS settings differ too.

Finally, behaviors your code may quietly depend on: default transaction isolation and locking semantics differ between Oracle and PostgreSQL; sequence-generated IDs may not be perfectly gap-free; and anything that read CURRVAL, used Oracle hints, or relied on implicit type coercion needs review. None of this is exotic, but it is invisible until you go looking, which is exactly why a partner who has migrated applications off Oracle inventories the data-access layer during assessment rather than discovering it during cutover.

VTesting, validation, and performance on a different planner

Correct data is necessary but not sufficient. The application has to behave identically and perform acceptably on an engine whose optimizer makes different choices than Oracle’s. This phase is where re-platforms are actually de-risked.

Run validation on three levels. Data fidelity comes from DMS validation plus your reconciliation queries (covered above). Functional correctness comes from your application regression suite executed against Aurora — ideally the full suite, with particular attention to the converted PL/pgSQL objects and any code path that touched the rewritten SQL. Behavioral equivalence comes from comparison testing: run representative read and write workloads against both Oracle and Aurora and diff the results, which surfaces the empty-string-is-NULL and date-truncation class of bugs that pass unit tests but change query results.

Performance is its own workstream because PostgreSQL’s planner is not Oracle’s. Queries that were fast on Oracle can choose a different plan on Aurora; you tune by running production-representative load against Aurora before cutover, reading EXPLAIN ANALYZE, adding or reshaping indexes (PostgreSQL partial and expression indexes are powerful here), and making sure autovacuum and statistics (ANALYZE) are configured so the planner has good estimates. Connection pooling — PgBouncer or RDS Proxy — usually matters more on PostgreSQL than teams expect for high-concurrency apps. Budget an explicit tuning phase; "it was instant on Oracle" is not a guarantee on day one, and discovering that in production is the avoidable failure mode.

The discipline that makes all of this tractable is sequencing: migrate a non-critical database first to prove the toolchain and the team’s PostgreSQL muscle, then graduate to the systems the business runs on. The first migration teaches you your own application’s Oracle-isms cheaply; the critical one then benefits from a rehearsed process rather than a first attempt.

VICutover and rollback for a database the business runs on

The conversion and the data move are engineering. The cutover is risk management. For a system the business depends on, the entire migration is judged on one question: what happens if something is wrong at the moment of switch?

The answer is a rehearsed runbook with a rollback you do not have to invent under pressure. The single most important design choice is to keep the source Oracle database authoritative until the cutover is proven. Because DMS CDC syncs Aurora from Oracle (not the reverse), rollback during the window is trivial: if validation fails after you switch, you repoint the application back to Oracle, which never stopped being correct. You only burn that bridge once you are certain. A typical mission-critical cutover runs like this:

• Pre-cutover (days before) — Aurora live under CDC, trailing Oracle by seconds. Full regression suite green against Aurora. DMS validation clean and reconciliation queries matching. Performance tested at production load with plans tuned. Rollback runbook written and rehearsed in staging.
• Cutover window (the minutes that matter) — Announce a short maintenance window. Stop application writes to Oracle. Let DMS apply the final change backlog until Aurora is fully caught up. Run final row-count and checksum reconciliation. Repoint connection strings / endpoints to Aurora.
• Smoke test on Aurora — Execute the critical-path transactions — login, the core write paths, a representative report — against Aurora before reopening to users. A scripted smoke test takes minutes and catches the connection-string-typo and missing-grant class of failure immediately.
• Go / no-go decision — If smoke tests pass, reopen traffic on Aurora and watch error rates and latency closely. If anything is wrong, execute rollback: repoint to Oracle, reopen, debug Aurora offline. Because Oracle was never demoted, rollback costs minutes, not data.
• Post-cutover stabilization — Keep Oracle running read-only as a safety net for a defined window — commonly one to four weeks — while Aurora proves itself in production, then decommission Oracle and stop the support renewal. That decommission date is the day the savings actually start.

None of this removes risk entirely — a heterogeneous migration of a core system is genuinely hard — but migrating non-critical databases first, rehearsing the cutover, and keeping the rollback explicit converts an unbounded risk into a bounded, reversible event. That discipline is precisely what you are paying a vetted migration partner for, and precisely what AWS MAP funds them to execute well.

VIIThe license-savings model that justifies the conversion

The conversion costs real engineering, so the model has to clear it. For Oracle→Aurora it almost always does, because the recurring Oracle bill it deletes is large and perpetual while the conversion is one-time.

Build it on three lines. First, the Oracle cost you delete: licenses (per processor, after the core-factor multiplier), the ~22% annual support renewal, the options the schema depends on (Partitioning, Advanced Compression, Diagnostics and Tuning Packs, Active Data Guard, RAC) — each licensed separately and per-core — plus the hardware refresh and DR standby if you run it yourself. A single Oracle Enterprise Edition processor license lists around $47,500, and one physical core often counts as more than one "processor" once the core-factor table is applied, so a mid-sized estate carries a seven-figure annual Oracle bill before anything new ships. Second, the Aurora cost you take on: compute (instance hours or Aurora Serverless v2 capacity), storage, I/O, and backups — with no license layer at all. Third, the one-time conversion: SCT/DMS work, PL/SQL and application rewrites, testing, and cutover — the line MAP and database-migration funding are designed to offset.

In practice, moving a workload off Oracle Enterprise Edition with options to Aurora PostgreSQL cuts database TCO by roughly 60–90%. It skews higher when you were paying for expensive options and a DR standby — Aurora provides Multi-AZ HA, automated backups, and read replicas natively, with no per-feature licensing — and lower when you were already on Standard Edition. Payback on the conversion is commonly under a year on the support renewal alone, which is why these projects survive CFO scrutiny that kills most "modernization" initiatives. The one caveat that keeps the model honest: the savings are real only after decommission. While Oracle runs in parallel as a safety net you are paying for both, so the decommission date is a line item, not an afterthought.

VIIIMAP + database-migration funding: a partner runs it, AWS funds most of it

A funded, done-for-you Oracle→Aurora migration is the CloudRoute mechanism. You do not assemble an internal SCT/DMS team from scratch and learn PostgreSQL’s edge cases live on a production database — you get matched to a vetted AWS partner who has done heterogeneous Oracle migrations before, and AWS funding covers most or all of the engagement.

The vehicle is the AWS Migration Acceleration Program (MAP), run through the AWS Partner Network in three phases. Assess is a TCO and readiness analysis — the partner runs SCT across your Oracle databases to produce the auto-convertibility numbers, the action-item inventory, and a real savings model; this phase is frequently AWS-funded and gives you go/no-go data before you commit a dollar. Mobilize builds the AWS landing zone and pilots the conversion of a representative database. Migrate & Modernize is the production conversion, the CDC cutover, and stabilization. AWS provides credits and funding scaled to the migration’s size across these phases, and there are AWS programs aimed specifically at database migrations off commercial engines like Oracle.

The honest framing matters: MAP funding applies to qualifying migrations — typically those with a meaningful committed AWS spend after migration — and the exact amount is scoped during Assess. For a substantial Oracle estate that clears the bar, the conversion can be largely or entirely AWS-funded. For smaller workloads that do not qualify for full funding, the value is still real: a partner who has converted Oracle PL/SQL and run DMS CDC cutovers de-risks the hardest parts, rather than your team meeting SCT’s extension pack and PostgreSQL’s planner for the first time on a system the business runs on.

CloudRoute’s role is the routing. You tell us the shape of the estate — how much Oracle, which editions and options, how much PL/SQL, what the databases do, your timeline — and we match you to a partner with a genuine Oracle→Aurora track record and the AWS tier to file MAP. The partner runs the assessment, scopes the funding, and executes. AWS funds the engagement; the partner is paid through MAP; CloudRoute is paid a commission by the partner. You see no invoice from us.