Oracle Grid Release Update by using Ansible Playbooks

Ansible is a friend in need and a friend indeed for DBAs.

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 this blog post, I will mention about Redhat Ansible and show how you can patch your grid infrastructure by using it.

Ansible is an open source, command-line IT automation software application written in Python. It can configure systems, deploy software, and orchestrate advanced workflows to support application deployment, system updates, and more. Ansible's main strengths are simplicity and ease of use. It is a little bit similar to dcli commands in Exadata environments, but much more powerful as you may run playbooks which consist of different tasks.

Ansible stands out from other configuration management tools because it doesn't need agents installed on target hosts. Instead, it leverages SSH for connecting to them and executes commands directly through the shell. This means Ansible communicates with remote hosts securely using SSH, eliminating the need for additional software on the managed machines. It's like having a conversation with someone directly rather than going through intermediaries. This simplicity and efficiency make Ansible easy to deploy and use across a wide range of environments.

In this post, 

I will use blt01 host as the ansible host. Target environment is a two node cluster. (gns01, gns02). I will patch grid software to 19.22 by using out-of-place methodology with no down time. 

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 Grid 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.

	[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)

    * A sample one-off patch (555.1 Recommended) - p34672698_1922000DBRU_Linux-x86-64.zip

    * Grid home Setup - LINUX.X64_193000_grid_home.zip    

Lets start with creating new grid home directory. Here is the first playbook we will run.

	[ansible@blt01 ansible]$ cat projects/giru/prepare_grid_allnodes.yml
	- name: Prepare new grid home directories
	hosts: dbservers
	remote_user: root
	vars:
	new_grid_home: /u01/app/19.22/grid
	patch_dir: /u01/app/Setup
	tasks:
	- name: Ensure patch directory exists
	ansible.builtin.file:
	path: "{{ patch_dir }}"
	state: directory
	owner: oracle
	group: oinstall
	- name: Ensure new grid home directory exists
	ansible.builtin.file:
	path: "{{ new_grid_home }}"
	state: directory
	owner: oracle
	group: oinstall
	[ansible@blt01 ansible]$ ansible-playbook -i hosts projects/giru/prepare_grid_allnodes.yml
	PLAY [dbservers] ********************************************************************************************************************
	TASK [Gathering Facts] **************************************************************************************************************
	ok: [gns02]
	ok: [gns01]
	TASK [Ensure patch directory exists] ************************************************************************************************
	ok: [gns01]
	ok: [gns02]
	TASK [Ensure new grid home directory exists] ****************************************************************************************
	changed: [gns01]
	changed: [gns02]
	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_grid_allnodes.yml hosted with ❤ by GitHub

