In this post we will see how we can migrate an On-Premise Oracle Database to AWS RDS, before which we need to be well aware of the below constraints which you might face while migrating your databases

For more posts on AWS RDS please check the INDEX page or visit our YouTube channel

DMS supports the following Oracle database editions:

  • Oracle Enterprise Edition
  • Oracle Standard Edition
  • Oracle Express Edition
  • Oracle Personal Edition

For Source databases, AWS DMS supports all Oracle database editions for versions 10.2 and later (for versions 10.x), 11g and up to 12.2, 18c, and 19c. 

For Target databases hosted in AWS RDS, AWS DMS supports all Oracle database editions for versions 11g (versions 11.2.0.4 and later) and up to 12.2, 18c, and 19c.

AWS DMS supports basic schema migration, including the creation of tables and primary keys. AWS DMS doesn’t automatically create secondary indexes, foreign keys, user accounts, and so on in the target database.

Indexes, triggers, and referential integrity constraints can affect your migration performance and cause your migration to fail. 

For a full load task, it is recommended to drop your primary key indexes, secondary indexes, referential integrity constraints, and data manipulation language (DML) triggers. Alternatively, you can delay their creation until after the full load tasks are complete. Indexes arent required during a full load task, and indexes incur maintenance overhead if they are present. Because the full load task loads groups of tables at a time, referential integrity constraints are violated. Similarly, insert, update, and delete triggers can cause errors, for example, if a row insert is triggered for a previously bulk loaded table. Other types of triggers also affect performance due to added processing.

You can build primary key and secondary indexes before a full load task if your data volumes are relatively small and the additional migration time doesn’t concern you. Referential integrity constraints and triggers should always be disabled.

For more information on AWS DMS migration requirements, it is recommended to follow the AWS site.

 

Let us now come back to our migration, where we are migrating DMA user from Source to Target using INFRA as the DMS user.

DMS User : INFRA

Source User :  DMA –  This is the user to be migrated to AWS RDS 

Source Database : PRODDB  running in 11.2.0.1 version

Target Database :  INFRA running in 19c version

Make sure to open necessary connections with your ORACLE RDS port, so that there aren’t any connectivity errors

For inbound connections to your Oracle RDS, open the RDS port to the specified IP address from where incoming connections need to be received. In the below example, we have opened all ports, also the security group for our EC2 instance, since we are migrating the Database from EC2 instance to RDS. In real life, please DONOT open all ports, which would make your database vulnerable to the open world.

 

 

Proceed with the below steps for

Source Database :

Grant the below privileges to the Source user :

grant SELECT on SYS.V_$ARCHIVED_LOG to DMA;
grant SELECT on SYS.V_$NLS_PARAMETERS to DMA;
grant SELECT on SYS.V_$PARAMETER to DMA;
grant SELECT on SYS.V_$THREAD to DMA;
grant SELECT on SYS.V_$TIMEZONE_NAMES to DMA;
grant SELECT on SYS.V_$TRANSACTION to DMA;
grant SELECT on SYS.V_$LOG to DMA;
grant SELECT on SYS.V_$LOGFILE to DMA;
grant SELECT on SYS.V_$LOGMNR_CONTENTS to DMA;
grant SELECT on SYS.V_$LOGMNR_LOGS to DMA;
grant SELECT on SYS.DBA_REGISTRY to DMA;
grant SELECT on SYS.ALL_OBJECTS to DMA;
grant SELECT on SYS.V_$DATABASE to DMA;
grant SELECT on SYS.OBJ$ to DMA;
grant SELECT on SYS.ALL_TABLES to DMA;
grant SELECT on SYS.ALL_TAB_COLS to DMA;
grant SELECT on SYS.ALL_TAB_PARTITIONS to DMA;
grant SELECT on SYS.ALL_USERS to DMA;
grant SELECT on SYS.DBA_TABLESPACES to DMA;
grant SELECT on SYS.ALL_ENCRYPTED_COLUMNS to DMA;
grant SELECT on SYS.ALL_INDEXES to DMA;
grant SELECT on SYS.ALL_IND_COLUMNS to DMA;
grant SELECT on SYS.ALL_LOG_GROUPS to DMA;
grant SELECT on SYS.ALL_CATALOG to DMA;
grant SELECT on SYS.ALL_CONSTRAINTS to DMA;
grant SELECT on SYS.ALL_CONS_COLUMNS to DMA;

 

