🎯 Target Audience: Intermediate to Advanced Cloud Architects, DBAs, and DevOps Engineers who manage enterprise migrations, analyze Oracle AWR reports, or tune hybrid AWS network architectures.
Cross-posted from my infrastructure postmortem series at pavanbhatia.hashnode.dev.
At 1:40 AM on Sunday, our 7 TB Oracle-to-Amazon RDS migration was on the verge of collapse.
What looked like a routine cutover had turned into a system-wide latency failure with no obvious root cause.
Database CPU sat below 15%. Storage I/O looked healthy. Application logs showed zero errors.
Yet user-facing latency had exploded by nearly 800%.
Our final UAT validation had stalled completely, and we had less than four hours before business traffic resumed.
As the lead cloud architect driving the cutover, I had to decide whether to continue debugging live under extreme time pressure — or abort the migration and execute a controlled rollback.
The Data Pump Concurrency Bottleneck
Our initial staging runs with Oracle Data Pump (impdp) showed that the 7 TB data payload was tracking toward a 24-hour import window. In a strict 48-hour cutover timeline, spending half of our entire allocation moving raw bytes was terrifying. It left zero margin for validation, error remediation, or a clean rollback if things went sideways.
Vertical scaling did not solve the bottleneck; the issue was process- and I/O-level concurrency. We provisioned a memory-optimized RDS instance class and maximized storage IOPS, but the import throughput remained unchanged. To find the stall, we pulled an Automatic Workload Repository (AWR) report during the import run.
The metrics told us something critical immediately: compute wasn't the bottleneck. The import workers were completely serializing around index and constraint operations while the database sat mostly idle.
The AWR report immediately ruled out infrastructure saturation:
Top AWR Wait Events During Import
Event Waits Avg Wait % DB time
--------------------------- --------- -------- ---------
db file sequential read 4,120,500 3.6ms 52.4%
resmgr:cpu quantum 842,110 3.7ms 11.0%
SQL*Net message from client 9,104,220 0.2ms 8.0%
The absence of sustained CPU or storage waits confirmed that the bottleneck was orchestration overhead rather than raw infrastructure capacity.
We refactored our ingestion pipeline to focus on schema deconstruction rather than raw hardware scaling:
-
Parallelism Tuning: We increased the parallel workers incrementally during test runs until throughput plateaued efficiently around
32workers. -
Schema Deconstruction: We ran an initial import execution with index and constraint exclusions (
EXCLUDE=INDEX,CONSTRAINT), allowing flat tables to ingest via rapid, direct loads. We deferred all foreign keys and constraints to be validated later. - Concurrent Indexing: Once the raw data rows were loaded, we executed a multi-threaded script to rebuild indexes and constraints concurrently.
🎉 The Result
Total import time dropped from 24 hours to 8 hours and 12 minutes—a 66% performance gain that secured our ingestion window.
In-Flight Infrastructure: Catching the IaC Deadlock
With data ingestion optimized, we used subsequent test cycles to validate our infrastructure-as-code (IaC) deployment via Terraform. The automated pipeline consistently failed when attempting to provision our secondary read replica.
Our pipeline threw a generic AWS API InvalidDBInstanceState error, stating that the primary database was not in an available state to spin up a replica. Digging into the RDS engine events, we discovered that Oracle's MAX_STRING_SIZE parameter was the culprit. We had set it to EXTENDED to support 32,767-byte columns in our legacy schema.
Enabling EXTENDED requires the database instance to boot in upgrade mode and execute internal data dictionary conversion scripts (utl32k.sql).
Terraform's default concurrency created a race condition: the replica was being created before the primary had finished its upgrade.
This caused intermittent provisioning failures that were difficult to reproduce in staging but consistently triggered during full cutover runs.
What finally exposed the issue was noticing the primary instance repeatedly entering an internal upgrade state while Terraform simultaneously attempted replica creation. To bypass this timing limitation, we modified our deployment runbook into a two-phase execution:
-
Phase 1: We bootstrapped the cluster resources with a baseline parameter group utilizing the default
STANDARDstring setting, allowing the AWS API to establish the replication topology successfully. -
Phase 2: Once the resources were registered in our Terraform state file, we ran a targeted pipeline execution to apply the
EXTENDEDparameter group to the primary database alone.
The secondary replica automatically inherited and synchronized the data dictionary upgrades from the primary instance over the wire, stabilizing our deployments and removing the parallel provisioning race condition.
🔍 The Synchronization Loop: To ensure a "no-regrets" migration, we established a bi-directional synchronization path. As shown in the diagram, Oracle GoldenGate acted as our insurance policy. By keeping the on-premises legacy database in an active, up-to-the-second state with AWS RDS, we ensured that the decision to abort the cutover at 5:00 AM resulted in a seamless fallback rather than a data recovery crisis.
The Cutover Crisis: The 2.8ms Network Tax
The real failure surfaced during our live cutover validation. Our testing showed immediate performance degradation on our core dashboards, measured as P95 end-to-end API latency at the API gateway.
For about 20 minutes, the war room was convinced our AWS Direct Connect link was saturating under validation load, which briefly sent our investigation down a rabbit hole of network packet analysis. However, once we looked at the application traces, the true bottleneck emerged.
The issue was not inside the Oracle engine; it was the physical distance between our remaining on-premises application tier and the new cloud environment.
[On-Premises App Tier] ---> (0.4ms RTT) ---> [On-Prem Legacy Oracle]
[On-Premises App Tier] ---> (3.2ms RTT via Direct Connect) ---> [AWS RDS Oracle]
On-premises, our application servers and the legacy Oracle hardware shared the same local data center fabric, yielding a network Round-Trip Time (RTT) of 0.4ms. Moving the database to Amazon RDS via AWS Direct Connect introduced a hybrid network hop, increasing that RTT to 3.2ms.
⚠️ The Hidden Problem: A delta of 2.8ms appears negligible on an architectural diagram. At scale, however, latency multiplies across every application round-trip, turning small inefficiencies into system-wide failures.
💡 Key Realization: The cloud network link wasn't the constraint. Our query amplification was.
1. The Read Bottleneck (N+1 Query Chattiness)
Many of our core user dashboards relied on un-batched, sequential loops that executed thousands of individual SELECT queries to render a single interface view. For every user request, our dashboard looped over a list of items, firing one SELECT per item instead of a single IN-clause query.
The math broke our performance requirements:
- On-Premises Latency: 4,800 queries × 0.4ms RTT = 1.92 seconds network overhead
- AWS Cloud Latency: 4,800 queries × 3.2ms RTT = 15.36 seconds network overhead (Resulting in a monitored P95 response of 6.1 seconds)
The database completed each query quickly, then sat idle waiting for the application layer to request the next record over the network.
🔍 Architectural Breakdown: The diagram above captures exactly how our code loops behaved across both environments. On-premises, the tight local network fabric masked inefficient coding patterns. When stretched over a hybrid cloud link, the synchronous nature of the 4,800 sequential queries weaponized the 2.8ms delta against our application layer.
2. The Write Bottleneck (Sequence Fetch Allocation)
Unfortunately, the read path was only half the problem. The network tax similarly paralyzed our bulk data-entry processes.
The culprit was our legacy ORM primary key configuration, which utilized an Oracle sequence with an allocation size of 1 (INCREMENT BY 1). On-premises, the local fabric completely masked the fact that the application was making a dedicated network round-trip to ask the database for a new ID sequence number for every single row before executing the corresponding INSERT.
Over the 3.2ms cloud link, inserting 5,000 records forced 10,000 sequential round-trips (5,000 sequence fetches + 5,000 inserts), translating to over 30 seconds of pure network wait time per batch.
Modifying ORM behavior and data-access loops under that intense time pressure would have violated our change-control policy and risked data corruption. I made the call to abort the cutover and run a controlled rollback.
The migration was technically recoverable, but the operational risk window had closed.
The Reverse Oracle GoldenGate Safety Net
Because this entire validation loop was executed within our isolated testing environment, production data remained completely untouched. However, the simulation proved that our fallback mechanics were sound.
We ran Oracle GoldenGate in reverse: data changes flowed continuously from AWS RDS back to the on-premises database, keeping it configured as a live, active backup. Dropping back to on-premises during this window was entirely seamless. By 5:00 AM Sunday, we had safely rerouted testing traffic back to the legacy database. The fallback process was fully automated, with zero data loss and no disruption to our ongoing business operations.
We spent the subsequent workweek executing targeted application code fixes:
-
Query Batching: We refactored three key endpoints, replacing nested loops with batched
SELECTs. This consolidated our 4,800 iterative, single-record queries into 300 single batched SQL queries usingINclauses. Dashboard latency dropped from 4.5 seconds down to under 400ms under identical workloads. -
JDBC Fetch Tuning: We bumped the default Oracle JDBC driver fetch size from its conservative default up to
100. This ensured that when the database processed one of our 300 consolidated batch queries, the entire dataset was returned to the application server in a single round-trip. -
Sequence Refactoring: We updated our sequence definitions to allocate IDs in batches (
INCREMENT BY 50) and aligned our ORM generators accordingly. This enabled HiLo ID generation, allowing the application server to pull a pool of IDs in a single wire trip and assign them to rows entirely in-memory—reducing primary-key network requests by two orders of magnitude.
The following Saturday, we initiated the cutover again. The dashboard loops that had previously fired 4,800 sequential requests now fired a clean combined total of 350 net round-trips. After the fixes, our 5,000-record batch inserts completed in under 2 seconds instead of 30+.
We completed validation ahead of schedule and were fully live by 3:00 AM with no application performance bottlenecks. Our monitored user-facing P95 response time fell from 6.1 seconds to a crisp 900ms.
Post-Go-Live Validation & The Real-World Failover
To mitigate the risk of an unforeseen infrastructure failure during our first week in the cloud, we kept our reverse Oracle GoldenGate replication pipeline active for two weeks. This ensured that our decommissioned on-premises database remained an up-to-the-second replica of our production cloud environment, providing an immediate fallback option if a critical defect surfaced.
Three weeks after go-live, we got the validation every migration team quietly fears: a real infrastructure failure.
An Amazon EventBridge rule captured an RDS infrastructure event notification indicating that the primary instance in the active Availability Zone (AZ) had encountered a hardware fault. This triggered an automated RDS Multi-AZ failover, promoting the standby instance in the secondary AZ to primary.
Because our application connection pools recycled cleanly, the secondary instance assumed the active workload within two minutes. CloudWatch alarms and synthetic checks confirmed zero impact on user performance. We had successfully survived a production database failure in the cloud with zero downtime—prompting us to permanently decommission the legacy on-premises synchronization.
Real-World Outcomes
Six months after that second cutover weekend, the system processes over 12 million database transactions per day. Moving to Amazon RDS for Oracle eliminated our legacy hardware maintenance overhead, and after the application-side optimizations, overall end-to-end P95 latency is now 35% faster than our previous on-premises baseline.
We spent months planning storage throughput, replication pipelines, rollback mechanics, and failover scenarios. In the end, the migration nearly failed because our application had been built around a network assumption nobody realized existed until the database moved 40 miles away.
Every migration exposes a different constraint. Ours exposed latency amplification, ORM query chattiness, and infrastructure sequencing failures that our on-premises environment had masked for years. The most important lesson wasn't the specific Oracle or AWS tuning itself—it was validating architectural assumptions early enough that rollback remained controlled when those assumptions broke.
Thanks for reading!
If you enjoyed this infrastructure breakdown, follow me here on DEV.to for more deep-dives into real-world production failures and cloud architecture.
You can also find me on LinkedIn to discuss distributed systems and large-scale AWS migrations.
Question for readers
Have you seen latency amplification or N+1 query issues surface only after moving to cloud or distributed systems?
Would love to hear similar migration war stories.
Some implementation details and timelines have been generalized slightly to respect internal enterprise confidentiality requirements while preserving the technical architecture and operational lessons.
