How do I use read replica to monitor and troubleshoot replication lags for an Amazon RDS for Oracle instance?

Short description

Use the Oracle Active Data Guard License to configure the read replica for your RDS for Oracle instance. If replica is read-only, then an Active Data Guard License is required. When you create a read replica for your instance, the primary and read replica instances operate in the Data Guard maximum performance mode. For more information, see Oracle Data Guard protection modes on the Oracle website. Then, use the read replica to offload the read operations from the primary instance.

You can also promote the read replica as a standalone database (DB) instance in the event of disaster recovery. For more information, see Promoting a read replica to be a standalone DB instance.

Before you configure a read replica for your instance, check the limitations related to version and licenses. For more information, see Miscellaneous considerations for RDS for Oracle replicas.

Resolution

Note the following when you use the read replica for your RDS for Oracle instance:

• Amazon RDS is a managed service. The replication process is managed by an internal RDS_DATAGUARD user. This user is configured when you create a read replica instance.
• You can't stop the managed recovery process from the admin user AWS account.
• You can't configure or modify the Oracle Data Guard Broker for RDS read replica.
• You can't delay the replication of data from a primary instance to a read replica instance.
• Modifications in the option group are reflected to the read replica instance. If the read replicas are configured in the same AWS Region, then the instances are associated with the same option group. For more information, see Option group limitations for RDS for Oracle replicas.

Monitor the replication lag

If the replica can't keep up with the primary instance, then replication might lag. To monitor the replication lag in Amazon CloudWatch, view the Amazon RDS metric ReplicaLag at Read replica instance. This metric shows the replication lag in milliseconds. For more information, see Monitoring read replication and Monitoring Amazon RDS metrics with Amazon CloudWatch.

To monitor and troubleshoot the replication lag in Active Data Guard, complete the following steps:

1. To check the Active Data Guard lag metrics, run the following command:

   sql > SELECT name,value,datum_time,time_computed FROM v$dataguard_stats;

2. To check that there's an archive log gap between the primary and read replica instances, run the following command:

   sql >  SELECT archived.thread# THREAD,max(archived.sequence#) PRIMARY,max(applied.sequence#) STANDBY,(max(archived.sequence#) - max(applied.sequence#)) GAP FROM v$archived_log archived,v$archived_log applied WHERE archived.thread#=applied.thread# AND applied.applied='YES' GROUP BY archived.thread#;

   Note: RDS for Oracle doesn't alert you when there's a replication lag. Create a CloudWatch alarm for the RDS metric ReplicaLag to send notifications whenever the read replica doesn't keep up with the primary instance.

Troubleshoot the replication lag

• If the primary instance is unavailable, then use the Amazon RDS console to check the current state of the instance. Also, check if the instance is accessible from other SQL clients. If the primary instance is unavailable, then the read replica can't keep up with the primary instance. Check Amazon RDS events to find why the primary instance is unavailable. Then, fix the instance to make sure that it's accessible.
• If the primary instance is under a high CPU or memory overload, then you might experience a high transport lag. Monitor the CloudWatch metrics for the instance. Also, use Enhanced Monitoring to identify whether a particular process causes an unusual memory or CPU usage spike. For more information, see Viewing OS metrics in the RDS console. When the CPU or memory load reduces, the replica might keep up with the primary instance. If the overload is as expected on the instance, scale up the instance class, IOPS, or throughput.
• Monitor the alert.log files for the primary instance and read replica to identify alerts or ORA errors. These might affect the transport or apply processes. For more information, see Oracle database log files. Resolve the identified errors to reduce the lag.
• To replicate these changes, you must recreate the standby instance. It's a best practice to update the redo log parameters before you configure the read replica. For more information, see Changing your logging configuration.
• Read replica instances configured with less compute or capacity compared to the primary instance might slow down the application of changes. This results in a replication lag. Monitor the RDS metrics in CloudWatch to check for decreased flow in both instances. If there are no throttled instances in the primary instance and the read replica is throttling, then identify the resource that's throttled. Then, scale up the storage or compute configuration as needed.
• You might experience replica lag when the primary instance has no load, and the read replica instance has a large load that throttles resources. To resolve replica lag, scale up your resources in the replica instance. Or troubleshoot and fix the throttled resources in the read replica instance.

Related information

Working with read replicas for Amazon RDS for Oracle

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