As new grid homes are created on target servers, I will unzip 19.3 grid_home.zip and apply Release updates and one off patches on top of it with the below playbook.

	[ansible@blt01 ansible]$ cat projects/giru/apply_giru_onfirstnode.yml
	- name: Apply Oracle Grid Infrastructure Release Update
	hosts: dbservers
	remote_user: oracle
	run_once: true
	vars:
	u01_size_mb: 11045
	new_grid_home: /u01/app/19.22/grid
	old_grid_home: /u01/app/19.20/grid
	patch_dir: /u01/app/Setup
	source_patch_dir: /u01/app/Setup
	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 grid home
	ansible.builtin.unarchive:
	src: "{{ patch_dir }}/LINUX.X64_193000_grid_home.zip"
	dest: "{{ new_grid_home }}"
	remote_src: yes
	- name: Unzip OPatch to new grid home
	ansible.builtin.unarchive:
	src: "{{ patch_dir }}/p6880880_122010_Linux-x86-64.zip"
	dest: "{{ new_grid_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 }}/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 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: "{{ old_grid_home }}/bin/olsnodes | tr '\n' ',' | sed 's/,$//'"
	register: cluster_nodes
	- name: Register OSDBA value for response file
	ansible.builtin.shell:
	cmd: "grep 'OSDBA=' {{ old_grid_home }}/install/response/grid_20*.rsp"
	register: osdba
	- name: Register OSOPER value for response file
	ansible.builtin.shell:
	cmd: "grep 'OSOPER=' {{ old_grid_home }}/install/response/grid_20*.rsp"
	register: osoper
	- name: Register OSASM value for response file
	ansible.builtin.shell:
	cmd: "grep 'OSASM=' {{ old_grid_home }}/install/response/grid_20*.rsp"
	register: osasm
	- name: Create contents of response file
	ansible.builtin.copy:
	dest: "{{ patch_dir }}/grid_oop.rsp"
	content: |
	oracle.install.responseFileVersion=/oracle/install/rspfmt_crsinstall_response_schema_v19.0.0
	INVENTORY_LOCATION=/u01/app/oraInventory
	oracle.install.option=CRS_SWONLY
	ORACLE_BASE=/u01/app/oracle
	{{ osdba.stdout }}
	{{ osoper.stdout }}
	{{ osasm.stdout }}
	oracle.install.crs.config.clusterNodes={{ cluster_nodes.stdout }}
	ORACLE_HOME={{ new_grid_home }}
	oracle.install.crs.config.ClusterConfiguration=STANDALONE
	oracle.install.crs.config.configureAsExtendedCluster=false
	oracle.install.crs.config.gpnp.configureGNS=false
	oracle.install.crs.config.autoConfigureClusterNodeVIP=false
	- name: Clean up grid_home setup zip files
	ansible.builtin.file:
	path: "{{ item }}"
	state: absent
	loop:
	- "{{ patch_dir }}/LINUX.X64_193000_grid_home.zip"
	- "{{ patch_dir }}/p35940989_190000_Linux-x86-64.zip"
	- "{{ patch_dir }}/p34672698_1922000DBRU_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 GIRU and oneoffs
	ansible.builtin.shell:
	cmd: |
	{{ new_grid_home }}/gridSetup.sh -silent \
	-ignorePrereqFailure -waitforcompletion \
	-applyRU {{ patch_dir }}/GIRU/35940989 \
	-applyOneOffs {{ patch_dir }}/GIRUOneOff/34672698 \
	-responsefile {{ patch_dir }}/grid_oop.rsp
	environment:
	CV_ASSUME_DISTID: OL7
	NEWGRIDHOME: "{{ new_grid_home }}"
	register: apply_giru
	failed_when: "'Successfully Setup Software' not in apply_giru.stdout"
	- name: Output patching success message
	ansible.builtin.debug:
	msg: "Patching succeeded. InsaneDBA was here."
	when: apply_giru.rc in [6,0]
	[ansible@blt01 ansible]$ ansible-playbook -i hosts -l gns01 projects/giru/apply_giru_onfirstnode.yml
	PLAY [apply Oracle Grid Infrastructure Release Update] **********************************************************************************
	TASK [Gathering Facts] ******************************************************************************************************************
	ok: [gns01]
	TASK [copy setup files to target server] ************************************************************************************************
	ok: [gns01]
	TASK [unzip new grid_home] **************************************************************************************************************
	changed: [gns01]
	TASK [unzip OPatch to new grid_home] ****************************************************************************************************
	changed: [gns01]
	TASK [prepare_patches directories] ******************************************************************************************************
	changed: [gns01]
	TASK [unzip GIRU system 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 osasm value for responsefile] ********************************************************************************************
	changed: [gns01]
	TASK [create contents of responsefile] **************************************************************************************************
	changed: [gns01]
	TASK [Clean up grid_home setup zip files] ***********************************************************************************************
	changed: [gns01]
	TASK [check u01 free disk space] ********************************************************************************************************
	changed: [gns01]
	TASK [apply giru and oneoffs] ***********************************************************************************************************
	changed: [gns01]
	TASK [output patching success message] **************************************************************************************************
	ok: [gns01] => {
	"msg": "Patching Succeeded by KKK DBA Team - InsaneDBA was here..."
	}
	PLAY RECAP ******************************************************************************************************************************
	gns01 : ok=16 changed=13 unreachable=0 failed=0 skipped=0 rescued=0 ignored=0

view raw apply_giru_onfirstnode.yml hosted with ❤ by GitHub

