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

Step 5: Use the AWS Schema Conversion Tool (AWS SCT) to Convert the Oracle Schema to Aurora MySQL

Before you migrate data to Aurora MySQL, you convert the Oracle schema to an Aurora MySQL schema as described following.

To convert an Oracle schema to an Aurora MySQL schema using AWS Schema Conversion Tool (AWS SCT)

  1. Launch the AWS Schema Conversion Tool (AWS SCT). In the AWS SCT, choose File, then choose New Project. Create a new project called DMSDemoProject. Enter the following information in the New Project window and then choose OK.

    For This Parameter Do This

    Project Name

    Type DMSDemoProject.

    Location

    Use the default Projects folder and the default Transactional Database (OLTP) option.

    Source Database Engine

    Choose Oracle.

    Target Database Engine

    Choose Amazon Aurora (MySQL Compatible).

    
                                 Creating a new project in the AWS Schema Conversion
                                    Tool
  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. For example, a sample connection string you use with SQL Workbench/J might be "jdbc:oracle:thin:@do1xa4grferti8y.cqiw4tcs0mg7.us-west-2.rds.amazonaws.com:1521:ORCL". For the AWS SCT Server name, you remove "jdbc:oracle:thin:@" and use just the server name: "do1xa4grferti8y.cqiw4tcs0mg7.us-west-2.rds.amazonaws.com"

    Server port

    Type 1521.

    Oracle SID

    Type ORCL.

    User name

    Type oraadmin.

    Password

    Provide the password for the admin user that you assigned when creating the Oracle DB instance using the AWS CloudFormation template.

    
                                 Creating a new project in the AWS Schema Conversion
                                    Tool
  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. Select only the HR schema.

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

    For This Parameter Do This

    Type

    Choose SID.

    Server name

    Use the AuroraJDBCConnectionString value you used to connect to the Aurora MySQL DB instance, 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:mysql://dmsdemo-auroracluster-1u1ogdfg35v.cluster-cqiw4tcs0mg7.us-west-2.rds.amazonaws.com:3306". For the AWS SCT Server name, you remove "jdbc:oracle:thin:@" and ":3306" to use just the server name: "dmsdemo-auroracluster-1u1ogdfg35v.cluster-cqiw4tcs0mg7.us-west-2.rds.amazonaws.com"

    Server port

    Type 3306.

    User name

    Type auradmin.

    Password

    Provide the password for the admin user that you assigned when creating the Oracle DB instance using the AWS CloudFormation template.

    
                                 Creating a new project in the AWS Schema Conversion
                                    Tool

    AWS SCT analyses the HR schema and creates a database migration assessment report for the conversion to Amazon Aurora MySQL.

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

  6. Right-click the HR schema and select Create Report.

    
                                  Database migration report in AWS SCT
  7. Check the report and the action items it suggests. 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. Save the report as .csv or .pdf format for detailed analysis, and then choose the Action Items tab. In the action items, you will see two issues: 1. MySQL does not support Check constraints and 2. MySQL does not support Sequences.

    Regarding action item #1, SCT automatically provisions triggers to simulate check constraints in Aurora MySQL database (Emulating triggers). For example, a check constraint for SAL > 0 in the EMPLOYEES table (in Oracle) is enforced with the help of before and update trigger statements in Aurora MySQL. If you would like to have this logic handled at the application layer, then you can drop or update the triggers if required.

    Regarding action item #2, there are three sequence objects in the source database that are used to generate primary keys for the EMPLOYEES (EMPLOYEE_ID), DEPARTMENTS (DEPARTMENT_ID), LOCATIONS (LOCATION_ID) tables. As mentioned earlier in this walkthrough, one alternative to using sequences for Surrogate keys in Aurora MySQL is using the auto_increment feature. To enable the auto_increment feature, you must change the settings for SCT. For brevity, the following substeps show enabling auto_increment for EMPLOYEE_ID column in the EMPLOYEES table only. The same procedure can be repeated for the other sequence objects.

    Before starting, please note enabling the auto_increment option requires some additional steps via SCT due to the below reasons:

    The good news is that the latest release of SCT provides a Mapping Rules feature that can be used to achieve the above transformation using the following steps:

    1. For the EMPLOYEES table, you must identify the primary key and foreign key relationships by running the following query on the source Oracle database. Note the columns that need to be specified in the SCT Mapping rules.

      Copy
      SELECT * FROM (SELECT PK.TABLE_NAME, C.COLUMN_NAME, PK.CONSTRAINT_TYPE FROM DBA_CONSTRAINTS PK, DBA_CONS_COLUMNS C WHERE PK.CONSTRAINT_NAME = C.CONSTRAINT_NAME AND PK.OWNER = 'HR' AND PK.TABLE_NAME = 'EMPLOYEES' AND PK.CONSTRAINT_TYPE = 'P' UNION SELECT FK.TABLE_NAME, COL.COLUMN_NAME, FK.CONSTRAINT_TYPE FROM DBA_CONSTRAINTS PK, DBA_CONSTRAINTS FK, DBA_CONS_COLUMNS COL WHERE PK.CONSTRAINT_NAME = FK.R_CONSTRAINT_NAME AND FK.CONSTRAINT_TYPE = 'R' AND FK.CONSTRAINT_NAME = COL.CONSTRAINT_NAME AND PK.OWNER = 'HR' AND PK.TABLE_NAME = 'EMPLOYEES' AND PK.CONSTRAINT_TYPE = 'P' ) ORDER BY 3 ASC;

      The results of the query should be similar to the following:

      Copy
      TABLE_NAME COLUMN_NAME CONSTRAINT_TYPE EMPLOYEES EMPLOYEE_ID P JOB_HISTORY EMPLOYEE_ID R EMPLOYEES MANAGER_ID R DEPARTMENTS MANAGER_ID R
    2. Choose Settings, and then choose Mapping Rules.

    3. Specify the Mapping rule for Data type conversions for the list of identified columns in Step1. You will need to specify 4 rules, one for each column as described below.

      For this Parameter

      Rule1

      Rule2

      Rule3

      Rule4

      Name

      EMP_SEQ1

      EMP_SEQ2

      JOB_SEQ1

      DEPT_SEQ1

      For

      Select Column

      Select Column

      Select Column

      Select Column

      Where

      (Schema Name) And (Table Name) And (Column Name)

      HR

      EMPLOYEES

      EMPLOYEE_ID

      HR

      EMPLOYEES

      MANAGER_ID

      HR

      JOB_HISTORY

      EMPLOYEE_ID

      HR

      DEPARTMENTS

      MANAGER_ID

      Actions

      Select Change data type

      Select Change data type

      Select Change data type

      Select Change data type

      To

      SMALLINT

      SMALLINT

      SMALLINT

      SMALLINT

      Note that in a real-world scenario you would choose the data type based on your requirements.

      
                                        Choosing Convert schema in AWS SCT
    4. Choose Yes for “Would you like to save Mapping Rule settings?”

  9. Right-click the HR schema, and then choose Convert schema.

    
                                Choosing Convert schema in AWS SCT
  10. Choose Yes for the confirmation message. AWS SCT then converts your schema to the target database format.

    
                                 AWS SCT schema conversion
  11. Choose the HR schema, and then choose Apply to database to apply the schema scripts to the target Aurora MySQL instance, as shown following.

    
                                 Applying AWS SCT schema scripts
  12. Choose the HR schema, and then choose Refresh from Database to refresh from the target database, as shown following.

    
                                 Refreshing from the target database

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