Oracle to EPAS cutover with a four-hour window

Choose EPAS 16 over community PostgreSQL, because the PoC failed on rewrite volume, not on throughput. EPAS provides Oracle-compatible parsing and PL/SQL behaviour for most of the constructs in this schema: CONNECT BY hierarchical queries, ROWNUM, NVL/DECODE, Oracle outer join syntax, synonyms, packages, and PRAGMA AUTONOMOUS_TRANSACTION. That converts a large share of the 190 rewrites into compatibility verification, and verification is far cheaper than rewriting and retesting 214 packages.

What stays hard, and must be planned rather than discovered:

• The 22 database links have no Oracle-equivalent in EPAS. They become postgres_fdw (if the targets are Postgres) or oracle_fdw (if Oracle stays), which means connection strings, credentials and error handling all change.
• Commit-inside-loop procedures: in Postgres, transaction control inside a routine requires a procedure called via CALL, and it cannot be invoked inside an already-open transaction block. Those 40 procedures need call-site changes even if the bodies compile.
• ROWID has no stable equivalent; anything using it as a durable key must be redesigned around a real primary key.
• NUMBER mapping must be checked deliberately - numeric versus double precision changes both rounding and index usage - and empty-string versus NULL semantics differ unless the Oracle-compatible string behaviour is enabled.

Also price the operational side: EPAS compatibility reduces code change, not the need to retest plans, LOBs and the 900-million-row table.

Four hours cannot move 1.6 TB, so the bulk work happens days earlier and the window covers only the delta and the switch.

Pre-window (T−7 to T−1 days):

• Convert schema with a migration tool, review the type mappings, and load the initial data set with parallel unload/load (8 jobs). At a realistic sustained rate the 1.6 TB load plus index builds is many hours, so it must not be inside the window. Build indexes after load, not before.
• Start change capture before the snapshot is taken so the snapshot point and the replication start point line up. Validate CDC throughput against 400 TPS now, and specifically measure LOB throughput - a 300 GB LOB segment is the usual place where CDC lag hides.
• Rehearse the switch twice on a clone. The PoC failed because nobody had ever rehearsed it; that is the cheapest risk to remove.

Window (4 hours):

1. Quiesce writes at the application layer, or put the Oracle schema in read-only mode. Confirm no writers.
2. Let CDC drain. The lag you measured in rehearsal should be minutes, not hours; if it is not, stop and defer rather than forcing it.
3. Reconcile: row counts per table, hashes on the 900-million-row table in chunks, LOB byte lengths.
4. Resynchronise sequences with setval to the source's last number plus cache size, so the first inserts after switch do not collide.
5. ANALYZE the largest tables - plans on unanalyzed 900-million-row tables are the most common post-migration outage.
6. Repoint connection strings and run the smoke tests.

Budget: about one hour to quiesce and drain, one for reconciliation, one for sequences/analyze, one for switch and smoke tests, with the deferral decision point at the end of hour two.

Verification has three layers, and the skipped checks are almost always in the second and third.

Data correctness:

• Row counts per table catch nothing subtle; add chunked hash comparisons on the 900-million-row table and a byte-length or hash comparison on the LOB segment.
• Compare NULL counts on columns that held Oracle empty strings - this is the classic silent divergence.
• Confirm the highest sequence values exceed the source, and confirm no target row has a default-generated key that collides with replicated data.

Behavioural correctness:

• Run the 40 commit-in-loop procedures through their real call sites, including the ones invoked from inside a transaction block, which will fail in Postgres even though they compiled.
• Test the 22 DB links for error propagation and timeouts, not just for connectivity - most link rewrites are tested happy-path only.
• Replay the top 30 SQL statements by elapsed time from the source's AWR data and compare plans and runtimes. A migration is a plan regression event even when the data is perfect.

Operational readiness:

• Backups and PITR on the new cluster actually restore, and the restore has been timed.
• Monitoring covers the new cluster's replication, WAL generation and connection counts before go-live, not after.
• The rollback position is explicit: after cutover, Oracle is read-only and you have a defined reverse-sync path for N hours; state the number and make sure someone owns it.