AWS Database Blog

Configure reverse replication using AWS DMS to migrate an Oracle database to Amazon RDS for Oracle

When migrating your database to the cloud, you should prepare a failback plan (or recovery plan) if the migration fails. To provide failback functionality, AWS Database Migration Service (AWS DMS) supports reverse replication, which you can use as a minimal downtime failback strategy while migrating from Oracle on premises to Amazon Relational Database Service (Amazon RDS) for Oracle, Amazon Elastic Compute Cloud (Amazon EC2), or Amazon RDS Custom for Oracle. The failback is achieved via the AWS DMS change data capture (CDC) reverse replication feature, which works similar to AWS DMS CDC forward replication except the replication configuration is done at the opposite endpoints.

Implementing a reverse replication setup is an important strategy that can act as a safety net, enabling you to roll back changes without losing critical data or experiencing extended downtime. This can be useful in a variety of situations, such as data integrity issues, application incompatibilities, performance degradation, unexpected downtime, failed migration attempts, security vulnerabilities, or even compliance failures.

You should plan, document, and test a failback approach that can revert back to the source database if any issues come up during or after migration. The failback approach should minimize downtime and also ensure that there is no data loss.

In this post, we show you how to configure AWS DMS for reverse replication as a failback approach. This approach uses minimal downtime for setting up and performing the failback during the migration of an on-premises Oracle database to an RDS for Oracle database.

Terminology

To follow along with this post, you should be familiar with the following concepts:

  • AWS DMS CDC – We use an AWS DMS CDC task to capture ongoing changes to the source database generated during the migration process. The CDC process that AWS DMS uses when replicating ongoing changes from a source endpoint collects changes to the database logs by using the database engine’s native API. AWS DMS CDC reverse replication works similar to AWS DMS CDC forward replication, with replication configuration done at the opposite endpoints. For both forward and reverse replication, AWS DMS reads ongoing changes from the online or archive redo logs based on the SCN.
  • SCN – This solution is based on the system change number (SCN), which is a logical, internal timestamp used by Oracle databases. The SCN orders events that occur within the database, which is necessary to satisfy the ACID properties of a transaction. Oracle databases use SCNs to mark the location where all changes have been written to disk so that a recovery action doesn’t apply already written changes. Oracle also uses SCNs to mark the point where no redo exists for a set of data so that recovery can stop.
  • LSN – The redo log sequence number (LSN) is an increasing number that uniquely identifies each redo log file. The log sequence is increased when a log switch is performed.

Solution overview

In the following sections, we demonstrate how to set up reverse replication and implement it while migrating from an on-premises Oracle database to an RDS for Oracle database. In this example, we use an RDS for Oracle database as the source and an on-premises Oracle database as the target for reverse replication, but you can use the same steps for Oracle running on Amazon EC2 as the source and on-premises Oracle database as the target.

There are two ways to set up reverse replication: back to the same source database or to the different database.

Method A: Reverse replication for failback to the same on-premises database

In this setup, there is no initial load required from Amazon RDS for Oracle to the on-premises Oracle database. The only requirement is to create the AWS DMS CDC task from the current SCN of the target database to the source database post-cutover. After the task starts, it continues to replicate any new changes in Amazon RDS for Oracle to the on-premises database. This allows you to fall back to the on-premises database after confirming that there are no more changes left to be applied from the RDS for Oracle database.

Along with database failback, dependent components like applications, unstructured data on a shared file system, and so on also require a failback strategy. Components like applications, database, shared storage, and more should be consistent between the source and target before failback. It’s recommended to perform failback testing prior to production cutover to AWS. During this test, you can evaluate the steps and duration required to perform a failback of integrated components.

The following diagram illustrates this replication strategy.

A VPN connection is recommended, or you can also use AWS Direct Connect.

For reverse replication, complete the following steps during the cutover after the data validation is done between the source and target database:

  1. Export and import the data from Oracle on premises to Amazon RDS for Oracle using the flashback SCN.
  2. Create the CDC replication task from Oracle on premises to Amazon RDS for Oracle.
  3. Start the CDC task using the flashback SCN captured in Step 1.

Perform the cutover.

  1. Get the current SCN from the RDS for Oracle database.
  2. Create the reverse CDC-only replication task from the RDS for Oracle database to the on-premises Oracle database.
  3. Configure the reverse replication task for the SCN captured in Step 4 and start the reverse CDC-only replication tasks from the RDS for Oracle database to the on-premises Oracle database.

There could be certain scenarios in which you would like to use wave wise migration. In wave wise migration, multiple applications that share the same database are migrated in a phased manner and in each phase, the migrated application and dependent database objects are migrated together. This gives the flexibility to start with smaller, not mission-critical applications first and critical ones last, thereby reducing the risk associated with database migration. In this scenario, you can migrate those applications and respective data to Amazon RDS for Oracle first. In this scenario, the application workloads are distributed between the on-premises Oracle database and RDS for Oracle database. For such migrations, it’s important to maintain database consistency and design a proper failback strategy, where you may consider using the bidirectional replication feature available with AWS DMS.

