Automating Oracle Patching With an Ansible Module

Editor’s Note: Because our bloggers have lots of useful tips, every now and then we bring forward a popular post from the past. We originally published today’s post on February 13, 2019.
This blog post has been on my To Do list for quite some time. I finally found some time to write about it. I will try to keep it short and clear.
In 2017, when I discovered Ansible, I developed a custom Ansible module for Oracle patching. The idea was to learn Ansible and, at the same time, make something valuable.
I wrote the module in Python and the current version is +/- 2000 lines of code (including comments :) ). I come from a development environment (Java, PHP, .NET) and this project was pretty interesting to me as it gave me an opportunity to get familiar with Python.
Synopsis
The main purpose of the module is to automate the patching process of Oracle database and grid infrastructure binaries with PSUs, BPs and RUs released by Oracle.
One-off patches: The module won’t work with one-off patches as I didn’t design it for that, although you can extend it to support one-off patches.
The module will use OPatchAuto if the Oracle home it’s patching is grid infrastructure, otherwise, it will use standard OPatch steps.
The patching is customizable via a role’s variables definition. For example, you can run just prerequisites without applying the patch, patch binaries without database dictionary changes, skip the OJVM patch, etc.
The module supports 11g, 12c and 18c database versions. It should work properly on 10g as well, but I haven’t tested it.
Expected actions performed by the module:
- Identify which database instances, listeners and ASM instances are running
- Shut down all listeners and database instances only if patching the home from which services are running
- Start up all previously stopped services after it finishes with patching*
- Skip databases which are not in READ WRITE state**
- Identify if a given database is in STANDBY or PRIMARY role***
- Always patches GI homes with OPatchAuto
- Always patches DB homes with OPatch
- Make multiple restarts of the databases and listeners during the process
*** Databases in STANDBY role are not patched
Note: If you encounter an error and restart the process, the module will not automatically start previously stopped services. The module will note stopped services at the beginning of the process and it will leave the services stopped at the end of execution. Due to the nature of how Oracle performs patching, in some cases if something breaks, you might need to intervene manually. In other words, if you restart the Ansible process don’t expect it to continue from where it stopped.
OPatch has support for the “resume” functionality. That’s something I can take a look at implementing into the module. As of now, however, there is no such option.
Real Application Clusters
The module supports Real Application Clusters (RAC). All you need to do is specify a group of hosts.
There is one tricky moment with clusters; when a node patching is complete and when the CRS is started, the operation is asynchronous, meaning the module will get an OK state when it executes crsctl start crs command. At that point, from the module perspective, CRS is up and running. That’s why I’ve implemented a check every 10 seconds with a timeout of 10 minutes during which the CRS is checked to see if all services are online prior to continuing to patch other nodes. By default the module will prompt the user to provide the root password. It’s necessary for OPatchAuto and it’s only applicable when patching grid infrastructure software.
Logging
During the whole process, all steps and output are logged in a log file on the target machines.
Currently, there are two logging modes: standard (default) and debug. You switch between the modes with True / False value for the debug variable. In debug mode, a more descriptive output is written in the log file.
As an example, if you run OJVM patching with debug mode for 11g you would see the entire output of the post install SQL script that’s executed.
At the end of the patching, the log file is copied over to the initial control machine. So, if you patch multiple nodes you will get all log files.
How to run
There are three steps you need to take prior to running the playbook:
1. Define the patch binaries location. The patch binaries location is defined with “swlib_path” variable in “vars/global.yml.”
2. Define patch metadata. You need to define each patch metadata in “vars/patch_dictionary/patch_dict.yml.” The format is as follows:
-- 25437795: -> patch_id (it's in the name of the file you download from Oracle) patch_proactive_bp_id -> patch proactive bundle patch id (if it's bundle patch) patch_gi_id: -> GI patch ID. If it's GI only, the "patch_gi_id" has the same value as "patch_id" patch_db_id -> DB patch ID patch_ocw_id -> OCW patch ID (applicable if the patch is COMBO patch) patch_ojvm_id -> OJVM patch ID patch_acfs_id: -> ACFS patch ID patch_dbwlm_id: -> DBWLM path ID patch_dir: -> patch directory (directory where patch file is extracted) file: -> patch file name (not used currently) only_oh: -> whether the patch is for OH binaries only desc: -> patch description (usually should contain the patch name) --
3. Define Oracle homes and databases to be patched in vars/main.yml file. For example:
-- # # List of oracle homes and databases to patch. # ora_home_list: - oracle_owner: -> OS owner of the oracle binaries oracle_home_path: -> OH OS path oratab_file: -> Absolute path for oratab file. This can be ignored if the global value is set. run_only_checks: -> Indicator whether to run onl prereq checks against OH patch_id: -> Patch ID of the patch which is to be applied. This module needs to find a match in "vars/patch_dictionary/patch_dict.yml" patch_only_oh: -> Indicator whether to patch only OH without the databases (True/False) patch_ojvm: -> Indicator whether to apply OJVM patch (applicable if the patch is COMBO) (True/False) patch_db_all: -> Indicator whether to apply the patch on all databases after patching the OH ("patch_only_oh" has precedence over "patch_db_all") (True/False) patch_db_list: "" -> Comma separated list (in quotes!) of specific databases to patch ("patch_db_all" has precedence over "patch_db_list") host: -> It allows the user to specify a mapping to specific host for which this list entry is valid. It's applicable only if the playbook is executed against group of hosts backup_oh: -> Indicator whether to backup oracle home binaries (True/False) skip: -> Main indicator whether to skip this item list or not debug: -> Enables debug mode (True/False) --
Once you’ve defined the necessary variables, you start the playbook with:
ansible-playbook path_to_playbook -k
The -k option is not mandatory if you use SSH keys for authentication.
Required packages
For this module to work, you need to install “pexpect” package on the target machine. You need to do this because in some (most) cases OPatch will (prompt) ask questions. The module uses “pexpect” to populate OPatch answers.
If the required package is missing, the module will fail with a message: module fail: Required “pexpect” (RPM) library not found.
Module download location
You can download all playbook files along with the module from here (GitHub).
Feel free to test and use the module and let me know if you need any assistance or encounter any bugs.
You can post questions and / or bugs here (GitHub repository issues page).
Is there room for improvement?
Of course there is; that’s something I’ll consider for future updates of the module.
Example run
As a final note, here’s an example where I’ve patched 18c binaries with patch: 28822489 — Database Release Update 18.5.0.
patch_dict.yml (patch metadata definition):
28822489: patch_proactive_bp_id: patch_gi_id: patch_db_id: 28822489 patch_ocw_id: patch_ojvm_id: patch_acfs_id: patch_dbwlm_id: patch_dir: 28822489 file: p28822489_180000_Linux-x86-64.zip only_oh: False desc: "Database Release Update 18.5.0"
ora_home_list variable definition in main.yml:
ora_home_list: - oracle_owner: oracle oracle_home_path: /u01/app/oracle/18.3.0.0/db1-base oratab_file: run_only_checks: debug: False patch_id: 28822489 patch_only_oh: False patch_ojvm: True patch_db_all: True patch_db_list: "" # Comma separated list of db_unique_names host: backup_oh: False # Indicator whether to backup oracle home skip: False
Playbook run
[ansible@ansible-control ansible-orapatch]$ ansible-playbook orapatch.yml -k SSH password: -->[Applicable if you patch Grid Infrastructure]<-- Enter root password (press enter to skip): Enter root password again (press enter to skip): PLAY [Patch oracle software] ********************************************************************************************************************************************************************************************************** TASK [Gathering Facts] **************************************************************************************************************************************************************************************************************** ok: [ora18c] TASK [assert] ************************************************************************************************************************************************************************************************************************* ok: [ora18c] => { "changed": false, "msg": "All assertions passed" } TASK [orapatch : [SYSTEM] Include vars] *********************************************************************************************************************************************************************************************** ok: [ora18c] TASK [orapatch : [SYSTEM] Push sql scripts] ******************************************************************************************************************************************************************************************* ok: [ora18c] TASK [orapatch : [SYSTEM] Ensure 'orapatch' log file exists] ************************************************************************************************************************************************************************** ok: [ora18c] TASK [orapatch : [SYSTEM] Start logger session] *************************************************************************************************************************************************************************************** ok: [ora18c] TASK [orapatch : Backup oracle home] ************************************************************************************************************************************************************************************************** skipping: [ora18c] => (item=[0, {u'patch_db_list': u'', u'skip': False, u'run_only_checks': None, u'patch_id': 28822489, u'oracle_home_path': u'/u01/app/oracle/18.3.0.0/db1-base', u'patch_db_all': True, u'debug': False, u'oracle_owner': u'oracle', u'patch_ojvm': True, u'patch_only_oh': False, u'host': None, u'oratab_file': None, u'backup_oh': False}]) TASK [orapatch : Check OPatch minimum version] **************************************************************************************************************************************************************************************** ok: [ora18c] => (item={u'patch_db_list': u'', u'skip': False, u'run_only_checks': None, u'patch_id': 28822489, u'oracle_home_path': u'/u01/app/oracle/18.3.0.0/db1-base', u'patch_db_all': True, u'debug': False, u'oracle_owner': u'oracle', u'patch_ojvm': True, u'patch_only_oh': False, u'host': None, u'oratab_file': None, u'backup_oh': False}) [WARNING]: Module did not set no_log for root_password TASK [orapatch : Check conflicts against OH] ****************************************************************************************************************************************************************************************** ok: [ora18c] => (item={u'patch_db_list': u'', u'skip': False, u'run_only_checks': None, u'patch_id': 28822489, u'oracle_home_path': u'/u01/app/oracle/18.3.0.0/db1-base', u'patch_db_all': True, u'debug': False, u'oracle_owner': u'oracle', u'patch_ojvm': True, u'patch_only_oh': False, u'host': None, u'oratab_file': None, u'backup_oh': False}) TASK [orapatch : Patch OH] ************************************************************************************************************************************************************************************************************ ok: [ora18c] => (item={u'patch_db_list': u'', u'skip': False, u'run_only_checks': None, u'patch_id': 28822489, u'oracle_home_path': u'/u01/app/oracle/18.3.0.0/db1-base', u'patch_db_all': True, u'debug': False, u'oracle_owner': u'oracle', u'patch_ojvm': True, u'patch_only_oh': False, u'host': None, u'oratab_file': None, u'backup_oh': False}) TASK [orapatch : Patch DB] ************************************************************************************************************************************************************************************************************ ok: [ora18c] => (item={u'patch_db_list': u'', u'skip': False, u'run_only_checks': None, u'patch_id': 28822489, u'oracle_home_path': u'/u01/app/oracle/18.3.0.0/db1-base', u'patch_db_all': True, u'debug': False, u'oracle_owner': u'oracle', u'patch_ojvm': True, u'patch_only_oh': False, u'host': None, u'oratab_file': None, u'backup_oh': False}) TASK [orapatch : Patch OH OJVM] ******************************************************************************************************************************************************************************************************* ok: [ora18c] => (item={u'patch_db_list': u'', u'skip': False, u'run_only_checks': None, u'patch_id': 28822489, u'oracle_home_path': u'/u01/app/oracle/18.3.0.0/db1-base', u'patch_db_all': True, u'debug': False, u'oracle_owner': u'oracle', u'patch_ojvm': True, u'patch_only_oh': False, u'host': None, u'oratab_file': None, u'backup_oh': False}) TASK [orapatch : Patch DB OJVM] ******************************************************************************************************************************************************************************************************* ok: [ora18c] => (item={u'patch_db_list': u'', u'skip': False, u'run_only_checks': None, u'patch_id': 28822489, u'oracle_home_path': u'/u01/app/oracle/18.3.0.0/db1-base', u'patch_db_all': True, u'debug': False, u'oracle_owner': u'oracle', u'patch_ojvm': True, u'patch_only_oh': False, u'host': None, u'oratab_file': None, u'backup_oh': False}) TASK [orapatch : [SYSTEM] End logger session] ***************************************************************************************************************************************************************************************** ok: [ora18c] TASK [orapatch : [SYSTEM] Fetch orapatch logfile] ************************************************************************************************************************************************************************************* changed: [ora18c] PLAY RECAP **************************************************************************************************************************************************************************************************************************** ora18c : ok=14 changed=1 unreachable=0 failed=0