AWS Database Blog

Alternatives to the Oracle flashback database feature in Amazon RDS for Oracle

Customers may prefer to host their Oracle database workloads in a managed service such as Amazon Relational Database Service (Amazon RDS) for Oracle because of the benefits offered by managed services. However, there could be workloads that have dependencies on Oracle features that aren’t supported by Amazon RDS for Oracle.

For example, the flashback database is a feature of the Oracle Database that allows you to restore a database to a point in time. The feature can be used to help recover from user errors, data corruption, other unforeseen disasters, or as a fall-back option for planned activities such as application and database upgrades. The recovery is applied directly on the live database and you won’t have to configure additional hardware and wait to complete a full restore. This is particularly valuable in an on-premises environment, where normally it’s hard to provision on-demand hardware to host one or more copies of your database for a short time. On the other hand, performing a flashback operation directly on your production database involves some risks and in case of an unpredictable problem, you may have to run a full restore with the potential for a significant impact on the service availability. By leveraging the agility and flexibility of the AWS Cloud, you can reach for the same level of protection without needing to operate directly on the live database and help avoid the performance overhead and resource consumption associated with the flashback database feature.

Amazon RDS for Oracle supports flashback table, flashback query, flashback transaction, and Oracle flashback features, but does not support flashback database to restore the entire database to a specific point-in-time. This post describes the alternatives to the Oracle flashback database capability in Amazon RDS for Oracle.

Solution overview

You can either utilize the snapshot restore method or the read replicas activation method as substitutes for the Oracle flashback database feature. The read replica activation method uses the RDS replica feature and the ability to activate the replica to be a full read/write primary instance. Snapshot restore uses the RDS snapshot capabilities to return the database to a certain point in time. You do need to be mindful that both the snapshot and read replica capabilities require additional licensing for the period of time the snapshot and replica are present. Another attention point is lazy loading; when you create a DB instance from a DB snapshot you can use the restored DB instance as soon as its status is available. The data continue to be loaded in the background from Amazon S3 backup or if you explicitly request it. You can perform operations that involve full-table scans to mitigate the performance overhead of lazy loading on tables to which you require quick access. Another option is to run an RMAN VALIDATE at database level. In both cases you can use a degree of parallelism based on the cores of the restored instance to speed up the operation.

In this post, we walk you through using both methods via the AWS Management Console and AWS Command Line Interface (AWS CLI).

Snapshot restore

With DB snapshot you can create a storage volume snapshot of your DB instance at a precise point in time, backing up the entire DB instance and not just the individual databases. You can create a new DB instance with a new URL endpoint by restoring from the DB snapshot. If you decide to point your application to the restored DB instance you can just rename the old instance and the restored instance to the original instance name.

Another option, if you have configured a custom CNAME entry in Amazon Route 53 private zone to point to your RDS DB instance, is to edit the CNAME entry with the restored DB instance endpoint without updating the RDS DB instance names. This way the connection to the restored RDS DB instance will be available in a few seconds.

The following diagram illustrates this solution’s workflow:

Read replica activation

For the read replica activation method, we can create a new DB instance that acts as a read replica for an existing source DB instance before a planned potentially risky activity, like a massive application code release or a DB software upgrade, to provide a quick rollback strategy. The read replica DB instance is promoted to be a standalone DB instance just before starting the planned activity on the source DB instance. In case of an unrecoverable issue that requires a no-go, we can quickly roll back to the previous DB status by connecting to the promoted replica DB instance. If Multi-AZ and other read replicas were configured on your original RDS DB instance, take into consideration the time it takes to enable the Multi-AZ and create the additional read replicas on the promoted replica DB instance to meet your needs in terms of RTO and RPO.

The following diagrams illustrate this architecture. First, we configure the RDS read replica.

Then we promote the RDS read replica to a standalone RDS DB instance.

We then switch the client connection to the promoted RDS DB read replica by renaming it to the original instance name.

Flashback using snapshot restore

In this section, we demonstrate how to simulate the flashback database feature in Oracle using the snapshot restore method via either the Amazon RDS console or AWS CLI. You can take a snapshot backup or use a point in time recovery; since the snapshot backup will create a full backup and not need to apply additional logs which will speed up recovery time, this is the approach we will show in this solution.

Take a snapshot backup

To start, we take a snapshot backup at the desired point in time.

