Migration Patterns for On-Prem Oracle DB to Amazon RDS for Oracle (R Lane – Replatform)

Database Migration Pattern Determination (Strategies)

The size of the database

Analyzed the database model

If the business rules are embedded in the application or the business rules are part of the database in the form of stored procedure, packages and are invoked from the application

Large Object columns (LOB) in the database and to identify the dependency of database objects on the table with the LOB columns

The version and edition of the Oracle Database software

The database options, tools, and utilities that are available like RAC, ASM

Network connectivity and bandwidth throughput between the source database on-prem environment and AWS RDS Region

Network latency from the source database on-prem environment to AWS RDS Region

The amount of time that is available for migration

The application downtime tolerance

High Transaction and high change rate of the database

Whether the migration and switchover to AWS will be done in one step or a sequence of steps over time

Why Amazon RDS?

Easily deploy and maintain hardware, OS, Database software, build in monitoring capability, fully managed service

Data encryption at rest and in transit, industry compliance

Automatic Multi-AZ data replication, automated backups, snapshots, failover

Scale compute and storage with a few clicks, minimal downtime for business-critical applications

Pay low rates and only for the resources consume no long-term commitments

Database Migration Patterns Planning

Oracle Native Tool Data pump + AWS Data Migration Service (DMS) with Change Data Capture (CDC)

Cross version Migration – Migrate from a lower version to a higher version like 10g R2 to 12c? / 11c R2 at the time of migration

Cross platform migration – Migrate Oracle database running on AIX platform or Solaris platform on-prem to Linux platform, it is a great use case for doing cross Replatform of Oracle database

