Incrementally migrate from Amazon RDS for Oracle to Amazon RDS for PostgreSQL using Oracle SQL Developer and AWS SCT

Created by Pinesh Singal (AWS)

Environment: PoC or pilot	Source: Amazon RDS for Oracle	Target: Amazon RDS for PostgreSQL
R Type: Re-architect	Workload: Oracle	Technologies: Migration; Databases; Modernization
AWS services: Amazon EC2; Amazon RDS

Summary

Many migration strategies and approaches run in multiple phases that can last from a few weeks to several months. During this time, you can experience delays because of patching or upgrades in the source Oracle DB instances that you want to migrate to PostgreSQL DB instances. To avoid this situation, we recommend that you incrementally migrate the remaining Oracle database code to PostgreSQL database code.

This pattern provides an incremental migration strategy with no downtime for a multi-terabyte Oracle DB instance that has a high number of transactions performed after your initial migration and that must be migrated to a PostgreSQL database. You can use this pattern’s step-by-step approach to incrementally migrate an Amazon Relational Database Service (Amazon RDS) for Oracle DB instance to an Amazon RDS for PostgreSQL DB instance without signing in to the Amazon Web Services (AWS) Management Console.

The pattern uses Oracle SQL Developer to find the differences between two schemas in the source Oracle database. You then use AWS Schema Conversion Tool (AWS SCT) to convert the Amazon RDS for Oracle database schema objects to Amazon RDS for PostgreSQL database schema objects. You can then run a Python script in the Windows Command Prompt to create AWS SCT objects for the incremental changes to the source database objects.

Note: Before you migrate your production workloads, we recommend that you run a proof of concept (PoC) for this pattern's approach in a testing or non-production environment.

Prerequisites and limitations

Prerequisites 

• An active AWS account.

• An existing Amazon RDS for Oracle DB instance. 

• An existing Amazon RDS for PostgreSQL DB instance.

• AWS SCT, installed and configured with JDBC drivers for Oracle and PostgreSQL database engines. For more information about this, see Installing AWS SCT and Installing the required database drivers in the AWS SCT documentation. 

• Oracle SQL Developer, installed and configured. For more information about this, see the Oracle SQL Developer documentation. 

• The incremental-migration-sct-sql.zip file (attached), downloaded to your local computer.

Limitations 

• The minimum requirements for your source Amazon RDS for Oracle DB instance are:

  • Oracle versions 10.2 and later (for versions 10.x), 11g (versions 11.2.0.3.v1 and later) and up to 12.2, and 18c for the Enterprise, Standard, Standard One, and Standard Two editions

• The minimum requirements for your target Amazon RDS for PostgreSQL DB instance are:  

  • PostgreSQL versions 9.4 and later (for versions 9.x), 10.x, and 11.x

• This pattern uses Oracle SQL Developer. Your results might vary if you use other tools to find and export schema differences.

• The SQL scripts generated by Oracle SQL Developer can raise transformation errors, which means that you need to perform a manual migration.

• If the AWS SCT source and target test connections fail, make sure that you configure the JDBC driver versions and inbound rules for the virtual private cloud (VPC) security group to accept incoming traffic.

Product versions

• Amazon RDS for Oracle DB instance version 12.1.0.2 (version 10.2 and later)

• Amazon RDS for PostgreSQL DB instance version 11.5 (version 9.4 and later)

• Oracle SQL Developer version 19.1 and later

• AWS SCT version 1.0.632 and later

Architecture

Source technology stack  

• Amazon RDS for Oracle DB instance

Target technology stack  

• Amazon RDS for PostgreSQL DB instance

Source and target architecture

The following diagram shows the migration of an Amazon RDS for Oracle DB instance to an Amazon RDS for PostgreSQL DB instance.

The diagram shows the following migration workflow:

1. Open Oracle SQL Developer and connect to the source and target databases.

2. Generate a diff report and then generate the SQL scripts file for the schema difference objects. For more information about diff reports, see Detailed diff reports in the Oracle documentation.

3. Configure AWS SCT and run the Python code.

4. The SQL scripts file converts from Oracle to PostgreSQL.

5. Run the SQL scripts file on the target PostgreSQL DB instance. 

Automation and scale

You can automate this migration by adding additional parameters and security-related changes for multiple functionalities in a single program to your Python script.

Tools

• AWS SCT – AWS Schema Conversion Tool (AWS SCT) converts your existing database schema from one database engine to another.

• Oracle SQL Developer – Oracle SQL Developer is an integrated development environment (IDE) that simplifies the development and management of Oracle databases in both traditional and cloud-based deployments.

Code 

The incremental-migration-sct-sql.zip file (attached) contains the complete source code for this pattern.

Epics

Create the SQL scripts file for the source database schema differences

Task	Description	Skills required
Run Database Diff in Oracle SQL Developer.	Sign in to your source Oracle DB instance, choose Tools, and then choose Database Diff. Choose your source database in Source Connection. Choose the updated or patched source database in Destination Connection. Configure the remaining options according to your requirements, choose Next, and then choose Finish to generate the diff report.	DBA
Generate the SQL scripts file.	Choose Generate Script to generate the differences in the SQL files.  This generates the SQL scripts file that AWS SCT uses to convert your database from Oracle to PostgreSQL.	DBA

Use the Python script to create the target DB objects in AWS SCT

Task	Description	Skills required
Configure AWS SCT with the Windows Command Prompt.	Copy the AWSSchemaConversionToolBatch.jar file from your pre-installed AWS SCT folder and paste it into your working directory. Deploy the Python code from the run_aws_sct_sql.py file from the incremental-migration-sct-sql.zip folder (attached). This creates .xml files and .sct files in the projects directory with your source and target database environment configuration details. It also reads the SQL scripts file that you generated in Oracle SQL Developer. Finally, it creates .sql file objects in the output directory. Configure the source and target environment configuration details in the database_migration.txt file by using the following format: #source_vendor,source_hostname,source_dbname,source_user,source_pwd,source_schema,source_port,source_sid,target_vendor,target_hostname,target_user,target_pwd,target_dbname,target_port ORACLE,myoracledb.cokmvis0v46q.us-east-1.rds.amazonaws.com,ORCL,orcl,orcl1234,orcl,1521,ORCL,POSTGRESQL,mypgdbinstance.cokmvis0v46q.us-east-1.rds.amazonaws.com,pguser,pgpassword,pgdb,5432 4. Modify the AWS SCT configuration parameters according to your requirements and then copy the SQL scripts file into your working directory in the input subdirectory.	DBA
Run the Python script.	Run the Python script by using the following command: $ python run_aws_sct_sql.py database_migration.txt This creates the DB objects SQL file. Non-converted codes with transformation errors can be manually converted.	DBA
Create the objects in Amazon RDS for PostgreSQL	Run the SQL files and create objects in your Amazon RDS for PostgreSQL DB instance.	DBA

Related resources

• Oracle on Amazon RDS 

• PostgreSQL on Amazon RDS

• Using the AWS SCT user interface

• Using Oracle as a source for AWS SCT

Attachments

To access additional content that is associated with this document, unzip the following file: attachment.zip