Oracle to AWS: Migration Paths for Database-Heavy Workloads

Oracle database migrations are among the highest-value infrastructure projects a CTO or IT Director can sponsor. Oracle licence costs at enterprise scale — Processor licences at $47,500 USD each, plus 22% annual support — make even a multi-year migration programme attractive if it eliminates or substantially reduces the Oracle dependency. The challenge is that Oracle workloads are deeply diverse: some organisations use Oracle purely as a transactional RDBMS, others have years of PL/SQL business logic, data warehouse schemas, and Oracle-specific features like Materialized Views with Query Rewrite and Oracle Text baked into their architecture.

There is no single migration path. The right AWS target depends on what the Oracle database is actually doing.

Choosing the Right AWS Target

The first decision — and the most consequential — is target platform selection. AWS offers three primary destinations for Oracle workloads:

Amazon RDS for PostgreSQL or Aurora PostgreSQL — the right choice for OLTP workloads where the primary concern is transactional throughput, high availability, and reducing Oracle licence cost. Aurora PostgreSQL offers up to 3x the throughput of standard RDS PostgreSQL with automatic multi-AZ failover, and its storage scales automatically to 128 TB. For Oracle databases under ~10 TB that are primarily serving application read/write traffic, this is usually the correct target.

Amazon RDS for Oracle — the “lift-and-shift” option. AWS manages the EC2 instance, OS patching, and backup, but you still pay Oracle licence costs (either Bring Your Own Licence or the included Licence Edition). This is a valid interim step — it eliminates hardware and reduces operational overhead — but it does not solve the licence cost problem. Use this path when the application is so tightly coupled to Oracle-specific features that a schema conversion is not feasible in the current planning horizon.

Amazon Redshift — appropriate when the Oracle database is functioning as a data warehouse or analytical query engine rather than as a transactional database. Oracle databases that are primarily targets for ETL loads and sources for BI reporting are often good Redshift candidates.

For data-intensive organisations, the combination of Aurora PostgreSQL (for OLTP) and Redshift (for analytics, fed by DMS replication) often replaces a single monolithic Oracle database that was trying to serve both workloads.

AWS Schema Conversion Tool: What It Handles and What It Does Not

AWS SCT is the standard tool for Oracle-to-PostgreSQL schema conversion, and it is genuinely useful — but its conversion rate statistics can be misleading. SCT may report a 70% automatic conversion rate for a schema, but that 30% of manually-converted objects typically represents 80% of the complexity and business risk.

Objects that SCT converts reliably:

• Standard CREATE TABLE DDL
• Simple views without Oracle-specific syntax
• Basic PL/SQL procedures that use standard SQL and common PL/SQL constructs

Objects that consistently require manual rewriting:

• DBMS_ package calls (DBMS_JOB, DBMS_SCHEDULER, DBMS_CRYPTO, DBMS_OUTPUT)
• Oracle sequences used with NEXTVAL — PostgreSQL uses SERIAL or GENERATED AS IDENTITY
• Oracle hierarchical queries (CONNECT BY PRIOR) — rewrite to PostgreSQL recursive CTEs
• ROWNUM pseudo-column — replace with ROW_NUMBER() window function
• Oracle outer join syntax (+ operator) — rewrite to ANSI LEFT OUTER JOIN
• Packages — Oracle packages have no direct PostgreSQL equivalent; split into schemas and functions

Here is the CONNECT BY hierarchical query rewrite, which appears frequently in Oracle schemas that model organisational hierarchies or bill-of-materials structures:

-- Oracle: hierarchical query with CONNECT BY
SELECT
    employee_id,
    manager_id,
    first_name,
    LEVEL AS depth,
    SYS_CONNECT_BY_PATH(first_name, '/') AS path
FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR employee_id = manager_id;

-- PostgreSQL equivalent: recursive CTE
WITH RECURSIVE org_hierarchy AS (
    -- Anchor: root nodes
    SELECT
        employee_id,
        manager_id,
        first_name,
        1 AS depth,
        first_name::TEXT AS path
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    -- Recursive: children
    SELECT
        e.employee_id,
        e.manager_id,
        e.first_name,
        oh.depth + 1,
        oh.path || '/' || e.first_name
    FROM employees e
    INNER JOIN org_hierarchy oh ON e.manager_id = oh.employee_id
)
SELECT employee_id, manager_id, first_name, depth, path
FROM org_hierarchy;

