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.
With this option we can minimize the downtime of your migration.
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 22.214.171.124 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 + CDC task, it is recommended to add secondary indexes before the CDC phase. Because AWS DMS uses logical replication, secondary indexes that support DML operations should be in-place to prevent full table scans.
AWS DMS provides ongoing replication of data, keeping the source and target databases in sync. It replicates only a limited amount of data definition language (DDL). AWS DMS doesn’t propagate items such as indexes, users, privileges, stored procedures, and other database changes not directly related to table data.
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 126.96.36.199 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;
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
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
|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)
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 videos and posts on AWS RDS, click on the below links :