Now we will tag new home in Oracle central inventory with the -switchGridHome command, run root.sh from new grid homes and grid software will be started on new grid_home on both nodes. Later we will copy the files we need from old grid_home to the new grid_home and update .bashrc file to use new grid_home values.

	[ansible@blt01 ansible]$ cat projects/giru/finish_giru_allnodes.yml
	- name: Finish Oracle Grid Infrastructure Release Update
	hosts: dbservers
	remote_user: root
	vars:
	crs_value: CRS="true"/>
	new_grid_home: /u01/app/19.22/grid
	old_grid_home: /u01/app/19.20/grid
	patch_dir: /u01/app/Setup
	tasks:
	- name: Register cluster nodes for response file
	ansible.builtin.shell:
	cmd: "{{ old_grid_home }}/bin/olsnodes | tr '\n' ',' | sed 's/,$//'"
	register: cluster_nodes
	- name: Tag the new grid home in inventory
	ansible.builtin.shell:
	cmd: |
	$NEWGRIDHOME/gridSetup.sh -silent \
	-switchGridHome oracle.install.option=CRS_SWONLY \
	ORACLE_HOME={{ new_grid_home }} \
	oracle.install.crs.config.clusterNodes={{ cluster_nodes.stdout }} \
	oracle.install.crs.rootconfig.executeRootScript=false
	become: yes
	become_user: oracle
	environment:
	NEWGRIDHOME: "{{ new_grid_home }}"
	- name: Check CRS flag in inventory
	ansible.builtin.shell:
	cmd: |
	grep "{{ new_grid_home }}" /u01/app/oraInventory/ContentsXML/inventory.xml |
	grep CRS |
	awk 'END { print $6 }'
	register: crsflag
	failed_when: crsflag.stdout != crs_value
	- name: Execute root.sh and start grid from new home
	ansible.builtin.command:
	cmd: "{{ new_grid_home }}/root.sh"
	throttle: 1
	- name: Remove response file from stage directory
	ansible.builtin.file:
	path: "{{ patch_dir }}/grid_oop.rsp"
	state: absent
	- name: Copy old grid home dependent files to the new grid home
	ansible.builtin.copy:
	src: "{{ old_grid_home }}/sqlplus/admin/glogin.sql"
	dest: "{{ new_grid_home }}/sqlplus/admin/"
	owner: oracle
	group: oinstall
	remote_src: yes
	- name: Replace old_grid_home value in .bashrc files with the new_gridhome value
	ansible.builtin.replace:
	path: "{{ item }}"
	regexp: "{{ old_grid_home }}"
	replace: "{{ new_grid_home }}"
	loop:
	- /root/.bashrc
	- /home/oracle/.bashrc
	[ansible@blt01 ansible]$ ansible-playbook -i hosts projects/giru/finish_giru_allnodes.yml
	PLAY [Finish Oracle Grid Infrastructure Release Update] *********************************************************************************
	TASK [Gathering Facts] ******************************************************************************************************************
	ok: [gns02]
	ok: [gns01]
	TASK [register cluster nodes for responsefile] ******************************************************************************************
	changed: [gns01]
	changed: [gns02]
	TASK [tag the new grid_home in inventory] ***********************************************************************************************
	changed: [gns02]
	changed: [gns01]
	TASK [check crs flag in inventory] ******************************************************************************************************
	changed: [gns01]
	changed: [gns02]
	TASK [execute root.sh and start grid from new home] *************************************************************************************
	changed: [gns01]
	changed: [gns02]
	TASK [remove response file from stage directory] ****************************************************************************************
	ok: [gns02]
	changed: [gns01]
	TASK [Copy old grid home dependent files to the new grid home] **************************************************************************
	ok: [gns01]
	ok: [gns02]
	TASK [replace old_gridhome value in .bashrc files with the new_gridhome value] **********************************************************
	changed: [gns01]
	changed: [gns02]
	PLAY RECAP ******************************************************************************************************************************
	gns01 : ok=8 changed=6 unreachable=0 failed=0 skipped=0 rescued=0 ignored=0
	gns02 : ok=8 changed=5 unreachable=0 failed=0 skipped=0 rescued=0 ignored=0

view raw finish_giru_allnodes.yml hosted with ❤ by GitHub

