Ansible for Oracle Database Clones

/in , /by

Ansible LogoA frequent job requirement of all Oracle DBA’s is cloning a database or entire application for development or testing purposes. In previous years, many DBA’s performed manual clones by cutting and pasting commands they stored in text documents. Craftier DBA’s learned that their time was better spent automating the task with shell scripts. While better than manual cloning, shell scripts were usually not consistent or did not handle errors very well. These days, there are several configuration and orchestration tools that can be leveraged to perform environment clones or refreshes. One such tool is Ansible®, and using Ansible for Oracle Database clones is very intuitive for the typical DBA or sysadmin mindset.

Shadandy has been using Ansible to clone databases for years. It can be used to clone standalone on-premises databases as well as Amazon RDS for Oracle instances. It can also clone entire Oracle E-Business Suite environments spanning multiple nodes.

In one instance, Shadandy was able to help a customer who was having problems with the DBA resources provided by one of the huge offshore/outsourcing companies. We will not say which one, but our experience with dealing with any of the offshore companies has been similar. The offshore DBA’s were manually executing clone steps that had been spoon fed to them, but the environment refreshes were never performed consistently. Why automate when you can bill more hours to sit there and cut-n-paste? Then get paid to do it again when things were not done correctly the first time.

Our solution was to remove the offshore DBA’s from the equation by automating all the steps they were cutting and pasting into a single, cohesive Ansible playbook. Do the playbooks require upkeep and maintenance? Yes. Does maintaining the Ansible playbooks require as much time as watching a database clone for hours on end? No. Are environment refreshes performed in a more timely and consistent manner? Yes. Can they be scheduled to execute automatically? Yes. What’s not to like, unless you are an offshore company getting paid for time a butt is in a seat?

Our typical Ansible automation framework starts with a host inventory file for each environment. A separate inventory file for each environment avoids mistakenly running a command against all the environments at the same time. An example inventory file for an OADEV1 EBS environment is something like this:

[oa_all_web]
web1.yourdomain.net
web2.yourdomain.net

[oa_all_ext]
ext1.yourdomain.net
ext2.yourdomain.net

[oa_all_ccm]
ccm1.yourdomain.net
ccm2.yourdomain.net

[oa_all_db]
oadev1db.yourdomain.net

[oa_all:children]
oa_all_ext
oa_all_web
oa_all_ccm
oa_all_db

[oadev_all:children]
oa_all

[oadev1:children]
oa_all

The inventory allows the Ansible playbook to perform different operations for internal web nodes, external web nodes, concurrent manager nodes, and the database server. If your environment contains other 3rd party applications such as Kofax MarkView, the inventory can be crafted to handle them.

Additional group configuration files drive what Ansible does as it connects to each node. For an OADEV1 environment, the Ansible playbook reads variables from the following files:

group_vars/oadev1
group_vars/oadev_all
group_vars/oa_all/custom_top_dirs
group_vars/oa_all/dba_directories
group_vars/oa_all/oa_all_default
group_vars/oa_all/oa_all_vault
group_vars/oa_all/stage_dirs

Every environment will be a little different, but you can see the flexibility of storing configurations for each environment. An added perk is that Ansible provides the ansible-vault utility to encrypt sensitive files.

While we are not going to provide the exact playbook code, an EBS environment refresh can be broken down into individual playbooks and/or tasks. Here is an example ebs_autoconfig_all.yml playbook that calls a task to run Autoconfig on all application nodes:

---
- include: ebs_autoconfig_ext.yml
- include: ebs_autoconfig_web.yml
- include: ebs_autoconfig_ccm.yml

Breaking down the playbook above, the ebs_autoconfig_web.yml playbook runs Autoconfig on all internal web nodes:

---
- name: Playbook used to run Autoconfig on the internal web nodes of an environment.
hosts: "oa_all_web"
any_errors_fatal: true
user: "{{ appl_os_user }}"
vars:
apps_pass: "{{vault_ebs_environment[env_name|lower]['database']['credentials']['apps']}}"
tasks:
- include: tasks/ebs_app_run_autoconfig.yml

The actual tasks/ebs_app_run_autoconfig.yml Autoconfig task is:

---
- name: Run Autoconfig.
shell: '. ~/.profile; . {{ appl_shared}}/apps/apps_st/appl/APPS{{ env_name }}_{{ ansible_hostname }}.env;perl ${AD_TOP}/bin/adconfig.pl contextfile=${CONTEXT_FILE} appspass={{ apps_pass }} -parallel'
no_log: True

To execute the Autoconfig playbook, a DBA can simply execute a single command:

ansible-playbook -i hosts.oadev1 ebs_autoconfig_all.yml

Similar tasks and playbooks can be executed for SQL scripts, RMAN duplicates, SAN snapshots, and all other operations required to perform a database or application clone in your environment. Leveraging Ansible for automation, Shadandy was able to reduce a 10-node EBS environment refresh from one or more days (if done correclty the first time) to 3-4 hours. We even used Ansible to fully automate their database and EBS code deployments.

If your company requires database or EBS environment configuration or clone automation, please contact Shadandy to see how we can be of assistance.