In this post we will see how we can migrate an On-premise Oracle database to Mysql RDS using AWS SCT and AWS DMS
Before we proceed with the migration, we need to know what is AWS SCT
What is AWS SCT?
You can use the AWS Schema Conversion Tool (AWS SCT) to convert your existing database schema from one database engine to another. Your converted schema is suitable for an Amazon Relational Database Service (Amazon RDS) MySQL, MariaDB, Oracle, SQL Server, PostgreSQL DB, an Amazon Aurora DB cluster, or an Amazon Redshift cluster. The converted schema can also be used with a database on an Amazon EC2 instance or stored as data on an Amazon S3 bucket.
AWS SCT provides a project-based user interface to automatically convert the database schema of your source database into a format compatible with your target Amazon RDS instance. If schema from your source database can’t be converted automatically, AWS SCT provides guidance on how you can create equivalent schema in your target Amazon RDS database.
AWS SCT is supported only in 64 bit OS Version
How to install the Schema Conversion Tool
The below link will route you to the download page and will guide you on how to install AWS SCT :
Install the Java Database Connectivity (JDBC) drivers for your source and target database engines. :
For the AWS SCT to work correctly, you must install the JDBC drivers for your source and target database engines.
To verify the AWS SCT MSI file on WIndows, unzip the downloaded file, right-click the MSI file and select Properties and navigate to Digital Signatures tab and verify that the digital signature is from Amazon Services LLC.
Install the SCT MSI file after verification and open it after installation.
Follow the below steps after successful installation of SCT Tool
1. Update the Driver Locations
- Click on settings –> Global Settings
If you donot set the drivers locations globally, applications asks you for the location of the drivers whenever you connect
- Choose Drivers
Choose the Driver (Oracle,Mysql in my example) which you need to select from the listed and browse and select the files from the path where you downloaded the JDBC Drivers
- Click OK
2. Set the AWS Credentials with which your AWS SCT can access your AWS Console
- From Global Settings go to AWS Service Profiles and provide the details as highlighted below
- You need to specify your S3 bucket only if you are using a feature that connects to your S3 bucket
- Test the connections and verify whether it is passing the test or not
- Click OK
For converting your database from Oracle, the Oracle schemas needs to be granted the below privileges :
- SELECT ANY DICTIONARY
- SELECT on SYS.USER$ TO <sct_user>
Create Project for migration
To migrate data from a source to a target, AWS SCT loads all metadata from source and target databases into a tree structure. This structure appears in AWS SCT as the tree view in the main project window.
Some databases can have a large number of objects in the tree structure. You can use tree filters in AWS SCT to search for objects in the source and target tree structures. When you use a tree filter, you don’t change the objects that are converted when you convert your database. The filter only changes what you see in the tree.
1. Click on File–>New Project
2. Provide the Project Name, Location of the project, Select OLTP,OLAP or NoSQL Database as per your requirement
3. Source Engine –> Oracle
4. Target ENgine –> Amazon RDS for MySQL
5. Once you click OK, you will find the highlighted options in your tab
6. Click on Connect to Oracle to configure the connectivity details of your Source Database and provide the details as per your requirements, store the password of your user and test the connection. Use SSL in your project environment for Secure connection between SCT and your Database
7. Once the connection test is successful, click OK
8. You can see the Tree Structure for your Source Database as mentioned above, once you connect to your Database
9. Similarly connect to your target database clicking on Connect to Amazon RDS for MySQL after which you would get the tree for your target database too
10. Now we need to filter the tree settings
11. From Global Settings traverse to Tree View, select the desired vendor, and then you can Hide empty Schemas, Hide System Schemas
12. If you are planning to filter out locally for just a single database, you can filter by clicking on the Funnel near your connection and provide the schema which you need
13. If there are changes in the source database after your successful connection, you can refresh the Schemas tab to make changes effective
Database Migration Assessment Report
The database migration assessment report summarizes all of the action items for schema that can’t be converted automatically to the engine of your target Amazon RDS DB instance. The report also includes estimates of the amount of effort that it will take to write the equivalent code for your target DB instance
Steps to create Database Migration Assessment Report:
1. To create the report you need to right click on the concerned schema and click on Create Report
2. The report would be something like below where it will show which all objects it can convert and which needs manual intervention for conversion.
In our example, being a demo environment,our checks passed for conversion of all objects
The colour ranges depict whether any actions need to be taken or not.
The report can be saved to pdf for further clarifications
Convert your Schema
Please follow the below steps to convert your schema :
1. In source, right click on the concerned schema and click on Convert Schema
2. Once the schemas have been converted, the same can be seen in the target database places in the right corner of your AWS SCT Tool
3. This doesnt mean that your schema has been migrated to your RDS instance. This has just been converted to be compatible with AWS RDS for MySQL
4. To make changes effective, you need to apply this to your database
5. This will just create the database and table structure in your RDS instance. Now you need to import the data using DMS. From the Mapping tab, you can check the status of your Apply to Database work
You can also check for any converted objects in the Related Converted objects , Creation date
6. To migrate using DMS, please refer to our blog for Full Load Migration
7. After successful DMS migration,you can check the datas to be present in your AWS RDS for MySQL. Also you can check the status of your DMS migration fro your SCT Tool
Hope you liked my work. Please do share and like
For more videos and posts on AWS RDS, click on the below links :