AWS Database Blog

Migrating Oracle Database from On-Premises or Amazon EC2 Instances to Amazon Redshift

Ballu Singh and Pubali Sen are solutions architects at Amazon Web Services.

AWS Database Migration Service (AWS DMS) helps you migrate databases to AWS easily and securely. The AWS Database Migration Service can migrate your data to and from most widely used commercial and open-source databases. The service supports homogenous migrations such as Oracle to Oracle. It also supports heterogeneous migrations between different database platforms, such as Oracle to Amazon Aurora or Microsoft SQL Server to MySQL. The source database remains fully operational during the migration, minimizing downtime to applications that rely on the database.

Data replication with AWS Database Migration Service integrates tightly with the AWS Schema Conversion Tool (AWS SCT), simplifying heterogeneous database migration projects. You can use AWS SCT for heterogeneous migrations. You can use the schema export tools native to the source engine for homogenous migrations.

In this blog post, we focus on migrating the data from Oracle Data Warehouse to Amazon Redshift.

In the past, AWS SCT couldn’t convert custom code, such as views and functions, from Oracle Data Warehouse to a format compatible with the Amazon Redshift. To migrate views and functions, you had to first convert the Oracle Data Warehouse schema to PostgreSQL. Then you’d apply a script to extract views and functions that are compatible with Amazon Redshift.

After an update based on customer feedback, we’re happy to let you know that with AWS SCT and AWS DMS, you can now migrate Oracle Data Warehouse to Amazon Redshift along with views and functions.

The following diagram illustrates the migration process.

Prerequisites
To get started with this migration, take the following steps:

Creating a Stack
In this post, you use Launch Stack following to launch an AWS CloudFormation stack. The launch process also creates the architecture shown preceding. You can then see the results in the AWS DMS console. At the end of the migration, you tear down the CloudFormation stack.

The link launches the stack in the US West (Oregon) Region (us-west-2).

Some resources incur costs as long as the resources are in use.

 

To launch your stack and name it, do the following:

  1. Log in to your AWS account if you haven’t done so already.
  2. Choose Launch Stackto launch the CloudFormation console with the prepopulated CloudFormation template. Choose Next.
  3. For Stack name, use the prepopulated orclrsmigration or type a custom name. Choose KeyName, type MasterUserPassword for Redshift Cluster, and choose Next.
  4. On the Reviewpage, acknowledge that CloudFormation will create AWS Identity and Access Management (IAM) roles as a result of launching the stack. Choose Create.
  5. To see the stack creation progress, choose Refresh and then Restore, and then select the stack to see the launch events.
  6. When the stack is successfully launched, the Status changes from CREATE_IN_PROGRESS to CREATE_COMPLETE. To view the values used in the next section, choose Outputs.

The infrastructure created by this CloudFormation template is used in the next section. You should see the values that are listed in the following table.

Seeing the Schema Conversion Tool in Action

To see the AWS SCT tool in action, do the following:

  1. Start the AWS Schema Conversion Tool.
  2. Choose New Project from the File The New Project dialog box appears.

    Add the following preliminary project information:

    Project Name Type a name for your project, which is stored locally on your computer.
    Location Type the location for your local project file.
    Data Warehouse (OLAP)
    Source DB Engine Oracle DW
    Target DB Engine Amazon Redshift
  3. Choose OK to create your AWS Schema Conversion Tool project.
  4. To connect to your Oracle data warehouse, choose Connect to Oracle DW.
    The Connect to Oracle DW dialog box appears. Provide the Oracle DW source database connection information.Type the following values into the form, and then choose Next.

    Server name <SourceEC2EndpointDns>
    Server port 1521
    Oracle SID XE
    User name dms_sample
    Password dms_sample
    Use SSL Unchecked
  5. Choose Test Connectionto verify that you can successfully connect to your source database.
  6. Choose OKto connect to your source database.
  7. Select the DMS_SAMPLE database and then choose Next.
  8. Review the Database Migration Assessment Report, and then choose Next.
  9. To connect to Amazon Redshift, choose Connect to Amazon Redshift.
    The Connect to Amazon Redshift dialog box appears. Provide the target database connection information.Type the following values into the form, and then choose Next.

    Server name <TargetRedshiftEndpointDns>
    Server port 5439
    database dev
    User name admin
    Password Password selected in CloudFormation
    Use SSL Unchecked
  10. Choose Test Connectionto verify that you can successfully connect to your source database.
  11. Choose OKto connect to your target database.

Converting Your Schema
Next, convert your schema:

  1. Choose View, and then choose Main View.
  2. In the left panel that displays the schema from your source database, choose dms_sample schema object to convert. Open the context (right-click) menu for the object, and then choose Convert schema.
  3. When the AWS Schema Conversion Tool finishes converting the schema, you can view the proposed schema in the target Amazon Redshift cluster along with views and functions.
    At this point, no schema is applied to your target Amazon Redshift cluster. You can edit the schema in this window. The edited schema is stored as part of your project. The edited schema is written to the target DB instance when you choose to apply your converted schema to the database.
  4. In the right panel that displays the schema from your target database, choose dms_sample schema object to convert. Open the context (right-click) menu for the object, and then choose Apply to database.

At this point, the database schema is available for the Amazon Redshift cluster with no data in it.

Configuring AWS DMS to Migrate a Database

Next, we’ll move into DMS:

  1. On the AWS Management Console, choose DMS, and then choose Create migration.
  2. On the Welcome to AWS Database Migration Service page, choose Next.
  3. The Create replication instance page appears.

    On this page, specify the following values, and then choose Next.

    Name Type a name for the replication instance that contains from 8 to 16 printable ASCII characters (excluding /,”, and @).
    Description Type a brief description of the replication instance.
    Instance class dms.t2.medium
    VPC Choose the Amazon Virtual Private Cloud (Amazon VPC) you want to use. Use VPC, where your source or target or both resides.
    Multi-AZ No
    Publicly Accessible Checked
  4. Keep Advance tab values as the defaults, and choose Next.

Working with a Database Endpoint

  1. Specify the following values for the Source endpoint.
Endpoint Identifier Type the name you want to use to identify the endpoint.
Source Engine Oracle
Server Name <SourceEC2EndpointDns>
Port 1521
SSL Mode None
User name dms_sample
Password dms_sample
  1. Specify the following values for the Target endpoint.
Endpoint Identifier Type the name you want to use to identify the endpoint.
Source Engine Redshift
Server Name <RedshiftEndpointDns>
Port 5439
SSL Mode None
User name admin
Password Password selected in CloudFormation

You can test the endpoint connection by choosing Run test, after the replication instance is created successfully.


Creating a Task

On the Create Task page, specify the task options.

The following table describes the task settings.

Task name Type a name for the task.
Replication Instance Type a description for the task.
Source endpoint Shows the source endpoint to use.
Target endpoint Shows the target endpoint to use.
Replication instance Shows the replication instance to use.
Migration type Migrate existing data
Start task on create Check

For Target table preparation mode, choose Do nothing.

For Table mapping, choose dms_sample, select Add Selection rule, then and choose Create Task.

Monitoring Your Task

If you choose Start task on create when you create a task, your task begins immediately to migrate your data when you choose Create task. You can view statistics and monitoring information for your task by choosing the running task from the AWS Management Console. The following screenshot shows the table statistics of a database migration.

Conclusion

In this post, I have demonstrated how easy it is to migrate a commercial database in Oracle Data Warehouse to Amazon Redshift along with views and functions.

I welcome your comments or questions below.