AWS Prescriptive Guidance
Patterns

Migrate data from an on-premises Oracle database to Aurora PostgreSQL

R Type :ReArchitect

source :Databases: Relational

target :Amazon Aurora PostgreSQL

tags :oracle, database, amazon aurora postgresql, data guard

categories :Software Infrastructure

Summary

This pattern provides guidance for data migration from an on-premises Oracle database to Amazon Aurora PostgreSQL. It targets an online data migration strategy with a minimal amount of downtime for multi-terabyte Oracle databases that contain large tables with high Data Manipulation Language (DML) activities. An Oracle Active Data Guard standby database is used as the source to offload data migration from the primary database. The replication from the Oracle primary database to standby can be suspended during the full load to avoid ORA-01555 errors. 

Table columns in primary keys (PKs) or foreign keys (FKs), with data type NUMBER, are commonly used to store integers in Oracle. We recommend that you convert these to INT or BIGINT in PostgreSQL for better performance. You can use the open-source tool Ora2Pg to handle this type of data conversion during full data load. The data migration in this pattern uses Ora2Pg for full load and AWS Database Migration Service (AWS DMS) for Change Data Capture (CDC).

Assumptions and Prerequisites

Prerequisites

Li

mitations

  • Database size limit: 64 TB

Architecture

Source technology stack

  • On-premises Oracle databases (versions 11g and 12c, Enterprise Edition) with Oracle Active Data Guard standby configured (for a complete, up-to-date list of sources, see Sources for Data Migration in the AWS DMS documentation)

Target technology stack

  • Aurora PostgreSQL

Source and target architecture

Tools Used

AWS DMS - AWS Database Migration Service (AWS DMS) supports several source and target databases. See Using an Oracle Database as a Source for AWS DMS in the AWS DMS documentation for a list of supported Oracle source and target database versions and editions. If the source database is not supported by AWS DMS, you must select another method for migrating the data in Phase 6 (in the Epics section). 

Important note:  Because this is a heterogeneous migration, you must first check to see whether the database supports a commercial off-the-shelf (COTS) application. If the application is COTS, consult the vendor to confirm that Aurora PostgreSQL is supported before proceeding. For more information, see AWS DMS Step-by-Step Migration Walkthroughs in the AWS documentation.

AWS SCT - The AWS Schema Conversion Tool (AWS SCT) facilitates heterogeneous database migrations by automatically converting the source database schema and a majority of the custom code to a format that's compatible with the target database. The custom code that the tool converts includes views, stored procedures, and functions. Any code that the tool cannot convert automatically is clearly marked so that you can convert it yourself. 

Ora2Pg -Ora2Pg is a free tool that you can use to migrate an Oracle database to PostgreSQL using an Aurora-compatible schema. It can be used for large enterprise database migrations. Ora2Pg consists of a Perl script (ora2pg) and a Perl module (Ora2Pg.pm). Ora2Pg needs an Oracle instant client, a PostgreSQL on Aurora client, and the Perl modules DBD:: Oracle and DBD:: Pg for connectivity to Oracle and Aurora PostgreSQL. You can configure the source by setting the Data Source Name (DSN) to the Oracle database in the Ora2Pg configuration file. You can import data directly into an Aurora PostgreSQL database by setting its DSN in the configuration file. You can manage parallel jobs by using the configuration options.

Epics

Plan the migration

Tasks

Title Description Skills Predecessor
Validate the source and target database versions. DBA
Install AWS SCT and drivers. DBA
Add and validate the AWS SCT prerequisite users and grants-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, AppOwner

Prepare the target database

Tasks

Title Description Skills Predecessor
Create an Aurora PostgreSQL target database. DBA
Extract users, roles, and grants list from the source database. DBA
Map the existing database users to the new database users. AppOwner
Create users in the target database. DBA
Apply roles from the previous step to the target Aurora PostgreSQL database. DBA
Review database options, parameters, network files, and database links from the source database, and evaluate their applicability to the target database. DBA, AppOwner
Apply any relevant settings to the target database. DBA

Prepare for database object code conversion

Tasks

Title Description Skills Predecessor
Configure AWS SCT connectivity to the target database. DBA
Convert the schema in AWS SCT, and save the automated converted code as a .sql file. DBA, AppOwner
Manually convert any database objects that failed to convert automatically. DBA, AppOwner
Optimize the database code conversion. DBA, AppOwner
Separate the .sql file into multiple .sql files based on the object type. DBA, AppOwner
Validate the SQL scripts in the target database. DBA, AppOwner

Install Ora2Pg

Tasks

Title Description Skills Predecessor
Launch an Amazon Elastic Compute Cloud (Amazon EC2) instance using the Amazon Machine Image (AMI) for Linux. SysAdmin
Install Ora2Pg and the required Oracle database to the PostgreSQL database on Aurora, and install the Perl module on an EC2 instance. DBA
Set options in the Ora2Pg configuration file, and perform connection and data load tests. DBA

Migrate data – full load (Ora2Pg)

Tasks

Title Description Skills Predecessor
Create the schema and tables in the target database. DBA
Create the Ora2Pg configuration files for jobs by either grouping tables or splitting a big table based on the table size. DBA, AppOwner
Stop the applications on the source Oracle databases for a short period. AppOwner
Verify that the Oracle standby database is synchronous with the primary database, and stop the replication from the primary database to the standby database. DBA
Start applications on the source Oracle database. AppOwner
Start the Ora2Pg data full load jobs in parallel from the Oracle standby database to the Aurora PostgreSQL database. DBA
Create PKs and secondary indexes after the full load is complete. DBA
Validate the data. DBA

Migrate data – CDC (AWS DMS)

Tasks

Title Description Skills Predecessor
Create an AWS DMS replication instance. DBA
Create the source and target endpoints. DBA
Create AWS DMS ongoing replication tasks by using the time or system change number (SCN) when the Oracle standby was synchronized with the primary database, and before the applications were restarted in the previous task, at the custom start time per SCN. DBA
Start AWS DMS tasks in parallel to replicate ongoing changes from the Oracle standby database to the Aurora PostgreSQL database. DBA
Re-establish the replication from the Oracle primary database to the standby database. DBA
Monitor the logs and stop the applications on the Oracle database when the target Aurora PostgreSQL database is almost synchronous with the source Oracle database. DBA, AppOwner
Stop the AWS DMS tasks when the target is fully synchronized with the source Oracle database. DBA
Create FKs and validate the data in the target database. DBA
Create functions, views, triggers, sequences, and other object types in the target database. DBA
Apply role grants in the target database. DBA

Migrate the application

Tasks

Title Description Skills Predecessor
Use AWS SCT to analyze and convert the SQL statements inside the application code. AppOwner
Create new application servers on AWS. AppOwner
Migrate the application code to the new servers. AppOwner
Configure the application server for the target database and drivers. AppOwner
Fix any code that's specific to the source database engine in the application. AppOwner
Optimize the application code for the target database. AppOwner

Cut over

Tasks

Title Description Skills Predecessor
Point the new application server to the target database. DBA & AppOwner
Perform sanity checks. DBA & AppOwner
Go live. DBA & AppOwner

Close the project

Tasks

Title Description Skills Predecessor
Shut down temporary AWS resources. DBA, SysAdmin
Review and validate the project documents. DBA, AppOwner
Gather metrics around time to migrate, % of manual vs. tool, cost savings, etc. DBA, AppOwner
Close out the project and provide feedback. DBA, AppOwner

References and Help

References

Tutorials and videos

Contact and help

Migration Pattern Library Support: aws-mpl@amazon.com