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

Step 4: Use the AWS Schema Conversion Tool (AWS SCT) to Convert the Oracle Schema to PostgreSQL

Before you migrate data to PostgreSQL, you convert the Oracle schema to a PostgreSQL schema.

To convert an Oracle schema to a PostgreSQL schema using AWS SCT

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

    Parameter Description

    Project Name

    Type AWS Schema Conversion Tool Oracle to PostgreSQL.

    Location

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

    Source Database Engine

    Choose Oracle.

    Target Database Engine

    Choose Amazon RDS for PostgreSQL.

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

    Parameter Description

    Type

    Choose SID.

    Server name

    Type the server name.

    Server port

    Type the Oracle port number. The default is 1521.

    Oracle SID

    Type the database SID.

    User name

    Type the Oracle admin username.

    Password

    Provide the password for the admin user.

    
                                Test Oracle connection 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.

  4. Choose Connect to Amazon RDS for PostgreSQL. In the Connect to Amazon PostgreSQL dialog box, enter the following information and then choose Test Connection.

    Parameter Description

    Server name

    Type the server name.

    Server port

    Type the PostgreSQL port number. The default is 5432.

    Database

    Type the database name.

    User name

    Type the PostgreSQL admin username.

    Password

    Provide the password for the admin user.

    
                                Test PostgreSQL connection in the AWS Schema Conversion
                                    Tool
  5. Choose OK to close the alert box, then choose OK to close the dialog box to start the connection to the PostgreSQL DB instance.

  6. Open the context (right-click) menu for the schema to migrate, and then choose Convert schema.

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

    
                            AWS SCT schema conversion

    AWS SCT analyses the schema and creates a database migration assessment report for the conversion to PostgreSQL.

  8. Select Assessment Report View from View to check the report.

    The report breaks down by each object type and by how much manual change is needed to successfully convert it.

    
                                Database migration report in AWS SCT

    Generally packages, procedures, and functions are most likely to have some issues to resolve because they contain the most custom PL/SQL code. AWS SCT also provides hints about how to fix these objects.

    
                            Detailed Database migration report in AWS SCT
  9. Choose the Action Items tab.

    
                            Action Items in AWS SCT

    The Action Items tab shows each issue for each object that requires attention.

    For each conversion issue, you can complete one of the following actions:

    • Modify the objects on the source Oracle database so that AWS SCT can convert the objects to the target PostgreSQL database.

      1. Modify the objects on the source Oracle database.

      2. Repeat the previous steps to convert the schema and check the assessment report.

      3. If necessary, repeat this process until there are no conversion issues.

      4. Choose Main View from View, and open the context (right-click) menu for the target PostgreSQL schema, and choose Apply to database to apply the schema changes to the PostgreSQL database.

        
                                        Apply Schema Changes
    • Instead of modifying the source schema, modify scripts generated by AWS SCT before applying the scripts on the target PostgreSQL database.

      1. Open the context (right-click) menu for the target PostgreSQL schema name, and select Save as SQL. Next, choose a name and destination for the script.

      2. In the script, modify the objects to correct conversion issues.

      3. Run the script on the target PostgreSQL database.

    For more information, see Converting Database Schema to Amazon RDS by Using the AWS Schema Conversion Tool in the AWS Schema Conversion Tool User Guide.

  10. Use AWS SCT to create mapping rules.

    1. Under Settings, select Mapping Rules.

    2. In addition to the two default mapping rules that convert schema names and table names to lower case, create additional mapping rules that are required based on the action items.

    3. Save the mapping rules.

      
                                    Saving Mapping Rules
    4. Click Export script for DMS to export a JSON format of all the transformations that the AWS DMS task will use to determine which object from the source corresponds to which object on the target. Click Save.