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

In this post we will see how we can migrate and replicate ongoing changes from 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

With this option we can minimize the downtime of your migration.

DMS supports the following Oracle database editions:

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; grant EXECUTE on DBMS_LOGMNR 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

 

Supplemental Logging :

AWS DMS requires supplemental logging be enabled to enable Change Data Capture (CDC) to replicate ongoing changes.

To set up supplemental logging, take the following steps:

1. Verify that supplemental logging is enabled for the database.

Run the sample query following to verify that the current version of the Oracle database is supported by AWS DMS. If the query runs without error, the returned database version is supported.

SELECT name, value, description FROM v$parameter WHERE name = ‘compatible’;

Run the query following to verify that supplemental logging is enabled for the database. If the returned result is YES or IMPLICIT, supplemental logging is enabled for the database.

SELECT supplemental_log_data_min FROM v$database;

If needed, enable supplemental logging for the database by running the command following.

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

For RDS:

exec rdsadmin.rdsadmin_util.alter_supplemental_logging(‘ADD’);

2. Verify that the required supplemental logging is enabled for each table.

If a primary key exists, add supplemental logging for the primary key. You can do this either by using the format to add supplemental logging on the primary key, or by adding supplemental logging on the primary key columns.

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS; –> For enabling supplemental logging at Database level

alter table table_name add supplemental log data (PRIMARY KEY) columns; –> For enabling supplemental logging at Database level

For RDS:

exec rdsadmin.rdsadmin_util.alter_supplemental_logging(‘ADD’,’PRIMARY KEY’); –> For enabling supplemental logging at Database level

If no primary key exists and the table has multiple unique indexes, add all of the columns for the unique index that AWS DMS selects to the supplemental log. AWS DMS selects the first unique index from an alphabetically ordered, ascending list.

ALTER TABLE table_name ADD SUPPLEMENTAL LOG GROUP example_log_group (ID,NAME) ALWAYS;

If no primary key exists and there is no unique index, add supplemental logging on all columns.

alter table table_name add supplemental log data (ALL) columns;

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

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 Identifier. The 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 and replicate ongoing changes for Full Load Migration

4. Select Don’t use custom CDC stop mode in CDC Stop Mode settings.

5.  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. 

6. Select Don’t Stop in “Stop task after full load completes”

7. 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.

8. 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 :

9. Click on Add new Selection Rule in Table Mappings

10. 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

11. Click on Transformation Rules and Add new Transformation Rules

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

13. Click on Create Task

14. 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 :

15. The Status of your task would change to Load Complete, replication ongoing once your migration is complete

16. You can verify whether your replication is working properly or not by simply inserting into a table or by creating another table and enabling supplemental logging on that table.

In the below screenshot, we have inserted 2 rows into WLSLOG table, which is reflected under the Table Statistics inserts column :

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

Full Load Migration of On-premise Oracle Database to AWS RDS Oracle

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