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.
