Oracle to Aurora PostgreSQL: Cutover Under a Six-Hour Freeze

1. PL/SQL is the real project, not the schema. 340 packages will not convert mechanically. SCT automates DDL and simple bodies; anything using CONNECT BY, MERGE, hints, autonomous transactions, DBMS_* packages, pipelined functions or AQ needs hand-rewriting, often as PL/pgSQL or by moving logic into the application. Budget by package complexity, and build an exception list from the pilot rather than trusting the 71% figure - that percentage counts statements converted, not packages that behave identically.

2. LONG and LONG RAW cannot ride through DMS. Convert them to CLOB/BLOB in Oracle first (or via SCT), then migrate. Also check DMS LOB mode: Limited LOB mode silently truncates LOBs beyond MaxLobSize, and Full LOB mode is slow. For 3.2 TB with document blobs, decide whether blobs move through DMS at all or via S3 + application-level reload.

3. Materialized views. 90 MVs become tables plus refresh jobs in PostgreSQL. Some should become real tables refreshed by pg_cron on a schedule; some should become views. Each needs an owner and a freshness requirement, and each has to be excluded from the DMS table list or loaded in the right order.

4. Semantic gaps that pass conversion and fail in production. Oracle treats the empty string as NULL, PostgreSQL does not - this breaks unique constraints and IS NULL logic and is the classic silent migration bug. Also reconcile NUMBER precision and scale, DATE carrying a time component, sequences versus identity columns, and case sensitivity of unquoted identifiers.

5. The 19-hour full load is not the cutover, but it sizes your rehearsal. Rehearse against a scaled copy, and confirm the read-only window covers the final CDC drain, validation and connection-string flip - not a bulk reload.

Separate the two lag metrics first. DMS reports CDCLatencySource (time to read and parse redo) and CDCLatencyTarget (time to apply to the target). They have different fixes, and teams routinely guess wrong.

If source latency dominates: the task is struggling to mine redo generated by a 40-million-row batch. Actions: run the task on a larger instance class for more memory and CPU; ensure the Oracle account can read archive logs efficiently (direct redo access rather than JDBC log mining where supported); set an appropriate MaxFileSize and FetchSize; and confirm archive log retention comfortably exceeds the longest possible outage of the task.

If target latency dominates: apply is the bottleneck. Actions: drop or defer non-essential secondary indexes and foreign keys on the Aurora target during replication and rebuild after cutover; consider parallel apply options if your DMS version and engine pair support them; raise target instance size for write throughput; and stop running reporting queries on the same Aurora writer the task writes to.

Attack the change volume itself. A single 40-million-row batch is the root cause. Splitting it into committed chunks (say 100k rows per commit) reduces redo burst size and lets CDC interleave. Purging or archiving rows before migration shrinks the source. If the batch can be shifted to run before the full load rather than during CDC, the catch-up window collapses.

Guardrail. Before cutover you need proof that the task keeps up: run it for at least a full week including the nightly batch and record the maximum observed CDCLatencyTarget and the time to drain it.

Pre-cutover (weeks before). Rehearse the entire sequence end to end at least twice on a clone. Freeze schema changes; every DDL after the SCT run must be scripted and applied to both sides. Turn on DMS validation for supported types early, because it flags mismatches while you still have time, and accept it is slow and does not validate LOBs the way you might assume - pair it with your own checksums.

Validation before the flip. Row counts per table are necessary but weak. Add: sums and hashes over numeric and date columns for the largest tables, a comparison of the 20 highest-value business queries (run against Oracle and Aurora and diff the results), and a check of every unique constraint and foreign key actually present in the target - SCT sometimes defers them.

Cutover sequence.

1. T-minus 0: put Oracle in read-only mode; application returns friendly errors or serves cached reads.
2. Let CDC drain. Watch CDCLatencyTarget fall to zero and hold there for several consecutive minutes with no source traffic; then stop the task.
3. Run final validation and resolve differences.
4. Flip connection strings or DNS to the Aurora writer endpoint; start the application; watch error rate and p95 latency.
5. Leave Oracle intact and read-only for the agreed rollback period.

Rollback. The honest answer is that rollback is clean only until Aurora accepts writes. Define the rollback point explicitly with the business: before writes begin, revert the connection string and you lose nothing; after writes begin, reverting to Oracle requires either reverse replication or an accepted loss window. Set that decision deadline in the runbook, name the person who makes the call, and state the dollar and data cost of each branch. Do not plan to improvise it at 3 a.m.