Description:-
In this article we are going to see Oracle Automation-Applying PSU patch in Oracle 12c Database Using Ansible Tool
Let’s start the Demo:-
Steps to apply oracle PSU patch using Ansible :-
Step 1:- Download the ansible-oracle-psu role from github site.Click Here
Step 2:-Copy the ZIP file to ansible server and unzip the file
[oracle@ansible Desktop]# unzip ansible-oracle-psu.zip Archive: ansible-oracle-master.zip 1bbb35d690c102e23488537754ff8d1e0a99dc15 creating: ansible-oracle-master/
[oracle@ansible Desktop]# ls -lrt
total 4272
drwxr-xr-x. 7 root root 4096 Jul 30 11:12 ansible-oracle-psu
-rwxrw-rw-. 1 ansible ansible 349507 Jul 30 22:33 ansible-oracle-psu.zip
Step 3:- Ansible playbook file for applying path in ORACLE_HOME
Sample file:-
- hosts: servers
roles:
- role: psu-apply
Role name: Ansible role psu-apply to apply Oracle patches (PSU) to single instance (SI) and RAC instances.
Step 4:- Copy the p6880880_121010_Linux-x86-64.zip(opatch utility upgrade) and PSU patch(p27338041_121020_Linux-x86-64.zip) file to the target server.
TARGET :-
[oracle@test OPatch]$ cd /u01/ [oracle@test u01]$ ls -lrt drwxr-xr-x. 4 oracle oinstall 38 Jul 26 15:36 app -rwxrwxrwx. 1 oracle oinstall 496419192 Aug 1 19:22 p27338041_121020_Linux-x86-64.zip -rwxrwxrwx. 1 oracle oinstall 99183505 Aug 2 12:14 p6880880_121010_Linux-x86-64.zip
Step 5:- Create a role folder under /etc/ansible & copy the ansible-oracle-psu folder and rename as psu-apply folder
[oracle@ansible ansible]$ mkdir roles [oracle@ansible ansible]$ mv ansible-oracle-psu psu-apply [oracle@ansible ~]$ cd /etc/ansible/roles/psu-apply/ [oracle@ansible psu-apply]$ ls -lrt total 16 -rwxrwxr-x. 1 oracle oinstall 1300 Sep 19 2017 README.md drwxrwxr-x. 2 oracle oinstall 51 Aug 10 23:58 files -rwxrwxr-x. 1 oracle oinstall 62 Aug 11 00:04 patch.yml drwxrwxr-x. 2 oracle oinstall 21 Aug 12 11:15 meta drwxrwxr-x. 2 oracle oinstall 4096 Aug 12 13:20 templates drwxrwxr-x. 2 oracle oinstall 21 Aug 12 13:51 defaults drwxrwxr-x. 2 oracle oinstall 4096 Aug 12 14:06 tasks drwxrwxr-x. 2 oracle oinstall 21 Aug 12 14:06 vars
Step 6:- Run the tree command and see the execution of .yml files to apply a patch for ORACLE_HOME.
[root@ansible roles]# tree psu-apply
psu-apply
├── defaults
│ └── main.yml
├── files
│ ├── check_patches.sql
│ └── getMOSPatch.sh
├── meta
│ └── main.yml
├── patch.retry
├── patch.yml
├── README.md
├── tasks
│ ├── conflict_check.yml
│ ├── dbbp_rac.yml
│ ├── dbbp_si.yml
│ ├── extract_files.yml
│ ├── main.yml
│ ├── oneoff_apply.yml
│ ├── oneoff_rollback.yml
│ ├── pre_checks.yml
│ ├── psu_rac.yml
│ ├── psu_si.yml
│ ├── rollback_patch.yml
│ ├── rollback_rac.yml
│ ├── rollback_si.yml
│ ├── run_catbundle.yml
│ └── ru_si.yml
├── templates
│ ├── getMOSPatch.sh.cfg.j2
│ ├── ocmrsp.expect.j2
│ ├── patch_list.j2
│ ├── quarterly_patch_list.j2
│ └── srvctl_state.j2
└── vars
└── main.yml
6 directories, 29 files
Role Variables :-
vars/main.yml
patch_type: psu dictionary variable for information for 12.1.0.2 and 11.2.0.4
defaults/main.yml
patch_name: APR2018 # Should match quarterly_patches dictionary variable defined in vars/main.yml
shutdown_listener: true # set to false if patching an empty oracle home (ie new 12c install)
rollback_psu: false # set to true if rolling back current PSU.
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.
oracle_version: expects values of 11.2.0.4 or 12.1.0.2
Step 7:- In main.yml file,provide the below entries
[oracle@ansible defaults]$ cat main.yml
---
patch_name: APR2018 # Should match psu dictionary variable defined in vars/main.yml
shutdown_listener: true # set to false if patching an empty oracle home (ie new 12c install)
rollback_psu: false # set to true if rolling back current PSU.
oracle_home: "/u01/app/oracle/product/12.1.0/dbhome_1/"
oracle_base: /u01/app/oracle
tns_admin: "{{ oracle_home }}/network/admin"
oracle_stage_install: /u01
oracle_stage_base: /u01
oracle_install_type: si
oracle_version: 12.1.0.2
patch_type: psu
patch_directory: /u01
env:
ORACLE_HOME: "{{ oracle_home }}"
ORACLE_BASE: "{{ oracle_base }}"
TNS_ADMIN: "{{ tns_admin }}"
PATH: "{{ oracle_home }}/bin:{{ oracle_home }}/OPatch:$PATH:/bin:/usr/bin::/usr/ccs/bin"
oracle_user: oracle # User that will own the Oracle Installations.
oracle_group: oinstall # Primary group for oracle_user.
Step 8:- Provide the PSU patch details
[oracle@ansible psu-apply]$ cd vars/
[oracle@ansible vars]$ cat main.yml
---
env:
ORACLE_HOME: "{{ oracle_home }}"
ORACLE_BASE: "{{ oracle_base }}"
TNS_ADMIN: "{{ tns_admin }}"
PATH: "{{ oracle_home }}/bin:{{ oracle_home }}/OPatch:$PATH:/bin:/usr/bin::/usr/ccs/bin"
# Latest opatch versions and files.
# Files should exist in path defined by oracle_stage_install.
opatch:
12.1.0.2:
version: 12.2.0.1.14
filename: p6880880_121010_Linux-x86-64.zip
# Database Proactive Patch for 12.1
psu:
12.1.0.2:
APR2018:
patchversion: 12.1.0.2.180417
filename: p27338041_121020_Linux-x86-64.zip
patchid: 27338041
si_patchid: 27338041
Step 9:- The list of tasks has been executed for applying patch in target ORACLE_HOME.
tasks/main.yml --- role: psu-apply #file: main.yml - debug: var=patch_type - name: Run pre-check tasks include: pre_checks.yml tags: pre_checks - name: Extract PSU and One-off patches include: extract_files.yml when: not rollback_psu tags: extract_files - name: Stop EM Agent shell: "{{ agent_home }}/bin/emctl stop agent" become_user: "{{ oracle_user }}" when: agent_home is defined and shutdown_listener tags: stop_agent - name: Apply Quarterly Patch include: "{{ patch_type }}_{{ oracle_install_type }}.yml" become_user: "{{ oracle_user }}" when: not rollback_psu tags: apply - name: Rollback Quarterly Patch include: "rollback_{{ oracle_install_type }}.yml" become_user: "{{ oracle_user }}" when: rollback_psu tags: rollback - name: Start EM Agent shell: "{{ agent_home }}/bin/emctl start agent" become: yes become_user: "{{ oracle_user }}" when: agent_home is defined and shutdown_listener tags: start_agent - name: Opatch lsinventory shell: "{{ oracle_home }}/OPatch/opatch lsinventory -oh {{ oracle_home }}" become_user: "{{ oracle_user }}" with_items: - "{{ oracle_home }}" - "{{ grid_home|default(None) }}" when: item is defined register: lsinv_post tasks/pre_checks.yml --- - name: fail if required variables are not defined fail: msg="{{ item }} is not defined for {{ ansible_hostname }}" when: "{{ item }} is not defined" tags: always with_items: - oracle_install_type - oracle_version - patch_type - patch_name - name: Check for zip file stat: path: "{{ oracle_stage_install }}" get_checksum: false get_md5: false register: patch_st failed_when: "not {{ patch_st.stat.exists }}" - name: Check if OPatch zip file exists stat: path: "{{ oracle_stage_install }}" register: opatch_st failed_when: "not {{ opatch_st.stat.exists }}" # - name: fail if PSU-Specific one-off patches are missing # stat: # path: "{{ oracle_stage_install }}/{{ item.filename }}" # register: patch_st # with_items: oneoff_patches[ quarterly_patches[patch_type][oracle_version][patch_name].patchversion ]|default([]) # when: "{{ oneoff_patches[ quarterly_patches[patch_type][oracle_version][patch_name].patchversion ] }} is defined" # - name: Fail if missing PSU-Specific one-off patches # fail: msg="Oops! Missing one-off patch {{ item.item.filename }}" # with_items: patch_st.results # when: # - "{{ oneoff_patches[ quarterly_patches[patch_type][oracle_version][patch_name].patchversion ] }} is defined" # - "not {{ item.stat.exists }}"
Step 10 :- Check the opatch version and inventory in ORACLE_HOME
[oracle@fhpasadbdr01 OPatch]$ ./opatch version OPatch Version: 12.1.0.1.8 OPatch succeeded.
Step 11 :- Stop the database & listener services
Playbook file to apply patch :-
[oracle@ansible psu-apply]$ cat patch.yml
---
- hosts: database-servers
roles:
- role: psu-apply
Step 12:- Check ping status before applying a patch
[oracle@ansible psu-apply]$ ansible all -m ping test | SUCCESS => { "changed": false, "ping": "pong" }
Syntax check:-
[oracle@ansible psu-apply]$ ansible-playbook patch.yml --syntax-check playbook: patch.yml
Step 13:- Run the playbook file to apply a database patch in ORACLE_HOME.
patch.yml file will update the opatch utility and apply PSU patch in ORACLE_HOME which we copied to /u01 directory
[oracle@ansible psu-apply]$ ansible-playbook patch.yml
PLAY [database-servers] **********************************************************
TASK [Gathering Facts] ***************************************************************************************************
ok: [test]
TASK [psu-apply : debug] *************************************************************************************************
ok: [test] => {
"patch_type": "psu"
}
TASK [psu-apply : fail if required variables are not defined] ************************************************************
skipping: [test] => (item=oracle_install_type)
skipping: [test] => (item=oracle_version)
skipping: [test] => (item=patch_type)
skipping: [test] => (item=patch_name)
TASK [psu-apply : Check for zip file] ************************************************************************************
ok: [test]
TASK [psu-apply : Check if OPatch zip file exists] ***********************************************************************
ok: [test]
TASK [psu-apply : Create Patch directory] ********************************************************************************
ok: [test]
TASK [psu-apply : Unzip Quarterly Patch] *********************************************************************************
skipping: [test]
TASK [psu-apply : Unzip oneoff patches] **********************************************************************************
TASK [psu-apply : Stop EM Agent] *****************************************************************************************
skipping: [test]
TASK [psu-apply : Apply Quarterly Patch] *********************************************************************************
included: /etc/ansible/roles/psu-apply/tasks/psu_si.yml for test
TASK [psu-apply : PSU SI | update opatch] *******************************************************************************
changed: [test]
TASK [psu-apply : PSU SI | Get list of patches in PSU] *****************************************************************
ok: [test]
TASK [psu-apply : PSU SI | Template patch list] *************************************************************************
ok: [test]
TASK [psu-apply : PSU SI | check for conflicts] *************************************************************************
changed: [test] => (item=CheckConflictAgainstOHWithDetail)
changed: [test] => (item=CheckSystemSpace)
TASK [psu-apply : debug] *************************************************************************************************
ok: [test] => {
"opatch_conflicts": {
"changed": true,
"msg": "All items completed",
"results": [
{
"_ansible_ignore_errors": null,
"_ansible_item_label": "CheckConflictAgainstOHWithDetail",
"_ansible_item_result": true,
"_ansible_no_log": false,
"_ansible_parsed": true,
"changed": true,
"cmd": "opatch prereq CheckConflictAgainstOHWithDetail -phBaseFile /u01/quarterly_patch_list_12.1.0.2_APR2018.txt",
"delta": "0:00:03.900157",
"end": "2018-08-10 23:13:51.674497",
"failed": false,
"invocation": {
"module_args": {
"_raw_params": "opatch prereq CheckConflictAgainstOHWithDetail -phBaseFile /u01/quarterly_patch_list_12.1.0.2_APR2018.txt",
"_uses_shell": true,
"argv": null,
"chdir": null,
"creates": null,
"executable": null,
"removes": null,
"stdin": null,
"warn": true
}
},
"item": "CheckConflictAgainstOHWithDetail",
"rc": 0,
"start": "2018-08-10 23:13:47.774340",
"stderr": "",
"stderr_lines": [],
"stdout": "Oracle Interim Patch Installer version 12.2.0.1.14\nCopyright (c) 2018, Oracle Corporation. All rights reserved.\n\nPREREQ session\n\nOracle Home : /u01/app/oracle/product/12.1.0/dbhome_1\nCentral Inventory : /u01/app/oraInventory\n from : /u01/app/oracle/product/12.1.0/dbhome_1//oraInst.loc\nOPatch version : 12.2.0.1.14\nOUI version : 12.1.0.2.0\nLog file location : /u01/app/oracle/product/12.1.0/dbhome_1/cfgtoollogs/opatch/opatch2018-08-10_23-13-48PM_1.log\n\nInvoking prereq \"checkconflictagainstohwithdetail\"\n\nPrereq \"checkConflictAgainstOHWithDetail\" passed.\n\nOPatch succeeded.",
"stdout_lines": [
"Oracle Interim Patch Installer version 12.2.0.1.14",
"Copyright (c) 2018, Oracle Corporation. All rights reserved.",
"",
"PREREQ session",
"",
"Oracle Home : /u01/app/oracle/product/12.1.0/dbhome_1",
"Central Inventory : /u01/app/oraInventory",
" from : /u01/app/oracle/product/12.1.0/dbhome_1//oraInst.loc",
"OPatch version : 12.2.0.1.14",
"OUI version : 12.1.0.2.0",
"Log file location : /u01/app/oracle/product/12.1.0/dbhome_1/cfgtoollogs/opatch/opatch2018-08-10_23-13-48PM_1.log",
"",
"Invoking prereq \"checkconflictagainstohwithdetail\"",
"",
"Prereq \"checkConflictAgainstOHWithDetail\" passed.",
"",
"OPatch succeeded."
]
},
{
"_ansible_ignore_errors": null,
"_ansible_item_label": "CheckSystemSpace",
"_ansible_item_result": true,
"_ansible_no_log": false,
"_ansible_parsed": true,
"changed": true,
"cmd": "opatch prereq CheckSystemSpace -phBaseFile /u01/quarterly_patch_list_12.1.0.2_APR2018.txt",
"delta": "0:00:03.471081",
"end": "2018-08-10 23:13:55.508123",
"failed": false,
"invocation": {
"module_args": {
"_raw_params": "opatch prereq CheckSystemSpace -phBaseFile /u01/quarterly_patch_list_12.1.0.2_APR2018.txt",
"_uses_shell": true,
"argv": null,
"chdir": null,
"creates": null,
"executable": null,
"removes": null,
"stdin": null,
"warn": true
}
},
"item": "CheckSystemSpace",
"rc": 0,
"start": "2018-08-10 23:13:52.037042",
"stderr": "",
"stderr_lines": [],
"stdout": "Oracle Interim Patch Installer version 12.2.0.1.14\nCopyright (c) 2018, Oracle Corporation. All rights reserved.\n\nPREREQ session\n\nOracle Home : /u01/app/oracle/product/12.1.0/dbhome_1\nCentral Inventory : /u01/app/oraInventory\n from : /u01/app/oracle/product/12.1.0/dbhome_1//oraInst.loc\nOPatch version : 12.2.0.1.14\nOUI version : 12.1.0.2.0\nLog file location : /u01/app/oracle/product/12.1.0/dbhome_1/cfgtoollogs/opatch/opatch2018-08-10_23-13-52PM_1.log\n\nInvoking prereq \"checksystemspace\"\n\nPrereq \"checkSystemSpace\" passed.\n\nOPatch succeeded.",
"stdout_lines": [
"Oracle Interim Patch Installer version 12.2.0.1.14",
"Copyright (c) 2018, Oracle Corporation. All rights reserved.",
"",
"PREREQ session",
"",
"Oracle Home : /u01/app/oracle/product/12.1.0/dbhome_1",
"Central Inventory : /u01/app/oraInventory",
" from : /u01/app/oracle/product/12.1.0/dbhome_1//oraInst.loc",
"OPatch version : 12.2.0.1.14",
"OUI version : 12.1.0.2.0",
"Log file location : /u01/app/oracle/product/12.1.0/dbhome_1/cfgtoollogs/opatch/opatch2018-08-10_23-13-52PM_1.log",
"",
"Invoking prereq \"checksystemspace\"",
"",
"Prereq \"checkSystemSpace\" passed.",
"",
"OPatch succeeded."
]
}
]
}
}
TASK [psu-apply : PSU SI | get list of database names on host] **********************************************************
changed: [test]
TASK [psu-apply : dictionary variable with latest PSU information for 12.1.0.2 and 11.2.0.4SI | shutdown databases running in oracle_home] ***************************************************
TASK [psu-apply : PSU SI | shutdown listener in oracle_home] ************************************************************
changed: [test]
TASK [psu-apply : PSU SI | opatch apply] ********************************************************************************
changed: [test]
TASK [psu-apply : debug] *************************************************************************************************
ok: [test] => {
"opatch_apply.stdout_lines": [
"Oracle Interim Patch Installer version 12.2.0.1.14",
"Copyright (c) 2018, Oracle Corporation. All rights reserved.",
"",
"",
"Oracle Home : /u01/app/oracle/product/12.1.0/dbhome_1",
"Central Inventory : /u01/app/oraInventory",
" from : /u01/app/oracle/product/12.1.0/dbhome_1//oraInst.loc",
"OPatch version : 12.2.0.1.14",
"OUI version : 12.1.0.2.0",
"Log file location : /u01/app/oracle/product/12.1.0/dbhome_1/cfgtoollogs/opatch/opatch2018-08-10_23-13-57PM_1.log",
"",
"Verifying environment and performing prerequisite checks...",
"OPatch continues with these patches: 19769480 20299023 20831110 21359755 21948354 22291127 23054246 24006101 24732082 25171037 25755742 26609783 26713565 26925311 27338041 ",
"",
"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/12.1.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 sub-patch '19769480' to OH '/u01/app/oracle/product/12.1.0/dbhome_1'",
"",
"Composite patch 27338041 successfully applied.",
"Log file location: /u01/app/oracle/product/12.1.0/dbhome_1/cfgtoollogs/opatch/opatch2018-08-10_23-13-57PM_1.log",
"",
"OPatch succeeded."
]
}
TASK [psu-apply : PSU SI | apply oneoff patches] ************************************************************************
skipping: [test]
TASK [psu-apply : PSU SI | startup databases] ***************************************************************************
TASK [psu-apply : PSU SI | 12.1 - startup pluggable databases] **********************************************************
TASK [psu-apply : PSU SI | Load Modified SQL Files into the Database] ***************************************************
included: /etc/ansible/roles/psu-apply/tasks/run_catbundle.yml for test
TASK [psu-apply : 11.2 - Load Modified SQL Files into the Database] ******************************************************
TASK [psu-apply : 12.1 - Load Modified SQL Files into the Database] ******************************************************
TASK [psu-apply : PSU SI | startup listener in oracle_home] *************************************************************
changed: [test]
TASK [psu-apply : Rollback Quarterly Patch] ******************************************************************************
skipping: [test]
TASK [psu-apply : Start EM Agent] ****************************************************************************************
skipping: [test]
TASK [psu-apply : Opatch lsinventory] ************************************************************************************
changed: [test] => (item=/u01/app/oracle/product/12.1.0/dbhome_1/)
changed: [test] => (item=)
OPatch succeeded.",
"stdout_lines": [
"Oracle Interim Patch Installer version 12.2.0.1.14",
"Copyright (c) 2018, Oracle Corporation. All rights reserved.",
"",
"",
"Oracle Home : /u01/app/oracle/product/12.1.0/dbhome_1",
"Central Inventory : /u01/app/oraInventory",
" from : /u01/app/oracle/product/12.1.0/dbhome_1//oraInst.loc",
"OPatch version : 12.2.0.1.14",
"OUI version : 12.1.0.2.0",
"Log file location : /u01/app/oracle/product/12.1.0/dbhome_1/cfgtoollogs/opatch/opatch2018-08-10_23-24-05PM_1.log",
"",
"Lsinventory Output file location : /u01/app/oracle/product/12.1.0/dbhome_1/cfgtoollogs/opatch/lsinv/lsinventory2018-08-10_23-24-05PM.txt",
"",
"--------------------------------------------------------------------------------",
"Local Machine Information::",
"Hostname: test.localdomain.com",
"ARU platform id: 226",
"ARU platform description:: Linux x86-64",
"",
"Installed Top-level Products (1): ",
"",
"Oracle Database 12c 12.1.0.2.0",
"There are 1 products installed in this Oracle Home.",
"",
"",
"Interim patches (1) :",
"",
"Patch 27338041 : applied on Fri Aug 10 23:20:33 EDT 2018",
"Unique Patch ID: 22036385",
"Patch description: \"Database Patch Set Update : 12.1.0.2.180417 (27338041)\"",
" Created on 16 Mar 2018, 02:05:00 hrs PST8PDT",
"Sub-patch 26925311; \"Database Patch Set Update : 12.1.0.2.180116 (26925311)\"",
"Sub-patch 26713565; \"Database Patch Set Update : 12.1.0.2.171017 (26713565)\"",
"Sub-patch 26609783; \"Database Patch Set Update : 12.1.0.2.170814 (26609783)\"",
"Sub-patch 25755742; \"Database Patch Set Update : 12.1.0.2.170718 (25755742)\"",
"Sub-patch 25171037; \"Database Patch Set Update : 12.1.0.2.170418 (25171037)\"",
"Sub-patch 24732082; \"Database Patch Set Update : 12.1.0.2.170117 (24732082)\"",
"Sub-patch 24006101; \"Database Patch Set Update : 12.1.0.2.161018 (24006101)\"",
"Sub-patch 23054246; \"Database Patch Set Update : 12.1.0.2.160719 (23054246)\"",
"Sub-patch 22291127; \"Database Patch Set Update : 12.1.0.2.160419 (22291127)\"",
"Sub-patch 21948354; \"Database Patch Set Update : 12.1.0.2.160119 (21948354)\"",
"Sub-patch 21359755; \"Database Patch Set Update : 12.1.0.2.5 (21359755)\"",
"Sub-patch 20831110; \"Database Patch Set Update : 12.1.0.2.4 (20831110)\"",
"Sub-patch 20299023; \"Database Patch Set Update : 12.1.0.2.3 (20299023)\"",
"Sub-patch 19769480; \"Database Patch Set Update : 12.1.0.2.2 (19769480)\"",
"---------------------------------------------------------",
"OPatch succeeded."
]
}
]
}
PLAY RECAP **********************************************************
test : ok=19 changed=7 unreachable=0 failed=0
Step 14:- Start the database and listener
Step 15 :- Check the Opatch lsinventory of ORACLE_HOME
Catch Me On:- Hariprasath Rajaram
Telegram:https://t.me/joinchat/I_f4DhGF_Zifr9YZvvMkRg
LinkedIn:https://www.linkedin.com/in/hari-prasath-aa65bb19/
Facebook:https://www.facebook.com/HariPrasathdba
FB Group:https://www.facebook.com/groups/894402327369506/
FB Page: https://www.facebook.com/dbahariprasath/?
Twitter: https://twitter.com/hariprasathdba
