Oracle Binary Database Patching

Overview:

• This is an automation application to apply quarterly oracle security patches to database binaries on Solaris 10
• This application patches both 11.2.0.4 and 12.1.0.2 binaries - there is conditional logic which chooses the appropriate tasks to run for each binary version yet many of the steps overlap
• This entire automation application boils down to 4 inputs:
  1. oracle_binary_version - the oracle binary version to be patched
  2. opatch_latest_zip - the opatch utility software to be installed in case an upgrade is needed
  3. oneoff_latest_zip - the quarterly security patch to be applied
  4. patch_identifier - the string used to check proper patch application to databases
• These inputs can be consumed by a Tower survey instead of group_vars in Ansible Core.

Dependency List:

• Input opatch and one-off zips are present on the sql server in /home/oracle/Patching
• Other required oracle scripts present on sql server in /home/oracle/Patching
• ansible_python_interpreter points to a python distribution with python >= v2.6 and pexpect >= v3.3 installed for that distribution. This is by default /usr/bin/python in group_vars but can be overridden on the group/host level in inventory by setting the ansible_python_interpreter variable
• Sudo configured correctly in ansible.cfg - currently sudo_exe = /usr/local/bin/sudo. This can be overridden on the group/host level in inventory by setting the ansible_sudo_exe variable
• Path to bash is /bin/bash
• All relevant Oracle listeners are started and running prior to content execution
• $ORACLE_HOME/bin/sqlplus is the path to run the sqlplus command
• Interactive Oracle scripts do not generate prompts beyond which are covered by current pexpect responses. This one would require some extra development
• Any individual Oracle script does not take > 1 hour to complete (current timeout setting). This setting can be adjusted in /library/expect.py

Inventory:

Don't run this in parallel! There should be two inventory subgroups for any patching inventory

1. oracle_patching - oracle server(s) to patch
2. tower_server - necessary for email error reporting | Note: ansible_user must be set in inventory. This user must be able to sudo to the oracle user

Tags=Roles=Documentation:

1. prerequisite - running tags=prerequisite with patch_oracle.yml is always safe. This checks basic Ansible software requirements and discloses what databases are targeted with the patch. This content lives in the Ansible prep role.
2. prep - tags=prerequisite,prep does some oracle patching related checks ensuring presence of required files as well as assessing the opatch utility version.
3. patching - tags=prequisite,prep,patching does the core patching process where listeners are brought down, databases are stopped, and binaries are patched. This should be used sparingly or not at all.
4. post - tags=prequisite,prep,patching,post is the same thing as running the playbook tagless. Post ensures that the binary-relevant databases reflect the newly applied patch.

Significance: Running just the prerequisite tasks can be done ahead of patching. Running prerequisite and prep ahead of time should make you extremely confident that the patching will work and would give a database team time to remedy one-off patching conflicts. Beyond this you should just run the playbook tagless.

Error Handling:

There were 4 spots identified as high points of failure. These are wrapped in Ansible rescue blocks that send email notifications to {{ email_distribution_group }} based on a failure. High-failure of points include:

1. The opatch utility upgrade failed (prep)
2. There is a flagged one-off patch conflict that will interfere with patching (prep)
3. The command 'opatch apply' failed (patching)
4. The applied patch didn't apply to one or all databases (post)

Significance: In all cases, the strategy here is to send an email to {{ email_distribution_group }} defined in group_vars. This is why if possible running the prerequisite and prep content ahead of time will mitigate most of what could go wrong. The job logs will provide more insight to the root problem.

Testing! A note to a future developer:

IMPORTANT! Deprecation warnings are suppressed in ansible.cfg, but the SUDO_EXE setting used with the Solaris /usr/local/bin/sudo location will be deprecated in Ansible v2.8.