AWS Data Migration Service (DMS) Full Load + Change Data Capture (CDC

Most cost effective. Minimal downtime. Easy to configure and simple to use in Full load mode + CDC

AWS Data Migration Service with Snowball Edge + AWS Data Migration Service (DMS) with Change Data Capture (CDC)

If the size of the source database is > 20 TB, Use AWS Snowball edge to ship the dump file from on-prem location to nearest AWS data center, loaded in a S3 bucket and import the data onto target database on Amazon RDS.

Migration of large Oracle databases from on-prem to Amazon RDS over the network is a not an optimal option, so AWS Snowball Edge and combination of AWS DMS is a very useful way to migrate large data sets onto cloud.

Migration Pattern : AWS DMS with Oracle Data pump and Change Data Capture (CDC)

How does it happen?

This pattern describes how to migrate an Oracle database running on-prem to Amazon RDS for Oracle by using Oracle Data Pump & AWS DMS with CDC

The source technology stack is Oracle databases on-prem and target technology stack is Amazon RDS for Oracle (R Type – Replatform)

For self-managed Oracle databases on-prem, AWS DMS supports Oracle database editions for versions 10.2 & later (for versions 10.x), 11g and up to 12.2, 18c, and 19c

For Amazon RDS for Oracle databases that AWS manages, AWS DMS supports all Oracle database editions for versions 11g (versions 11.2.0.4 & later) & up to 12.2, 18c, and 19c

Limitations - Database size limit is 64 TB

Provision an AWS DMS replication instance with the source and target database end points configured

Export the source database schemas using Oracle Data pump with a flashback SCN for point-in-time export from the on-prem Oracle database

Transfer the dump file to the target instance on AWS cloud using database links, direct connect etc. on a S3 bucket

Import from the schemas using Oracle Data Pump into Amazon RDS for Oracle database

Create an AWS DMS replication tasks using CDC to perform live replication and replicate the changes only using the flashback SCN

Validate the data base schemas on the target Amazon RDS for Oracle DB

Migration Pattern : AWS Data Migration Service Full Load + CDC

How does it happen?

This pattern describes how to migrate an Oracle database running on-prem to Amazon RDS for Oracle by using AWS DMS with CDC

The source technology stack is Oracle databases on-prem and target technology stack is Amazon RDS for Oracle (R Type – Replatform)

For self-managed Oracle databases on-prem, AWS DMS supports Oracle database editions for versions 10.2 & later (for versions 10.x), 11g and up to 12.2, 18c, and 19c

For Amazon RDS for Oracle databases that AWS manages, AWS DMS supports all Oracle database editions for versions 11g (versions 11.2.0.4 & later) & up to 12.2, 18c, and 19c

Limitations - Database size limit is 64 TB

Provision an AWS DMS replication instance with the source and target database end points configured

Create an AWS DMS replication tasks using Full Load + CDC to perform live replication and replicate the changes

The task migrates existing data and then updates the target database based on changes to the source database

During a full load migration, where existing data from the source is moved to the target, Amazon DMS loads data from tables on the source data store to tables on the target data store

When the full load for a given table is complete, Amazon?DMS immediately begins to apply the cached changes for that table from the online redo log files and archive redo log files

Validate the data base schemas on the target Amazon RDS for Oracle DB

Migration Pattern : AWS Data Migration Service with Snowball Edge

How does it happen?

This pattern describes how to migrate an Oracle database running on-prem to Amazon RDS for Oracle by using AWS DMS with Snowball Edge

The source technology stack is Oracle databases on-prem and target technology stack is Amazon RDS for Oracle (R Type – Replatform)

For self-managed Oracle databases on-prem, AWS DMS supports Oracle database editions for versions 10.2 & later (for versions 10.x), 11g and up to 12.2, 18c, and 19c

For Amazon RDS for Oracle databases that AWS manages, AWS DMS supports all Oracle database editions for versions 11g (versions 11.2.0.4 & later) & up to 12.2, 18c, and 19c

Limitations - Database size limit is 64 TB

When the database is of 20 to 30 TBs and don’t want to move the data over network by using native tools and DMS, in those cases DMS integrate with AWS service? Snowball Edge

The Snowball device will be plugged locally and the database dump file from the Oracle on-prem database will be move to the Snowball Edge

Once the data is loaded, the Snowball Edge will be shipped to the nearest AWS data center and uploaded into an Amazon S3 bucket and loaded into the target Amazon RDS

Create 2 DMS tasks, one will be the local task at the data center which will keep tracking of all the changes happening, since started loading the data onto the Snowball Edge

There will be a remote DMS task that will read the S3 bucket and apply all the changes from the on-prem Oracle DB to target Amazon RDS DB

The local DMS task will take all the changes from the on-prem database to a S3 bucket and then the remote DMS task will read the same S3 bucket and getting all the changes replicated to the target Amazon RDS

Tools used for Oracle Database Migration Patterns

Oracle Data Pump - Oracle Data Pump enables very high-speed movement of data from one database to another database/external files. It is available from 10g release (version 10.1) and later. It exports in block mode and run parallel execution

AWS Data Migration Services - AWS Database Migration Service (AWS DMS) is a web service that?can be used to migrate data from a source data store to a target data store. Migrate between source and target endpoints that use the same database engine or different database engines

CDC (Change Data Capture) - CDC enables continuous data replication from on-prem source database to target database on AWS cloud keeping the target database always in-sync

Replication Instance - Dedicated EC2 instance. It supports T2, T3, C4, C5, R4, and R5 Amazon EC2 instance compute instance type

If doing a heterogenous workload database migration which involves data type conversion of columns, should ideally use C (Compute intensive) instance type since C family instance type is compute optimized for better performance

If doing a homogeneous database workload migration with large dataset and large change rate, will need more memory intensive instance type and can typically use R (Memory intensive) instance type

Best Practices for Oracle Database Migration Patterns using Data Migration Services

Enable CloudWatch logs so that can have more information for troubleshoot and make sure can see what is going on the database

Considering LOB mode carefully – Need to choose the LOB mode very carefully. There are 4 LOB modes from where one can choose from like –

Full LOB mode – Whatever is the size of the LOB columns, all LOB modes columns data will be migrated in a piece mill method, i.e., row by row but migration will be slow

Limited LOB Mode – Can find out the max size of the LOB column in the source Oracle database and specify the same. In that case, DMS task will use batching to migrate the LOB mode but if there are any LOB column greater than the size specified, data will be truncated. In this case, migration will be faster

In Line LOB mode – Can specify, say 99% of the LOB columns to have a specific size and 1% will vary. In this case, DMS task will migrate in a balanced mode i.e., 99% in batching mode and 1% in piece mill method

Don’t include LOB column – DMS task will not migrate LOB columns while migrating from source Oracle database to target database on cloud

Use Large DB instance for maximum throughput - Dedicated EC2 instance. AWS DMS currently supports the T2, T3, C4, C5, R4, and R5 Amazon EC2 instance classes for replication instances:

If doing a heterogenous workload database migration which involves data type conversion of columns, should ideally use C4 & C5 type instance type since this instance type is compute optimized for better performance

If doing a homogeneous database workload migration with large dataset and large change rate, will need more memory intensive instance type and can typically use R4 & R5 instance type

Don’t use Multi-AZ for initial load

Target Preparation – There are 3 options –

Do Nothing : AWS DMS task assumes target tables are pre created. In full load mode/ full load + CDC, ensure that target database is empty

Drop table on target : AWS DMS drops the target tables and re-creates them before migration

Choose AWS DMS mode carefully can be of 2 modes –

Log Miner : In this mode, DMS task uses native Lg Miner API to read the online redo logs and archive redo log files

Binary Reader : In this mode, DMS uses native method that copies the redo log files and archive redo log files into the DMS replication EC2 instances and process them accordingly. Binary mode reduces load from the source database.

If the source on-prem Oracle database workload update statement that updates the LOB columns, binary mode should be used since these changes are not captured in the Log Miner