[GUIDE]: Creating an Oracle RDS Read Replica
AWS recently announce the support of Read Replica for Oracle RDS starting with versions 220.127.116.11.v10 and higher 12.1 versions, for all 12.2 versions, and for all 18 versions.
AWS RDS uses Oracle Data Guard to replicate the database changes from source to replica. Updates made to the source DB instance are asynchronously copied to the Read Replica. Normally a Read replica is used to offload heavy read traffic for an application. The load can be reduced on the source database by routing read queries from the applications to the Read Replica.
Creation of read replica doesn’t require an outage for the source RDS. Amazon RDS sets the necessary parameters and permissions for the source DB instance and the Read Replica without any service interruption. A snapshot is taken of the source DB instance, and this snapshot becomes the Read Replica. No outage occurs when a read replica instance is deleted.
Pre-requisites for configuring the replica in Oracle
- Must enable automatic backups on the source database – Set backup retention to value other than zero.
- Enable force logging mode on the database
exec rdsadmin.rdsadmin_util.force_logging(p_enable => true);
- If there are any changes to be done on source database’s redo logs like changing the logsize, it should be done before the read replica is created. Modifications after the replica creation can cause the online redo logging configuration to get out of sync with the standby logging configuration.
- Make sure that the setting of the max_string_size parameter is same on the source DB instance and the Read Replica if using different parameter groups
- Login to AWS RDS console and select the source database
- Click on ActionsàCreate read replica
- Choose the instance specifications for the read replica. Recommended to use the same instance class and storage type as the source
- For Multi-AZ deployment, choose Yes to create a standby of your replica in another Availability Zone for failover support for the replica.
- Choose the DB instance Identifier for the read replica
- Choose Create read replica
Amazon RDS for Oracle keeps a minimum of two hours of transaction logs on the source DB instance. Logs are purged from the source after two hours or after the archivelog retention hours setting has passed, whichever is longer.
A Read Replica is created with the same storage type as the source DB instance. However, you can create a Read Replica that has a different storage type from the source DB.
We can create up to five Read Replicas from one source DB instance. The Oracle DB engine version of the source DB instance and all of its Read Replicas must be the same.
When the source RDS is upgraded to higher version, Amazon RDS upgrades the Read Replicas immediately after upgrading the source DB instance, regardless of a Read Replica’s maintenance window
Oracle read replica can neither be stopped nor another read replica can be created from the existing replica (cascading read replicas not supported for Oracle). Also, the source RDS cannot be stopped if the read replica is running.
Similar to another engines, read replica can be promoted into standalone DB instance. When you promote a Read Replica, the DB instance is rebooted before it becomes available.
Replication monitoring can be done at the AWS RDS console and the database level
The ReplicaLag metric is the sum of the Apply Lag value and the difference between the current time and the apply lag’s DATUM_TIME value. The DATUM_TIME value is the last time the Read Replica received data from its source DB instance.
Data dictionary views used for checking the replication lag:
- V$ARCHIVED_LOG – Shows which commits have been applied to the Read Replica.
- V$DATAGUARD_STATS – Shows a detailed breakdown of the components that make up the replicaLag
- V$DATAGUARD_STATUS – Shows the log output from Oracle’s internal replication processes.
- Must have an Active Data Guard license
- Oracle Read Replicas are only available on the Oracle Enterprise Edition (EE) engine
- Oracle Read Replicas are available for Oracle version 18.104.22.168.v10 and higher 12.1 versions, for all 12.2 versions, and for all 18 versions
- Oracle Read Replicas are only available for DB instances on the EC2-VPC platform
- Oracle Read Replicas are only available for DB instances running on DB instance classes with two or more vCPUs
- Amazon RDS for Oracle does not intervene to mitigate high replica lag between a source DB instance and its Read Replicas. Ensure that the source DB instance and its Read Replicas are sized properly, in terms of compute and storage, to suit their operational load
- Amazon RDS for Oracle Read Replicas must belong to the same option group as the source database. Modifications to the source option group propagate to Read Replicas
- Cross-region Read Replicas currently not supported
- The replica process in Oracle cannot stopped. Only option is to delete the read replica
- Amazon RDS doesn’t support circular replication
- Currently no manual snapshots of Amazon RDS for Oracle Read Replicas can be created or enable automatic backups for them