Automation – Apply Database Release Update Patch in Oracle 19c using Ansible
Manoj Kumar
Nov 20, 2023 1:00:00 PM
Contents
Introduction: In this blog, I will show you how to use Ansible to automate Oracle database patching. I applied the latest Oracle 19c RU Patch “Database Release Update 19.21.0.0.231017” on the Oracle 19c Database and also rolled back using Ansible.
Pre-Requisite:
1. You should have one Ansible controller machine where Ansible is installed
2. And DB server machine where 19c DB is installed (you can have many DB servers), I tested with one here
Steps:
1. You can download/clone or fork the oracle_patching repository from my GitHub account.
Basically, I used the original repo from here
2. Need to copy the zip file to the Ansible server unzip it and change it as per your environment.
This is from my controller, I created an Ansible role ru-apply and Ansible playbook file patch.yml
[ansadmin@ansible-controller roles]$ pwd
/etc/ansible/roles
[ansadmin@ansible-controller roles]$ ls -lrt
total 0
drwxrwxr-x. 8 ansadmin ansadmin 161 Oct 31 16:43 ru-apply
[ansadmin@ansible-controller roles]$ cd ru-apply/
[ansadmin@ansible-controller ru-apply]$ ll
total 16
-rw-rw-r--. 1 ansadmin ansadmin 1682 Apr 20 2022 CHANGELOG.md
drwxrwxr-x. 2 ansadmin ansadmin 22 Oct 31 16:45 defaults
drwxrwxr-x. 2 ansadmin ansadmin 31 Oct 30 21:03 files
drwxrwxr-x. 2 ansadmin ansadmin 22 Apr 20 2022 meta
-rw-rw-r--. 1 ansadmin ansadmin 46 Oct 31 16:43 patch.yml
-rw-rw-r--. 1 ansadmin ansadmin 71 Apr 20 2022 README.md
drwxrwxr-x. 2 ansadmin ansadmin 4096 Oct 31 16:49 tasks
drwxrwxr-x. 2 ansadmin ansadmin 116 Oct 30 21:05 templates
drwxrwxr-x. 2 ansadmin ansadmin 22 Oct 31 16:50 vars
[ansadmin@ansible-controller ru-apply]$ cat patch.yml
---
- hosts: all
roles:
- role: ru-apply
[ansadmin@ansible-controller ru-apply]$
3. Copy the p6880880_190000_Linux-x86-64.zip (Opatch Upgrade File) and p35643107_190000_Linux-x86-64.zip (RU Update Patch File) to target DB Server.
DB Server:
[oracle@db-server u01]$ ls -lrt
drwxrwxr-x. 4 oracle oinstall 40 Oct 27 14:41 app
-rwxrwxr-x. 1 oracle oinstall 127774864 Oct 27 17:07 p6880880_190000_Linux-x86-64.zip
-rw-rw-r--. 1 oracle oinstall 1815725977 Oct 30 14:45 p35643107_190000_Linux-x86-64.zip
[oracle@db-server u01]$
4. Run the tree command and see the execution of .yml files to apply the patch for Oracle Home.
[ansadmin@ansible-controller roles]$ tree ru-apply
ru-apply
├── CHANGELOG.md
├── defaults
│ └── main.yml
├── files
│ └── check_patches.sql
├── meta
│ └── main.yml
├── patch.yml
├── README.md
├── tasks
│ ├── apply_quarterly_patches.yml
│ ├── conflict_check.yml
│ ├── extract_files.yml
│ ├── get_dbinstance_list.yml
│ ├── main.yml
│ ├── rollback_si.yml
│ ├── run_catbundle.yml
│ ├── RU_si.yml
│ └── update_opatch.yml
├── templates
│ ├── patch_list.j2
│ ├── quarterly_patch_list.j2
│ ├── quarterly_patch_list.j2.old
│ └── srvctl_state.j2
└── vars
└── main.yml
5. Role variables:
vars/main.yml = provide the patch details
patch_type: ru dictionary variable for information for 19.3.. like
opatch:
19.3.0.0:
version: 12.2.0.1.40
filename: p6880880_190000_Linux-x86-64.zip
quarterly_patches:
RU:
19.3.0.0:
OCT2023:
patchversion: 19.21.0.0.231017
filename: p35643107_190000_Linux-x86-64.zip
patchid: 35643107
si_patchid: 35643107
defaults/main.yml
patch_name: OCT2023 # Should match ru dictionary variable defined in vars/main.yml
shutdown_listener: true # set to false if patching an empty oracle home (ie new 12c install)
rollback_ru: false # set to true if rolling back current RU.
oracle_home: "/u01/app/oracle/product/19.0.0/dbhome_1"
oracle_base: /u01/app/oracle
tns_admin: "/network/admin"
oracle_stage_install: /u01
oracle_stage_base: /u01
oracle_install_type: si
oracle_version: 19.3.0.0
patch_type: RU
patch_directory: /u01
Required Inventory Variables
oracle_install_type: valid values are SI or RAC. Used to determine if host is a single instance database install or part of a RAC cluster.
oracle_stage_install: directory that contains all oracle install files
oracle_stage: directory to store logs and scripts used during playbook execution.
6. And here is the task list to execute for applying the patch
task/main.yml
---
#role: ru-apply
#file: main.yml
- debug: var=patch_type
#- name: Run pre-check tasks
#include: pre_checks.yml
#tags: pre_checks
- name: Extract RU and One-off patches
include: extract_files.yml
when: not rollback_ru
tags: extract_files
- name: Update opatch
include: update_opatch.yml
tags: update_opatch
- name: Apply Quarterly Patch
include: "_.yml"
become_user: ""
when: not rollback_ru
tags: apply
- name: Rollback Quarterly Patch
include: "rollback_.yml"
become_user: ""
when: rollback_ru
tags: rollback
7. Now let’s test it first we need to see if we can ping/connect db servers from the Ansible Controller.
8. Before running the Ansible playbook to apply the patch, let’s do a syntax check.
9. Now do the dry run first before running.
[ansadmin@ansible-controller ru-apply]$ ansible-playbook patch.yml --check
10. And finally run the playbook to apply the patch.
[ansadmin@ansible-controller ru-apply]$ ansible-playbook patch.yml
[DEPRECATION WARNING]: "include" is deprecated, use include_tasks/import_tasks instead. See https://docs.ansible.com/ansible-core/2.14/user_guide/playbooks_reuse_includes.html for details. This feature
will be removed in version 2.16. Deprecation warnings can be disabled by setting deprecation_warnings=False in ansible.cfg.
PLAY [all] ***************************************************************************************************************************************************************************************************
TASK [Gathering Facts] ***************************************************************************************************************************************************************************************
[WARNING]: Platform linux on host db_server is using the discovered Python interpreter at /usr/bin/python3.6, but future installation of another Python interpreter could change the meaning of that
path. See https://docs.ansible.com/ansible-core/2.14/reference_appendices/interpreter_discovery.html for more information.
ok: [db_server]
TASK [ru-apply : debug] **************************************************************************************************************************************************************************************
ok: [db_server] => {
"patch_type": "RU"
}
TASK [ru-apply : Create Patch directory] *********************************************************************************************************************************************************************
ok: [db_server]
TASK [ru-apply : debug] **************************************************************************************************************************************************************************************
ok: [db_server] => {
"quarterly_patches[patch_type][oracle_version][patch_name].filename": "p35643107_190000_Linux-x86-64.zip"
}
TASK [ru-apply : Unzip Quarterly Patch] **********************************************************************************************************************************************************************
ok: [db_server]
TASK [ru-apply : Check opatch version in database home] ******************************************************************************************************************************************************
ok: [db_server]
TASK [ru-apply : debug] **************************************************************************************************************************************************************************************
ok: [db_server] => {
"database_opatch_version": {
"changed": false,
"cmd": "/u01/app/oracle/product/19.0.0/dbhome_1/OPatch/opatch version",
"delta": "0:00:00.503409",
"end": "2023-11-01 01:50:26.895656",
"failed": false,
"msg": "",
"rc": 0,
"start": "2023-11-01 01:50:26.392247",
"stderr": "",
"stderr_lines": [],
"stdout": "OPatch Version: 12.2.0.1.40\n\nOPatch succeeded.",
"stdout_lines": [
"OPatch Version: 12.2.0.1.40",
"",
"OPatch succeeded."
]
}
}
TASK [ru-apply : Update opatch in database home] *************************************************************************************************************************************************************
skipping: [db_server]
TASK [ru-apply : Apply Quarterly Patch] **********************************************************************************************************************************************************************
[DEPRECATION WARNING]: "include" is deprecated, use include_tasks/import_tasks/import_playbook instead. This feature will be removed in version 2.16. Deprecation warnings can be disabled by setting
deprecation_warnings=False in ansible.cfg.
included: /etc/ansible/roles/ru-apply/tasks/RU_si.yml for db_server
TASK [ru-apply : RU SU | update opatch] **********************************************************************************************************************************************************************
ok: [db_server]
TASK [ru-apply : RU SU | Get list of patches] ****************************************************************************************************************************************************************
ok: [db_server]
TASK [ru-apply : RU SU | Template patch list] ****************************************************************************************************************************************************************
ok: [db_server]
TASK [ru-apply : RU SU | check for conflicts] ****************************************************************************************************************************************************************
changed: [db_server] => (item=CheckConflictAgainstOHWithDetail)
changed: [db_server] => (item=CheckSystemSpace)
TASK [ru-apply : debug] **************************************************************************************************************************************************************************************
ok: [db_server] => {
"opatch_conflicts": {
"changed": true,
"msg": "All items completed",
"results": [
{
"ansible_loop_var": "item",
"changed": true,
"cmd": "opatch prereq CheckConflictAgainstOHWithDetail -ph /u01/35643107",
"delta": "0:00:15.536175",
"end": "2023-11-01 01:50:49.856225",
"failed": false,
"invocation": {
"module_args": {
"_raw_params": "opatch prereq CheckConflictAgainstOHWithDetail -ph /u01/35643107",
"_uses_shell": true,
"argv": null,
"chdir": null,
"creates": null,
"executable": null,
"removes": null,
"stdin": null,
"stdin_add_newline": true,
"strip_empty_ends": true
}
},
"item": "CheckConflictAgainstOHWithDetail",
"msg": "",
"rc": 0,
"start": "2023-11-01 01:50:34.320050",
"stderr": "",
"stderr_lines": [],
"stdout": "Oracle Interim Patch Installer version 12.2.0.1.40\nCopyright (c) 2023, Oracle Corporation. All rights reserved.\n\nPREREQ session\n\nOracle Home : /u01/app/oracle/product/19.0.0/dbhome_1\nCentral Inventory : /u01/app/oraInventory\n from : /u01/app/oracle/product/19.0.0/dbhome_1/oraInst.loc\nOPatch version : 12.2.0.1.40\nOUI version : 12.2.0.7.0\nLog file location : /u01/app/oracle/product/19.0.0/dbhome_1/cfgtoollogs/opatch/opatch2023-11-01_01-50-34AM_1.log\n\nInvoking prereq \"checkconflictagainstohwithdetail\"\n\nPrereq \"checkConflictAgainstOHWithDetail\" passed.\n\nOPatch succeeded.",
"stdout_lines": [
"Oracle Interim Patch Installer version 12.2.0.1.40",
"Copyright (c) 2023, Oracle Corporation. All rights reserved.",
"",
"PREREQ session",
"",
"Oracle Home : /u01/app/oracle/product/19.0.0/dbhome_1",
"Central Inventory : /u01/app/oraInventory",
" from : /u01/app/oracle/product/19.0.0/dbhome_1/oraInst.loc",
"OPatch version : 12.2.0.1.40",
"OUI version : 12.2.0.7.0",
"Log file location : /u01/app/oracle/product/19.0.0/dbhome_1/cfgtoollogs/opatch/opatch2023-11-01_01-50-34AM_1.log",
"",
"Invoking prereq \"checkconflictagainstohwithdetail\"",
"",
"Prereq \"checkConflictAgainstOHWithDetail\" passed.",
"",
"OPatch succeeded."
]
},
{
"ansible_loop_var": "item",
"changed": true,
"cmd": "opatch prereq CheckSystemSpace -ph /u01/35643107",
"delta": "0:00:07.632033",
"end": "2023-11-01 01:50:58.486930",
"failed": false,
"invocation": {
"module_args": {
"_raw_params": "opatch prereq CheckSystemSpace -ph /u01/35643107",
"_uses_shell": true,
"argv": null,
"chdir": null,
"creates": null,
"executable": null,
"removes": null,
"stdin": null,
"stdin_add_newline": true,
"strip_empty_ends": true
}
},
"item": "CheckSystemSpace",
"msg": "",
"rc": 0,
"start": "2023-11-01 01:50:50.854897",
"stderr": "",
"stderr_lines": [],
"stdout": "Oracle Interim Patch Installer version 12.2.0.1.40\nCopyright (c) 2023, Oracle Corporation. All rights reserved.\n\nPREREQ session\n\nOracle Home : /u01/app/oracle/product/19.0.0/dbhome_1\nCentral Inventory : /u01/app/oraInventory\n from : /u01/app/oracle/product/19.0.0/dbhome_1/oraInst.loc\nOPatch version : 12.2.0.1.40\nOUI version : 12.2.0.7.0\nLog file location : /u01/app/oracle/product/19.0.0/dbhome_1/cfgtoollogs/opatch/opatch2023-11-01_01-50-51AM_1.log\n\nInvoking prereq \"checksystemspace\"\n\nPrereq \"checkSystemSpace\" passed.\n\nOPatch succeeded.",
"stdout_lines": [
"Oracle Interim Patch Installer version 12.2.0.1.40",
"Copyright (c) 2023, Oracle Corporation. All rights reserved.",
"",
"PREREQ session",
"",
"Oracle Home : /u01/app/oracle/product/19.0.0/dbhome_1",
"Central Inventory : /u01/app/oraInventory",
" from : /u01/app/oracle/product/19.0.0/dbhome_1/oraInst.loc",
"OPatch version : 12.2.0.1.40",
"OUI version : 12.2.0.7.0",
"Log file location : /u01/app/oracle/product/19.0.0/dbhome_1/cfgtoollogs/opatch/opatch2023-11-01_01-50-51AM_1.log",
"",
"Invoking prereq \"checksystemspace\"",
"",
"Prereq \"checkSystemSpace\" passed.",
"",
"OPatch succeeded."
]
}
],
"skipped": false
}
}
TASK [ru-apply : RU SU | get list of database names on host] *************************************************************************************************************************************************
changed: [db_server]
TASK [ru-apply : debug] **************************************************************************************************************************************************************************************
ok: [db_server] => {
"dblist": {
"changed": true,
"cmd": "cat /etc/oratab|grep /u01/app/oracle/product/19.0.0/dbhome_1|egrep ':N|:Y'|grep -v \\*|cut -f1 -d':'",
"delta": "0:00:00.014374",
"end": "2023-11-01 01:50:59.534368",
"failed": false,
"msg": "",
"rc": 0,
"start": "2023-11-01 01:50:59.519994",
"stderr": "",
"stderr_lines": [],
"stdout": "cdb1",
"stdout_lines": [
"cdb1"
]
}
}
TASK [ru-apply : RU SU | shutdown databases running in oracle_home] ******************************************************************************************************************************************
changed: [db_server] => (item=cdb1)
TASK [ru-apply : RU SU | shutdown listener in oracle_home] ***************************************************************************************************************************************************
changed: [db_server]
TASK [ru-apply : RU SU | opatch apply] ***********************************************************************************************************************************************************************
changed: [db_server]
TASK [ru-apply : debug] **************************************************************************************************************************************************************************************
ok: [db_server] => {
"opatch_apply.stdout_lines": [
"Oracle Interim Patch Installer version 12.2.0.1.40",
"Copyright (c) 2023, Oracle Corporation. All rights reserved.",
"",
"",
"Oracle Home : /u01/app/oracle/product/19.0.0/dbhome_1",
"Central Inventory : /u01/app/oraInventory",
" from : /u01/app/oracle/product/19.0.0/dbhome_1/oraInst.loc",
"OPatch version : 12.2.0.1.40",
"OUI version : 12.2.0.7.0",
"Log file location : /u01/app/oracle/product/19.0.0/dbhome_1/cfgtoollogs/opatch/opatch2023-11-01_01-51-05AM_1.log",
"",
"Verifying environment and performing prerequisite checks...",
"OPatch continues with these patches: 35643107 ",
"",
"Do you want to proceed? [y|n]",
"Y (auto-answered by -silent)",
"User Responded with: Y",
"All checks passed.",
"",
"Please shutdown Oracle instances running out of this ORACLE_HOME on the local system.",
"(Oracle Home = '/u01/app/oracle/product/19.0.0/dbhome_1')",
"",
"",
"Is the local system ready for patching? [y|n]",
"Y (auto-answered by -silent)",
"User Responded with: Y",
"Backing up files...",
"Applying interim patch '35643107' to OH '/u01/app/oracle/product/19.0.0/dbhome_1'",
"ApplySession: Optional component(s) [ oracle.network.gsm, 19.0.0.0.0 ] , [ oracle.pg4mq, 19.0.0.0.0 ] , [ oracle.rdbms.ic, 19.0.0.0.0 ] , [ oracle.rdbms.tg4db2, 19.0.0.0.0 ] , [ oracle.tfa, 19.0.0.0.0 ] , [ oracle.rdbms.tg4msql, 19.0.0.0.0 ] , [ oracle.ons.cclient, 19.0.0.0.0 ] , [ oracle.rdbms.tg4sybs, 19.0.0.0.0 ] , [ oracle.options.olap.api, 19.0.0.0.0 ] , [ oracle.network.cman, 19.0.0.0.0 ] , [ oracle.sdo.companion, 19.0.0.0.0 ] , [ oracle.xdk.companion, 19.0.0.0.0 ] , [ oracle.rdbms.tg4tera, 19.0.0.0.0 ] , [ oracle.net.cman, 19.0.0.0.0 ] , [ oracle.ons.eons.bwcompat, 19.0.0.0.0 ] , [ oracle.oid.client, 19.0.0.0.0 ] , [ oracle.rdbms.tg4ifmx, 19.0.0.0.0 ] , [ oracle.options.olap, 19.0.0.0.0 ] , [ oracle.jdk, 1.8.0.191.0 ] not present in the Oracle Home or a higher version is found.",
"",
"Patching component oracle.rdbms.util, 19.0.0.0.0...",
"",
"Patching component oracle.rdbms.rsf, 19.0.0.0.0...",
"",
"Patching component oracle.rdbms, 19.0.0.0.0...",
"",
"Patching component oracle.assistants.acf, 19.0.0.0.0...",
"",
"Patching component oracle.assistants.deconfig, 19.0.0.0.0...",
"",
"Patching component oracle.assistants.server, 19.0.0.0.0...",
"",
"Patching component oracle.blaslapack, 19.0.0.0.0...",
"",
"Patching component oracle.buildtools.rsf, 19.0.0.0.0...",
"",
"Patching component oracle.ctx, 19.0.0.0.0...",
"",
"Patching component oracle.dbdev, 19.0.0.0.0...",
"",
"Patching component oracle.dbjava.ic, 19.0.0.0.0...",
"",
"Patching component oracle.dbjava.jdbc, 19.0.0.0.0...",
"",
"Patching component oracle.dbjava.ucp, 19.0.0.0.0...",
"",
"Patching component oracle.duma, 19.0.0.0.0...",
"",
"Patching component oracle.javavm.client, 19.0.0.0.0...",
"",
"Patching component oracle.ldap.owm, 19.0.0.0.0...",
"",
"Patching component oracle.ldap.rsf, 19.0.0.0.0...",
"",
"Patching component oracle.ldap.security.osdt, 19.0.0.0.0...",
"",
"Patching component oracle.marvel, 19.0.0.0.0...",
"",
"Patching component oracle.network.rsf, 19.0.0.0.0...",
"",
"Patching component oracle.odbc.ic, 19.0.0.0.0...",
"",
"Patching component oracle.ons, 19.0.0.0.0...",
"",
"Patching component oracle.ons.ic, 19.0.0.0.0...",
"",
"Patching component oracle.oracore.rsf, 19.0.0.0.0...",
"",
"Patching component oracle.perlint, 5.28.1.0.0...",
"",
"Patching component oracle.precomp.common.core, 19.0.0.0.0...",
"",
"Patching component oracle.precomp.rsf, 19.0.0.0.0...",
"",
"Patching component oracle.rdbms.crs, 19.0.0.0.0...",
"",
"Patching component oracle.rdbms.dbscripts, 19.0.0.0.0...",
"",
"Patching component oracle.rdbms.deconfig, 19.0.0.0.0...",
"",
"Patching component oracle.rdbms.oci, 19.0.0.0.0...",
"",
"Patching component oracle.rdbms.rsf.ic, 19.0.0.0.0...",
"",
"Patching component oracle.rdbms.scheduler, 19.0.0.0.0...",
"",
"Patching component oracle.rhp.db, 19.0.0.0.0...",
"",
"Patching component oracle.sdo, 19.0.0.0.0...",
"",
"Patching component oracle.sdo.locator.jrf, 19.0.0.0.0...",
"",
"Patching component oracle.sqlplus, 19.0.0.0.0...",
"",
"Patching component oracle.sqlplus.ic, 19.0.0.0.0...",
"",
"Patching component oracle.wwg.plsql, 19.0.0.0.0...",
"",
"Patching component oracle.ldap.rsf.ic, 19.0.0.0.0...",
"",
"Patching component oracle.ldap.client, 19.0.0.0.0...",
"",
"Patching component oracle.rdbms.dv, 19.0.0.0.0...",
"",
"Patching component oracle.rdbms.install.common, 19.0.0.0.0...",
"",
"Patching component oracle.rdbms.hsodbc, 19.0.0.0.0...",
"",
"Patching component oracle.nlsrtl.rsf, 19.0.0.0.0...",
"",
"Patching component oracle.xdk.rsf, 19.0.0.0.0...",
"",
"Patching component oracle.odbc, 19.0.0.0.0...",
"",
"Patching component oracle.xdk.parser.java, 19.0.0.0.0...",
"",
"Patching component oracle.ctx.atg, 19.0.0.0.0...",
"",
"Patching component oracle.network.listener, 19.0.0.0.0...",
"",
"Patching component oracle.ctx.rsf, 19.0.0.0.0...",
"",
"Patching component oracle.rdbms.hs_common, 19.0.0.0.0...",
"",
"Patching component oracle.dbtoolslistener, 19.0.0.0.0...",
"",
"Patching component oracle.xdk, 19.0.0.0.0...",
"",
"Patching component oracle.rdbms.rman, 19.0.0.0.0...",
"",
"Patching component oracle.rdbms.drdaas, 19.0.0.0.0...",
"",
"Patching component oracle.install.deinstalltool, 19.0.0.0.0...",
"",
"Patching component oracle.ovm, 19.0.0.0.0...",
"",
"Patching component oracle.rdbms.install.plugins, 19.0.0.0.0...",
"",
"Patching component oracle.mgw.common, 19.0.0.0.0...",
"",
"Patching component oracle.xdk.xquery, 19.0.0.0.0...",
"",
"Patching component oracle.network.client, 19.0.0.0.0...",
"",
"Patching component oracle.ldap.ssl, 19.0.0.0.0...",
"",
"Patching component oracle.oraolap.api, 19.0.0.0.0...",
"",
"Patching component oracle.javavm.server, 19.0.0.0.0...",
"",
"Patching component oracle.sdo.locator, 19.0.0.0.0...",
"",
"Patching component oracle.oraolap, 19.0.0.0.0...",
"",
"Patching component oracle.oraolap.dbscripts, 19.0.0.0.0...",
"",
"Patching component oracle.rdbms.lbac, 19.0.0.0.0...",
"",
"Patching component oracle.precomp.common, 19.0.0.0.0...",
"",
"Patching component oracle.precomp.lang, 19.0.0.0.0...",
"",
"Patching component oracle.jdk, 1.8.0.201.0...",
"Patch 35643107 successfully applied.",
"N-Apply process is complete. No bug fixes are lost.",
"Sub-set patch [29517242] has become inactive due to the application of a super-set patch [35643107].",
"Please refer to Doc ID 2161861.1 for any possible further required actions.",
"Log file location: /u01/app/oracle/product/19.0.0/dbhome_1/cfgtoollogs/opatch/opatch2023-11-01_01-51-05AM_1.log",
"",
"OPatch succeeded."
]
}
TASK [ru-apply : RU SU | apply oneoff patches] ***************************************************************************************************************************************************************
skipping: [db_server]
TASK [ru-apply : RU SU | startup databases] ******************************************************************************************************************************************************************
changed: [db_server] => (item=cdb1)
TASK [ru-apply : RU SU | Load Modified SQL Files into the Database] ******************************************************************************************************************************************
included: /etc/ansible/roles/ru-apply/tasks/run_catbundle.yml for db_server
TASK [ru-apply : Load Modified SQL Files into the Database] **************************************************************************************************************************************************
changed: [db_server] => (item=cdb1)
TASK [ru-apply : RU SU | startup listener in oracle_home] ****************************************************************************************************************************************************
changed: [db_server]
TASK [ru-apply : Rollback Quarterly Patch] *******************************************************************************************************************************************************************
skipping: [db_server]
TASK [ru-apply : Opatch lsinventory] *************************************************************************************************************************************************************************
changed: [db_server] => (item=/u01/app/oracle/product/19.0.0/dbhome_1)
changed: [db_server] => (item=)
PLAY RECAP ***************************************************************************************************************************************************************************************************
db_server : ok=24 changed=9 unreachable=0 failed=0 skipped=3 rescued=0 ignored=1
[ansadmin@ansible-controller ru-apply]$
11. Verify using SQL for patch applied
SQL> set serverout on;
exec dbms_qopatch.get_sqlpatch_status;SQL>
Patch Id : 35643107
Action : APPLY
Action Time : 01-NOV-2023 02:29:07
Description : Database Release Update : 19.21.0.0.231017 (35643107)
Logfile :
/u01/app/oracle/cfgtoollogs/sqlpatch/35643107/25405995/35643107_apply_CDB1_CDBRO
OT_2023Nov01_02_07_27.log
Status : SUCCESS
PL/SQL procedure successfully completed.
SQL>
12. Same way I tested the rollback as well, for rollback we need to change the line in /etc/ansible/roles/ru-apply/defaults/main.yml file
from:
rollback_ru: false # set to true if rolling back current RU.
to:
rollback_ru: true # set to true if rolling back current RU.
When we do the rollback_ru: true and run the playbook again, it will rollback the same patch.
see below:
[ansadmin@ansible-controller ru-apply]$ ansible-playbook patch.yml
[DEPRECATION WARNING]: "include" is deprecated, use include_tasks/import_tasks instead. See https://docs.ansible.com/ansible-core/2.14/user_guide/playbooks_reuse_includes.html for details. This feature
will be removed in version 2.16. Deprecation warnings can be disabled by setting deprecation_warnings=False in ansible.cfg.
PLAY [all] ***************************************************************************************************************************************************************************************************
TASK [Gathering Facts] ***************************************************************************************************************************************************************************************
[WARNING]: Platform linux on host db_server is using the discovered Python interpreter at /usr/bin/python3.6, but future installation of another Python interpreter could change the meaning of that
path. See https://docs.ansible.com/ansible-core/2.14/reference_appendices/interpreter_discovery.html for more information.
ok: [db_server]
TASK [ru-apply : debug] **************************************************************************************************************************************************************************************
ok: [db_server] => {
"patch_type": "RU"
}
TASK [ru-apply : Create Patch directory] *********************************************************************************************************************************************************************
skipping: [db_server]
TASK [ru-apply : debug] **************************************************************************************************************************************************************************************
skipping: [db_server]
TASK [ru-apply : Unzip Quarterly Patch] **********************************************************************************************************************************************************************
skipping: [db_server]
TASK [ru-apply : Check opatch version in database home] ******************************************************************************************************************************************************
ok: [db_server]
TASK [ru-apply : debug] **************************************************************************************************************************************************************************************
ok: [db_server] => {
"database_opatch_version": {
"changed": false,
"cmd": "/u01/app/oracle/product/19.0.0/dbhome_1/OPatch/opatch version",
"delta": "0:00:00.512442",
"end": "2023-10-31 17:47:46.221175",
"failed": false,
"msg": "",
"rc": 0,
"start": "2023-10-31 17:47:45.708733",
"stderr": "",
"stderr_lines": [],
"stdout": "OPatch Version: 12.2.0.1.40\n\nOPatch succeeded.",
"stdout_lines": [
"OPatch Version: 12.2.0.1.40",
"",
"OPatch succeeded."
]
}
}
TASK [ru-apply : Update opatch in database home] *************************************************************************************************************************************************************
skipping: [db_server]
TASK [ru-apply : Apply Quarterly Patch] **********************************************************************************************************************************************************************
skipping: [db_server]
[DEPRECATION WARNING]: "include" is deprecated, use include_tasks/import_tasks/import_playbook instead. This feature will be removed in version 2.16. Deprecation warnings can be disabled by setting
deprecation_warnings=False in ansible.cfg.
TASK [ru-apply : Rollback Quarterly Patch] *******************************************************************************************************************************************************************
included: /etc/ansible/roles/ru-apply/tasks/rollback_si.yml for db_server
TASK [ru-apply : SI ROLLBACK | get list of database names on host] *******************************************************************************************************************************************
changed: [db_server]
TASK [ru-apply : SI ROLLBACK | shutdown databases running in oracle_home] ************************************************************************************************************************************
changed: [db_server] => (item=cdb1)
TASK [ru-apply : SI ROLLBACK | shutdown listener in oracle_home] *********************************************************************************************************************************************
changed: [db_server]
TASK [ru-apply : SI ROLLBACK | Rollback PSU Specific One-off Patches] ****************************************************************************************************************************************
skipping: [db_server]
TASK [ru-apply : SI ROLLBACK | opatch rollback] **************************************************************************************************************************************************************
changed: [db_server]
TASK [ru-apply : debug] **************************************************************************************************************************************************************************************
ok: [db_server] => {
"opatch_rollback.stdout_lines": [
"Oracle Interim Patch Installer version 12.2.0.1.40",
"Copyright (c) 2023, Oracle Corporation. All rights reserved.",
"",
"",
"Oracle Home : /u01/app/oracle/product/19.0.0/dbhome_1",
"Central Inventory : /u01/app/oraInventory",
" from : /u01/app/oracle/product/19.0.0/dbhome_1/oraInst.loc",
"OPatch version : 12.2.0.1.40",
"OUI version : 12.2.0.7.0",
"Log file location : /u01/app/oracle/product/19.0.0/dbhome_1/cfgtoollogs/opatch/opatch2023-10-31_17-48-29PM_1.log",
"",
"",
"Patches will be rolled back in the following order: ",
" 35643107",
"The following patch(es) will be rolled back: 35643107 ",
"",
"Please shutdown Oracle instances running out of this ORACLE_HOME on the local system.",
"(Oracle Home = '/u01/app/oracle/product/19.0.0/dbhome_1')",
"",
"",
"Is the local system ready for patching? [y|n]",
"Y (auto-answered by -silent)",
"User Responded with: Y",
"",
"Rolling back patch 35643107...",
"",
"RollbackSession rolling back interim patch '35643107' from OH '/u01/app/oracle/product/19.0.0/dbhome_1'",
"",
"Patching component oracle.rdbms.util, 19.0.0.0.0...",
"",
"Patching component oracle.rdbms.rsf, 19.0.0.0.0...",
"Deleting \"kubscrf.o\" from archive \"/u01/app/oracle/product/19.0.0/dbhome_1/lib/libclient19.a\"",
"Deleting \"kubsbd.o\" from archive \"/u01/app/oracle/product/19.0.0/dbhome_1/lib/libclient19.a\"",
"Deleting \"kubsppd.o\" from archive \"/u01/app/oracle/product/19.0.0/dbhome_1/lib/libclient19.a\"",
"Deleting \"kubsbdcellcore.o\" from archive \"/u01/app/oracle/product/19.0.0/dbhome_1/lib/libclient19.a\"",
"Deleting \"kpuadg.o\" from archive \"/u01/app/oracle/product/19.0.0/dbhome_1/lib/libclient19.a\"",
"Deleting \"kubsorcpb.o\" from archive \"/u01/app/oracle/product/19.0.0/dbhome_1/lib/libclient19.a\"",
"Deleting \"kubsutl.o\" from archive \"/u01/app/oracle/product/19.0.0/dbhome_1/lib/libclient19.a\"",
"Deleting \"kubsorccore.o\" from archive \"/u01/app/oracle/product/19.0.0/dbhome_1/lib/libclient19.a\"",
"Deleting \"kubscsvcore.o\" from archive \"/u01/app/oracle/product/19.0.0/dbhome_1/lib/libclient19.a\"",
"Deleting \"kubsconv.o\" from archive \"/u01/app/oracle/product/19.0.0/dbhome_1/lib/libclient19.a\"",
"Deleting \"kubscell.o\" from archive \"/u01/app/oracle/product/19.0.0/dbhome_1/lib/libclient19.a\"",
"Deleting \"kubsbdcore.o\" from archive \"/u01/app/oracle/product/19.0.0/dbhome_1/lib/libclient19.a\"",
"Deleting \"kubsdesccore.o\" from archive \"/u01/app/oracle/product/19.0.0/dbhome_1/lib/libclient19.a\"",
"Deleting \"kubsbufio.o\" from archive \"/u01/app/oracle/product/19.0.0/dbhome_1/lib/libclient19.a\"",
"Deleting \"kubslistcore.o\" from archive \"/u01/app/oracle/product/19.0.0/dbhome_1/lib/libclient19.a\"",
"Deleting \"qesxldsb.o\" from archive \"/u01/app/oracle/product/19.0.0/dbhome_1/lib/libcommon19.a\"",
"Deleting \"skfparity.o\" from archive \"/u01/app/oracle/product/19.0.0/dbhome_1/lib/libcommon19.a\"",
"Deleting \"kdzu_art.o\" from archive \"/u01/app/oracle/product/19.0.0/dbhome_1/lib/libcommon19.a\"",
"Deleting \"kuzroci.o\" from archive \"/u01/app/oracle/product/19.0.0/dbhome_1/lib/libcommon19.a\"",
"Deleting \"qesdsbc.o\" from archive \"/u01/app/oracle/product/19.0.0/dbhome_1/lib/libcommon19.a\"",
"Deleting \"kziamc.o\" from archive \"/u01/app/oracle/product/19.0.0/dbhome_1/lib/libcommon19.a\"",
"Deleting \"kgoms.o\" from archive \"/u01/app/oracle/product/19.0.0/dbhome_1/lib/libgeneric19.a\"",
"Deleting \"skgzfndd.o\" from archive \"/u01/app/oracle/product/19.0.0/dbhome_1/lib/libgeneric19.a\"",
"Deleting \"qsodamd.o\" from archive \"/u01/app/oracle/product/19.0.0/dbhome_1/lib/libgeneric19.a\"",
"Deleting \"skgrlib.o\" from archive \"/u01/app/oracle/product/19.0.0/dbhome_1/lib/libgeneric19.a\"",
"Deleting \"skgzepclib.o\" from archive \"/u01/app/oracle/product/19.0.0/dbhome_1/lib/libgeneric19.a\"",
"Deleting \"qsodalob.o\" from archive \"/u01/app/oracle/product/19.0.0/dbhome_1/lib/libgeneric19.a\"",
"Deleting \"skgrlib_ipcdat.o\" from archive \"/u01/app/oracle/product/19.0.0/dbhome_1/lib/libgeneric19.a\"",
"Deleting \"qcpi8.o\" from archive \"/u01/app/oracle/product/19.0.0/dbhome_1/lib/libgeneric19.a\"",
"Deleting \"skgzibr.o\" from archive \"/u01/app/oracle/product/19.0.0/dbhome_1/lib/libgeneric19.a\"",
"",
"Patching component oracle.rdbms, 19.0.0.0.0...",
"Deleting \"kge.o\" from archive \"/u01/app/oracle/product/19.0.0/dbhome_1/lib/libserver19.a\"",
"Deleting \"kubsbufio.o\" from archive \"/u01/app/oracle/product/19.0.0/dbhome_1/lib/libserver19.a\"",
"Deleting \"ktbdat.o\" from archive \"/u01/app/oracle/product/19.0.0/dbhome_1/lib/libserver19.a\"",
"Deleting \"kubsd.o\" from archive \"/u01/app/oracle/product/19.0.0/dbhome_1/lib/libserver19.a\"",
"Deleting \"qjsntrans.o\" from archive \"/u01/app/oracle/product/19.0.0/dbhome_1/lib/libserver19.a\"",
"Deleting \"kswspf.o\" from archive \"/u01/app/oracle/product/19.0.0/dbhome_1/lib/libserver19.a\"",
"Deleting \"hcsbtmpl.o\" from archive \"/u01/app/oracle/product/19.0.0/dbhome_1/lib/libserver19.a\"",
"Deleting \"kztoken.o\" from archive \"/u01/app/oracle/product/19.0.0/dbhome_1/lib/libserver19.a\"",
"Deleting \"kjspare.o\" from archive \"/u01/app/oracle/product/19.0.0/dbhome_1/lib/libserver19.a\"",
"Deleting \"kpoxcd.o\" from archive \"/u01/app/oracle/product/19.0.0/dbhome_1/lib/libserver19.a\"",
"Deleting \"kziam.o\" from archive \"/u01/app/oracle/product/19.0.0/dbhome_1/lib/libserver19.a\"",
"Deleting \"kbclgdr.o\" from archive \"/u01/app/oracle/product/19.0.0/dbhome_1/lib/libserver19.a\"",
"Deleting \"kcert.o\" from archive \"/u01/app/oracle/product/19.0.0/dbhome_1/lib/libserver19.a\"",
"Deleting \"ksmpgaum.o\" from archive \"/u01/app/oracle/product/19.0.0/dbhome_1/lib/libserver19.a\"",
"Deleting \"kpdbapx.o\" from archive \"/u01/app/oracle/product/19.0.0/dbhome_1/lib/libserver19.a\"",
"Deleting \"kbcs.o\" from archive \"/u01/app/oracle/product/19.0.0/dbhome_1/lib/libserver19.a\"",
"Deleting \"hcscalctpl.o\" from archive \"/u01/app/oracle/product/19.0.0/dbhome_1/lib/libserver19.a\"",
"Deleting \"hcscalctoz.o\" from archive \"/u01/app/oracle/product/19.0.0/dbhome_1/lib/libserver19.a\"",
"Deleting \"hcscalctsg.o\" from archive \"/u01/app/oracle/product/19.0.0/dbhome_1/lib/libserver19.a\"",
"Deleting \"kqro.o\" from archive \"/u01/app/oracle/product/19.0.0/dbhome_1/lib/libserver19.a\"",
"",
"Patching component oracle.assistants.acf, 19.0.0.0.0...",
"",
"Patching component oracle.assistants.deconfig, 19.0.0.0.0...",
"",
"Patching component oracle.assistants.server, 19.0.0.0.0...",
"",
"Patching component oracle.blaslapack, 19.0.0.0.0...",
"",
"Patching component oracle.buildtools.rsf, 19.0.0.0.0...",
"",
"Patching component oracle.ctx, 19.0.0.0.0...",
"",
"Patching component oracle.dbdev, 19.0.0.0.0...",
"",
"Patching component oracle.dbjava.ic, 19.0.0.0.0...",
"",
"Patching component oracle.dbjava.jdbc, 19.0.0.0.0...",
"",
"Patching component oracle.dbjava.ucp, 19.0.0.0.0...",
"",
"Patching component oracle.duma, 19.0.0.0.0...",
"",
"Patching component oracle.javavm.client, 19.0.0.0.0...",
"",
"Patching component oracle.ldap.owm, 19.0.0.0.0...",
"",
"Patching component oracle.ldap.rsf, 19.0.0.0.0...",
"",
"Patching component oracle.ldap.security.osdt, 19.0.0.0.0...",
"",
"Patching component oracle.marvel, 19.0.0.0.0...",
"",
"Patching component oracle.network.rsf, 19.0.0.0.0...",
"",
"Patching component oracle.odbc.ic, 19.0.0.0.0...",
"",
"Patching component oracle.ons, 19.0.0.0.0...",
"",
"Patching component oracle.ons.ic, 19.0.0.0.0...",
"",
"Patching component oracle.oracore.rsf, 19.0.0.0.0...",
"",
"Patching component oracle.perlint, 5.28.1.0.0...",
"",
"Patching component oracle.precomp.common.core, 19.0.0.0.0...",
"",
"Patching component oracle.precomp.rsf, 19.0.0.0.0...",
"",
"Patching component oracle.rdbms.crs, 19.0.0.0.0...",
"",
"Patching component oracle.rdbms.dbscripts, 19.0.0.0.0...",
"",
"Patching component oracle.rdbms.deconfig, 19.0.0.0.0...",
"",
"Patching component oracle.rdbms.oci, 19.0.0.0.0...",
"",
"Patching component oracle.rdbms.rsf.ic, 19.0.0.0.0...",
"",
"Patching component oracle.rdbms.scheduler, 19.0.0.0.0...",
"",
"Patching component oracle.rhp.db, 19.0.0.0.0...",
"",
"Patching component oracle.sdo, 19.0.0.0.0...",
"",
"Patching component oracle.sdo.locator.jrf, 19.0.0.0.0...",
"",
"Patching component oracle.sqlplus, 19.0.0.0.0...",
"",
"Patching component oracle.sqlplus.ic, 19.0.0.0.0...",
"",
"Patching component oracle.wwg.plsql, 19.0.0.0.0...",
"",
"Patching component oracle.ldap.rsf.ic, 19.0.0.0.0...",
"",
"Patching component oracle.ldap.client, 19.0.0.0.0...",
"",
"Patching component oracle.rdbms.dv, 19.0.0.0.0...",
"",
"Patching component oracle.rdbms.install.common, 19.0.0.0.0...",
"",
"Patching component oracle.rdbms.hsodbc, 19.0.0.0.0...",
"",
"Patching component oracle.nlsrtl.rsf, 19.0.0.0.0...",
"",
"Patching component oracle.xdk.rsf, 19.0.0.0.0...",
"Deleting \"jzntrans.o\" from archive \"/u01/app/oracle/product/19.0.0/dbhome_1/lib/libxml19.a\"",
"Deleting \"jznpathval.o\" from archive \"/u01/app/oracle/product/19.0.0/dbhome_1/lib/libxml19.a\"",
"",
"Patching component oracle.odbc, 19.0.0.0.0...",
"",
"Patching component oracle.xdk.parser.java, 19.0.0.0.0...",
"",
"Patching component oracle.ctx.atg, 19.0.0.0.0...",
"",
"Patching component oracle.network.listener, 19.0.0.0.0...",
"",
"Patching component oracle.ctx.rsf, 19.0.0.0.0...",
"",
"Patching component oracle.rdbms.hs_common, 19.0.0.0.0...",
"",
"Patching component oracle.dbtoolslistener, 19.0.0.0.0...",
"",
"Patching component oracle.xdk, 19.0.0.0.0...",
"",
"Patching component oracle.rdbms.rman, 19.0.0.0.0...",
"",
"Patching component oracle.rdbms.drdaas, 19.0.0.0.0...",
"",
"Patching component oracle.install.deinstalltool, 19.0.0.0.0...",
"",
"Patching component oracle.ovm, 19.0.0.0.0...",
"",
"Patching component oracle.rdbms.install.plugins, 19.0.0.0.0...",
"",
"Patching component oracle.mgw.common, 19.0.0.0.0...",
"",
"Patching component oracle.xdk.xquery, 19.0.0.0.0...",
"",
"Patching component oracle.network.client, 19.0.0.0.0...",
"",
"Patching component oracle.ldap.ssl, 19.0.0.0.0...",
"",
"Patching component oracle.oraolap.api, 19.0.0.0.0...",
"",
"Patching component oracle.javavm.server, 19.0.0.0.0...",
"",
"Patching component oracle.sdo.locator, 19.0.0.0.0...",
"Deleting \"mdgrt1ht.o\" from archive \"/u01/app/oracle/product/19.0.0/dbhome_1/lib/libordsdo19.a\"",
"Deleting \"mdgrimath.o\" from archive \"/u01/app/oracle/product/19.0.0/dbhome_1/lib/libordsdo19.a\"",
"Deleting \"mdgrspary.o\" from archive \"/u01/app/oracle/product/19.0.0/dbhome_1/lib/libordsdo19.a\"",
"",
"Patching component oracle.oraolap, 19.0.0.0.0...",
"",
"Patching component oracle.oraolap.dbscripts, 19.0.0.0.0...",
"",
"Patching component oracle.rdbms.lbac, 19.0.0.0.0...",
"",
"Patching component oracle.precomp.common, 19.0.0.0.0...",
"",
"Patching component oracle.precomp.lang, 19.0.0.0.0...",
"",
"Patching component oracle.jdk, 1.8.0.201.0...",
"RollbackSession removing interim patch '35643107' from inventory",
"Inactive sub-set patch [29517242] has become active due to the rolling back of a super-set patch [35643107].",
"Please refer to Doc ID 2161861.1 for any possible further required actions.",
"Log file location: /u01/app/oracle/product/19.0.0/dbhome_1/cfgtoollogs/opatch/opatch2023-10-31_17-48-29PM_1.log",
"",
"OPatch succeeded."
]
}
TASK [ru-apply : SI ROLLBACK | startup databases] ************************************************************************************************************************************************************
changed: [db_server] => (item=cdb1)
TASK [ru-apply : SI ROLLBACK | startup pluggable databases] **************************************************************************************************************************************************
changed: [db_server] => (item=cdb1)
TASK [ru-apply : SI ROLLBACK | Run the datapatch utility] ****************************************************************************************************************************************************
included: /etc/ansible/roles/ru-apply/tasks/run_catbundle.yml for db_server
TASK [ru-apply : Load Modified SQL Files into the Database] **************************************************************************************************************************************************
changed: [db_server] => (item=cdb1)
TASK [ru-apply : SI ROLLBACK | startup listener in oracle_home] **********************************************************************************************************************************************
changed: [db_server]
TASK [ru-apply : Opatch lsinventory] *************************************************************************************************************************************************************************
changed: [db_server] => (item=/u01/app/oracle/product/19.0.0/dbhome_1)
changed: [db_server] => (item=)
PLAY RECAP ***************************************************************************************************************************************************************************************************
db_server : ok=16 changed=9 unreachable=0 failed=0 skipped=6 rescued=0 ignored=0
[ansadmin@ansible-controller ru-apply]$