It is time to say goodbye to old grid 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.

	[ansible@blt01 ansible]$ cat projects/giru/remove_giru_allnodes.yml
	- name: Deinstall Oracle Grid Infrastructure
	hosts: dbservers
	remote_user: root
	vars:
	removed_value: REMOVED="T"/>
	old_grid_home: /u01/app/19.20/grid
	patch_dir: /u01/app/Setup
	ansible_python_interpreter: /usr/bin/python3
	tasks:
	- name: Create response file for silent deinstallation
	ansible.builtin.shell:
	cmd: "{{ old_grid_home }}/deinstall/deinstall -checkonly"
	become: yes
	become_user: oracle
	become_method: su
	throttle: 1
	- name: Register old grid_home response file for deinstallation
	ansible.builtin.shell:
	cmd: "ls -lsrt /tmp/deinstall*/response/*.rsp | tail -1 | awk '{print $NF}'"
	register: oldgridresponse
	- name: Unlock old grid_home for deinstallation
	ansible.builtin.shell:
	cmd: "{{ old_grid_home }}/crs/install/rootcrs.sh -unlock -crshome {{ old_grid_home }} -paramfile {{ oldgridresponse.stdout }}"
	- name: Recursively change ownership of old_grid_home directory
	ansible.builtin.file:
	path: "{{ old_grid_home | dirname }}"
	state: directory
	recurse: yes
	owner: oracle
	group: oinstall
	- name: Deinstall old grid_home
	ansible.builtin.expect:
	command: "/bin/bash -c '{{ old_grid_home }}/deinstall/deinstall'"
	responses:
	'Do you want to continue': 'y'
	echo: yes
	timeout: 600
	become: yes
	become_user: oracle
	run_once: true
	register: deinstall_result
	- name: Check inventory whether old_grid_home is tagged with removed flag
	ansible.builtin.shell:
	cmd: "grep '{{ old_grid_home }}' /u01/app/oraInventory/ContentsXML/inventory.xml | awk '{ print $NF }'"
	register: removedflag
	- name: Remove old grid_home directory
	ansible.builtin.file:
	path: "{{ old_grid_home | dirname }}"
	state: absent
	when: deinstall_result.rc == 0 or removedflag.stdout == removed_value
	[ansible@blt01 ansible]$ ansible-playbook -i hosts projects/giru/removelocal_giru_allnodes.yml
	PLAY [Deinstall Oracle Grid Infrastructure] *********************************************************************************************
	TASK [Gathering Facts] ******************************************************************************************************************
	ok: [gns02]
	ok: [gns01]
	TASK [create response file for silent deinstall] ****************************************************************************************
	changed: [gns01]
	changed: [gns02]
	TASK [register old grid_home responsefile for deinstall] ********************************************************************************
	changed: [gns01]
	changed: [gns02]
	TASK [unlock old grid_home for deinstall] ***********************************************************************************************
	changed: [gns01]
	changed: [gns02]
	TASK [Recursively change ownership of old_grid_home directory] **************************************************************************
	changed: [gns02]
	changed: [gns01]
	TASK [deinstall oldgridhome] ************************************************************************************************************
	changed: [gns01]
	changed: [gns02]
	TASK [check inventory whether old_gridhome is tagged with removed flag] *****************************************************************
	changed: [gns01]
	changed: [gns02]
	TASK [remove old grid_home directory] ***************************************************************************************************
	changed: [gns01]
	changed: [gns02]
	PLAY RECAP ******************************************************************************************************************************
	gns01 : ok=8 changed=7 unreachable=0 failed=0 skipped=0 rescued=0 ignored=0
	gns02 : ok=8 changed=7 unreachable=0 failed=0 skipped=0 rescued=0 ignored=0

view raw remove_giru_allnodes.yml hosted with ❤ by GitHub

All grid 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 21, 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 and included the playbook to initiate the recovery process (MRP) on standby databases as shown below.

	#!/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
	#

view raw start_recover_onremote.sh hosted with ❤ by GitHub

	- name: Start Database Recovery Process on Standby Databases
	hosts: dbservers
	remote_user: oracle
	vars:
	db_home: /u01/app/oracle/product/19.22/dbhome_1
	recovery_script_dest_dir: /home/oracle
	recovery_script_dir: /etc/ansible/projects
	patch_dir: /u01/app/Setup
	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: 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: '750'
	- name: Debug: List found instances
	ansible.builtin.debug:
	msg: "Found database instance: {{ item }}"
	loop: "{{ find_instances.stdout_lines }}"
	- name: Start recovery
	ansible.builtin.shell: "{{ recovery_script_dest_dir }}/start_recover_onremote.sh {{ item }} {{ db_home }}"
	loop: "{{ find_instances.stdout_lines }}"
	register: recovery_output
	- name: Debug: Recovery output
	ansible.builtin.debug:
	var: item.stdout
	loop: "{{ recovery_output.results }}"

view raw start_recover_allnodes.yml hosted with ❤ by GitHub

	- name: Import playbook to prepare new Oracle Grid Infrastructure Home
	ansible.builtin.import_playbook: prepare_grid_allnodes.yml
	- name: Import playbook to apply Oracle Grid Infrastructure Release Update
	ansible.builtin.import_playbook: apply_giru_onfirstnode.yml
	- name: Import playbook to finish Oracle Grid Infrastructure Release Update
	ansible.builtin.import_playbook: finish_giru_allnodes.yml
	- name: Import playbook to start Database Recovery Process on Standby Databases
	ansible.builtin.import_playbook: start_recover_allnodes.yml
	- name: Import playbook to remove old grid home
	ansible.builtin.import_playbook: remove_giru_allnodes.yml

view raw apply_grid_update_main.yml hosted with ❤ by GitHub

Hope it helps.