Case Study Hero Background

Case Study

Oracle12c to AWS Aurora PostgreSQL 11 Database Migration for Young Living

Customer: Young Living

Industry: Retail

Business Pain:

In an effort to reduce their corporate dependencies on Oracle databases and further adopt Cloud Platforms for greater manageability, flexibility, and performance, Young Living needed to re-platform multiple Oracle12c databases to AWS Aurora PostgreSQL within a six-hour downtime maintenance period. Fourteen schemas with a total of 4TB of data, of which 2TB is used by the large database/schema, were part of the migration project. Young Living engaged TekStream for the following tasks:

  • Execute AWS Schema Conversion Tool (SCT) on database schemas, update schema objects and application code, and deploy to Aurora PostgreSQL.
  • Create Database Migration Service (DMS) tasks to migrate data from Oracle to Aurora PostgreSQL within a six-hour or less downtime window.

How we fixed it:

Data type changes were implemented by TekStream to optimize application and database performance. Legacy applications using Oracle data types RAW and CLOB/NBLOB were migrated/converted to PostgreSQL data types UUID and JSONB. UUID generates a globally unique ID and JSONB stores JSON data in a compressed binary format for efficient storage and retrieval. The Aurora PostgreSQL data types provide optimal storage and performance.

The Schema Conversion Tool (SCT) was run against all 14 database schemas to analyze and perform required data type changes from Oracle (RAW, CLOB/NCLOB) to PostgreSQL (BYTEA, JSONB). A mapping rule was implemented to change CLOB/NBLOB data types to JSONB and reflected in the DDL script created by SCT. Young Living was also interested in converting to PostgreSQL UUID data type, however, AWS DMS does not support the UUID data type. This conversion complexity can be solved by first migrating Oracle RAW data types to the PostgreSQL BYTEA data type, followed by steps/scripts to convert from data type BYTEA to UUID. This issue/effort was outside the scope of the project.

AWS Database Migration Service (DMS) and DMS tasks were created to perform the data migration operation. The large Oracle Source database and the Oracle Standby database were used as the source and all other source Oracle databases were on-premise Oracle Primary databases. The process followed this approach for source Oracle Standby database in AWS:

  • Stop the Oracle12c Standby database MRP process to stop the application of data changes to the database.
  • Capture the current System Change Number (SCN) of the Oracle12c Standby database
  • SCN is utilized as the starting point for Change Data Capture task to read and apply data changes from the SCN and forward.
  • Start Full Data Load and CDC DMS Tasks
  • Monitor the CDC Source and CDC Latency to determine the data/time gap between the Source and Target databases.

The designed approach incorporates AWS Database Migration Service (DMS) using Change Data Capture for the large source Oracle schema. The AWS DMS solution was able to migrate and replicate data from both an Oracle12c Primary DB and an Oracle12c Standby Database to Aurora PostgreSQL to provide for a minimal downtime duration for application workload cutover from Oracle to Aurora PostgreSQL.

For two large schemas, the Oracle12c Standby database was used as the source to eliminate data replication workload impact on the Production database and eliminate the risk of encountering fatal ORA-1555 Snapshot too old errors during initial data load tasks.

TekStream implemented Change Data Capture to provide for a near-zero data sync lag between the Source Oracle database and Target Aurora PostgreSQL database, reducing the database cut-over downtime and shifting more focus on application cut-over tasks. We met the production downtime requirement: 6 hours or less. This migration also provided a migration approach and template that will be utilized for further Oracle database migrations to the AWS Cloud and adoption of Aurora PostgreSQL databases. Adopting a Cloud Platform such as AWS provides greater flexibility, management controls, and expansion for Young Living beyond what their existing on-premise datacenter was able to provide and the movement to Aurora PostgreSQL provides them with a powerful alternative database allowing them to continue moving workloads away from their legacy Oracle implementations.