Oracle to Managed PostgreSQL with a Ten-Minute Write Pause
A financial services firm is migrating a 4.2 TB Oracle 12c database to a cloud-managed PostgreSQL service. The database has 11 schemas, 2.1 billion rows in the largest transactions table, and 38 stored procedures with Oracle-specific PL/SQL. The business allows a six-hour maintenance window on Saturday night, but the application teams can pause writes for no more than ten minutes. Data quality checks found 1.2% duplicate customer records and 4,000 orphaned foreign keys. The current backup is a weekly full plus daily incremental; restore testing last succeeded eight months ago. The CIO wants a one-shot cutover with a rollback plan of 'restore from backup if needed.' The target service supports logical replication and database links but not Oracle packages.
1. Select the migration method and explain why a one-shot offline migration will or will not work.
Read the worked answer
A one-shot offline migration cannot meet a ten-minute write pause for 4.2 TB. Use a hybrid: schema conversion first, then an initial consistent load, then change data capture for the delta. Convert the 11 schemas with a tool, but manually review the 38 PL/SQL procedures; Oracle packages do not exist in PostgreSQL, so each must be rewritten as functions or application code and unit-tested. Run the initial load during the six-hour window while writes continue, then let CDC catch up. Before cutover, run a rehearsal that measures apply lag at production change rates. The ten-minute pause is only for final drain: stop writes, let CDC apply the last transactions, validate, switch the connection string. If conversion of procedures is incomplete, do not start the load; it will create a false sense of progress.
2. Design the validation plan, including how to handle the duplicate records and orphaned foreign keys.
Read the worked answer
Validation must be layered. Row counts per table are necessary but insufficient at 2.1 billion rows. Use per-table checksums on stable columns, partition-level counts, key aggregate reconciliation (daily transaction totals, balances, customer counts), and referential-integrity checks. Reconcile the 1.2% duplicate customers and 4,000 orphaned foreign keys before cutover, not after; otherwise the target inherits dirty data and the migration gets blamed. Run application-level smoke tests for every critical journey, plus a performance test at expected concurrency. Compare query plans for the top 20 statements. Keep a signed reconciliation report per schema. Automate the checks so they can run again during the ten-minute drain. A green dashboard is not validation unless the business owner signs the reconciliation.
3. Replace the CIO's rollback plan with an executable rollback and abort strategy, including decision criteria.
Read the worked answer
The CIO's rollback plan is not a rollback plan. A backup restore eight months after the last successful test would lose data and exceed the window. Build rollback before cutover: keep the Oracle source intact and read-only after cutover, or set up reverse replication from PostgreSQL to Oracle for a defined period. Define abort criteria in advance: CDC lag above 15 minutes at T-2 hours, procedure conversion failures, reconciliation variance above the agreed tolerance, or performance below the SLA in rehearsal. Name a decision authority and a decision time, for example 01:00, with a go/no-go call. If aborted, revert the connection string and resume Oracle writes; if committed, freeze schema changes and monitor for 72 hours. Test the rollback in rehearsal, including the application connection string change. Restore-from-backup is a disaster recovery option, not a migration rollback.