Rollback option for migration to AWS RDS instance using DMS

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 […]

database managed services asm migration remote dba support database management Oracle linux
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 https://aws.amazon.com/blogs/database/migrating-oracle-databases-with-near-zero-downtime-using-aws-dms 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’);
end;
/

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;
declare
cursor c1 is select owner, table_name from dba_tables where owner = upper(‘&USER.’);
begin
dbms_output.enable(1000000);
for i in c1 loop
dbms_output.put_line(i.table_name);
execute immediate ‘ALTER TABLE ‘ || i.owner || ‘.’ || i.table_name ||’ ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS’;
end loop;
end;
/
&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;

CURRENT_SCN
———–
2221318

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;
declare
cursor c1 is select owner, trigger_name from dba_triggers where owner=(‘&USER’);
begin
dbms_output.enable(1000000);
for i in c1 loop
dbms_output.put_line(i.trigger_name);
execute immediate ‘ALTER trigger ‘|| i.owner || ‘.’ || i.trigger_name ||’ DISABLE’;
end loop;
end;
/

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 xxxx.xxxxxxxxxx.ap-southeast-2.rds.amazonaws.com \
–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 172.xx.x.xxx \
–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”: {
“EnableLogging”:true
},
“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.

When our Logistics client required a comprehensive health check and ongoing support for their two critical Oracle databases which supported the JD Edwards Logistics package essential for their distribution function, Blue Crystal Solutions were able to step in and remove the risks of instability, lack of future support, performance issues, non-scalable custom code and more.

Before digital archives, the nation's scientific data was stored in hard copies, susceptible to destruction. Yet, even with modern cloud storage like AWS, consistent updates and security measures are crucial to safeguard information. Blue Crystal Solutions (Blue Crystal) aided our Australian Federal Government client to securely upgrade and migrate biodiversity data.

We are continuing to grow and are looking for an Application Support and enhancement team member to join our Adelaide based team.

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, we can also work with your cyber teams to significantly improve your security posture whilst ensuring all your services with us are fortified by our integrated outage protection and 24×7 monitoring tool, BlueDiamond

Scroll to Top