Console

To use the console, complete the following steps:

  1. On the Amazon RDS console, choose Database in the navigation pane.
  2. Select the database you want to snapshot.
  3. On the Actions menu, choose Take snapshot.

  1. Give the snapshot a name and choose Take snapshot.

You can check the status of the backup on the Amazon RDS console.

AWS CLI

To run the snapshot backup through the AWS CLI, use the following code:

aws rds create-db-snapshot 
--db-instance-identifier blog-demo
--db-snapshot-identifier blog-demo-snapshot

Restore the snapshot

Now we restore the database to the snapshot backup.

Console

To use the console, complete the following steps:

  1. On the Amazon RDS console, select the snapshot we just took and on the Actions menu, choose Restore snapshot.

  1. Choose the DB instance identifier, the security group that is assigned to the database the snapshot was taken from, and the instance class.
  2. Choose Restore DB instance.

After the snapshot is restored, we can rename the databases so no application changes need to be pointed to the new restored database.

  1. Select the database we took the snapshot from and choose Modify.
  2. For DB instance identifier, update the instance name with the prefix -old.

  1. Choose Continue.
  2. Select Apply immediately, then choose Modify DB instance.

  1. When the restore is complete, we can perform the rename action on the restored database to name it the same instance name as the instance we initially took the backup from (for this example, blog-demo).

Once the renaming is complete, we can delete the old instance.

  1. Select the old instance and on the Actions menu, choose Delete.
  2. You can keep a final snapshot and automated backups, if desired.
  3. Enter delete me to confirm and choose Delete.

AWS CLI

To implement the solution using the AWS CLI, complete the following steps:

  1. To restore the backup, use the following code:
aws rds restore-db-instance-from-db-snapshot
--db-instance-identifier blog-demo-restore
--db-snapshot-identifier blog-demo-snapshot
--db-instance-class db.t3.medium —no-deletion-protection
  1. When the restore is complete, rename the instance we took the backup from:
aws rds modify-db-instance
--db-instance-identifier blog-demo
--new-db-instance-identifier blog-demo-old
  1. When the rename is complete, rename the restored database to the same name as the instance we took the snapshot from:
aws rds modify-db-instance
--db-instance-identifier blog-demo-restore
--new-db-instance-identifier blog-demo
  1. Now we can remove the initial database we renamed:
aws rds delete-db-instance
--db-instance-identifier blog-demo-old
--skip-final-snapshot
--delete-automated-backups

Flashback using read replica activation

The flashback procedure based on the read replica feature consists of the following steps:

  1. Prepare the source database.
  2. Create a mounted Oracle replica in the same Availability Zone.
  3. Check the replica lag and promote the read replica when the planned maintenance window starts.
  4. Wait for the read replica promotion to be complete to have a point of consistency in case of rollback.
  5. Check the status of the promoted read replica and execute a connection test.
  6. Run the scheduled activity on the source database.
  7. If a roll back is needed after the planned activity, point your application to the promoted read replica, renaming the old instance and the promoted instance to the original instance name.

Another option, if you have configured a custom CNAME entry in the Amazon Route 53 private zone to point to your RDS DB instance, is to edit the CNAME entry with the promoted DB instance endpoint without updating the RDS DB instance names. This way the connection to the promoted RDS DB instance will be available in a few seconds.

Prepare the source database

In this section we provide the steps needed to enable automatic backup and force logging mode.

Enable automatic backup

Before a DB instance can serve as a source DB instance, make sure to enable automatic backups on the source DB instance. You can check if the automatic backups are enabled from the console or AWS CLI.

If necessary, enable the automated backup setting BackupRetentionPeriod to greater than 0.

Console

To check the automated backup on the console, complete the following steps:

  1. On the Amazon RDS console, choose Databases in the navigation pane.
  2. Choose the DB instance you want to verify.
  3. On the Maintenance & backup tab, check if Automated backups is enabled.

  1. To enable automated backup, choose Modify.
  2. For Backup retention period, choose a positive non-zero value, for example 7 days.
  3. Choose Continue.

  1. Select Apply immediately.
  2. On the confirmation page, choose Modify DB instance to save your changes and enable automated backups.

AWS CLI

Verify that BackupRetentionPeriod is greater than 0 with the following code:

