← Back to Blog

How to Migrate Oracle DB to Amazon Redshift Using AWS SCT

As data scientists or software engineers, we often encounter the need to migrate databases. This can be a challenging task, especially when dealing with large data sets. Today, we’re going to address how to migrate from Oracle DB to Amazon Redshift using AWS Schema Conversion Tool (SCT).
By Saturn Cloud | | Miscellaneous

How to Migrate Oracle DB to Amazon Redshift Using AWS SCT

As data scientists or software engineers, we often encounter the need to migrate databases. This can be a challenging task, especially when dealing with large data sets. Today, we’re going to address how to migrate from Oracle DB to Amazon Redshift using AWS Schema Conversion Tool (SCT).

What is AWS SCT?

Before diving into the migration process, let’s understand what AWS SCT is. AWS SCT is a tool designed to convert your commercial database schema and stored procedures, functions, and views to a format compatible with AWS services. It can be used to migrate databases from on-premises environments to the AWS Cloud, making it a perfect tool for our Oracle DB to Amazon Redshift migration.

Preparing for the Migration

Before starting the migration process, ensure that you’ve installed AWS SCT on your local machine. The AWS SCT installation guide can be found on the official AWS documentation.

Next, establish a connection with both your source (Oracle DB) and target (Amazon Redshift) databases. Here’s a brief guide on how to do this:

  1. Open AWS SCT and click on Add source on the toolbar.
  2. Select Oracle as the source database engine.
  3. Input the connection settings for your Oracle DB (host, port, SID, username, and password), then click Test connection to ensure the details are correct.
  4. Repeat the process for your target database, but this time select Amazon Redshift as the database engine.

Converting the Schema

With connections set up, the next step is to convert the Oracle DB schema to its equivalent in Amazon Redshift. Follow these steps:

  1. On the AWS SCT interface, select your Oracle DB from the left panel.
  2. Choose Convert Schema under the Actions menu.
  3. AWS SCT will analyze the Oracle DB schema and convert it into a format compatible with Amazon Redshift.

Note: There might be some objects that AWS SCT can’t convert automatically. For these, manual intervention is required. AWS SCT provides a detailed report on these objects for you to manually review and convert.

Migrating the Data

After converting the schema, the next step is migrating the data. AWS SCT facilitates this through integration with AWS DMS (Database Migration Service). To migrate the data:

  1. In AWS SCT, choose View and then AWS DMS tasks.
  2. Click on + to create a new migration task.
  3. Configure the task settings (source endpoint, target endpoint, migration type, and table mappings).
  4. Click on Run once to start the migration task.

Monitor the progress of your migration task in AWS DMS. Once the task completes, your data should now be migrated to Amazon Redshift.

Post-Migration Actions

After migration, validate your data in Amazon Redshift. You can do this by running some test queries or comparing data with your original Oracle DB. It’s also recommended to re-index your tables and gather statistics to optimize query performance.

Wrap Up

Database migration can be complex, but with tools like AWS SCT, the process becomes more manageable. By leveraging AWS SCT, you can migrate your Oracle DB to Amazon Redshift, taking advantage of the scalability and performance that a cloud-based data warehouse offers.

Remember to plan your migration carefully, taking into account the compatibility of your schema and the size of your database. With the right approach, migrating from Oracle DB to Amazon Redshift can be a seamless process that propels your data strategy forward.

Keywords: Oracle DB, Amazon Redshift, AWS SCT, schema conversion, database migration, AWS DMS

About Saturn Cloud

Saturn Cloud is your all-in-one solution for data science & ML development, deployment, and data pipelines in the cloud. Spin up a notebook with 4TB of RAM, add a GPU, connect to a distributed cluster of workers, and more. Join today and get 150 hours of free compute per month.

Try Saturn Cloud Now