# Incrementally migrate from Amazon RDS for Oracle to Amazon RDS for PostgreSQL using Oracle SQL Developer and AWS SCT
<a name="incrementally-migrate-from-amazon-rds-for-oracle-to-amazon-rds-for-postgresql-using-oracle-sql-developer-and-aws-sct"></a>

*Pinesh Singal, Amazon Web Services*

## Summary
<a name="incrementally-migrate-from-amazon-rds-for-oracle-to-amazon-rds-for-postgresql-using-oracle-sql-developer-and-aws-sct-summary"></a>

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](https://www.oracle.com/database/technologies/appdev/sqldeveloper-landing.html) 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
<a name="incrementally-migrate-from-amazon-rds-for-oracle-to-amazon-rds-for-postgresql-using-oracle-sql-developer-and-aws-sct-prereqs"></a>

**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](https://docs.aws.amazon.com/SchemaConversionTool/latest/userguide/CHAP_Installing.html#CHAP_Installing.Procedure) and [Installing the required database drivers](https://docs.aws.amazon.com/SchemaConversionTool/latest/userguide/CHAP_Installing.html#CHAP_Installing.JDBCDrivers) in the AWS SCT documentation. 
+ Oracle SQL Developer, installed and configured. For more information about this, see the [Oracle SQL Developer](https://www.oracle.com/database/technologies/appdev/sqldeveloper-landing.html) 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](https://docs.oracle.com/database/121/AEUTL/sql_rep.htm#AEUTL191) 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
<a name="incrementally-migrate-from-amazon-rds-for-oracle-to-amazon-rds-for-postgresql-using-oracle-sql-developer-and-aws-sct-architecture"></a>

**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.

![\[Migration workflow from Amazon RDS for Oracle to Amazon RDS for PostgreSQL.\]](http://docs.aws.amazon.com/prescriptive-guidance/latest/patterns/images/pattern-img/c7eed517-e496-4e8e-a520-c1e43397419e/images/bfbbed5e-db13-4a22-99aa-1a17f00f5faf.png)


The diagram shows the following migration workflow:

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

1. Generate a [diff report ](https://docs.oracle.com/cd/E93130_01/rules_palette/Content/Diff%20Reports/Detailed_Diff_Reports.htm)and then generate the SQL scripts file for the schema difference objects. For more information about diff reports, see [Detailed diff reports](https://docs.oracle.com/cd/E93130_01/rules_palette/Content/Diff%20Reports/Detailed_Diff_Reports.htm) in the Oracle documentation.

1. Configure AWS SCT and run the Python code.

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

1. 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
<a name="incrementally-migrate-from-amazon-rds-for-oracle-to-amazon-rds-for-postgresql-using-oracle-sql-developer-and-aws-sct-tools"></a>
+ [AWS SCT](https://docs.aws.amazon.com/SchemaConversionTool/latest/userguide/CHAP_Welcome.html) – AWS Schema Conversion Tool (AWS SCT) converts your existing database schema from one database engine to another.
+ [Oracle SQL Developer](https://www.oracle.com/database/technologies/appdev/sqldeveloper-landing.html) – 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
<a name="incrementally-migrate-from-amazon-rds-for-oracle-to-amazon-rds-for-postgresql-using-oracle-sql-developer-and-aws-sct-epics"></a>

### Create the SQL scripts file for the source database schema differences
<a name="create-the-sql-scripts-file-for-the-source-database-schema-differences"></a>


| Task | Description | Skills required | 
| --- | --- | --- | 
| Run Database Diff in Oracle SQL Developer.  | [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/prescriptive-guidance/latest/patterns/incrementally-migrate-from-amazon-rds-for-oracle-to-amazon-rds-for-postgresql-using-oracle-sql-developer-and-aws-sct.html) | 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
<a name="use-the-python-script-to-create-the-target-db-objects-in-aws-sct"></a>


| Task | Description | Skills required | 
| --- | --- | --- | 
| Configure AWS SCT with the Windows Command Prompt.  | [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/prescriptive-guidance/latest/patterns/incrementally-migrate-from-amazon-rds-for-oracle-to-amazon-rds-for-postgresql-using-oracle-sql-developer-and-aws-sct.html)<pre>#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<br /><br />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</pre>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.  | [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/prescriptive-guidance/latest/patterns/incrementally-migrate-from-amazon-rds-for-oracle-to-amazon-rds-for-postgresql-using-oracle-sql-developer-and-aws-sct.html) | 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
<a name="incrementally-migrate-from-amazon-rds-for-oracle-to-amazon-rds-for-postgresql-using-oracle-sql-developer-and-aws-sct-resources"></a>
+ [Oracle on Amazon RDS](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_Oracle.html) 
+ [PostgreSQL on Amazon RDS](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_PostgreSQL.html)
+ [Using the AWS SCT user interface](https://docs.aws.amazon.com/SchemaConversionTool/latest/userguide/CHAP_UserInterface.html)
+ [Using Oracle as a source for AWS SCT](https://docs.aws.amazon.com/SchemaConversionTool/latest/userguide/CHAP_Source.Oracle.html)

## Attachments
<a name="attachments-c7eed517-e496-4e8e-a520-c1e43397419e"></a>

To access additional content that is associated with this document, unzip the following file: [attachment.zip](samples/p-attach/c7eed517-e496-4e8e-a520-c1e43397419e/attachments/attachment.zip)