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:

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