By Nassyam Basha and Monowar Mukul
GitHub: https://github.com/asiandevs/PhysicalStandbyWithDGBroker
Introduction
Data Guard is one of the best high availability solutions for the mission-critical database, which provides zero data loss, which falls under Maximum Availability Architecture. We know that there is no change in creating a physical standby database and configuring Data Guard broker procedure since 10g except a few minor changes. In this article, we will deploy standby creation and configuration of Data Guard broker completely through Ansible. We find Ansible to be one of the best simple IT solutions of DevOps.
Why Ansible for Data Guard? – Observations!
Recently we are working with one of the largest banking customers and observed two implementations for their total 60+ production databases.
#1: Data Guard Broker Configuration
All the banks should perform Switchover test quarterly as per the orders from the government. This leads to the need that all the 60+ databases implemented with Data Guard to have a similar configuration, including naming convention. We’ve have observed that to configure Data Guard Broker consumed a minimum of 20-25+ minutes for each database. In our case implementing the Data Guard Broker for all the infrastructure took around 1,500 minutes (25 hours), excluding any troubleshooting issues or human errors. Spending 25 Hours on just Data Guard Broker configuration is definitely a lot of manual efforts, and it may take 8-9 days.
We were interested if we reduce that manual effort if we use Ansible to implement Data Guard broker. Analysis of efforts required to implement this solution included:
- Preparation of the ansible scripts/yml files.
- Ensure all the hosts accessible with control server (ssh setup, i.e., password-less)
- Test with one or two databases broker configuration to ensure the script is working fine.
These efforts of planning and testing are estimated to take at most a workday. Using Ansible automation the advantages include:
- Reduction of time.
- No human errors
- We are achieving the same configuration to all the infrastructure
- We can deploy the Data Guard broker to all the targets with a single script, single launch.
#2: Creating Additional Physical Standby – To Perform a Switchover
Another example with the same customer, which is currently in the Implementation phase is the migration of databases to new Exadata machines. The production Exadata machine [ORA-X1] is at its end-of-life and being decommissioned. There is currently a disaster recovery machine ORA-X2 in place. We created one more physical standby [ORA-X3] for the production database. The cutover will include ORA-X3 becoming the new primary and DR[ORA-X2] will be the standby for this new primary database. In this process, we have to create almost 64 Data Guards for the production databases.
The Database sizes vary from 10GB to 40TB, and the overall setup may consume from 4 hours to 48 hours approximately for each database. Consider, on average, we are spending 10 Hours for each database, and the overall infrastructure will be ready in 27 working days. This procedure is a completely manual method by DBA.
In such scenarios, ansible will work efficiently with lesser window and with no manual work and a lot of time can be saved. In short, for the bulk operations and the huge manual steps then ansible suits well and also minimizes human errors.
Data Guard Deployment with Ansible
In this article, we will create a physical standby database for the production database, and the protection mode will be configured as Maximum performance with real-time apply. The playbook will be able to perform all pre-tasks and also post-tasks, including the Data Guard broker setup.
From the control server, prepare the main playbook as follows, which consists of the hosts – one for production database (ORA-X1) and one for standby database (ORA-X2).
Use the following Data Guard setup reference table throughout this article.
Let’s start reviewing each of the ansible files involved.
Explore the roles of sbdb19c_create – it performs all the prerequisites of Data Guard such as configuring listener with static entry, enabling force logging, creating standby redo log files and etc.
From the tree structure, let’s review the vars/main.yml and how it is prepared. This file stores all the variables of this deployment used. In the phase of deployment, this file will be accessed whenever the variables processed.
Variables:
We’ve prepared all the playbooks and along with the files required for the Data Guard setup. Please note that these are the main configuration files. The technical scripts, which create standby redo logs, creating spfile, password files, can be pulled from GIT.
Screenshots of the Playbook Execution:
Summary
We have explained the multiple use cases of the ansible how it helps in automation of the DBA tasks and the power of DevOps in IT. In this article, we have shown Data Guard deployment and Broker configuration tasks using ansible with step by step and also explained the various playbooks and variables used. Finally, we have also validated the synchronization between primary and standby databases using ansible.
About the Authors
Monowar Mukul is currently working as a Principal Oracle Database Specialist. He has been working as an Oracle DBA consultant over 17 years with Oracle MAA space for both Exadata and non-Exadata Systems including Oracle Cloud Infrastructure Space. He achieved Oracle Certified Master (Oracle 12c DBA, Oracle 12c HA, and Oracle 11g DBA). He worked across various business sectors including tertiary education, energy, government, mining and transport in Australia. Check out his LinkedIn for more information about him and his work achievements.
Nassyam Basha is a Database Administrator. He has more than a decade of experience as a Production Oracle DBA, currently working as a Database Expert with eProseed KSA. He holds a master’s degree in Computer Applications from the University of Madras. He is an Oracle 11g Certified master an Oracle ACE Director. He actively participates in Oracle-related forums such as OTN with a status of Super Hero, Oracle support awarded as “Guru” and acting as OTN Moderator and written numerous articles with OTN and on Toad World. He maintains an Oracle technology-related blog and can be reached through his LinkedIn.
Additional Resources
COLLABORATE 20 will take place April 19-23, 2020 at the Mandalay Bay Resort and Casino in Las Vegas, Nevada! Don’t miss this chance to share inspiration, insights, and solutions with your peers, vendors, and the Oracle team! Register before March 6, 2020, to take advantage of Early Bird pricing.