Oracle Database Release Update by Using Ansible Playbooks

Ansible to Rule Them All

I’ve moved my blog from https://insanedba.blogspot.com to https://dincosman.com Please update your bookmarks and follow/subscribe at the new address for all the latest updates and content. More up-to-date content of this post may be available there.

In the previous post "Oracle Grid Software Patching by using Ansible Playbooks", I mentioned how Ansible orchestration can be used to make Oracle Grid Software Patching a cleaner and simpler process.

Now I will also patch database software using Ansible Playbooks.

In this post, 

I will use the blt01 host as the ansible host. The target environment is a two-node RAC database (gns01, gns02). I will patch the grid software to 19.22 using the out-of-place methodology with no downtime. 

Although there are lots of useful blog posts about how you may use Ansible for automating tasks, there is not much about complete Oracle database software patching. 

Ansible is simply installed by running "yum install ansible"  from the EPEL repository. Add your hosts (gns01, gns02) in the hosts file under /etc/ansible/hosts and create ssh equivalency for the oracle and root users.

	[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

view raw install_ansible.txt hosted with ❤ by GitHub

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

Let's start with creating a new database directory. Here is the first playbook we will run.

	[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

view raw prepare_dbru_allnodes.yml hosted with ❤ by GitHub

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.

	[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

view raw apply_dbru_onfirstnode.yml hosted with ❤ by GitHub

Run root.sh with the root user on all nodes. New database homes are getting ready for switching.

	[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

view raw apply_dbru_runrootallnodes.yml hosted with ❤ by GitHub

We will copy the files we need from the old dbhome to the new dbhome and update the .bashrc file to use new dbhome values. Since all databases in my environment use pure unified auditing, it is time to relink new homes with the unified auditing option on. Now all databases will be modified with the srvctl utility and oracle_home values will be updated. Instances will be started from new dbhomes on both nodes. The recovery process (MRP) will be started on standby databases. Here is the playbook that takes care of everything.

	[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

view raw finish_dbru_onallnodes.yml hosted with ❤ by GitHub

For primary databases, it is time to run datapatch. The following playbook will be run on only the first nodes of primary databases.

	[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 }}"

view raw apply_datapatch_onprimary.yml hosted with ❤ by GitHub

It is time to say goodbye to old database homes. We will remove them by simply running the deinstall command. This one is a little bit tricky, as we will use the expect module for replying to prompt questions. Here it is.

	[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

view raw remove_dbru_onallnodes.yml hosted with ❤ by GitHub

All database software patching is complete. It is a wonderful example of an automate-first approach. With this approach, you decrease the 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.

	- 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

view raw apply_db_update_main.yml hosted with ❤ by GitHub

Hope it helps.