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
[[email protected] 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
8 Comments. Leave new
Thanks Ivica for this informative article.
i have the following questions if you don’t mind:
what if i perform automation for database patching using normal shell scripting ….what do you think Ansible will provide more advantages in this case?
Also, there are now changes many in the community have noticed with Oracle patching….for example when i “manually” patch Oracle database the patch needs to be in a directory “owned” by the Oracle linux user….also the latest Opatch utility is required to be placed under $ORACLE_HOME…..is this covered your Ansible module ?
Regards,
Emad Al-Mousa
Hi Emad,
If you already have developed shell script for patching, that’s fine. Ansible as automation framework, one of its benefits is that it gives you more control of how you structure the tasks (steps). It makes whole management of the playbook much easier and in more readable. Changes to the steps can be short and easy, does not require code changes etc. (I am talking here in general for Ansible).
There are also Ansible’s pre-defined (out of the box) modules which you can use for various tasks. My point is, the outcome would be the same regardless if you use shell script or Ansible approach. But, if you need to make some changes to the shell script you will have to effectively write/change code.
Regarding your other two statements, I always store OPatch in ORACLE_HOME directory, in fact I have never used it outside ORACLE_HOME. My module also use OPatch from ORACLE_HOME.
Regarding the patch binaries ownership, also I always (as standard) configure the owner to be the same owner as it is for the ORACLE_HOME. I think I have also encountered some problems if ownership is not correctly set.
The module does not cover that part (updating OPatch and changing patch binary files ownership). It expects that to be already configured properly. In fact, I have intentionally not added patch download/extract functionally as there is a very good script from Maris Elsins which you can use to get latest patch binaries, extract etc.
Link: https://github.com/MarisElsins/getMOSPatch
I think he also has an Ansible playbook for getMOSPatch, but I am not sure if it’s published.
Thanks,
Ivica
Can we do it for inactive home as well Sir.
Hi Ivica,
I have 2 questions related to using this Ansible module.
1) Is Python also required to be installed on the target DB servers along with the pexpect package?
2) I hear that Ansible is not available for AIX environment.
Is this module good enough for us to set it up on a Linux environment so that we can manage databases on an AIX platform please?
Thanks,
Umesh
Does this script work for 19c?
hi team, can you please let me know what exactly is the value for patch location?
i keep hit this error after i run the playbook :
TASK [orapatch : Patch OH] **************************************************************************************************************************
failed: [ansible1] (item={u’patch_db_list’: u”, u’patch_ojvm’: False, u’skip’: False, u’run_only_checks’: None, u’host’: None, u’patch_only_oh’: False, u’oratab_file’: u’/etc/oratab’, u’oracle_owner’: u’oracle’, u’patch_db_all’: True, u’oracle_home_path’: u’/u01/app/oracle/12.2/dbhome_1′, u’debug’: True, u’patch_id’: 33587128, u’backup_oh’: False}) => {“ansible_loop_var”: “item”, “changed”: false, “item”: {“backup_oh”: false, “debug”: true, “host”: null, “oracle_home_path”: “/u01/app/oracle/12.2/dbhome_1”, “oracle_owner”: “oracle”, “oratab_file”: “/etc/oratab”, “patch_db_all”: true, “patch_db_list”: “”, “patch_id”: 33587128, “patch_ojvm”: false, “patch_only_oh”: false, “run_only_checks”: null, “skip”: false}, “msg”: “[orapatch] module fail: Argument(s) Error… Patch location is not valid for apply”, “rc”: 245}
I put the zip file for the patch on the server at /home/oracle/patch
thanks for sharing this
Hi Ivica, I’m trying to run the playbook but I get a “no_log” error:
TASK [orapatch : Check OPatch minimum version] **********************************************************************************************************************************************
failed: [oranodo1] (item=None) => {“censored”: “the output has been hidden due to the fact that ‘no_log: true’ was specified for this result”, “changed”: false}
fatal: [oranodo1]: FAILED! => {“censored”: “the output has been hidden due to the fact that ‘no_log: true’ was specified for this result”, “changed”: false}
Can you guide me?
Thanks for building this automation tool.