Managed disaster recovery and managed reader farm with Amazon RDS for Oracle using Oracle Active Data Guard

Many AWS users are taking advantage of the managed database offerings in the Amazon Relational Database Service (Amazon RDS) portfolio to remove much of the undifferentiated heavy lifting from their day-to-day activities. With Amazon RDS for Oracle, users can significantly reduce the administrative overhead of managing and maintaining an Oracle database.

Amazon RDS for Oracle provides Multi-AZ deployment to provide enhanced availability and durability for database (DB) instances within a specific AWS Region, and is often an effective disaster recovery (DR) solution for most use cases. However, several customers running mission critical databases have a business requirement for their DR configuration to span across different AWS regions. At the same time, these customers want to be able to leverage their DR investments to handle some of their production read workloads.

Currently, you can implement self-managed DR solutions for Amazon RDS for Oracle using any of the following:

Implementing low-cost, cross-region DR for Amazon RDS for Oracle using DB snapshots. For more information, see Cross-Region Automatic Disaster Recovery on Amazon RDS for Oracle Database Using DB Snapshots and AWS Lambda.
Implementing DR for Amazon RDS for Oracle DR using AWS Database Migration Service (AWS DMS). For more information, see Disaster recovery on Amazon RDS for Oracle using AWS DMS.
Deploying cross region DR using Oracle GoldenGate for Amazon RDS for Oracle. For more information, see Implementing cross-region disaster recovery using Oracle GoldenGate for Amazon RDS for Oracle.

With in-region and cross-region Read Replicas support on Amazon RDS for Oracle, you can easily create the replicas using Oracle Active Data Guard. This post discusses the managed DR and managed reader farm capabilities of Amazon RDS for Oracle Read Replicas. It also covers other use cases such as offloading read workloads from the primary DB instance, scaling out read workloads over a reader farm, and data proximity.

Introduction to Amazon RDS for Oracle Read Replicas

Amazon RDS for Oracle Read Replicas (both in-region and cross-region) use Oracle Active Data Guard between the primary and the physical standby (Read Replica) DB instances. Amazon RDS for Oracle configures asynchronous Data Guard replication between the primary and the Read Replica, where the Read Replica is open in read-only mode while the Redo Apply is active. The primary and the Read Replica DB instances operate in Data Guard Max Performance mode, which uses log writer (LGWR) asynchronous transport (ASYNC), with no affirmation on the Read Replica I/O (NOAFFIRM). In this mode, the transactions are committed as soon as the redo generated is written to the online redo log. Additionally, the redo is written to the Read Replica’s redo logs asynchronously. This solution provides the highest level of data protection possible without impacting the performance of the primary DB instance.

Depending on the database workload and amount of redo logs it generates, you can achieve low RPO and RTO in single-digit minutes. RDS Oracle creates a secure communication channel between the primary and Read Replicas, and manages the setup and configuration of security groups and access control lists to enable secure redo transport between the primary and Read Replicas.

Use cases for In-region Read Replicas

The following are possible use cases for in-region Read Replicas:

Offload read workloads – Customers can offload their read workload from the primary DB instance to the Read Replica DB instance and therefore boost the performance of read-write transactions running on the primary
Scale out read workloads – Customers can scale out read workloads over a reader farm of up to five Read Replicas
Availability – Customers can promote a given Read Replica as a new standalone database to achieve additional availability in case of primary DB failure

The following diagram illustrates the architecture for Amazon RDS for Oracle In-region Read Replicas.

Use cases for Cross-region Read Replicas

The following are possible use cases for cross-region Read Replicas:

Managed disaster recovery – Using managed DR offering customers can promote a Read Replica in another Region to be a new standalone production database
Data proximity – Customers can place replicas closer to the application users of a given Region to reduce read latencies
Offload read workloads – Customers can offload their read workload from the primary DB instance to the Read Replica DB instance . The Read Replicas can reside in any AWS Region.
Scale out read workloads – Customers can scale out read workloads over a reader farm of up to five Read Replicas that can reside in any AWS Region

The following diagram illustrates the architecture for Amazon RDS for Oracle Cross-region Read Replicas.

Creating Read Replicas on the Amazon RDS console

Amazon RDS for Oracle Read Replicas provide enhanced performance and durability for DB instances. Amazon RDS creates a second DB instance using a snapshot of the source DB instance. To enable Read Replicas, you must enable automatic backups (set the backup retention period to a value greater than 0) and force logging on the source DB instance. To enable force logging mode, connect to the DB instance and enter the following code:

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

For more information about creating Read Replicas and their limitations, see Read Replica Limitations with Oracle.

This section walks you through the creation of cross-region Read Replica.