Oracle Data Guard and High Availability: The AWS Equivalent

Many Oracle installations depend on Data Guard for synchronous replication and automatic failover. This is a sophisticated HA solution that organisations are understandably reluctant to give up. Aurora PostgreSQL’s Multi-AZ configuration is the functional equivalent: synchronous replication to a standby in a different Availability Zone, automatic failover in typically 30 seconds, and a single connection endpoint that abstracts the primary/standby topology from applications.

For organisations that need cross-Region disaster recovery — the equivalent of Oracle Data Guard Far Sync or standby databases in remote sites — Aurora Global Database provides managed cross-region replication with recovery time objectives under 1 minute.

The key difference from Oracle Data Guard is that Aurora handles all of this without requiring DBA intervention in the replication configuration. There are no standby redo log management tasks, no switchover scripts to maintain, and no manual synchronisation checks. The trade-off is less granular control over replication behaviour.

Using AWS DMS for the Data Migration

AWS Database Migration Service handles the ongoing data replication from Oracle to the AWS target. For OLTP migrations, the typical pattern is:

1. Use SCT to convert the schema and deploy to the target
2. Load initial data using DMS full-load task (DMS parallelises table loads across multiple threads)
3. Switch to DMS CDC (Change Data Capture) mode, which reads Oracle redo logs via LogMiner to replicate ongoing changes in near-real-time
4. Validate data with AWS DMS data validation or a custom row count and checksum comparison
5. Cut over applications to the new endpoint during a maintenance window

Oracle LogMiner — the mechanism DMS uses for CDC — requires supplemental logging to be enabled on the source:

-- Enable supplemental logging on Oracle source
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (UNIQUE INDEX) COLUMNS;

-- For specific tables, enable all column logging if needed
ALTER TABLE my_schema.critical_table ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;

LogMiner adds a small but measurable overhead to the Oracle redo log generation rate — typically 5–15% more redo volume. For organisations running Oracle at capacity, this overhead needs to be factored into the cutover window planning. See our guide on AWS DMS Database Migration for detailed DMS task configuration.

Handling Oracle Licences During Migration

A common mistake is decommissioning Oracle too quickly and losing the ability to roll back. A more conservative approach:

• Month 1–3: Deploy the AWS target, run DMS replication, but keep Oracle fully licensed and active
• Month 3–6: Cut read traffic to AWS (reporting, analytics queries); Oracle remains the write master
• Month 6–9: Cut write traffic; Oracle transitions to read-only archive
• Month 9–12: Oracle decommission; licence termination or non-renewal at next anniversary

Oracle licences typically renew annually. Coordinate the technical migration timeline with the licence renewal date. Missing a renewal date by one month means paying another full year of support — a cost that can easily exceed $500,000 CAD for large deployments.

Cost Comparison: Oracle Enterprise Edition vs. Aurora PostgreSQL

A typical Oracle Enterprise Edition deployment on physical servers with two sockets (eight cores per socket, 0.5 licence factor for physical cores) requires eight Oracle Processor licences: 8 × $47,500 = $380,000 USD in perpetual licence cost plus $83,600/year in annual support. Over five years, the total Oracle licence cost is approximately $798,000 USD.

An equivalent Aurora PostgreSQL deployment — two db.r6g.4xlarge instances in a Multi-AZ configuration with 120 GB storage — costs approximately $7,400 USD/month on-demand, or roughly $4,100/month with a 1-year Reserved Instance commitment. Over five years with Reserved Instances, that is approximately $246,000 USD — a 69% saving.

Factor in the operational overhead reduction (no DBA patching of Oracle, no manual Data Guard management) and the saving is larger still. For further cost management guidance across your AWS data estate, see AWS Cost Optimisation for Data Teams.

Conclusion

Oracle migrations are complex, commercially sensitive, and organisation-changing programmes. The technical path — SCT for schema conversion, DMS for data migration, Aurora or RDS as the target — is well-defined. The difficulty is in the details: PL/SQL rewriting, Oracle-specific feature replacement, and coordinating the migration timeline with Oracle licence renewal dates to maximise savings.

Infra IT Consulting works with data-heavy organisations across Canada, the UK, and Africa on Oracle migration assessments and delivery programmes. If you are evaluating your Oracle estate or ready to start a migration, contact us to discuss a scoped assessment.