Rollback option for migration to AWS RDS instance using DMS

database managed services asm migration remote dba support database management
Oracle Database Migration Planning

When planning for migration of Oracle databases, there are a number of considerations, one of the most important planning considerations is the ability to “Rollback”. This article defines the “Rollback” of an Oracle database migration as the process to migrate the data back to the original source, including both the original data set and any changes that occurred to the data set after the migration took place.

There are multiple well-known and tested methods for migrating Oracle databases from one location to another, and for the “Rollback” of such migrations.

Oracle Database Migration to AWS.

There are two widely used methods to migrate Oracle databases to AWS:

Oracle Data Guard
One of most robust and tested methods for the migration from on-premises Oracle databases to cloud providers (AWS EC2/Azure VM), It is a relatively straight forward process, however a number of limitations present:

• The source and target platforms must have the same endian format.
• The source and target databases must be Enterprise Edition in order to allow automated log shipping and application.

During cutover, the source database is switched over to the target database, this method guarantees data consistency and provides a rollback option as a contingency.

However, the use of Oracle DataGuard is possible when migrating to an AWS EC2 instance, it is not possible when migrating to an AWS RDS instance.

AWS Data Migration Service

AWS Data Migration Service (DMS) mitigates the limitations that present when using Oracle DataGuard, it is more flexible and has more options. Please see this link to get more familiar with AWS DMS and what it can offer.

AWS DMS allows migrations to both EC2 and RDS instances.

While the use of AWS DMS offers more flexibility, additional steps are required to enable the rollback to the original database. DMS can and should be configured for a rollback scenario. This configuration can be done by reverting the direction of the DMS replication. The process to achieve this is outlined below.

Reverse replication setup after Cutover (In AWS)
1. Enable database supplemental logging.
SQL> begin
rdsadmin.rdsadmin_util.alter_supplemental_logging(p_action => ‘ADD’);

2. Enable supplemental logging at the table level, in this case for all columns as we don’t know much about the indexing in this schema (Primary or Unique index is required by Log Miner process).

SQL> set serveroutput on;
cursor c1 is select owner, table_name from dba_tables where owner = upper(‘&USER.’);
for i in c1 loop
execute immediate ‘ALTER TABLE ‘ || i.owner || ‘.’ || i.table_name ||’ ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS’;
end loop;
&USER is the schema user for which the replication will be created. SQL Plus will prompt for a value to be entered.

3. Once the migration to AWS is completed ( after turning the replication task off and before the first start of the application that is now pointing at the AWS instance), get the current database SCN, this is essential to identify the point from where the data set will be replicated back to the on-premises instance

SQL> SELECT current_scn FROM v$database;


Reverse replication setup after Cutover (On-Premises)

As soon as the on-premises database is not in use – execute the following steps:

1. Disable triggers on the database, as CDC replication is not supported when triggers are enabled
SQL> set serveroutput on;
cursor c1 is select owner, trigger_name from dba_triggers where owner=(‘&USER’);
for i in c1 loop
execute immediate ‘ALTER trigger ‘|| i.owner || ‘.’ || i.trigger_name ||’ DISABLE’;
end loop;

2. Create a new source endpoint, which now points to AWS and test it

aws dms create-endpoint \
–endpoint-identifier “ora-dms-source” \
–endpoint-type source \
–engine-name oracle \
–username admin \
–password xxxxxxx \
–server-name \
–port 1521 \
–database-name ORCL | grep EndpointArn

3. Create a target endpoint and test it

aws dms create-endpoint \
–endpoint-identifier “ora-dms-target” \
–endpoint-type target \
–engine-name oracle \
–username system \
–password xxxxxxxx \
–server-name \
–port 1521 \
–extra-connection-attributes ‘useLogMinerReader=N;useBfile=Y;addSupplementalLogging=Y;’ \
–database-name pdb | grep EndpointArn

4. Create a replication task which, as soon as it’s created and started, kicks off replication back to on-premises Oracle instance.

aws dms create-replication-task \
–replication-task-identifier ora-ec2-to-rds \
–source-endpoint-arn arn:aws:dms:ap-southeast-2:9999999999999:endpoint:DNPXNECA4J6GD5NQBUVVWCVGUOGEVJEPR7C2WFI \
–target-endpoint-arn arn:aws:dms:ap-southeast-2:9999999999999:endpoint:ZG6QUD7NSZMEH6OFJGGNJQUPZNSWC2QJY5VTX3Y \
–replication-instance-arn arn:aws:dms:ap-southeast-2:9999999999999:rep:OXGZXSBBGIZY7PHVQ73ZNQPGRY3C3SSKOU3ZCAA \
–migration-type cdc \
–cdc-start-position 2221318 \
–table-mappings ‘
“rules”: [
“rule-type”: “transformation”,
“rule-id”: “313105764”,
“rule-name”: “313105764”,
“rule-target”: “schema”,
“object-locator”: {
“schema-name”: “DMSTEST”
“rule-action”: “rename”,
“value”: “DMSTEST”,
“old-value”: null
“rule-type”: “selection”,
“rule-id”: “312878417”,
“rule-name”: “312878417”,
“object-locator”: {
“schema-name”: “DMSTEST”,
“table-name”: “%”
“rule-action”: “include”,
“filters”: []
‘ \
–replication-task-settings ‘{
“Logging”: {
“ChangeProcessingTuning”: {
“MemoryLimitTotal”: 2048,
“TargetMetadata”: {
“TargetSchema”: “admin”,
“FullLobMode”: true,
“LimitedSizeLobMode”: false

If a rollback is required:
1. The replication task will be stopped and the triggers will be enabled
2. The application will be redirected to point at the on-premises database.

AWS DMS can be used for migration of Oracle database to Oracle, AWS, or Azure. AWS DMS replication simplifies migration from on-premises to AWS RDS. While there are many options and details that are not discussed in this article, it highlights a mechanism that could be essential for many migration projects.

Learn More.

Efficient database management is not just about storing and retrieving information; it's a strategic asset that, when harnessed correctly, can propel an organisation towards sustainable growth.

For those of you in Adelaide who were unable to experience AWS re:Invent Event held in Las Vegas this year, we’re bringing the event to you in Adelaide as an offical sponsor.

In the evolving world of technology, organisations, from large businesses to national government bodies, seek partners that provide reliable and innovative solutions to enhance operational efficiency and productivity. A standout in this sphere, Blue Crystal Solutions, headquartered in Adelaide with offices in Melbourne, Brisbane, Sydney, and Perth, offers precisely such expertise. Here’s how they stand tall in cloud consulting and beyond.

Blue Crystal Solutions: your trusted & innovative IT partner.

Australian owned and operated since 2004, we provide information technology services locally, nationally and beyond.

We are a specialised supplier of Cloud, Application, Database & Infrastructure, Operating System Management, Modernisation and Transformation services. With security at the forefront of everything we do, all your services are fortified by our integrated outage protection  and monitoring tool, BlueDiamond

Scroll to Top