Voiced by Amazon Polly
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.
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.
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.
Pioneers in Cloud Consulting & Migration Services
- Reduced infrastructural costs
- Accelerated application deployment
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
- Select Create a standby instance (recommended for production usage) in the Multi-AZ deployment option.
- Create database
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.
- Provide Option Group Details and create it.
- Add APEX and APEX-DEV options to the option groups.
- 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.
- Select Apply immediately and click on Modify DB Instance.
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
yum install java
su - apexuser
- Download sql develepor
unzip the sql develepor
export JAVA_HOME=/usr/lib/jvm/jre alias sql="/home/apexuser/sqlcl/bin/sql" export PATH=$JAVA_HOME/bin:$PATH
- Login to RDS using below connection string
- Now unlock the public user account
ALTER USER APEX_PUBLIC_USER IDENTIFIED BY Test1234;
ALTER USER APEX_PUBLIC_USER ACCOUNT UNLOCK;
- 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);
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
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.
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.
WRITTEN BY Deepak Surendran