ORACLE AND AWS RDS FOR ORACLE TIP 4: DMS limitation and my experience (Pros X Cons) to migrate your Oracle database On-prem to AWS RDS

So this is the fourth and final article about how to migrate your Oracle database On-Prem to AWS. You can check the previous articles here :

1- ORACLE AND AWS RDS FOR ORACLE TIP 1: HOW TO CONFIGURE YOUR ORACLE ON-PREM TO BE MIGRATED TO AWS RDS ORACLE? A guide to use Amazon Data Migration Service (DMS).

2- ORACLE AND AWS RDS FOR ORACLE TIP 2: HOW TO CONFIGURE YOUR ORACLE AWS RDS TO RECEIVE DATA FROM AN ORACLE ON-PREM? A guide to use Amazon Data Migration Service (DMS).

3-ORACLE AND AWS RDS FOR ORACLE TIP 3: HOW TO CREATE A TASK AT DMS AND START YOUR MIGRATION

When I started the first article I wrote “For those who attended the Oracle Open World 2019 in San Francisco, California in the USA (if you didn’t check out my compilation video about the event : https://www.youtube.com/watch?v=8bOLbXOJHmAh ) and have had the experience to also attend the KeyNotes probably would prefer to migrate their Oracle database On-Prem to Oracle Cloud Ed2 instead of AWS Amazon EC2 or RDS.”. So I have already known how challenging it would be performing a migration from an Oracle’s Product to an Amazon’s service.

However, before starting to write about the limitation of the DMS tool and the Pros and Cons based on my experience performing many migrations to AWS, I would like to finish some topics regarding the last article where I have written how to start the task to perform the migration. One of the problems that you may have if the DMS tool doesn’t find the archive sequence in the archive area of the Source database, you will face an error similar this : “-49282892: 2019-11-17T13:27:49:574556 [SOURCE_CAPTURE ]D: Get archived REDO log with sequence 104086 for the first SCN in thread 1 (oracdc_reader.c:304) “. The troubleshooting for this case is the same for those who have already managed Oracle Data Guard and have worked in cases where the Target Standby is in GAP and looking for some sequence number in the Source : restore of the sequence number in the Source. So below is an example:

techdatabasket>rman

Recovery Manager: Release 12.1.0.2.0 - Production on Mon Dec 2 07:44:48 2019

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

RMAN> connect target /

connected to target database: TECHDATABASKET (DBID=73732538271)

RMAN> connect catalog  catalog <user>/<password>@<string>

RMAN> run
{
allocate channel ch1 type 'sbt_tape'   PARMS="SBT_LIBRARY=/opt/cvlt/simpana/Base64/libobk.so, BLKSIZE=1048576 ENV=(CV_mmsApiVsn=2,CV_channelPar=ch1,ThreadCommandLine= -cn pdmdbq05 -vm InstanceTechDATAbasket001)";
restore archivelog from logseq=106471 until logseq=107235;
release channel ch1;
};

Ok, now let’s talk about the DMS limitation which I will consider as Cons and as a suggestion to Amazon to improve it. SO let’s start with the 2 limitations that you can find in “Using an Oracle Database as a Target for AWS Database Migration Service”:

• “AWS DMS doesn’t support the Use direct path full load option for tables with INDEXTYPE CONTEXT. As a workaround, you can use array load. “
• “With the batch optimized apply option, loading into the net changes table uses a direct path, which doesn’t support XML type. As a workaround, you can use transactional apply mode. “: During my migration, this is the one that I have found.

Now, my observation during my migration:

• Unlike several migrations I had done previously, I found only a few posts and articles on how to migrate an Oracle On-Prem to AWS RDS and this is what made me create some articles on this subject I intend to help DBAs when they are asked to migrate their Oracle On-Prem to AWS.
• Very good support: Amazon has the option to either open a ticket at their support by message, chat or call and I did like the response time and the high quality of their support.
• According to the several migrations I made, it was the first time I had to have full knowledge of all database types since DMS as already shown above does not support XMLTYPE and some issues to migrate CLOB, BLOB tables when these tables don’t have a primary key. For instance, if you think in Oracle products as Data Pump or Data Guard I never had such a problem.
• Many manual procedures: Such procedures as enabling logging for each table and the database in general in the Source database and disabling constraints on the target database and only enabling when migration is complete takes time. Could exist any method that would automate this and so you wouldn’t have to create a script to do it manually.
• From the second article of this series “What you have to know is that Amazon recommends creating your tablespace using Big files, not Small files. Why? Because if you create your tablespace to use Small files you can’t resize this datafile instead you will have to create a new data file. For instance, if you have created a small file with 2MB you can’t resize this datafile afterwards. To increase the tablespace that contains the last datafile you must create a new Small file. However, if you create your tablespace as Bigfile tablespace, you create just 1 datafile and the AWS RDS Oracle will resize this data file automatically.”.
• Some simple DBA tasks like checking the archive area or alert log can be a nightmare if you don’t know Amazon’s commands for it. Check out my posts about the archive area and the alert log in AWS RDS.

ORACLE AND AWS RDS FOR ORACLE TIP: Where is my alert log at AWS RDS?

ORACLE AND AWS RDS FOR ORACLE TIP: Archive log retention and switch log file in AWS RDS Oracle

• Amazon’s RDS database has the SYSDBA user removed, and has replaced it with their RDSADMIN user: I have seen some products as Oracle Golden Gate having problems with the script sequence.sql due this modification.
• Oracle Commerce Platform: For those who use this platform, if you read the MOS note ID 2363750.1 , you will find this saying “ We highly recommend that AWS RDS not be used with Oracle Commerce”.
• Oracle RAC is not supported on RDS: (RAC: ACTIVE X ACTIVE, RDS: ACTIVE X PASSIVE).
• Multitenant is not supported in RDS just EC2.
• Data Guard is not supported in RDS just EC2.
• Oracle E-BUSINESS SUITE: Application who needs to access the file system. So it isn’t supported in RDS.
• Oracle Open World Keynote 2019: I was in San Francisco and I attended the Keynote this year and besides of my consideration above, Larry Elisson has done a lot more of comparision between running your Oracle Database at AWS or at Gen 2 Cloud Autonomous Infrastructure. Check out the video of the KeyNote: https://www.youtube.com/watch?v=lIgGrDQb2OQ
• From the first article of this series “Note: DMS will only push Table DDL and PK , all other additional objects may be created either before the CDC part , for instance indexes, or after the whole operation.”.

Let’s talk now what I think are Pros based on my migrations:

• Amazon RDS Multi-AZ Deployments: According to Amazon’s “Amazon RDS Multi-AZ deployments provide enhanced availability and durability for Database (DB) Instances, making them a natural fit for production database workloads. When you provision a Multi-AZ DB Instance, Amazon RDS automatically creates a primary DB Instance and synchronously replicates the data to a standby instance in a different Availability Zone (AZ). “.
• RDS is managed by AWS and it performs a lot of tasks that a DBA would perform in the daily-basis as Backup/Recovery, Multi-AZ in case of failures, Upgrades, Patching, both alerts and monitoring provided by CloudWatch and also maintenance in the hosts.
• You can use AWS Schema Conversion Tool: It is a tools that beyond helps the DBA to convert your existing database schema from one database engine to another can also be used to see how the data will be create in the target database.
• IO Benchmarking: Amazon helps you to choose the ideal class and engine version for your AWS RDS Oracle using CloudWatch or commands as iostat.
• Support to Open Source database as PostgreSQL and other databases as MySQL.
• Will Amazon RDS for Oracle be supported? This was one of my biggest concern and yes, we do have support as you check as below from Amazon:https://aws.amazon.com/oracle/faq/

BYOL: Under this model, you will continue to use your active Oracle support account and contact Oracle directly for Oracle Database specific service requests. If you have an active AWS Premium Support account, you can contact AWS Premium Support for Amazon RDS specific issues. Amazon Web Services and Oracle have multi-vendor support process for cases which require assistance from both organizations.

License Included: In this model, if you have an active AWS Premium Support account, you should contact AWS Premium Support for both Amazon RDS and Oracle Database specific service requests.

So my conclusion is that you can have your Oracle Database running as a Paas/DBaaS at AWS Console but I would suggest you also take a look in the Cros and Pros that I have written above plus perform your tests. Also as I consider moving an Oracle On-prem to AWS RDS Oracle a change of layer I do prefer Oracle running on OCI 2 due the advantages to be the same provider although I do also support Open Source running in AWS to have a lot of advantages as well. Nevertheless, just a reminder that all consideration above was made according to my experience and my opinion and not reflects the idea of my employer.

References:
Using an Oracle Database as a Target for AWS Database Migration Service (https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Target.Oracle.html)
Amazon RDS Multi-AZ Deployments (https://aws.amazon.com/rds/details/multi-az/)
Best Practices for AWS Database Migration Service (https://docs.aws.amazon.com/dms/latest/userguide/CHAP_BestPractices.html)
Data Types for AWS Database Migration Service (https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Reference.DataTypes.html)
Oracle FAQ (https://aws.amazon.com/oracle/faq/)

brunors

Hi! I am Bruno, a Brazilian born and bred. Former Oracle ACE, Computer Scientist, MSc in Data Science, over ten years of experience in companies such as IBM, Epico Tech, and Playtech based in three different countries (Brazil, Hungary, and Sweden) and joined projects remotely in many others. I am super excited to show you my interest in Databases, Cloud, Data Science, Data Engineering, Bigdata, AI, Programming, Software Engineering, and data in general.
(Continue reading)