Multi-AZ DR Testing for Oracle RDS using Oracle Application Express

Overview

Multi-AZ installations can offer high availability and failover support for DB instances. This failover support is offered by Amazon RDS using a variety of mechanisms. The Amazon failover technology is used for multi-AZ deployments of MariaDB, MySQL, Oracle, and PostgreSQL DB instances. SQL Server Database Mirroring (DBM) or Always On Availability Groups (AGs) are used by Microsoft SQL Server DB instances.

Amazon RDS automatically creates and maintains a synchronous standby replica in a different Availability Zone when a Multi-AZ DB instance is deployed. High availability DB instance operation helps improve availability during planned system maintenance.

Creating a Multi-AZ DB deployment specifies the Multi-AZ feature when creating a DB instance. You can also convert an existing DB instance to a Multi-AZ DB deployment by modifying it and setting the Multi-AZ option.

Pioneers in Cloud Consulting & Migration Services

Reduced infrastructural costs
Accelerated application deployment

Get Started

Events that causes the primary DB instance automatically switches to the standby replica

The RDS Multi-AZ instance’s primary host is unhealthy.
Due to a loss of network connectivity, the main host of the RDS Multi-AZ instance cannot be reached.
The primary RDS Multi-AZ instance is occupied and unresponsive.
The RDS Multi-AZ instance’s primary host’s storage volume failed.
The user asked for a DB instance failover.

Steps to Create Oracle RDS with Multi-AZ Option and Install the Basic Requirement for Oracle APEX

Oracle APEX Application contains the following elements:

A repository where the metadata for APEX components and apps is kept. The repository comprises the tables, indexes, and other objects installed in your Amazon RDS DB instance.
An HTTP listener that controls communication with Oracle APEX clients. The listener is located on a different host, such as your desktop computer, an Amazon EC2 instance, or an on-premises server at your business. The listener receives connections from web browsers, delivers them to an Amazon RDS DB instance for processing, and then returns the processed connections to the browsers after receiving the repository’s results.

Step 1 – Create Oracle RDS with Multi-AZ Option

Go to RDS Console and select Create Database option
Choose Standard Create
Select Oracle as the Engine type
Select Amazon RDS as the Database management type
Choose Oracle Enterprise Edition as Database Edition
Select the latest version as the Engine version
Choose Production as Templates

step1

Select Create a standby instance (recommended for production usage) in the Multi-AZ deployment option.

step1b

Create database

step1c

Step 2 – Adding the Amazon RDS APEX options

Create a new option group
Go to Amazon RDS Console and select Option Groups,
Click on create group.

step2

Provide Option Group Details and create it.

step2b

Add APEX and APEX-DEV options to the option groups.

step2c

step2d

Once we add these options to our Option groups, we must add them to our Oracle RDS.
Go to the Amazon RDS console and select our Amazon RDS and click on Modify, select the option group which we created.

step2e

Select Apply immediately and click on Modify DB Instance.

step2f

Step 3 – Installing basic requirements for APEX Installation

Create an EC2 Instance with Amazon Linux 2 and log in to the Instance,
Run the following commands

useradd -d /home/apexuser apexuser
passwd apexuser
yum install java

useradd -d /home/apexuser apexuser

passwd apexuser

yum install java

step3

java -version
su - apexuser

1 2	java -version su - apexuser

Download sql develepor
https://download.oracle.com/otn_software/java/sqldeveloper/sqlcl-22.4.0.342.1212.zip

unzip the sql develepor
unzip sqlcl-22.4.0.342.1212.zip
export JAVA_HOME=/usr/lib/jvm/jre alias sql="/home/apexuser/sqlcl/bin/sql" export PATH=$JAVA_HOME/bin:$PATH
sql /nolog

unzip the sql develepor

unzip sqlcl-22.4.0.342.1212.zip

export JAVA_HOME=/usr/lib/jvm/jre alias sql="/home/apexuser/sqlcl/bin/sql" export PATH=$JAVA_HOME/bin:$PATH

sql /nolog

conn admin/Test1234@drdbinstance1.cwtekktkfr7s.us-east-1.rds.amazonaws.com:1521/DEMODRDB

1	conn admin/Test1234@drdbinstance1.cwtekktkfr7s.us-east-1.rds.amazonaws.com:1521/DEMODRDB

step3b

Now unlock the public user account

ALTER USER APEX_PUBLIC_USER IDENTIFIED BY Test1234;
ALTER USER APEX_PUBLIC_USER ACCOUNT UNLOCK;

1 2	ALTER USER APEX_PUBLIC_USER IDENTIFIED BY Test1234; ALTER USER APEX_PUBLIC_USER ACCOUNT UNLOCK;

step3c

We must configure RESTful services in APEX (not needed for APEX 4.1.1.V1). For that, we need to connect to our RDS using the master user. Then please execute the below commands to run the stored procedure.
We need to create new database accounts for these users, which we can achieve by using the apex_rest_config.sql script, which will run by the stored procedure.

Please run the below commands to run the store procedure

