How to Migrate from Oracle to Redshift

How do I migrate an Oracle database to Amazon Redshift with minimal downtime?

Migrating from Oracle to Amazon Redshift moves schema and data to a scalable columnar warehouse with minimal downtime.

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.

Description

What is the fastest way to migrate Oracle to Redshift?

Combine AWS Schema Conversion Tool (SCT) for automatic DDL translation with AWS Database Migration Service (DMS) for continuous data replication. This pairing converts Oracle objects, creates them in Redshift, and streams changes until cut-over.

Which prerequisites must be in place?

Create a Redshift cluster, provision an S3 bucket, open network access to Oracle, and grant SELECT on source tables. Install SCT and DMS agents on a machine with access to both databases.

How do I convert Oracle schema?

Launch SCT, add Oracle as source and Redshift as target, run "Assess" to see compatibility, then click "Convert Schema". Review and apply converted DDL directly to Redshift or generate a .sql script for manual execution.

Manual DDL execution example

Download the DDL file from SCT, connect to Redshift via psql or Galaxy, and run the script. Verify objects with pg_table_def.

How do I migrate data in bulk?

Configure a DMS task in full-load + CDC mode. DMS extracts Oracle data, stages it as CSV in S3, and issues INSERT commands to Redshift. Enable logging to monitor throughput.

Sample DMS table mapping rule

{"rules":[{"rule-type":"selection","rule-id":"1","rule-name":"1","object-locator":{"schema-name":"HR","table-name":"%"},"rule-action":"include"}]}

How do I copy data without DMS?

Export Oracle data to S3 (e.g., using aws s3 cp) then run Redshift COPY. Use gzip compression to speed transfer.

COPY command demo

COPY Orders FROM 's3://ecom-bucket/orders/' IAM_ROLE 'arn:aws:iam::123:role/redshiftCopy' FORMAT AS CSV GZIP;

How do I verify row counts?

After each table load, compare SELECT COUNT(*) in Oracle and Redshift. Automate checks with SQL scripts or Galaxy notebooks.

How do I handle Oracle sequences?

Replace Oracle sequences with Redshift IDENTITY(1,1) columns or create a table-based sequence generator using GENERATED BY DEFAULT AS IDENTITY.

What downtime can I expect?

With DMS CDC, downtime is limited to final cut-over when you stop writes to Oracle, apply last changes to Redshift, and redirect applications.

Why How to Migrate from Oracle to Redshift is important

How to Migrate from Oracle to Redshift Example Usage


--Verify migrated orders revenue by day
SELECT order_date::date   AS day,
       SUM(total_amount)  AS daily_revenue
FROM   Orders
GROUP  BY day
ORDER  BY day DESC
LIMIT  7;

How to Migrate from Oracle to Redshift Syntax


--Typical converted table definition
CREATE TABLE Customers (
    id            INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    name          VARCHAR(100),
    email         VARCHAR(255),
    created_at    TIMESTAMP
);

--Bulk load syntax (S3 → Redshift)
COPY Customers
FROM 's3://ecom-bucket/customers/'
IAM_ROLE 'arn:aws:iam::123456789012:role/RedshiftCopyRole'
FORMAT AS CSV
TIMEFORMAT 'auto'
GZIP;

Common Mistakes

Skipping assessment reports. SCT’s compatibility report highlights objects that need manual fixes; ignoring it leads to failed DDL creation in Redshift. Always review and adjust before applying.
Using large batch INSERTs instead of COPY. COPY loads data in parallel from S3 and compresses automatically; INSERT loops slow migration by orders of magnitude. Prefer COPY or DMS full-load.