Introduction
With more and more IT infrastructures switching to the cloud and looking for massively scalable cloud adoption methods, migration from an Oracle database to AWS RDS for PostgreSQL is gaining popularity. Migration in AWS can be achieved in multifold ways ― using different tools and technologies. There are various reasons to migrate a database from Oracle to AWS PostgreSQL. Some of the most significant reasons are high Oracle licensing c ost, AWS PostgreSQL flexibility, and PostgreSQL customizability and scalability. The overall migration process from Oracle to AWS RDS for PostgreSQL is divided into automated and manual tasks. The automated tasks involve AWS Schema Conversion Tool and AWS Database Migration Service. Manual tasks are required for the objects that cannot be migrated automatically.
High-level architecture
One can understand the overview of the migration process by looking at the below diagram.
The high-level solution architect is flexible enough to cater to various Relational Database Management System’s (RDBMS) heterogeneous needs using various tools like AWS, SCT, and AWS DMS.
Tools for migration
- AWS DMS - AWS Database Migration Service (DMS) enables quick and secure database migration from customer datacenter to AWS. DMS helps in minimizing the application downtime during the migration process. It supports database migration from various source DB platforms to target databases.
- AWS SCT - There are various data migration object challenges during the migration process. However, AWS Schema Conversion Tool (SCT) helps to migrate at ease. It takes care of heterogeneous databases source schemas and converts them automatically to target databases compatible format.
- Amazon RDS - AWS RDS service is a quick, scalable, and secure database service. It is easy to set up and operate ― with high availability nd scalability. To start the migration process, the first step is to convert the source database (Oracle schema to PostgreSQL database schema). Schema migration tools that will help automate schema conversion are mentioned below:
- Ora2pg - Ora2pg tool helps ease the process of migration. After connect ingto an Oracle database, it extracts schemas, objects, and tables and generates the required SQL scripts. This SQL script can then be loaded into the target AWS PostgreSQL RDS.
- Ora_migrator - Oracle fdw (an extension) used to migrate Oracle DB to PostgreSQL.
- Orafce - This extension contains some useful functions that can be use d to port Oracle applications to PostgreSQL.
For demonstration, the AWS Schema Conversion Tool will help analyze the source database servers on Oracle.
Migration process
PostgreSQL, an open-source RDBMS, has become the most popular choice among several enterprise developers. Using AWS, one can deploy scalable PostgreSQL deployments in just a few minutes with a cost-efficient and scalable solution. The below process applies for migrating Oracle DB to AWS PostgreSQL RDS.
Schema creation in the target database
Amazon Schema Conversion Tool helps convert existing database schema from one database to another database engine. It provides the interface to convert the source Oracle schema into a format compatible with the target AWS PostgreSQL RDS. It also provides guidelines to follow while creating the corresponding schema in the target RDS database.
Upon successful installation, continue to create a new project in AWS Schema Conversion Tool by following the below steps:
- Select source as Oracle DB and target PostgreSQL
- Choose which schema needs migration by selecting the name
- Right-click the schema name and select “Convert Schema”
- Then select “Assessment Report View”
- The report shows a summary of the efforts required to convert the Oracle database to PostgreSQL.
SCT also tells why to fix these objects and how to do it.
Once the modifications are done, we can regenerate the schema re port and see if there are any other issues. Once schemas look good we can apply them to the target AWS PostgreSQL RDS.
The next step is to focus on the target database, column data types, object names, etc. Validate the respective schema. One challenge here is capitalization. Oracle uses uppercase for object names. Whereas, PostgreSQL uses lowercase.
Drop foreign key
The target should be free from any foreign key constraints and triggers. Identifying and disabling them is the next step. Disabling can be done by generating the table DDL. This is a manual task. Once the DDL table is generated, one can disable the foreign key components. Alternatively, you can delete them for a temporary period.
AWS DMS migration
Once the Schema migration is done from the source Oracle DB to target PostgreSQL RDS, we can start replicating the data. Apart from replicating the data from the source to the target, the AWS database migration service can also keep the data up-to-date with the CDC mode.
Below are some pre-prerequisites:-
- Oracle login with necessary permissions on the source database. Enabling supplemental logging to capture changes from the source Oracle database
- Archive logs must be enabled – Make sure that the source database is in ARCHIVELOG mode.
A replication instance must be created for DMS. AWS uses this replication instance to connect to the source Oracle DB server and target PostgreSQL.
Create two endpoints for the source and target database, respectively. This endpoint identifies the datastore type and location details about the source and target DB. It uses this information for creating the connection between the DBs and replication instances. The following endpoints need to be configured to proceed further:
Source endpoint – Oracle:
Go to DMS console and click on “Endpoints”
- Select endpoint type as – “Source Oracle”
- Endpoint identifier – Name of the endpoint identifier
- Source engine – Oracle
- Server name – On-premise Oracle Database Server (IP)
- Port – Enter the port number
- Enter the username, password, and database name
Click on “Run Test” to check if the DMS is communicating to the source Oracle DB server.
Target Endpoint – PostgreSQL
- Select endpoint type as – “Target_PostgreSQL”
- Endpoint identifier –Endpoint identifier name
- Source engine – Checkbox RDS and select the RDS instance
- Server name – Target PostgreSQL
- Port – Enter the port number
- Enter the username, password, and database name
Click on “Run Test” to check if DMS is communicating to the target RDS instance. Once endpoint creation is completed, we need to create the tasks as per the below instructions:
Follow the below-mentioned points as per the options available:
- Choose migration type: – (Option 2 of below diagram) “Migrate t he existing data and replicate ongoing changes”.
- Choose “Do Nothing” for target table preparation mode
- Include LOB columns must be selected in replication:- Limited LOB mode must be used (with default settings) for tables. This is applicable only for LOB columns that are 32 KB or less. Retain customized settings for Limited LOB mode. This is applicable only if the L OB columns exceed 32 KB.
- Stop task after the full load completes – Stop after applying cached changes.
- Enable logging.
In the next step, we can select and configure schemas, tables, a nd columns to include and ignore. We can also define a method like converting tables or schema names, converting capitalization (lower/upper), etc.
Upon successful creation of the task, no manual interference is required, as the task will commence automatically. DMS console will help monitor the progress of the specific task.
Next, stop the task, add all secondary objects, enable the fore ign key constraint, and start the task again to capture the on-going changes.
Cutover
During the “Cutover” phase when the application connections have stopped accessing the source Oracle DB and DMS has replicated the last data changes, we can stop the DMS task in the console and enable the triggers on the target DB.
Benefits
There are many benefits from both business and technical perspectives. Some of them are:
- PostgreSQL is an open-source database and has no licensing cost (community edition).
- There is no vendor lock-in risk in PostgreSQL DB.
- Fully Managed – Monitoring, security, alerting, backup, and disaster recovery.
- Easy availability from public cloud providers.
- Horizontal scaling and “Read Replicas” for performance.
- Scalable to meet the ever-growing demand.
Contact details:
Clouddatapractice@mindtree.com
This document is the exclusive property of Mindtree Limited (Mindtree). The recipient agrees that they will not copy, transmit, use or disclose the confidential and proprietary information in this document by any means without the expressed and written consent of Mindtree. By accepting a copy, the recipient agrees to adhere to these conditions to the confidentiality of Mindtree's practices and procedures; and to use these documents solely for responding to Mindtree's operations methodology.
