Skip to content

Latest commit

 

History

History
executable file
·
125 lines (82 loc) · 6.45 KB

sct.md

File metadata and controls

executable file
·
125 lines (82 loc) · 6.45 KB

Back to main guide|Next

AWS Schema Conversion Tool (SCT)

The following steps provide instructions for converting an Oracle database to an Amazon Aurora PostgreSQL database. In this activity, you perform the following tasks:

  • Log in to the Windows EC2 instance (this has the AWS SCT installed)

  • Use SCT to create a database migration project

  • Use SCT to convert the Oracle schema to a PostgreSQL schema and analyze schema conversion issues

  • Apply the converted schema to the Aurora PostgreSQL database

    Connect to EC2 Instance

Task 1 - Log in to Windows EC2 instance

  1. Go to the Amazon EC2 console and click on Instances in the left column.

  2. Select the instance with the name OracleXE-SCT and then click the Connect button.

    Note : If you need instructions on connecting to a Windows instance, please see our documentation.

Connect to EC2 Instance

  1. Click on Download Remote Desktop File to download the RDP file to connect to the EC2 instance OracleXE-SCT.

  2. Connect to the EC2 instance using the following password

    User Name: administrator
    Password : GPSreInvent@321

Task 2 - Launch the Schema Conversion Tool

Now that you are connected to the EC2 instance OracleXE-SCT, launch the Schema Conversion Tool from the shortcut on the desktop.

  1. Launch SCT from the shortcut on the desktop and create a new Database Migration Project using the tool. Create New Project

  2. Enter the following values and click OK.

    Parameter Value
    Project Name AWS Schema Conversion Tool Oracle to Aurora PostgreSQL
    Location Leave default
    Database type Transactional Database (OLTP)
    Source database engine Oracle
    Target database engine Amazon Aurora(PostgreSQL compatible)

    Create New Project

  3. Click Connect to Oracle on the top menu bar. Enter the source database details from the below table, and click Test Connection. Once the connection is successfully tested, click Ok.

    Parameter Value
    Type SID
    Server Name localhost
    Server Port 1521
    Oracle SID XE
    User Name hr
    Password hr123
    Use SSL Unchecked
    Store Password Checked

    Connect to Oracle

  4. Click Connect to Amazon Aurora(PostgreSQL compatible) on the top menu bar. Enter the target database details from the below table, and click Test Connection. Once the connection is successfully tested, click Ok.

    Parameter Value
    Server Name Get AuroraPostgreSQLEndpoint from CloudFormation stack output
    Server Port 5432
    Database AuroraPostgreSQLDB
    User Name postgres
    Password Aurora321
    Use SSL Unchecked
    Store Password Checked

    Connect to Amazon Aurora

  5. Select and expand the HR schema from the left-hand panel to inspect the schema objects.

    Schema Objects

Task 3 - Convert the Schema Using the Schema Conversion Tool

After creating a Database Migration Project, the next step is to convert the source Oracle schema to the target PostgreSQL schema.

  1. Right-click on the HR schema from Oracle source and select Convert Schema to generate the data definition language (DDL) statements for the target database.

Note: You may be prompted with a dialog box “These objects might already exist in the target database, Replace?” Select Yes and conversion will start.

Convert Schema

AWS SCT analyses the schema and creates a database migration assessment report for the conversion to PostgreSQL. Items with a red exclamation mark next to them cannot be directly translated from the source to the target. In this case, it includes the SECURE_DML and ADD_JOB_HISTORY procedures, and the EMP_DETAILS_VIEW view.

  1. Click on the View button, and choose Assessment Report view to view the detailed assessment report.

Assessment Report view

  1. Review the assessment report and check if there are any suggested actions to address schema conversion issues.

  2. Next, navigate to the Action Items tab in the report to see the items that have issues while converting to the new PostgreSQL schema.

Action Items

  1. Check each of the issues listed and compare the contents under the source Oracle panel and the target Aurora PostgreSQL panel. SCT has proposed resolutions by generating equivalent PostgreSQL DDL to convert the objects. Additionally, SCT highlights each conversion issue where it cannot automatically generate a conversion, and provide hints on how you can successfully convert the database object.

Notice the issue highlighted in the procedure ADD_JOB_HISTORY. You will see that SCT is unable to automatically convert the procedure. Ignore these issues for now. We will take a look at fixing these issues towards the end of the workshop in activity Resolving SCT action items.

  1. To migrate the converted schema to the target database, right click on the HR schema in the right-hand panel, and select Apply to database.

Apply to database

  1. When prompted if you want to apply the schema to the database, click Yes.

  2. At this point, the schema has been applied to the target database. Expand the HR schema to see the tables.

    Note: If you don’t see the tables, right click on HR schema and select Refresh from Database.

  3. Save the SCT project by clicking on File-> Save project.

Conclusion

This part of the workshop demonstrated how to convert and migrate the schema from an Oracle database to an Amazon Aurora PostgreSQL database. The AWS Schema Conversion Tool (SCT) automates the schema conversion to a large extent. These same steps can be followed to migrate SQL Server and Oracle workloads to other Amazon RDS engines including Aurora MySQL and MySQL.

Back to main guide|Next