$aws rds describe-db-instances
--db-instance-identifier blog-demo \
--query "DBInstances[*].DBInstanceIdentifier,BackupRetentionPeriod]"

To enable automatic backup with a backup retention of 7 days, use the following code:

aws rds modify-db-instance \
--db-instance-identifier blog-demo \
--backup-retention-period 7 \
--apply-immediately

Enable force logging mode

We recommend that you enable force logging mode. In force logging mode, the Oracle database writes redo records even when NOLOGGING mode is used with data definition language (DDL) statements, to make sure they are also applied to the replica DB instance. Complete the following steps to enable force logging mode:

  1. Log in to your Oracle database using a client tool such as SQL Developer or SQL*Plus.
  2. Enable force logging mode by running the following procedure:
SQL> select name,db_unique_name,force_logging from v$database;

NAME DB_UNIQUE_NAME FORCE_LOGGING
---------- -------------------- --------------------
DEMO DEMO_A NO

exec rdsadmin.rdsadmin_util.force_logging(p_enable => true);

SQL> select name,db_unique_name,force_logging from v$database;

NAME DB_UNIQUE_NAME FORCE_LOGGING
---------- -------------------- --------------------
DEMO DEMO_A YES

Create a read replica

Create a read replica in mounted status for the Source Oracle database instance in the same Availability Zone. Run this task some hours before the planned activity on the main database and be sure to have an active replica when the maintenance window starts. As a rule of thumb, you can estimate this time period executing a test on your specific system and adding at least two hours of contingency. Read replica DB instances are created with backups disabled. Other DB instance attributes (including DB security groups and DB parameter groups) are inherited from the source DB instance, except as specified.

Console

To create a read replica on the console, complete the following steps:

  1. On the Amazon RDS console, in the navigation pane, choose Databases.
  2. Select the Oracle DB instance that you want to use as the source for the replica.
  3. On the Actions menu, choose Create replica.

  1. For Replica mode, choose Mounted.
  2. For DB instance identifier, enter a name for the read replica.

  1. For Availability Zone, choose the zone of the source database.

  1. For the other settings, verify that they are the same of the source database.
  2. Choose Create read replica.

You can see the status of the replica on the console.

After the create replica operation is complete, optionally we can change the replica mode to mounted.

AWS CLI

To create a read replica with the AWS CLI, complete the following steps:

  1. Create a read replica with the following code:
aws rds create-db-instance-read-replica \
--db-instance-identifier blog-demo-b \
--source-db-instance-identifier blog-demo \
--availability-zone eu-central-1a
  1. Query the instance with the following code:
aws rds describe-db-instances 
--db-instance-identifier blog-demo \
--query "DBInstances[*].[DBInstanceIdentifier,DBInstanceStatus]"
  1. After the create replica operation is complete, optionally we can change the replica mode to open-read-only (this option requires an additional Oracle license):
aws rds modify-db-instance \
--db-instance-identifier $REPINSTANCENAME \
--replica-mode open-read-only

Check replica lag

At the beginning of the planned maintenance window scheduled to run the planned activity, we promote our read replica to have a consistent point of restoration in case a rollback is required at the end.

Before promoting the read replica, we have to check the replica lag to be sure that the replica database instance is in sync with the source database instance.

Connected to the source database, we run the following logfile switch command before checking the replica lag in order to get an updated lag statistic:

SQL> EXEC rdsadmin.rdsadmin_util.switch_logfile;

PL/SQL procedure successfully completed.

Now we can check the replica lag from the console or AWS CLI.

Console

To check the replica lag on the console, complete the following steps:

  1. On the Amazon RDS console, in the navigation pane, choose Databases.
  2. Choose the Oracle DB replica instance.
  3. In the Replication section, we can verify the Replication state and Lag columns.

  1. We can also check the Amazon CloudWatch Replica Lag metric on the Monitoring tab.

AWS CLI

To check the replica lag with the AWS CLI, use the following code:

aws cloudwatch get-metric-statistics --namespace "AWS/RDS" \
--metric-name ReplicaLag --start-time $(date -d "-1 min" +%FT%TZ) \
--end-time $(date +%FT%TZ) --period 60 \
--dimensions Name=DBInstanceIdentifier,Value=blog-demo-b \
--statistics Maximum

Promote the read replica

