Automating Oracle patching with an Ansible module

Posted in: Technical Track

This is a blog post I had 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.

The module is written 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: It won’t work with one-off patches as it’s not designed for that, although it can be extended to support one-off patches.

The module will use opatchauto if the Oracle home being patched is grid infrastructure, otherwise, it will use standard opatch steps.

The patching is customizable via 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:
  • The module will identify which database instances, listeners and ASM instances are running.
  • The module will shut down all listeners and database instances only if the home from which services are running is being patched.
  • The module will start up all previously stopped services after it completes with patching*
  • The module will skip databases which are not in READ WRITE state**
  • The module will identify if a given database is in STANDBY or PRIMARY role***
  • The module always patches GI homes with opatchauto
  • The module always patches DB homes with opatch
  • The module will make multiple restarts of the databases and listeners during the process
* Assuming no error occurred and module did not fail during the patching process.
** Even if the databases are specified for patching
*** Databases in STANDBY role are not patched

Note: If an error is encountered and you 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 patching is performed, in some cases if something breaks, a manual intervention might be needed. In other words, if you restart the Ansible process do not 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 have implemented a check every 10 seconds with a timeout of 10 minutes where the CRS is checked to see if all services are online prior to continuing to patch other nodes. The module by default will prompt the user to provide the root password. It is necessary for opatchauto and it is only applicable when grid infrastructure software is being patched.

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 control machine from where the patching started. So, if you patch multiple nodes you will get all log files.

How to run

There are 3 steps you need to do prior to run the playbook
  1. Define 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 have 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 “pexpect” package needs to be installed on the target machine. This is required because in some (most) cases OPatch will (prompt) ask questions. The module uses “pexpect” to populate OPatch answers.
If required package is missing the module will fail with a message: module fail: Required “pexpect” (RPM) library not found.

From where to download the module

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 some 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 to be considered for future updates of the module.

Example run

As final note, here is an example where I have 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
email

Interested in working with Ivica? Schedule a tech call.

About the Author

Senior Database Consultant
Ivica is an Oracle Certified Master 12c and 11g, and a recognized member of the Oracle ACE Program as an Oracle ACE Associate. He is a blogger and active contributor to the Oracle community and presents at many technology conferences. Known for his deep Oracle expertise and ability to troubleshoot quickly and efficiently, Ivica has the skills to solve problems quickly regardless of size and complexity. He is passionate about database performance and stability.

1 Comment. Leave new

Emad M Al-Mousa
March 13, 2019 11:30 am

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

Reply

Leave a Reply

Your email address will not be published. Required fields are marked *