Migrate an Oracle database to Aurora PostgreSQL using AWS DMS and AWS SCT
Created by Mark Szalkiewicz (AWS)
R Type: Re-architect | Source: Databases: Relational | Target: Amazon Aurora PostgreSQL |
Created by: AWS | Environment: PoC or pilot | Technologies: Databases; Migration |
Workload: Oracle | AWS services: Amazon Aurora |
Summary
This pattern describes how to migrate an Oracle database to Amazon Aurora PostgreSQL-Compatible Edition by using AWS Data Migration Service (AWS DMS) and AWS Schema Conversion Tool (AWS SCT).
The pattern covers source Oracle databases that are on premises, Oracle databases that are installed on Amazon Elastic Compute Cloud (Amazon EC2) instances, and Amazon Relational Database Service (Amazon RDS) for Oracle databases. The pattern converts these databases to Aurora PostgreSQL-Compatible.
Prerequisites and limitations
Prerequisites
An active AWS account
An Oracle database in an on-premises data center or in the AWS Cloud .
SQL clients installed either on a local machine or on an EC2 instance.
Java Database Connectivity (JDBC) drivers for AWS SCT connectors, installed on either a local machine or an EC2 instance where AWS SCT is installed.
If the source database supports a commercial off-the-shelf (COTS) application or is vendor-specific, you might not be able to convert it to another database engine. Before using this pattern, confirm that the application supports Aurora PostgreSQL-Compatible .
Limitations
Database size limit: 64 TB
Product versions
All Oracle database editions for versions 11g (versions 11.2.0.3.v1 and later) and up to 12.2, and 18c. For the latest list of supported versions, see Using an Oracle Database as a Source for AWS DMS and Using a PostgreSQL Database as a Target for AWS DMS. We recommend that you use the latest version of AWS DMS for the most comprehensive version and feature support. For information about Oracle database versions supported by AWS SCT, see the AWS SCT documentation.
Aurora supports the PostgreSQL versions listed in Database Engine Versions for Aurora PostgreSQL-Compatible.
Architecture
Source technology stack
One of the following:
An on-premises Oracle database
An Oracle database on an EC2 instance
An Amazon RDS for Oracle DB instance
Target technology stack
Aurora PostgreSQL-Compatible
Target architecture
Data migration architecture
From an Oracle database running in the AWS Cloud
From an Oracle database running in an on-premises data center
Tools
AWS DMS - AWS Data Migration Service (AWS DMS) helps you migrate your data to and from widely used commercial and open-source databases, including Oracle, MySQL, and PostgreSQL. The service supports homogeneous migrations such as Oracle to Oracle, and heterogeneous migrations such as Oracle to PostgreSQL or MySQL to Oracle.
AWS SCT - AWS Schema Conversion Tool (AWS SCT) makes heterogeneous database migrations easy by automatically converting the source database schema and a majority of the custom code to a format compatible with the target database.
Epics
| Task | Description | Skills required |
|---|---|---|
| Validate the source and target database version and engine. | DBA | |
| Create an outbound security group to the source and target databases. | SysAdmin | |
| Create and configure an EC2 instance for AWS SCT, if required. | DBA | |
| Download the latest version of AWS SCT and associated drivers. | DBA | |
| Add and validate the prerequisite users and grants in the source database. | DBA | |
| Create an AWS SCT project for the workload and connect to the source database. | DBA | |
| Generate an assessment report and evaluate feasibility. | DBA |
| Task | Description | Skills required |
|---|---|---|
| Create a target Amazon RDS DB instance, using Amazon Aurora as the database engine. | DBA | |
| Extract the list of users, roles, and grants from the source. | DBA | |
| Map the existing database users to the new database users. | App owner | |
| Create users in the target database. | DBA, App owner | |
| Apply roles from the previous step to the target database. | DBA | |
| Review the database for options, parameters, network files, and database links from the source and then evaluate applicability to the target. | DBA | |
| Apply any relevant settings to the target. | DBA |
| Task | Description | Skills required |
|---|---|---|
| Configure AWS SCT connectivity to the target database. | DBA | |
| Convert the schema using AWS SCT. | AWS SCT automatically converts the source database schema and most of the custom code to a format that is compatible with the target database. Any code that the tool cannot convert automatically is clearly marked so that you can convert it yourself. | DBA |
| Review the generated SQL report and save any errors and warnings. | DBA | |
| Apply automated schema changes to the target or save them as a .sql file. | DBA | |
| Validate that AWS SCT created the objects on the target. | DBA | |
| Manually rewrite, reject, or redesign any items that failed to convert automatically. | DBA, App owner | |
| Apply the generated role and user grants and review any exceptions. | DBA |
| Task | Description | Skills required |
|---|---|---|
| Determine the migration method. | DBA | |
| Create a replication instance from the AWS DMS console. | DBA | |
| Create the source and target endpoints. | DBA | |
| Create a replication task. | DBA | |
| Start the replication task and monitor the logs. | DBA |
| Task | Description | Skills required |
|---|---|---|
| Use AWS SCT to analyze and convert the SQL items within the application code. | When you convert your database schema from one engine to another, you also need to update the SQL code in your applications to interact with the new database engine instead of the old one. You can view, analyze, edit, and save the converted SQL code. | App owner |
| Create the new application servers on AWS. | App owner | |
| Migrate the application code to the new servers. | App owner | |
| Configure the application server for the target database and drivers. | App owner | |
| Fix any code specific to the source database engine in the application. | App owner | |
| Optimize the application code for the target engine. | App owner |
| Task | Description | Skills required |
|---|---|---|
| Perform the cutover. | DBA | |
| Lock the application from any further changes. | App owner | |
| Validate that all changes were propagated to the target database. | DBA | |
| Point the new application server to the target database. | App owner | |
| Perform a final comprehensive system check. | App owner | |
| Go live. | App owner |
| Task | Description | Skills required |
|---|---|---|
| Shut down the temporary AWS resources (AWS DMS replication instance and EC2 instance used for AWS SCT). | DBA, App owner | |
| Update feedback on the AWS DMS process for internal teams. | DBA, App owner | |
| Revise the AWS DMS process and improve the template if necessary. | DBA, App owner | |
| Review and validate the project documents. | DBA, App owner | |
| Gather metrics around time to migrate, percent of manual versus tool cost savings, and so on. | DBA, App owner | |
| Close the project and provide any feedback. | DBA, App owner |
Related resources
References
Tutorials and videos