Before proceeding with the read replica promotion, we will check the current state of the source and target databases, the connection throughout the source endpoint, the read replica endpoint, and the CNAME DNS alias that is now pointing to the source endpoint.

  1. Test the connection to the source database through the RDS instance endpoint:
sqlplus admin/$PASSWORD@blog-demo.yyyy.eu-central-1.rds.amazonaws.com:1521/DEMO

SQL> select name,db_unique_name,database_role from v$database;

NAME DB_UNIQUE_NAME DATABASE_ROLE
---------- -------------------- ----------------
DEMO DEMO_A PRIMARY
  1. Test the connection to the target replica database through the RDS instance endpoint:
sqlplus admin/$PASSWORD@blog-demo-b.xxxx.eu-central-1.rds.amazonaws.com:1521/DEMO

SQL> select name,db_unique_name,database_role from v$database;

NAME DB_UNIQUE_NAME DATABASE_ROLE
---------- -------------------- ----------------
DEMO DEMO_B PHYSICAL STANDBY
  1. Test the connection to the source database through the CNAME DNS alias:
sqlplus admin/$PASSWORD@demo.rds.blogdemo.com:1521/DEMO

SQL> select name,db_unique_name,database_role from v$database;

NAME DB_UNIQUE_NAME DATABASE_ROLE
---------- -------------------- ----------------
DEMO DEMO_A PRIMARY

Now we can promote the read replica. When you promote a read replica, the new DB instance that is created retains the option group and the parameter group of the former read replica. The promotion process can take several minutes or longer to complete, depending on the size of the read replica. After you promote the read replica to a new DB instance, it’s just like other DB instances.

Keep the following in mind:

  • Backup duration is a function of the number of changes to the database since the previous backup. If you plan to promote a read replica to a standalone instance, we recommend that you enable backups and complete at least one backup prior to promotion.
  • You can’t promote a read replica to a standalone instance when it has the backing-up status. If you have enabled backups on your read replica, configure the automated backup window so that daily backups don’t interfere with the read replica promotion.

Console

To promote the read replica on the console, complete the following steps:

  1. On the Amazon RDS console, choose Databases in the navigation pane.
  2. Select the read replica that you want to promote.
  3. On the Actions menu, choose Promote.

  1. On the Promote Read Replica page, enter the backup retention period as the source database.
  2. When the settings are as you want them, choose Continue.
  3. On the acknowledgment page, choose Promote read replica.

You can view the status of the replica on the console.

AWS CLI

To use the AWS CLI to promote the read replica instance to a standalone instance, use the following code:

aws rds promote-read-replica 
--backup-retention-period 7 \
--db-instance-identifier blog-demo-b

Wait for the read replica promotion to complete

At this point, we will wait for the database replica promotion to complete in order to verify a point of consistency in case of rollback. We will also check the connectivity to the promoted read replica instance to be sure that it’s ready to accept inbound connections in case of a rollback.

We can check the promotion status through the console or the AWS CLI.

Console

The following screenshot shows the promotion DB instance status on the console.

AWS CLI

To use the AWS CLI, check the promoted database instance status with the following code:

aws rds describe-db-instances 
--db-instance-identifier blog-demo-b \
--query "DBInstances[*].[DBInstanceIdentifier,DBInstanceStatus,ReplicaMode]"

Run a connection test using the promoted read replica RDS endpoint:

sqlplus admin/$PASSWORD@blog-demo-b.xxxx.eu-central-1.rds.amazonaws.com:1521/DEMO

SQL> select name,db_unique_name,database_role from v$database;

NAME DB_UNIQUE_NAME DATABASE_ROLE
---------- -------------------- ----------------
DEMO DEMO_B PRIMARY

Run the planned activity on the source database

At this point, we have a copy of our source database up and running and it’s ready to assume the role of new source database in case we need to roll back the changes applied to the database after our planned activity. Before actually executing the planned activity on the production RDS instance, be sure that the promoted RDS instance has the expected configuration to accommodate the production workload in case a rollback would be required. Check, for example, the RDS instance class and size, the storage autoscaling option and any other configurations such as parameters and option groups.

Rollback after the planned activity

In this scenario, we’ve run the planned activity on the source database, but the result is not as expected. We decree the no-go for this activity and proceed to the rollback.

