Oracle 19c to Google Cloud With a Four-Hour Cutover Window

Recommendation: AlloyDB for the shipment-event and core transactional schema; Cloud SQL for PostgreSQL for small satellite databases. The deciding factor is not 'PostgreSQL compatibility' - both provide it - but the 2.9 TB partitioned event table with heavy analytical reads alongside 1,400 rows/s of ingest.

Why AlloyDB for the core: its columnar engine accelerates analytical scans on the event table without a second copy of data in BigQuery, and its read pool lets reporting queries scale out without adding replicas that serve stale data to the application. For a table this size, Cloud SQL read replicas work but scale in coarse steps and cost more per unit of read throughput when the workload is mixed.

Why not Cloud SQL alone for the big table: Cloud SQL is a solid fit for the 330 smaller tables and is cheaper at low utilization. Splitting is defensible, but every split adds a cross-database join problem. Prefer AlloyDB for anything that joins the event table, Cloud SQL for genuinely independent schemas.

Why not BigQuery for the event table: the application still needs row-level OLTP access and low-latency lookups by shipment ID; BigQuery is not the system of record for that pattern. Use BigQuery downstream (Datastream or AlloyDB export) for long-horizon analytics.

Why not Spanner: global strong consistency is not required - everything is in one region - and Spanner would force a rewrite of far more application SQL than the PostgreSQL-compatible target. It buys availability guarantees the business has not asked for.

Migration risk comparison: AlloyDB is PostgreSQL-compatible for SQL and drivers but has differences in extensions and some internals; Cloud SQL is the more conservative target. If the 1,800 PL/SQL packages turn out to be the dominant risk, the conservative choice reduces blast radius - but do not let that drive the database choice for the 2.9 TB table.

The 4-hour window is for reads and verification; writes are handled by continuous replication. Sequence:

1. Full load (weeks before, not in the window). Use Database Migration Service or an equivalent full dump to seed AlloyDB. For the 2.9 TB table, load partition by partition so a failure at partition 20 does not restart the job. Run this against a snapshot to get a consistent starting point.
2. Continuous CDC. Use Datastream (or DMS continuous replication) from Oracle with ARCHIVELOG enabled and supplemental logging on the migrated tables. Datastream's Oracle source uses LogMiner, so redo volume and retention must be sized for the multi-day catch-up period. Monitor replication lag as a first-class SLI: the target is lag under 30 seconds for 72 hours before cutover, not 'it works'.
3. Cutover window. At T0, put the application in read-only mode and stop the scheduler (the 2,100 jobs must be quiesced explicitly - they will otherwise write during validation). Wait for lag to reach zero, record the SCN, and let replication drain.
4. Validate, don't assume. Reconcile per-table row counts for small tables; for the 2.9 TB table reconcile by monthly partition count plus a checksum of the last 7 days, which is where drift actually occurs. Reconcile sequences against MAX(id) plus a safety margin. Validate the 40 highest-value PL/SQL conversions against expected outputs captured from Oracle beforehand.
5. Flip writes. Repoint the connection string (via DNS or a config service, not by editing 40 application configs), restart schedulers, verify 1,400 rows/s ingest, and keep Oracle in read-only standby for 72 hours as a rollback source.

Rollback: if validation fails, abort at T0+3h, return the app to Oracle read-write, and reschedule. Because Oracle was only ever read-only, rollback is immediate and lossless - that is the whole point of doing this in the window rather than as a big-bang dump.

A dump and restore cannot meet the 5-minute write-downtime requirement. A 6.4 TB logical dump plus restore plus index build takes many hours; on Oracle-to-PostgreSQL it also requires a conversion pass, so the outage is measured in days, not the 4-hour window. More importantly, a dump gives you no continuous validation - the first time you learn a conversion is wrong is in production. Say this plainly and put an estimate on the table.

PL/SQL: automate the mechanical 70%, hand-convert the rest. Use the DMS conversion workspace (or ora2pg-style tooling) to produce PL/pgSQL drafts, then triage all 1,800 packages into three buckets:

• Mechanical (simple CRUD, straightforward control flow, native types): accept converted output after code review. This is the bulk.
• Oracle-specific (CONNECT BY, MERGE quirks, autonomous transactions, UTL_* packages, DBMS_* calls, AQ, pipelined table functions): hand-convert; prioritize by call frequency and business criticality, not alphabetically.
• Eliminate - many of those 1,800 are thin wrappers around SQL that should become views or application SQL. Cutting 300 packages is cheaper than converting them.

With 5 engineers and 9 weeks, the honest capacity is roughly 150-250 packages per week across the team including review and test. That is tight against 1,800; publish the triage in week 1 and negotiate scope early rather than discovering it in week 8.

Synonyms: PostgreSQL has no synonyms. Replace them with search_path entries, views, or foreign-data-wrapper/foreign-table definitions if the second Oracle instance stays reachable. Because that instance is not migrating, the cross-database dependency must be resolved explicitly - 180 synonyms may hide joins that will now be cross-system calls. Enumerate them in week 1; that is a scope risk, not a technical detail.

Sequences and identity: convert Oracle sequences to PostgreSQL identity columns where possible, and audit triggers/jobs that call sequence.NEXTVAL directly. Start each sequence above the current high-water mark to avoid collisions on cutover.