Consider the following guidelines for bidirectional replication:

  • To enable AWS DMS bidirectional replication, configure source and target endpoints for both databases P (on-premises Oracle database) and R (RDS for Oracle database).
  • Then create two tasks: one task for source P to move data to target R, and another task for source R to move data to target P.
  • Also, make sure that each task is configured with loopback prevention. Doing this prevents identical changes from being applied to the targets of both tasks, thereby corrupting the data for at least one of them. During the task creation, you can use the loopback prevention settings.

For example, the loopback prevention settings for AWS DMS Task 1 is as follows:

{
. . .

  "LoopbackPreventionSettings": {
    "EnableLoopbackPrevention": true,
    "SourceSchema": "OP_SCHEMA",
    "TargetSchema": "RDS_SCHEMA"
  },

. . .
}

The settings for task 2 are as follows:

{
. . .

  "LoopbackPreventionSettings": {
    "EnableLoopbackPrevention": true,
    "SourceSchema": "RDS_SCHEMA
    "TargetSchema": "OP_SCHEMA"
  },

. . .
}

Additionally, if you need to prevent the loopback of the DDL changes, use the task settings for change processing DDL handling as per the requirement.

Be aware of the limitations associated with bidirectional replication.

Method B: Reverse replication for failback to a different on-premises database

In this scenario, we set up reverse replication to a different on-premises database. Make sure you have an additional database license to set up the reverse replication.

In this setup, we need to copy the data from the source on-premises database to a different database. A task is created for the source to target and the target to a different on-premises database to maintain the data consistency so that failback can be done at any point in time.

The following diagram demonstrates the forward and reverse replication setup. During forward replication, data is migrated from the on-premises database to Amazon RDS for Oracle. Then you create the new AWS DMS CDC task to replicate incremental changes from Amazon RDS for Oracle to the on-premises database (different database).

To set up forward replication for your database migration, complete the following steps:

  1. Export the data using the flashback SCN from the Oracle on-premises database.
  2. Import the same exported data to a different Oracle on-premises database, which will be used during the failback.

For reverse replication of the database migration, continue with the following steps:

  1. Create the reverse replication task from the RDS for Oracle database to the Oracle on-premises (CP) database.
  2. Start the reverse replication task and check if the task is running without errors.
  3. Create the CDC task from the RDS for Oracle database to the Oracle on-premises database using the flashback SCN obtained in Step 1.
  4. Start the CDC task created in Step 5. The data coming from database P to database R will be replicated to the CP database via the reverse replication task created in Step 3.

You can fail back any time after this setup, but you need to make sure the data validation is done prior to failback. During the failback, you need to point the application to the CP database, which will act as the new production database.

Prerequisites

The following are the prerequisite steps for reverse replication setup:

  1. Configure a source endpoint (Amazon RDS for Oracle) for reverse replication.
  2. Configure a target endpoint (on-premises Oracle instance) for reverse replication.
  3. Grant privileges to the endpoint user for the target database.
  4. Drop any unique key, foreign key constraints, and indexes on the target database for reverse replication.
  5. Create a source endpoint (on-premises Oracle instance) and target endpoint (Amazon RDS for Oracle) for forward replication.
  6. If you’re using the failback method that involves a separate on-premises database, make sure you have the additional licenses necessary to provision the database.
  7. Enable supplemental logging in the RDS for Oracle database at the database level to make sure any changes to columns identified for the tables will be replicated to the target:
exec rdsadmin.rdsadmin_util.alter_supplemental_logging('ADD');
  1. Run the following commands to enable primary key supplemental logging:
exec rdsadmin.rdsadmin_util.alter_supplemental_logging('ADD','PRIMARY KEY');
exec rdsadmin.rdsadmin_util.alter_supplemental_logging('ADD','ALL COLUMNS');
  1. Enable the archive log retention on the source database:
exec rdsadmin.rdsadmin_util.set_configuration("archivelog retention hours", 24);

Set up reverse replication using Method A

The steps in this section are applicable to method A, in which you set up reverse replication for failback to the same on-premises database.

During the cutover window, complete the following steps:

  1. Stop the current replication task created from task 1, which is migrating data from the on-premises database to Amazon RDS for Oracle.
  2. Obtain the starting point of the CDC tasks. The following query will help you get the SCN on Amazon RDS for Oracle:
select current_scn from v$database ;
4045471

You can use either the database SCN or timestamp or checkpoint while creating the reverse replication task.

To avoid any data inconsistencies, carefully choose the start point for the replication-only task. AWS DMS only captures transactions that started after the chosen CDC start point. If you use the current SCN to start a CDC task, you miss the results of any open transactions and fail to migrate these results. Refer to Performing replication starting from a CDC start point for details.