To roll back to the initial status of the database, we have to recreate the original RDS DB instance DNS endpoint to point to the promoted RDS DB instance by renaming the two RDS DB instances as follows:

  • Rename the original RDS DB instance from demo-blog to demo-blog-old and stop the original instance
  • Rename the promoted RDS DB instance from demo-blog-b to demo-blog

This way the original RDS DB instance DNS entry will be recreated to point to the promoted RDS DB instance.

The new DNS name for the renamed DB instance becomes effective in about 10 minutes.

Console

To implement the rollback on the console, complete the following steps:

  1. On the Amazon RDS console, in the navigation pane, choose Databases.
  2. Choose the DB instance that you want to stop.
  3. Choose Modify.
  4. Enter the new name for the original RDS DB instance identifier (demo-blog-old).

  1. Select Apply immediately and choose Modify DB instance.

  1. Apply the same procedure to the promoted RDS DB instance and rename it to demo-blog.

AWS CLI

To roll back using the AWS CLI, complete the following steps:

  1. Rename the old source database instance:
aws rds modify-db-instance
--db-instance-identifier blog-demo \
--new-db-instance-identifier blog-demo-old
  1. Rename the promoted RDS database instance with the original source database instance name (demo-blog):
aws rds modify-db-instance
--db-instance-identifier blog-demo-b \
--new-db-instance-identifier blog-demo
  1. Test the connection to the database using the original source database RDS endpoint:
sqlplus admin/Welcome1@blog-demo.yyyy.eu-central-1.rds.amazonaws.com:1521/DEMO

SQL> select name,db_unique_name,database_role from v$database;

NAME DB_UNIQUE_NAME DATABASE_ROLE
---------- -------------------- ----------------
DEMO DEMO_B PRIMARY

Edit the CNAME entry

As anticipated in the previous sections, if your application connection strings are configured with a custom Route 53 CNAME entry defined in a private zone that points your initial Primary DB instance endpoint, you can simply update the CNAME to point to the promoted DB instance endpoint without renaming the two DB instances. This way the connection to the promoted RDS DB instance will be available in a few seconds. The high-level steps are as follows:

  1. Stop the original source database.
  2. Update the Route 53 DNS CNAME dedicated to your source database with the alias of the promoted read replica.
  3. Test the connection to the new source database.

Console

To edit the CNAME entry via the console, complete the following steps:

  1. On the Amazon RDS console, in the navigation pane, choose Databases.
  2. Choose the DB instance that you want to stop.
  3. On the Actions menu, choose Stop temporarily.

  1. Select the acknowledgement that the DB instance will restart automatically after 7 days.
  2. As an optional but recommended step, select Save the DB instance in a snapshot and enter the snapshot name for Snapshot name.
  3. Choose Stop temporarily to stop the DB instance.

  1. On the Route 53 console, choose Hosted zones in the navigation pane and then choose your hosted zone.
  2. Choose Edit hosted zone.

  1. In the Records list, select the CNAME associated to the source database.
  2. Choose Edit record.
  3. Update the value with the promoted read replica endpoint.
  4. Choose Save.

  1. Test the connection to the database using the CNAME alias endpoint:
sqlplus admin/Welcome1@demo.rds.blogdemo.com:1521/DEMO

SQL> select name,db_unique_name,database_role from v$database;

NAME DB_UNIQUE_NAME DATABASE_ROLE
---------- -------------------- ----------------
DEMO DEMO_B PRIMARY

Conclusion

With Amazon RDS for Oracle, we can use Amazon RDS functionalities to satisfy the same business goals of the Oracle flashback database feature. In this post, we discussed how to verify a restore point before a planned and potentially risky activity: we can either create a manual snapshot or promote a read replica just before the activity. The second option is more suitable for a production environment because it promotes a faster rollback in case of an unrecoverable issue introduced by the planned activity since you can configure the read replica enough time in advance, depending on your DB size and workload, to mitigate the effect of lazy loading storage restore.

If you have any comments or questions, leave them in the comments section.

About the authors

Tony Mullen is a Senior Database Specialist Solution Architect based in Manchester. With a focus on relational database engines, he assists customers in migrating and modernizing their database workloads to AWS.

Giorgio Bonzi is a Senior Database Specialist Solution Architect based in Milan. With a focus on Oracle database engine, he provides guidance and technical assistance to customers to help them design, deploy, and optimize relational database workloads on AWS.