Create Primary (source DB) instance.
For demonstrating the creation of Read Replica , the primary Database instance “mydbinstance” is already created in the AWS region “us-west-2” using the below CLI command.
```
aws rds create-db-instance \
  --db-instance-identifier mydbinstance \
  --db-name MYDB \
  --allocated-storage 20 \
  --storage-type gp2 \
  --db-instance-class db.m5.xlarge \
  --engine oracle-ee  \
  --port 1521 \
  --backup-retention-period 1 \
  --license-model byol \
  --master-user-password xxxxxxxxxxxx \
  --master-username admin \
  --engine-version 19.0.0.0.ru-2020-01.rur-2020-01.r1
```
For more information, see Creating an Oracle DB Instance and Connecting to a Database on an Oracle DB Instance.
When the primary DB instance status shows as Available, you can create the Read Replica (make sure to switch the Amazon RDS console to the Region where the primary DB instance is located).
On the Amazon RDS console, choose Databases.
Select mydbinstance.
From the Actions drop-down menu, choose Create read replica.
In the Instance specifications section, for DB instance class, choose m5.xlarge.
For Multi-AZ deployment, select No.
For Storage type, choose General Purpose (SSD).
In the Network & Security section, for Destination region, choose US East (N. Virginia).
For Destination DB subnet group, choose default.
For Availability zone, choose No preference.
For Publicly accessible, select Yes.
For VPC security groups, choose default.
In the Settings section, for Read replica source, choose mydbinstance.
For DB instance identifier, enter mydbinstance-ro.
In the Monitoring section, for Enhanced monitoring, select Disable enhanced monitoring.
In the Log exports section, leave all log types deselected.
In the Performance Insights section, select Disable Performance Insights.
In the Maintenance section, for Auto minor version upgrade, select No.
Choose Create read replica.
Creation takes 10–15 minutes to complete, after which the role for the primary DB instance shows as Master in us-west-2.
The role for the Read Replica shows as Replica in us-east-1.

Creating the Read Replica in the AWS CLI

Alternatively, you can also create the Read Replica in the AWS CLI or with the create-db-instance-read-replica Amazon RDS API. To create the cross-region Read Replica (mydbinstance-ro) in the us-east-1 with the primary DB instance (mydbinstance) running in us-west-2, enter the following code:

aws rds create-db-instance-read-replica \
--source-db-instance-identifier arn:aws:rds:us-west-2:xxxxxxxxxx:db:mydbinstance\
--source-region us-west-2\
--db-instance-identifier mydbinstance-ro\
--region us-east-1
-- kms-key-id  kms-key-id xxxxxxxxxx: xxxxxxxxxx:

If you create an encrypted Read Replica in a different Region, you must specify an AWS KMS key for the destination Region. AWS KMS encryption keys are specific to the Region that you create them in; you can’t use encryption keys from one Region in another Region.

Connecting to the Read Replica

You can connect to the Read Replica instance using your preferred client; this post uses SQL* Plus as shown below. For more information about connecting to the RDS Oracle database, see Connecting to Your Sample Oracle DB Instance.

To connect to the Read Replica using SQL* PLUS, enter the following code:

$ sqlplus 'admin@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST= mydbinstance-ro.xxxxxxxx. us-east-1.rds.amazonaws.com)(PORT=1521))(CONNECT_DATA=(SID=MYDB)))'

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 – Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> set lines 250 pages 10000
SQL> show parameter db_uniq

NAME				     	TYPE	 	VALUE
------------------------------------ -----------
db_unique_name			     string	MYDB_B

SQL> select open_mode,database_role from v$database;

OPEN_MODE	     		DATABASE_ROLE
-------------------- ----------------
READ ONLY WITH APPLY 	PHYSICAL STANDBY

Monitoring a Read Replica lag

You can monitor the replication lag on Amazon CloudWatch console, the Amazon RDS console, Oracle Enterprise Manager (OEM), or with Oracle Data Guard views (v$archived_log, v$dataguard_stats, v$dataguard_status).

To monitor on the Amazon RDS console, on the Databases page, select the Read Replica instance.

On the Monitoring tab, search for replica lag.

You can connect to the Read Replica instance using your preferred client; this post uses SQL Developer. For more information about connecting to the RDS Oracle database, see Connecting to Your Sample Oracle DB Instance. See the following code:

You can run the following queries on the Read Replica instance to check more details on the replication lag using Oracle Active Data Guard views. Below is the output from the Read Replica connected as admin user using SQL Developer.

SQL>  Select * from v$standby_event_histogram where upper(name)='APPLY_LAG';

SQL> Select name,value,datum_time,time_computed from v$dataguard_stats where upper(name)='APPLY_LAG';

Promoting a Read Replica

In the case of a disaster, complete the following steps to promote a given cross-region Read Replica as a new standalone production database:

Stop the applications that are accessing the old production database.
Make sure all the available redo logs have been applied to the cross-region Read Replica and there is no replication lag.
Promote the cross-region Read Replica.
Point applications to the listener endpoint of the newly promoted database.
Recreate Read Replicas off of the newly activated standalone database.

To promote the Read Replica manually, complete the following steps:

On the Databases page, select mydbinstance-ro.
From the Actions drop-down menu, choose Promote.
On the Promote Read Replica page, for Enable automatic backups, select Yes.
For Backup retention period, choose 1 days.
For Backup window, select No preference.
Choose Continue.
You now see a confirmation page.
Choose Promote Read Replica.
The process takes approximately 5 minutes. When it is successfully complete, you can verify the status of the master and replica instances on the Amazon RDS console. The role for both shows as Instance.
You can modify the DB instance identifier if needed by selecting the instance and choosing Modify.

Promoting a Read Replica via AWS CLI

To promote a Read Replica on the AWS CLI, enter the following code:

aws rds promote-read-replica \
 --db-instance-identifier mydbinstance-ro\

Planned maintenance

For both the major and minor upgrades, the engine upgrade is applied on the primary DB instance first, followed by applying the engine upgrade on all the replicas in parallel (Please note all the replicas are upgraded in the primary DB instance’s maintenance window). For more information about upgrading the database engine for Amazon RDS for Oracle, see Upgrading the Oracle DB Engine.

Licensing requirements

Amazon RDS for Oracle supports both in-region and cross-region Read Replica features if you’re using the bring your own license (BYOL) model with Oracle Database Enterprise Edition and own a license for the Oracle Active Data Guard option. You have to license Oracle Enterprise Edition and the Oracle Active Data Guard option for the primary DB instance and each Read Replica DB instance.

Compatibility

Amazon RDS for Oracle supports this feature for Oracle Enterprise Edition versions of 12.1 (starting from 12.1.0.2.v10) and higher.

Billing

A Read replica is billed as a standard DB instance and at the same rates. Just like a standard DB instance, the DB instance class of the Read Replica determines the rate per DB instance hour.

For Cross-region Read Replicas, the data transfer between the primary DB instance and the Read Replica DB instance across Regions is billed based on the data transfer rates for the source and destination regions. For more information about instance pricing, storage, data transfer, and regional availability, see Amazon RDS for Oracle pricing.

High availability for the Read Replicas

You can enable Multi-AZ to attain high availability for business-critical reporting workloads running on the Read Replicas. Upon Multi-AZ failovers (either on the primary or the Read Replica environments), the Data Guard replication between the source and the target is automatically reconfigured and no intervention is required.

The following CLI code shows how to enable Multi-AZ on the Read Replica.

## Modify read replica: convert SAZ to MAZ:

aws rds modify-db-instance \
 --db-instance-identifier mydbinstance-ro\
--apply-immediately\
-- multi-az

The instance class and the storage type you use for Read Replicas don’t need to be identical to that of the primary. For example, the in-region Read Replica could use a lower instance class, size, and storage type—just enough to handle the read workloads. If you want to use a cross-region Read Replica for DR, you should mirror it with that of the primary’s instance class, size, and the storage type so that when the cross-region Read Replica is promoted, it can handle all the production workload.

Using Multi-AZ vs. Read Replicas

Amazon RDS for Oracle Read Replicas are complimentary to Amazon RDS Multi-AZ deployments. While the Amazon RDS Multi-AZ provides high availability and data protection, the associated in region Read Replica renders the scalability of read-only workloads. While the cross region Read Replica provides Disaster Recovery in addition to the read scalability. Furthermore, if you want to run business-critical reporting workloads on the Read Replicas, you should enable Multi-AZ on the Read Replica DB instances. The following table compares Amazon RDS Multi-AZ with Amazon RDS for Oracle Read Replicas

Multi-AZ	Read Replicas
Synchronous replication—highly durable with zero data loss	Asynchronous replication—highly scalable
Primary use case High Availability and Disaster recovery within a single AWS region	Primary use case for in region Read Replica is read scalability and for cross region Read Replica it is Disaster Recovery across 2 AWS regions
Only primary instance is active at any point in time	All replicas are active and available for read scaling
Backups can come from secondary	RDS Oracle Read Replicas don’t support backups
Always in two Availability Zones within a Region	Can be within an Availability Zone, cross Availability Zone, or cross-region
Automatic failover when a problem is detected	Can be manually promoted to a standalone database

Summary

Amazon RDS for Oracle supports managed DR and managed scalability of read workloads over a farm of Read Replicas. If you’re an Oracle BYOL customer and need a DR strategy, you can move your self-managed mission-critical databases running in your data centers or on other clouds to Amazon RDS for Oracle and reap the benefits of managed DR and reader farm capabilities.

By moving to a managed database service model, you can take advantage of managed high availability, DR, scalability, and a plethora of other capabilities that Amazon RDS for Oracle provides. You can now focus on what matters to your organization’s business, such as optimizing database design, capacity sizing, and performance tuning. For more information on Amazon RDS for Oracle Read Replicas, see Working with Oracle Read Replicas for Amazon RDS and Working with RDS Read Replicas.

About the Authors

Sameer Malik is a Principal Database Solutions Architect with Amazon Web Services.

Srinagesh Battula is a Principal Product Manager with Amazon Web Services.

AWS Database Blog