Target Database :

1. We need to create the schema which needs to be migrated from Source to Target. Here we will migrate DMA schema to Target

2. Give the below privileges to DMS user, which we are taking as INFRA in here :

 

GRANT SELECT ANY TRANSACTION to INFRA;
GRANT CREATE SESSION,CREATE ANY TABLE to INFRA;
GRANT CREATE ANY INDEX to INFRA;
GRANT DROP ANY TABLE to INFRA;
GRANT SELECT ANY TABLE to INFRA;
GRANT INSERT ANY TABLE to INFRA;
GRANT UPDATE ANY TABLE to INFRA;
GRANT CREATE ANY VIEW to INFRA;
GRANT DROP ANY VIEW to INFRA;
GRANT CREATE ANY PROCEDURE to INFRA;
GRANT ALTER ANY PROCEDURE to INFRA;
GRANT DROP ANY PROCEDURE to INFRA;
GRANT CREATE ANY SEQUENCE to INFRA;
GRANT ALTER ANY SEQUENCE to INFRA;
GRANT DROP ANY SEQUENCE to INFRA;
GRANT CREATE ANY TABLE TO INFRA;
GRANT SELECT on V$LOGMNR_LOGS TO INFRA;
GRANT CONNECT TO INFRA;

GRANT RESOURCE TO INFRA;

GRANT UNLIMITED TABLESPACE TO DMA;

 

Create a Cloudwatch Role

Create a cloudwatch role to ensure you can check the cloudwatch logs during your migration process.

Follow the below steps to create the role :

1. Go to IAM –> Create Role

2. Click on DMS as a service –> Click Permissions

 

3. In Permissions, select the AmazonDMSCloudWatchLogsRole and click next

4. Provide the necessary Tags and click next

5.  Provide the Role Name as suggested by AWS : dms-cloudwatch-logs-role and the Create Role

 

Create an AWS DMS Replication Instance

When you create an AWS DMS replication instance, AWS DMS creates it on an Amazon EC2 instance in a virtual private cloud (VPC) based on the Amazon VPC service. This replication instance performs your database migration. 

AWS DMS uses a replication instance to connect to your source data store, read the source data, and format the data for consumption by the target data store. It also loads the data into the target data store. Most of this processing happens in memory. However, large transactions might require some buffering on disk. Cached transactions and log files are also written to disk.

You can create an AWS DMS replication instance in the following AWS Regions till date

Region Name
Asia Pacific (Tokyo) Region ap-northeast-1
Asia Pacific (Seoul) Region ap-northeast-2
Asia Pacific (Mumbai) Region ap-south-1
Asia Pacific (Singapore) Region ap-southeast-1
Asia Pacific (Sydney) Region ap-southeast-2
Canada (Central) Region ca-central-1
China (Beijing) Region cn-north-1
China (Ningxia) Region cn-northwest-1
Europe (Stockholm) Region eu-north-1
EU (Frankfurt) Region eu-central-1
Europe (Ireland) Region eu-west-1
EU (London) Region eu-west-2
EU (Paris) Region eu-west-3
South America (São Paulo) Region sa-east-1
US East (N. Virginia) Region us-east-1
US East (Ohio) Region us-east-2
US West (N. California) Region us-west-1
US West (Oregon) Region us-west-2

To create a Replication instance, follow the below steps :

1. Go the DMS Dashboard

2. Click on Replication instances under Resource Management

3. Click on Create Replication Instance

4. Provide the Name, Description, Instance Class, Engine Version, VPC, Availability Zone  and then hit Create

5. Wait until the status of your Replication Instance changes to Available

Creating your Endpoints

An endpoint provides connection, data store type, and location information about your data store. AWS Database Migration Service uses this information to connect to a data store and migrate data from a source endpoint to a target endpoint.

Let us proceed with in creating the Source Endpoint first (although you can create your Target endpoint too at first)

Source Endpoint:

1. Click on Endpoint under your Resource Management, just after Replication Instance

2. Hit on Create Endpoint

3. Select Source Endpoint

4. In the Endpoint Configuration, type the desired name for your Endpoint IdentifierThe name must be unique for all replication instances.

5. Select the Source Engine. Since we are migrating from Oracle Database as Source, Click on Oracle

6. Type the Source Server Name, Port, Username (Here we are using DMA), SID/Service Name (PRODDB)