EXEC rdsadmin.rdsadmin_run_apex_rest_config('apex_listener_password', 'apex_rest_public_user_password');
exec rdsadmin.rdsadmin_util.grant_sys_object('DBA_OBJECTS', 'MASTER_USER', 'SELECT', true);
exec rdsadmin.rdsadmin_util.grant_sys_object('DBA_ROLE_PRIVS', 'MASTER_USER', 'SELECT', true);
exec rdsadmin.rdsadmin_util.grant_sys_object('DBA_TAB_COLUMNS', 'MASTER_USER', 'SELECT', true);
exec rdsadmin.rdsadmin_util.grant_sys_object('USER_CONS_COLUMNS', 'MASTER_USER', 'SELECT', true);
exec rdsadmin.rdsadmin_util.grant_sys_object('USER_CONSTRAINTS', 'MASTER_USER', 'SELECT', true);
exec rdsadmin.rdsadmin_util.grant_sys_object('USER_OBJECTS', 'MASTER_USER', 'SELECT', true);
exec rdsadmin.rdsadmin_util.grant_sys_object('USER_PROCEDURES', 'MASTER_USER', 'SELECT', true);
exec rdsadmin.rdsadmin_util.grant_sys_object('USER_TAB_COLUMNS', 'MASTER_USER', 'SELECT', true);
exec rdsadmin.rdsadmin_util.grant_sys_object('USER_TABLES', 'MASTER_USER', 'SELECT', true);
exec rdsadmin.rdsadmin_util.grant_sys_object('USER_VIEWS', 'MASTER_USER', 'SELECT', true);
exec rdsadmin.rdsadmin_util.grant_sys_object('WPIUTL', 'MASTER_USER', 'EXECUTE', true);
exec rdsadmin.rdsadmin_util.grant_sys_object('DBMS_SESSION', 'MASTER_USER', 'EXECUTE', true);
exec rdsadmin.rdsadmin_util.grant_sys_object('DBMS_UTILITY', 'MASTER_USER', 'EXECUTE', true);

EXEC rdsadmin.rdsadmin_run_apex_rest_config('apex_listener_password', 'apex_rest_public_user_password');

exec rdsadmin.rdsadmin_util.grant_sys_object('DBA_OBJECTS', 'MASTER_USER', 'SELECT', true);

exec rdsadmin.rdsadmin_util.grant_sys_object('DBA_ROLE_PRIVS', 'MASTER_USER', 'SELECT', true);

exec rdsadmin.rdsadmin_util.grant_sys_object('DBA_TAB_COLUMNS', 'MASTER_USER', 'SELECT', true);

exec rdsadmin.rdsadmin_util.grant_sys_object('USER_CONS_COLUMNS', 'MASTER_USER', 'SELECT', true);

exec rdsadmin.rdsadmin_util.grant_sys_object('USER_CONSTRAINTS', 'MASTER_USER', 'SELECT', true);

exec rdsadmin.rdsadmin_util.grant_sys_object('USER_OBJECTS', 'MASTER_USER', 'SELECT', true);

exec rdsadmin.rdsadmin_util.grant_sys_object('USER_PROCEDURES', 'MASTER_USER', 'SELECT', true);

exec rdsadmin.rdsadmin_util.grant_sys_object('USER_TAB_COLUMNS', 'MASTER_USER', 'SELECT', true);

exec rdsadmin.rdsadmin_util.grant_sys_object('USER_TABLES', 'MASTER_USER', 'SELECT', true);

exec rdsadmin.rdsadmin_util.grant_sys_object('USER_VIEWS', 'MASTER_USER', 'SELECT', true);

exec rdsadmin.rdsadmin_util.grant_sys_object('WPIUTL', 'MASTER_USER', 'EXECUTE', true);

exec rdsadmin.rdsadmin_util.grant_sys_object('DBMS_SESSION', 'MASTER_USER', 'EXECUTE', true);

exec rdsadmin.rdsadmin_util.grant_sys_object('DBMS_UTILITY', 'MASTER_USER', 'EXECUTE', true);

step3d

Conclusion

Failover Mechanism is just theoretical knowledge for many of us, and we don’t have the exact working knowledge of how this mechanism works. In this blog, we created Oracle RDS using the Multi-AZ option and Installed the Oracle Apex Requirement. In my Next Blog, we will review the Oracle Apex application’s Installation steps and Failover mechanism.

Making IT Networks Enterprise-ready – Cloud Management Services

Accelerated cloud migration
End-to-end view of the cloud environment

Get Started

About CloudThat

CloudThat is an official AWS (Amazon Web Services) Advanced Consulting Partner and Training partner and Microsoft Gold Partner, helping people develop knowledge of the cloud and help their businesses aim for higher goals using best-in-industry cloud computing practices and expertise. We are on a mission to build a robust cloud computing ecosystem by disseminating knowledge on technological intricacies within the cloud space. Our blogs, webinars, case studies, and white papers enable all the stakeholders in the cloud computing sphere.

Drop a query if you have any questions regarding Oracle RDS, I will get back to you quickly.

To get started, go through our Consultancy page and Managed Services Package, CloudThat’s offerings.

FAQs

1. Are there any performance implications of running my DB instance as a Multi-AZ deployment?

ANS: – Due to the synchronous data replication on your behalf, you can experience longer latencies than you would with a typical DB instance deployment in a single Availability Zone.

2. Can I use the standby when running my database instance as a Multi-AZ deployment for read or write operations?

ANS: – A Multi-AZ standby is unable to fulfill read requests. Instead of promoting read scaling, multi-AZ installations are intended to improve database availability and durability. As a result, synchronous replication between primary and standby is used by the feature. Our method prevents accessing the standby for read or writes operations while ensuring that the primary and the standby are always in sync.

3. How do I deploy a Multi-AZ RDS?

ANS: – Choose “Yes” for “Multi-AZ Deployment” when launching a DB Instance through the AWS Management Console to create a Multi-AZ DB instance deployment. Alternatively, using the Amazon RDS APIs, use the CreateDBInstance API and specify the “Multi-AZ” argument to “true.” Set the Multi-AZ parameter to true in the ModifyDBInstance API or the AWS Management Console to change an existing standard (single-AZ) database instance to a Multi-AZ one.