You can wait a few minutes until there are no open transactions in the source before starting the reverse replication task. Otherwise, use the option in the task to make the task fail if the open transactions aren’t closed within the specified time.

If you want the task to fail when open transactions aren’t closed in time, you can set TransactionConsistencyTimeout. Refer to Records missing during full load for details.

Now we create the reverse replication task with the source as Amazon RDS for Oracle and target as the Oracle on-premises database. Make sure the task is created as the CDC replication task with ongoing changes with the LSN as the current_scn in T1 (which is 4045471).

  1. For the custom CDC start point for the source transaction, enable the custom CDC start mode.
  2. Specify an LSN.
  3. Add the required mapping rules.
  4. Enable Detailed debug mode.
  5. Create the task.

For more information about starting and stopping a particular SCN, refer to How to work with native CDC support in AWS DMS.

Set up reverse replication using Method B

The following steps are applicable to method B, in which you set up reverse replication for failback to a different on-premises database.

As mentioned earlier, the following step is done prior to the cutover, after the initial load of data from the Oracle source database to Amazon RDS for Oracle, and prior to starting the CDC replication.

  1. Get the current_scn value from Amazon RDS for Oracle:
select current_scn from v$database ;
4045471

Now we create the reverse replication task with the source as Amazon RDS for Oracle and the target as the Oracle on-premises database. Make sure the task is created as the CDC replication task with ongoing changes with the LSN as the current_scn in Amazon RDS for Oracle (which is 4045471).

  1. For the custom CDC start point for the source transaction, enable the custom CDC start mode.
  2. Specify an LSN.
  3. Add the required mapping rules.
  4. Enable Detailed Debug mode.
  5. Create the task.

You can turn off the detailed debug if the replication has run for some time without any issue.

Fail back the database using reverse replication

Before you fail back, make sure you take all the following precautions. If these points are not considered, then there are chances of data inconsistency and data lost post-failback.

  1. Prepare the RDS for Oracle database for the cutover. This includes stopping applications, long-running processes, inactive sessions, and so on.
  2. When the RDS for Oracle database is ready, check the CDC tasks logs for any errors.
  3. If you have multiple tasks, make sure that CDCLatencySource and CDCLatencyTarget is minimal or near to zero. If you have validation enabled, confirm that the tables’ validation state is showing as Validated. Also, confirm that Last Updated in the table statistics is the current system time.
  4. Stop all CDC tasks.
  5. Create the necessary secondary unique key constraints and foreign key constraints. Enable the triggers and reset the sequences as applicable.
  6. Complete post-cutover activities like changing the DNS hostname, restarting the application server on premises, and notifying users of the URL change.

Limitations

The following are the limitations when using reverse replication:

  • AWS DMS CDC can replicate supported data definition language (DDL) statements on the source database during the data migration process. Refer to DDL statements supported by AWS DMS for more details.
  • Consider creating separate export/import process or using separate AWS DMS migration tasks to migrate these unsupported database objects as per the requirement.
  • The sync should be maintained between the on-premises Oracle database and RDS for Oracle database. Otherwise, the failback could take some time to sync in the case of a planned failback, and there could be data loss in the case of an unplanned failback.
  • All limitations with AWS DMS and Oracle as a source or target still apply to the replication task using reverse replication. To know more about AWS DMS limitations, refer to Limitations on using Oracle as a source for AWS DMS.

Conclusion

You can use AWS DMS reverse replication as a minimal downtime failback solution when migrating from Oracle on premises to Amazon RDS for Oracle. In this post, we outlined the steps required for setting up reverse replication using the CDC component of AWS DMS. Depending on the migration effort required during cutover, you can decide whether to use the same database or another database for reverse replication setup.

Review the recommended best practices associated AWS DMS, source database considerations, and limitations associated with using an Oracle database as a source and target for AWS DMS.

Refer to link Rolling back from a migration with AWS DMS to learn more about the fallback options available in AWS DMS.

About the Authors

Abhishek Kumar Verma is a Senior Lead Consultant with the Amazon Web Services ProServe team. He has deep expertise in database migration and administration on Amazon RDS for Oracle, Amazon RDS for PostgreSQL, and Amazon Aurora PostgreSQL databases. He is also a subject matter expert in AWS DMS, Oracle GoldenGate, and Oracle Exadata. He works closely with customers to help migrate and modernize their databases and applications to AWS on a daily basis.

Jeevan Shetty is a Senior Lead Consultant with the AWS Professional Services Team. He has been supporting and enabling customers to migrate their databases from on-premises data centers to the AWS Cloud and also migrate from commercial database engines to open-source databases in Amazon.

Vivekananda Mohapatra is a Lead Consultant with the Amazon Web Services ProServe team. He has deep expertise in database development and administration on Amazon RDS for Oracle, Amazon RDS for PostgreSQL, Amazon Aurora PostgreSQL, and Amazon Redshift databases. He is also a subject matter expert in AWS DMS. He works closely with customers to help migrate and modernize their databases and applications to AWS on a daily basis.