7. Select the desired Replication Instance from Test Endpoint Connection for your Source Endpoint and Run Test to verify whether the connection is established or not.

NOTE : Your endpoint will get created, no matter whether your Test is successful or not

8. Click on Create Endpoint after the Test is successful

NOTE : If your endpoint connection fails, you need to check whether the Database port is open or not. Also, make sure the required privileges have been granted to the source user

Target Endpoint :

1. Click on Endpoint under your Resource Management, just after Replication Instance

2. Hit on Create Endpoint

3. Select Target Endpoint

4. Since we are migrating to an AWS RDS instance, select the “Select RDS DB Instance” and then your desired RDS instance where you need to migrate your database from the drop down menu :

5. The Target Database details will be automatically populated once the RDS DB Instance is selected. Test the connections and create your Target Endpoint

 

Creating Database Migration Tasks :

In AWS Database Migration Service (AWS DMS) task you need to specify what tables (or views) and schemas to use for your migration and any special processing, such as logging requirements, control table data, and error handling.

To migrate multiple schemas, create multiple replication tasks.

1. Click on Database Migration Tasks located under Migration and Click on Create Task

2. Provide Task Identifier, choose Replication Instance, Source Endpoint and Target Endpoint from the Drop Down menu.

3. Choose Migrate Existing Data for Full Load Migration

This process creates files or tables in the target database and automatically defines the metadata that is required at the target. It also populates the tables with data from the source. The data from the tables is loaded in parallel for improved efficiency. 

4. Select the desired option for Target Table Preparation Mode as required. Here we have selected DO NOTHING.

Do nothing – Here AWS DMS assumes that the target tables have been pre-created on the target. If the migration is a full load or full load plus CDC, make sure that the target tables are empty before starting the migration. 

Drop tables on target – Here AWS DMS drops the target tables and recreates them before starting the migration to ensure that the target tables are empty when the migration starts. AWS DMS creates only the objects required to efficiently migrate the data: tables, primary keys, and in some cases, unique indexes. AWS DMS doesn’t create secondary indexes, nonprimary key constraints, or column data defaults. If you are performing a full load plus CDC or CDC-only task, it is recommended to create secondary indexes that support filtering for update and delete statements before starting the migration.

Truncate – Here AWS DMS truncates all target tables before the migration starts. Truncate mode is appropriate for full load or full load plus CDC migrations where the target schema has been precreated before the migration starts. To precreate tables, you can use AWS SCT. 

5. As per your requirement, select the the option in Include LOB Columns in Replication. Here we are using Limited LOB Mode

Don’t include LOB columns – LOB columns are excluded from the migration.

Full LOB mode – Migrate complete LOBs regardless of size.

Limited LOB mode – Truncate LOBs to the value of the Max LOB size parameter.

5. We are also Enabling Validation and Cloudwatch logs, to verify that the data is migrated accurately from the source to the target and monitor the logs through Cloudwatch. 

Keep detailed debugging on for Cloudwatch logs as shown below :

6. Click on Add new Selection Rule in Table Mappings

7. Click on Enter a Schema in the drop down menu of Schema and enter the Schema name which you need to migrate. If you need to migrate all schemas, enter % in the Schema Name tab

Since we are migrating only DMA user, we have given only the required schema name.

As we are migrating all the tables under DMA schema, we have given % in the Table Name tab

8. Click on Transformation Rules and Add new Transformation Rules

9. Choose Target –> Schema, Schema Name –> DMA (Your desired user to be migrated), Action –> Rename to –> DMA

8. Click on Create Task

9. Once the task has been created, you can see the status of the task and also from the table statistics, the current status of the tables :

10. The Status of your task would change to Load Complete once your migration is complete

Monitoring the task :

While the task runs, you can monitor the same from Cloudwatch logs, if you have enabled Cloudwatch logs:

This is how your Cloudwatch logs show :

Hope you liked my work. Please do share and like

For more posts on AWS RDS please check the INDEX page or visit our YouTube channel

For more videos and posts on AWS RDS, click on the below links :

Heterogeneous Database Migration using AWS SCT

Migration and Replication (Full Load + CDC) of On-Premise Oracle 11G database to AWS RDS Oracle 19c

RMAN Tasks in AWS RDS Oracle

Daily Tasks in AWS RDS Oracle

Steps to integrate S3 with Amazon Oracle RDS

Steps to upgrade AWS Oracle RDS