Applying Database Release Update by using Ansible Playbooks
In the previous post “Oracle Grid Patching by using Ansible”, I mentioned how Ansible orchestration can be used to make Oracle Grid Software Patching more clean and simple way.
Now I will also patch database software by using Ansible Playbooks.
In this post,
I will use blt01 host as the ansible host. Target environment is a two node RAC database. (gns01, gns02). I will patch grid software to 19.22 by using out-of-place methodology with no down time.
Ansible to Rule Them All
Although, there are lots of useful blog posts about how you may use Ansible for automating tasks, there is not much about a complete Oracle database software patching.
Ansible is simply installed by “yum install ansible” from EPEL repository. Add your hosts(gns01, gns02) in the hosts file under /etc/ansible/hosts and create ssh equivalency for oracle and root users.
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| [root@blt01 ]# yum install ansible | |
| [root@blt01 ]# useradd ansible | |
| [root@blt01 ]# chown -R ansible:ansible /etc/ansible | |
| [ansible@blt01 ]$ cd /etc/ansible | |
| [ansible@blt01 ansible]$ cat hosts | |
| [dbservers] | |
| gns01 | |
| gns02 | |
| [ansible@blt01 ansible]$ ansible all -i hosts -k -u root -m authorized_key -a "user=root state=present key=\"{{ lookup('file','/home/ansible/.ssh/id_rsa.pub') }}\"" | |
| [ansible@blt01 ansible]$ ansible all -i hosts -k -u oracle -m authorized_key -a "user=oracle state=present key=\"{{ lookup('file','/home/ansible/.ssh/id_rsa.pub') }}\"" | |
| [ansible@blt01 ansible]$ mkdir -p projects/giru | |
| [ansible@blt01 ansible]$ mkdir -p projects/dbru | |
We need:
- OPatch – p6880880_122010_Linux-x86-64.zip
- 19.22 RU – p35940989_190000_Linux-x86-64.zip (GIRU System Patch involves DBRU)
- A sample one-off patch (555.1 Recommended) – p34672698_1922000DBRU_Linux-x86-64.zip
- OJVM patch (555.1 Recommended) – p35926646_190000_Linux-x86-64.zip
- Database home Setup – LINUX.X64_193000_db_home.zip
Lets start with creating new database directory. Here is the first playbook we will run.
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| [ansible@blt01 ansible]$ cat projects/dbru/prepare_dbru.yml | |
| – name: Configure New Oracle Database Homes | |
| hosts: dbservers | |
| vars: | |
| new_db_home: /u01/app/oracle/product/19.22/dbhome_1 | |
| patch_dir: /u01/app/Setup | |
| remote_user: oracle | |
| tasks: | |
| – name: Prepare patch directory for setup files | |
| ansible.builtin.file: | |
| path: "{{ patch_dir }}" | |
| state: directory | |
| owner: oracle | |
| group: oinstall | |
| – name: Prepare new database home | |
| ansible.builtin.file: | |
| path: "{{ new_db_home }}" | |
| state: directory | |
| owner: oracle | |
| group: oinstall | |
| [ansible@blt01 ansible]$ ansible-playbook -i hosts projects/dbru/prepare_dbru.yml | |
| PLAY [Configure New Oracle Database Homes] ********************************************************************************************** | |
| TASK [Gathering Facts] ****************************************************************************************************************** | |
| ok: [gns02] | |
| ok: [gns01] | |
| TASK [Prepare patch directory for setup files] ****************************************************************************************** | |
| ok: [gns01] | |
| ok: [gns02] | |
| TASK [Prepare new database home] ******************************************************************************************************** | |
| changed: [gns02] | |
| changed: [gns01] | |
| PLAY RECAP ****************************************************************************************************************************** | |
| gns01 : ok=3 changed=1 unreachable=0 failed=0 skipped=0 rescued=0 ignored=0 | |
| gns02 : ok=3 changed=1 unreachable=0 failed=0 skipped=0 rescued=0 ignored=0 |
As new database homes are created on target servers, I will unzip 19.3 db_home.zip and apply Release updates and one off patches on top of it with the below playbook.
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| [ansible@blt01 ansible]$ cat projects/dbru/apply_dbru_onfirstnode.yml | |
| – name: Apply Oracle Database Release Update | |
| hosts: dbservers | |
| vars: | |
| u01_size_mb: 11045 | |
| new_db_home: /u01/app/oracle/product/19.22/dbhome_1 | |
| old_db_home: /u01/app/oracle/product/19.20/dbhome_1 | |
| grid_home: /u01/app/19.22/grid | |
| patch_dir: /u01/app/Setup | |
| remote_user: oracle | |
| run_once: true | |
| tasks: | |
| – name: Copy setup files to target server | |
| ansible.builtin.copy: | |
| src: "{{ source_patch_dir }}/" | |
| dest: "{{ patch_dir }}/" | |
| owner: oracle | |
| group: oinstall | |
| – name: Unzip new DB_HOME | |
| ansible.builtin.unarchive: | |
| src: "{{ patch_dir }}/LINUX.X64_193000_db_home.zip" | |
| dest: "{{ new_db_home }}" | |
| remote_src: yes | |
| – name: Unzip OPatch to new DB_HOME | |
| ansible.builtin.unarchive: | |
| src: "{{ patch_dir }}/p6880880_122010_Linux-x86-64.zip" | |
| dest: "{{ new_db_home }}" | |
| remote_src: yes | |
| – name: Prepare patches directories | |
| ansible.builtin.file: | |
| path: "{{ item }}" | |
| state: directory | |
| owner: oracle | |
| group: oinstall | |
| loop: | |
| – "{{ patch_dir }}/GIRU" | |
| – "{{ patch_dir }}/OJVM" | |
| – "{{ patch_dir }}/GIRUOneOff" | |
| – name: unzip GIRU system patch | |
| ansible.builtin.unarchive: | |
| src: "{{ patch_dir }}/p35940989_190000_Linux-x86-64.zip" | |
| dest: "{{ patch_dir }}/GIRU" | |
| remote_src: yes | |
| – name: Unzip OJVM patch | |
| ansible.builtin.unarchive: | |
| src: "{{ patch_dir }}/p35926646_190000_Linux-x86-64.zip" | |
| dest: "{{ patch_dir }}/OJVM" | |
| remote_src: yes | |
| – name: unzip GIRU oneoff patch | |
| ansible.builtin.unarchive: | |
| src: "{{ patch_dir }}/p34672698_1922000DBRU_Linux-x86-64.zip" | |
| dest: "{{ patch_dir }}/GIRUOneoff" | |
| remote_src: yes | |
| – name: Register cluster nodes for response file | |
| ansible.builtin.shell: | |
| cmd: "{{ grid_home }}/bin/olsnodes | tr '\n' ',' | sed 's/,$//'" | |
| register: cluster_nodes | |
| – name: Register osdba value for response file | |
| ansible.builtin.shell: | |
| cmd: "grep 'OSDBA_GROUP=' {{ old_db_home }}/install/response/db_20*.rsp" | |
| register: osdba | |
| – name: Register osoper value for response file | |
| ansible.builtin.shell: | |
| cmd: "grep 'OSOPER_GROUP=' {{ old_db_home }}/install/response/db_20*.rsp" | |
| register: osoper | |
| – name: Register osbackupdba value for response file | |
| ansible.builtin.shell: | |
| cmd: "grep 'OSBACKUPDBA_GROUP=' {{ old_db_home }}/install/response/db_20*.rsp" | |
| register: osbackupdba | |
| – name: Register osdgdba value for response file | |
| ansible.builtin.shell: | |
| cmd: "grep 'OSDGDBA_GROUP=' {{ old_db_home }}/install/response/db_20*.rsp" | |
| register: osdgdba | |
| – name: Register oskmdba value for response file | |
| ansible.builtin.shell: | |
| cmd: "grep 'OSKMDBA_GROUP=' {{ old_db_home }}/install/response/db_20*.rsp" | |
| register: oskmdba | |
| – name: Register osracdba value for response file | |
| ansible.builtin.shell: | |
| cmd: "grep 'OSRACDBA_GROUP=' {{ old_db_home }}/install/response/db_20*.rsp" | |
| register: osracdba | |
| – name: Create contents of response file | |
| ansible.builtin.copy: | |
| dest: "{{ patch_dir }}/db_oop.rsp" | |
| content: | | |
| oracle.install.responseFileVersion=/oracle/install/rspfmt_dbinstall_response_schema_v19.0.0 | |
| oracle.install.option=INSTALL_DB_SWONLY | |
| UNIX_GROUP_NAME=oinstall | |
| INVENTORY_LOCATION=/u01/app/oraInventory | |
| ORACLE_BASE=/u01/app/oracle | |
| ORACLE_HOME={{ new_db_home }} | |
| oracle.install.db.InstallEdition=EE | |
| {{ osdba.stdout }} | |
| {{ osoper.stdout }} | |
| {{ osbackupdba.stdout }} | |
| {{ osdgdba.stdout }} | |
| {{ oskmdba.stdout }} | |
| {{ osracdba.stdout }} | |
| oracle.install.db.CLUSTER_NODES={{ cluster_nodes.stdout }} | |
| SECURITY_UPDATES_VIA_MYORACLESUPPORT=false | |
| DECLINE_SECURITY_UPDATES=true | |
| – name: Clean up DB home zip files | |
| ansible.builtin.file: | |
| path: "{{ item }}" | |
| state: absent | |
| loop: | |
| – "{{ patch_dir }}/LINUX.X64_193000_db_home.zip" | |
| – "{{ patch_dir }}/p35926646_190000_Linux-x86-64.zip" | |
| – "{{ patch_dir }}/p34672698_1922000DBRU_Linux-x86-64.zip" | |
| – "{{ patch_dir }}/p35940989_190000_Linux-x86-64.zip" | |
| – name: Check u01 free disk space | |
| ansible.builtin.shell: | |
| cmd: "df -P /u01 | awk 'END { print $4 }'" | |
| register: u01size | |
| failed_when: u01size.stdout|int < u01_size_mb * 1024 | |
| – name: Apply DBRU and one-offs | |
| ansible.builtin.shell: | |
| cmd: | | |
| {{ new_db_home }}/runInstaller -silent -ignorePrereqFailure -waitforcompletion \ | |
| -applyRU {{ patch_dir }}/GIRU/35940989 \ | |
| -applyOneOffs \ | |
| {{ patch_dir }}/OJVM/35926646,\ | |
| {{ patch_dir }}/GIRUOneOff/34672698 \ | |
| -responsefile {{ patch_dir }}/db_oop.rsp | |
| environment: | |
| CV_ASSUME_DISTID: OL7 | |
| NEWDBHOME: "{{ new_db_home }}" | |
| register: apply_dbru | |
| failed_when: "'Successfully Setup Software' not in apply_dbru.stdout" | |
| async: 3600 | |
| poll: 20 | |
| [ansible@blt01 ansible]$ ansible-playbook -i hosts -l gns01 projects/dbru/apply_dbru_onfirstnode.yml | |
| PLAY [Apply Oracle Database Release Update] ********************************************************************************************* | |
| TASK [Gathering Facts] ****************************************************************************************************************** | |
| ok: [gns01] | |
| TASK [Copy setup files to target server] ************************************************************************************************ | |
| changed: [gns01] | |
| TASK [unzip new db_home] **************************************************************************************************************** | |
| changed: [gns01] | |
| TASK [unzip OPatch to new db_home] ****************************************************************************************************** | |
| changed: [gns01] | |
| TASK [prepare patches directories] ****************************************************************************************************** | |
| ok: [gns01] | |
| TASK [unzip GIRU system patch] ********************************************************************************************************** | |
| changed: [gns01] | |
| TASK [unzip OJVM patch] ***************************************************************************************************************** | |
| changed: [gns01] | |
| TASK [unzip GIRU oneoff patch] ********************************************************************************************************** | |
| changed: [gns01] | |
| TASK [register cluster nodes for responsefile] ****************************************************************************************** | |
| changed: [gns01] | |
| TASK [register osdba value for responsefile] ******************************************************************************************** | |
| changed: [gns01] | |
| TASK [register osoper value for responsefile] ******************************************************************************************* | |
| changed: [gns01] | |
| TASK [register osbackupdba value for responsefile] ************************************************************************************** | |
| changed: [gns01] | |
| TASK [register osdgdba value for responsefile] ****************************************************************************************** | |
| changed: [gns01] | |
| TASK [register oskmdba value for responsefile] ****************************************************************************************** | |
| changed: [gns01] | |
| TASK [register osracdba value for responsefile] ***************************************************************************************** | |
| changed: [gns01] | |
| TASK [create contents of responsefile] ************************************************************************************************** | |
| changed: [gns01] | |
| TASK [clean up dbhome zip files] ******************************************************************************************************** | |
| changed: [gns01] | |
| TASK [check u01 free disk space] ******************************************************************************************************** | |
| changed: [gns01] | |
| TASK [apply dbru and oneoffs] *********************************************************************************************************** | |
| changed: [gns01] | |
| TASK [debug] **************************************************************************************************************************** | |
| ok: [gns01] => { | |
| "msg": "Patching Succeeded by KKK DBA Team – InsaneDBA was here…" | |
| } | |
| PLAY RECAP ****************************************************************************************************************************** | |
| gns01 : ok=20 changed=17 unreachable=0 failed=0 skipped=0 rescued=0 ignored=0 |
Run root.sh with root user on all nodes. New database homes are getting ready for switching.
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| [ansible@blt01 ansible]$ cat projects/dbru/apply_dbru_runrootallnodes.yml | |
| – name: Run root.sh in the new Oracle database home | |
| hosts: all | |
| vars: | |
| new_db_home: /u01/app/oracle/product/19.22/dbhome_1 | |
| old_db_home: /u01/app/oracle/product/19.20/dbhome_1 | |
| remote_user: root | |
| tasks: | |
| – name: Execute root.sh | |
| ansible.builtin.command: | |
| cmd: "{{ new_db_home }}/root.sh" | |
| throttle: 1 | |
| – name: Replace old_dbhome value in .bashrc files with the new_dbhome value | |
| ansible.builtin.replace: | |
| path: "{{ item }}" | |
| regexp: "{{ old_db_home }}" | |
| replace: "{{ new_db_home }}" | |
| loop: | |
| – /root/.bashrc | |
| – /home/oracle/.bashrc | |
| [ansible@blt01 ansible]$ ansible-playbook -i hosts projects/dbru/apply_dbru_runrootallnodes.yml | |
| PLAY [Run root.sh in the new Oracle database home] ************************************************************************************** | |
| TASK [Gathering Facts] ****************************************************************************************************************** | |
| ok: [gns02] | |
| ok: [gns01] | |
| TASK [execute root.sh] ****************************************************************************************************************** | |
| changed: [gns02] | |
| changed: [gns01] | |
| TASK [replace old_dbhome value in .bashrc files with the new_dbhome value] ************************************************************** | |
| changed: [gns02] | |
| changed: [gns01] | |
| PLAY RECAP ****************************************************************************************************************************** | |
| gns01 : ok=3 changed=2 unreachable=0 failed=0 skipped=0 rescued=0 ignored=0 | |
| gns02 : ok=3 changed=2 unreachable=0 failed=0 skipped=0 rescued=0 ignored=0 |
We will copy the files we need from old dbhome to the new dbhome and update .bashrc file to use new dbhome values.As all databases on my environment use pure unified auditing, it is time to relink new homes with unified auditing option on. Now all databases will be modified with srvctl utility and oracle_home values will be updated. Instances will be started from new dbhomes on both nodes. Recovery process (MRP) will be started on standby databases. Here is the playbook takes care of everything.
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| [ansible@blt01 ansible]$ cat projects/dbru/finish_dbru_onallnodes.yml | |
| – name: Finish Database Release Update | |
| hosts: dbservers | |
| vars: | |
| new_db_home: /u01/app/oracle/product/19.22/dbhome_1 | |
| old_db_home: /u01/app/oracle/product/19.20/dbhome_1 | |
| patch_dir: /u01/app/Setup | |
| recovery_script_dest_dir: /home/oracle | |
| recovery_script_dir: /etc/ansible/projects | |
| remote_user: oracle | |
| tasks: | |
| – name: Remove response file from stage directory | |
| ansible.builtin.file: | |
| path: "{{ patch_dir }}/db_oop.rsp" | |
| state: absent | |
| – name: Copy old db home dependent network files to the new db home | |
| ansible.builtin.copy: | |
| src: "{{ old_db_home }}/network/admin/" | |
| dest: "{{ new_db_home }}/network/admin/" | |
| owner: oracle | |
| group: oinstall | |
| remote_src: yes | |
| – name: Copy old db home dependent dbs parameter files to the new db home | |
| ansible.builtin.copy: | |
| src: "{{ old_db_home }}/dbs/" | |
| dest: "{{ new_db_home }}/dbs/" | |
| owner: oracle | |
| group: oinstall | |
| remote_src: yes | |
| – name: Copy old db home dependent customized files to the new db home | |
| ansible.builtin.copy: | |
| src: "{{ old_db_home }}/sqlplus/admin/glogin.sql" | |
| dest: "{{ new_db_home }}/sqlplus/admin/" | |
| owner: oracle | |
| group: oinstall | |
| remote_src: yes | |
| – name: Relink unified auditing in new_dbhome | |
| ansible.builtin.command: | |
| cmd: "make -f ins_rdbms.mk uniaud_on ioracle" | |
| chdir: "{{ new_db_home }}/rdbms/lib" | |
| environment: | |
| ORACLE_HOME: "{{ new_db_home }}" | |
| – name: Register databases | |
| ansible.builtin.shell: | |
| cmd: "ps -ef | grep ora_pmon | grep -v grep | awk '{ print $NF }' | cut -c10-50 | awk '{print substr($1, 1, length($1)-1)}'" | |
| register: find_databases | |
| – name: Register instances | |
| ansible.builtin.shell: | |
| cmd: "ps -ef | grep ora_pmon | grep -v grep | awk '{ print $NF }' | cut -c10-50" | |
| register: find_instances | |
| – name: Modify databases with srvctl to run from new_dbhome | |
| ansible.builtin.command: | |
| cmd: "srvctl modify database -d {{ item }} -oraclehome {{ new_db_home }}" | |
| loop: "{{ find_databases.stdout_lines }}" | |
| run_once: true | |
| – name: Bounce instances and start from new_dbhome | |
| ansible.builtin.shell: | |
| cmd: | | |
| export dbname=$(echo {{ item }} | awk '{print substr($1, 1, length($1)-1)}') | |
| srvctl stop instance -d $dbname -i {{ item }} -f | |
| srvctl start instance -d $dbname -i {{ item }} | |
| loop: "{{ find_instances.stdout_lines }}" | |
| throttle: 1 | |
| – name: Remove patch setup folders from stage directory | |
| ansible.builtin.file: | |
| path: "{{ item }}" | |
| state: absent | |
| loop: | |
| – "{{ patch_dir }}/GIRU" | |
| – "{{ patch_dir }}/GIRUOneOff" | |
| – "{{ patch_dir }}/OJVM" | |
| – name: Copy start recovery file to remote server | |
| ansible.builtin.copy: | |
| src: "{{ recovery_script_dir }}/start_recover_onremote.sh" | |
| dest: "{{ recovery_script_dest_dir }}" | |
| owner: oracle | |
| group: oinstall | |
| mode: '0750' | |
| – name: Start recovery | |
| ansible.builtin.command: | |
| cmd: "{{ recovery_script_dest_dir }}/start_recover_onremote.sh {{ item }} {{ new_db_home }}" | |
| loop: "{{ find_instances.stdout_lines }}" | |
| register: recovery_output | |
| – name: Debug Recovery output | |
| ansible.builtin.debug: | |
| var: item.stdout | |
| loop: "{{ recovery_output.results }}" | |
| [ansible@blt01 ansible]$ cat projects/start_recover_onremote.sh | |
| #!/bin/bash | |
| export ORACLE_SID=$1 | |
| export ORACLE_HOME=$2 | |
| $ORACLE_HOME/bin/sqlplus -s /nolog << END_SQL | |
| conn / as sysdba | |
| set serveroutput on feedback off; | |
| DECLARE | |
| inst_id number; | |
| mrp number; | |
| inst_name varchar2(50); | |
| BEGIN | |
| IF sys_context ('userenv','database_role') = 'PHYSICAL STANDBY' | |
| THEN | |
| select instance_number, instance_name into inst_id, inst_name from v\$instance ; | |
| select count(*) into mrp from v\$managed_standby where process='MRP0'; | |
| if inst_id = 1 and mrp = 0 | |
| THEN | |
| execute immediate 'ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT'; | |
| dbms_output.put_line('Recovery started on: ' || inst_name); | |
| ELSE | |
| dbms_output.put_line('No action taken. '); | |
| END IF; | |
| ELSE | |
| dbms_output.put_line('No action taken. '); | |
| END IF; | |
| END; | |
| / | |
| exit; | |
| END_SQL | |
| # | |
| ——————– | |
| [ansible@blt01 ansible]$ ansible-playbook -i hosts projects/dbru/finish_dbru_onallnodes.yml | |
| PLAY [Finish Database Release Update] *************************************************************************************************** | |
| TASK [Gathering Facts] ****************************************************************************************************************** | |
| ok: [gns02] | |
| ok: [gns01] | |
| TASK [remove response file from stage directory] **************************************************************************************** | |
| changed: [gns01] | |
| ok: [gns02] | |
| TASK [Copy old db home dependent network files to the new db home] ********************************************************************** | |
| changed: [gns01] | |
| changed: [gns02] | |
| TASK [Copy old db home dependent dbs parameter files to the new db home] **************************************************************** | |
| changed: [gns01] | |
| changed: [gns02] | |
| TASK [Copy old db home dependent customized files to the new db home] ******************************************************************* | |
| ok: [gns01] | |
| ok: [gns02] | |
| TASK [replace old_dbhome value in .bashrc files with the new_dbhome value] ************************************************************** | |
| changed: [gns01] | |
| changed: [gns02] | |
| TASK [relink unified auditing in new_dbhome] ******************************************************************************************** | |
| changed: [gns01] | |
| changed: [gns02] | |
| TASK [register databases] *************************************************************************************************************** | |
| changed: [gns01] | |
| changed: [gns02] | |
| TASK [register instances] *************************************************************************************************************** | |
| changed: [gns01] | |
| changed: [gns02] | |
| TASK [modify databases with srvctl to run from new_dbhome] ****************************************************************************** | |
| changed: [gns01] => (item=gnsdb) | |
| TASK [bounce instances and start from new_dbhome] *************************************************************************************** | |
| changed: [gns01] => (item=gnsdb1) | |
| changed: [gns02] => (item=gnsdb2) | |
| TASK [remove patch setup folders from stage directory] ******************************************************************************** | |
| changed: [gns01] | |
| ok: [gns02] | |
| TASK [Copy start recovery file to remote server] **************************************************************************************** | |
| ok: [gns01] | |
| ok: [gns02] | |
| TASK [start_recovery] ******************************************************************************************************************* | |
| changed: [gns02] => (item=gnsdb2) | |
| changed: [gns01] => (item=gnsdb1) | |
| TASK [debug recovery output] ************************************************************************************************************ | |
| ok: [gns01] => (item={u'stderr_lines': [], u'ansible_loop_var': u'item', u'end': u'2024-03-16 14:15:18.074746', u'failed': False, u'stdout': u'Recovery started on: gnsdb1', u'changed': True, u'item': u'gnsdb1', u'delta': u'0:00:06.378593', u'cmd': u'/home/oracle/start_recover_onremote.sh gnsdb1 /u01/app/oracle/product/19.22/dbhome_1', u'stderr': u'', u'rc': 0, u'invocation': {u'module_args': {u'warn': True, u'executable': None, u'_uses_shell': True, u'strip_empty_ends': True, u'_raw_params': u'/home/oracle/start_recover_onremote.sh gnsdb1 /u01/app/oracle/product/19.22/dbhome_1', u'removes': None, u'argv': None, u'creates': None, u'chdir': None, u'stdin_add_newline': True, u'stdin': None}}, u'stdout_lines': [u'Recovery started on: gnsdb1'], u'start': u'2024-03-16 14:15:11.696153'}) => { | |
| "ansible_loop_var": "item", | |
| "item": { | |
| "ansible_loop_var": "item", | |
| "changed": true, | |
| "cmd": "/home/oracle/start_recover_onremote.sh gnsdb1 /u01/app/oracle/product/19.22/dbhome_1", | |
| "delta": "0:00:06.378593", | |
| "end": "2024-03-16 14:15:18.074746", | |
| "failed": false, | |
| "invocation": { | |
| "module_args": { | |
| "_raw_params": "/home/oracle/start_recover_onremote.sh gnsdb1 /u01/app/oracle/product/19.22/dbhome_1", | |
| "_uses_shell": true, | |
| "argv": null, | |
| "chdir": null, | |
| "creates": null, | |
| "executable": null, | |
| "removes": null, | |
| "stdin": null, | |
| "stdin_add_newline": true, | |
| "strip_empty_ends": true, | |
| "warn": true | |
| } | |
| }, | |
| "item": "gnsdb1", | |
| "rc": 0, | |
| "start": "2024-03-16 14:15:11.696153", | |
| "stderr": "", | |
| "stderr_lines": [], | |
| "stdout": "Recovery started on: gnsdb1", | |
| "stdout_lines": [ | |
| "Recovery started on: gnsdb1" | |
| ] | |
| }, | |
| "item.stdout": "Recovery started on: gnsdb1" | |
| } | |
| ok: [gns02] => (item={u'stderr_lines': [], u'ansible_loop_var': u'item', u'end': u'2024-03-16 14:15:11.960710', u'failed': False, u'stdout': u'No action taken.', u'changed': True, u'item': u'gnsdb2', u'delta': u'0:00:00.205470', u'cmd': u'/home/oracle/start_recover_onremote.sh gnsdb2 /u01/app/oracle/product/19.22/dbhome_1', u'stderr': u'', u'rc': 0, u'invocation': {u'module_args': {u'warn': True, u'executable': None, u'_uses_shell': True, u'strip_empty_ends': True, u'_raw_params': u'/home/oracle/start_recover_onremote.sh gnsdb2 /u01/app/oracle/product/19.22/dbhome_1', u'removes': None, u'argv': None, u'creates': None, u'chdir': None, u'stdin_add_newline': True, u'stdin': None}}, u'stdout_lines': [u'No action taken.'], u'start': u'2024-03-16 14:15:11.755240'}) => { | |
| "ansible_loop_var": "item", | |
| "item": { | |
| "ansible_loop_var": "item", | |
| "changed": true, | |
| "cmd": "/home/oracle/start_recover_onremote.sh gnsdb2 /u01/app/oracle/product/19.22/dbhome_1", | |
| "delta": "0:00:00.205470", | |
| "end": "2024-03-16 14:15:11.960710", | |
| "failed": false, | |
| "invocation": { | |
| "module_args": { | |
| "_raw_params": "/home/oracle/start_recover_onremote.sh gnsdb2 /u01/app/oracle/product/19.22/dbhome_1", | |
| "_uses_shell": true, | |
| "argv": null, | |
| "chdir": null, | |
| "creates": null, | |
| "executable": null, | |
| "removes": null, | |
| "stdin": null, | |
| "stdin_add_newline": true, | |
| "strip_empty_ends": true, | |
| "warn": true | |
| } | |
| }, | |
| "item": "gnsdb2", | |
| "rc": 0, | |
| "start": "2024-03-16 14:15:11.755240", | |
| "stderr": "", | |
| "stderr_lines": [], | |
| "stdout": "No action taken.", | |
| "stdout_lines": [ | |
| "No action taken." | |
| ] | |
| }, | |
| "item.stdout": "No action taken." | |
| } | |
| PLAY RECAP ****************************************************************************************************************************** | |
| gns01 : ok=15 changed=11 unreachable=0 failed=0 skipped=0 rescued=0 ignored=0 | |
| gns02 : ok=14 changed=8 unreachable=0 failed=0 skipped=0 rescued=0 ignored=0 |
For primary databases, it is time to run datapatch. The following playbook will be run on only first nodes of primary databases.
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| [ansible@blt01 ansible]$ cat projects/dbru/apply_datapatch_onprimary.yml | |
| – name: Apply Data Patch on First Nodes of Primary Databases | |
| hosts: dbservers | |
| vars: | |
| new_db_home: /u01/app/oracle/product/19.22/dbhome_1 | |
| db_role: PRIMARY | |
| remote_user: oracle | |
| run_once: true | |
| tasks: | |
| – name: Register instances | |
| ansible.builtin.shell: | |
| cmd: ps -ef | grep ora_pmon | grep -v grep | awk '{ print $NF }' | cut -c10-50 | |
| register: find_instances | |
| – name: Apply datapatch | |
| ansible.builtin.shell: | |
| cmd: | | |
| export instname={{ item }} | |
| export dbname=$(echo $instname | awk '{print substr($1, 1, length($1)-1)}') | |
| export inst_id=$(echo $instname | awk '{print substr($1,length($1),1)}') | |
| export dbrole=$(srvctl config database -d $dbname | grep "Database role" | awk '{ print $NF }') | |
| if [ "$inst_id" == 1 ] && [ "$dbrole" == "{{ db_role }}" ]; then | |
| export ORACLE_HOME={{ new_db_home }} | |
| export ORACLE_SID=$instname | |
| $ORACLE_HOME/OPatch/datapatch -verbose | |
| fi | |
| register: datapatch_candidates | |
| loop: "{{ find_instances.stdout_lines }}" | |
| throttle: 1 | |
| – name: Debug datapatch output | |
| ansible.builtin.debug: | |
| var: item.stdout | |
| loop: "{{ datapatch_candidates.results }}" |
It is time to say goodbye to old database homes. We will remove it by simply running deinstall command. This one is a little bit tricky, we will use expect module for replying prompt questions and here it is.
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| [ansible@blt01 ansible]$ cat projects/dbru/remove_dbru_onallnodes.yml | |
| – name: Deinstall old Oracle Database Software and remove old oracle database home | |
| hosts: all | |
| vars: | |
| old_db_home: /u01/app/oracle/product/19.20/dbhome | |
| patch_dir: /u01/app/Setup | |
| ansible_python_interpreter: /usr/bin/python3 | |
| removed_value: REMOVED="T"/> | |
| remote_user: root | |
| tasks: | |
| – name: Deinstall old_dbhome | |
| become: yes | |
| become_user: oracle | |
| become_method: su | |
| ansible.builtin.expect: | |
| command: /bin/bash -c {{ old_db_home }}/deinstall/deinstall | |
| responses: | |
| 'Do you want to continue': 'y' | |
| 'Specify the list of database names': '' | |
| echo: yes | |
| timeout: 600 | |
| run_once: true | |
| register: deinstall_result | |
| – name: Check inventory whether old_dbhome is tagged with removed flag | |
| ansible.builtin.shell: | |
| cmd: "grep '{{ old_db_home }}' /u01/app/oraInventory/ContentsXML/inventory.xml | awk '{ print $NF }'" | |
| register: removedflag | |
| – name: Remove parent directory of old_db_home | |
| ansible.builtin.file: | |
| path: "{{ old_db_home | dirname }}" | |
| state: absent | |
| environment: | |
| OLDDBHOME: "{{ old_db_home }}" | |
| when: deinstall_result.rc == 0 or removedflag.stdout == removed_value | |
| [ansible@blt01 ansible]$ ansible-playbook -i hosts projects/dbru/remove_dbru_onallnodes.yml | |
| PLAY [Deinstall old Oracle Database Software and remove old oracle database home] ******************************************************* | |
| TASK [Gathering Facts] ****************************************************************************************************************** | |
| ok: [gns02] | |
| ok: [gns01] | |
| TASK [deinstall old_dbhome] ************************************************************************************************************* | |
| changed: [gns01] | |
| TASK [check inventory whether old_dbhome is tagged with removed flag] ******************************************************************* | |
| changed: [gns02] | |
| changed: [gns01] | |
| TASK [Remove parent directory of old_db_home] ******************************************************************************************* | |
| changed: [gns01] | |
| changed: [gns02] | |
| PLAY RECAP ****************************************************************************************************************************** | |
| gns01 : ok=4 changed=3 unreachable=0 failed=0 skipped=0 rescued=0 ignored=0 | |
| gns02 : ok=3 changed=2 unreachable=0 failed=0 skipped=0 rescued=0 ignored=0 |
All database software patching is complete. It is a wonderful example of automate-first approach. With this approach, you decrease chances of human error and increase productivity.
Revised on March 23, 2024;
I have incorporated valuable feedback and revised all my playbooks to adhere to some best practices.
- Ensured that all tasks consistently refer to the same variables.
- Improved the playbook’s readability and formatting for better clarity.
- Replaced some shell module commands with the cmd module for better compatibility and robustness.
- Replaced unzip commands with the unarchive module for better management of archives.
- Converted Ansible variables to lowercase for consistency and clarity.
- Adopted the ansible.builtin format for built-in modules to ensure compatibility across different Ansible versions, enhance readability, future-proof playbooks, and prevent potential conflicts with custom or user-defined modules.
Also, consolidated all playbooks into a single file as shown below.
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| – name: Import playbook to prepare new Oracle Database Home | |
| ansible.builtin.import_playbook: prepare_dbru_allnodes.yml | |
| – name: Import playbook to apply Oracle Oracle Database Release Update | |
| ansible.builtin.import_playbook: apply_dbru_onfirstnode.yml | |
| – name: Import playbook to run root.sh in new Oracle Database Homes | |
| ansible.builtin.import_playbook: apply_dbru_runrootallnodes.yml | |
| – name: Import playbook to finish Oracle Database Release Update | |
| ansible.builtin.import_playbook: finish_dbru_onallnodes.yml | |
| – name: Import playbook to apply Datapatch on Primary Databases | |
| ansible.builtin.import_playbook: apply_datapatch_onprimary.yml |
Hope it helps.
Osman’s DBlog 
Leave a reply to Ansible for DBA : Learn – My NoteBook Cancel reply