Menu
AWS Database Migration Service
Step-by-Step Migration Guide (API Version 2016-01-01)

Step 5: Use AWS SCT to Convert the Oracle Schema to Amazon Redshift

Before you migrate data to Amazon Redshift, you convert the Oracle schema to an Amazon Redshift schema as described following.

To convert an Oracle schema to an Amazon Redshift schema using AWS SCT

  1. Launch AWS SCT. In AWS SCT, choose File, then choose New Project. Create a new project called DWSchemaMigrationDemoProject. Enter the following information in the New Project window, and then choose OK.

    For This Parameter Do This

    Project Name

    Type DWSchemaMigrationDemoProject.

    Location

    Use the default Projects folder and the default Data Warehouse (OLAP) option.

    Source Database Engine

    Choose Oracle DW.

    Target Database Engine

    Choose Amazon Redshift.

    
                                 Creating a new project in AWS SCT
  2. Choose Connect to Oracle. In the Connect to Oracle dialog box, enter the following information, and then choose Test Connection.

    For This Parameter Do This

    Type

    Choose SID.

    Server name

    Use the OracleJDBCConnectionString value you used to connect to the Oracle DB instance, but remove the JDBC prefix information and the port and database name suffix. For example, a sample connection string you use with SQL Workbench/J might be "jdbc:oracle:thin:@abc12345678.cqi87654abc.us-west-2.rds.amazonaws.com:1521:ORCL". For the AWS SCT Server name, you remove "jdbc:oracle:thin:@" and ":1521:ORCL" and use just the server name: "abc12345678.cqi87654abc.us-west-2.rds.amazonaws.com".

    Server port

    Type 1521.

    Oracle SID

    Type ORCL.

    User name

    Type oraadmin.

    Password

    Type oraadmin123.

    
                                 Creating a new project in AWS SCT
  3. Choose OK to close the alert box, then choose OK to close the dialog box and to start the connection to the Oracle DB instance. The database structure on the Oracle DB instance is shown following. Select only the SH schema.

    Note

    If the SH schema does not appear in the list, choose Actions, then choose Refresh from Database.

    
                                 Testing a connection
  4. Choose Connect to Amazon Redshift. In the Connect to Amazon Redshift dialog box, enter the following information and then choose Test Connection.

    For This Parameter Do This

    Type

    Choose SID.

    Server name

    Use the RedshiftJDBCConnectionString value you used to connect to the Amazon Redshift cluster, but remove the JDBC prefix information and the port suffix. For example, a sample connection string you use with SQL Workbench/J might be " jdbc:redshift://oracletoredshiftdwusingdms-redshiftcluster-abc123567.abc87654321.us-west-2.redshift.amazonaws.com:5439/test". For the AWS SCT Server name, you remove " jdbc:redshift://" and :5439/test" to use just the server name: "oracletoredshiftdwusingdms-redshiftcluster-abc123567.abc87654321.us-west-2.redshift.amazonaws.com"

    Server port

    Type 5439.

    User name

    Type redshiftadmin.

    Password

    Type Redshift#123.

    
                                 Creating a new project in AWS SCT

    AWS SCT analyzes the SH schema and creates a database migration assessment report for the conversion to Amazon Redshift.

  5. Choose OK to close the alert box, then choose OK to close the dialog box to start the connection to the Amazon Redshift DB instance.

  6. In the Oracle DW view, open the context (right-click) menu for the SH schema and select Create Report.

  7. Review the report summary. To save the report, choose either Save to CSV or Save to PDF.

    The report discusses the type of objects that can be converted by using AWS SCT, along with potential migration issues and actions to resolve these issues. For this walkthrough, you should see something like the following.

    
                                 Database migration report in AWS SCT
  8. Choose the Action Items tab. The report discusses the type of objects that can be converted by using AWS SCT, along with potential migration issues and actions to resolve these issues. For this walkthrough, you should see something like the following.

    
                                Choosing Action Items in AWS SCT
  9. Open the context (right-click) menu for the SH item in the Schemas list, and then choose Collect Statistics. AWS SCT analyzes the source data to recommend the best keys for the target Amazon Redshift database. For more information, see Collecting or Uploading Statistics for the AWS Schema Conversion Tool.

  10. Open the context (right-click) menu for the SH schema, and then choose Convert schema.

  11. Choose Yes for the confirmation message. AWS SCT then converts your schema to the target database format.

    
                                 AWS SCT schema conversion

    Note

    The choice of the Amazon Redshift sort keys and distribution keys is critical for optimal performance. You can use key management in AWS SCT to customize the choice of keys. For this walkthrough, we use the defaults recommended by AWS SCT. For more information, see Optimizing Amazon Redshift by Using the AWS Schema Conversion Tool.

  12. In the Amazon Redshift view, open the context (right-click) menu for the SH schema, and then choose Apply to database to apply the schema scripts to the target Amazon Redshift instance.

  13. Open the context (right-click) menu for the SH schema, and then choose Refresh from Database to refresh from the target database.

The database schema has now been converted and imported from source to target.