If you know how to patch a single-instance database, then you'll learn that patching a RAC database has some additional steps and risk after reading this post.
If you'd like to patch a whole Grid infra and database, you should refer to: How to Patch Grid Infrastructure and Oracle Home, it's different.
Please note that, this tutorial explain how to update Oracle home software of a RAC database on all nodes. It does not intend to patch both Grid infrastructure and Oracle home, they are different Patch Set Update (PSU).
A. Pre-Installation
In this case, we're going to apply patch 31219939 (Oracle JavaVM Component 12.1.0.2.200714 Database PSU) for database on 12.1.0.2.0, which is solely for updating Oracle home.
1. Prepare Patch
Suppose we have done the following things:
- Replace OPatch with the latest one on all database servers, i.e. all nodes.
- Upload to the all database servers, i.e. all nodes.
- Unzip the patch on all database servers, again, all nodes.
[oracle@primary01 ~]$ unzip -q /home/oracle/patches/p31219939_121020_Linux-x86-64.zip -d /home/oracle/patches
2. Stop Working Instance
We update Oracle home on each node one by one, not parallelly.
[oracle@primary01 ~]$ srvctl stop instance -d compdb -i primdb1
[oracle@primary01 ~]$ srvctl status database -d compdb
Instance primdb1 is not running on node primary01
Instance primdb2 is running on node primary02
3. Check Requisite and Conflict
[oracle@primary01 ~]$ cd /home/oracle/patches/31219939/
[oracle@primary01 31219939]$ opatch prereq CheckConflictAgainstOHWithDetail -ph ./
Oracle Interim Patch Installer version 12.2.0.1.21
Copyright (c) 2020, Oracle Corporation. All rights reserved.
PREREQ session
Oracle Home : /u01/app/oracle/product/12.1.0/db_1
Central Inventory : /u01/app/oraInventory
from : /u01/app/oracle/product/12.1.0/db_1/oraInst.loc
OPatch version : 12.2.0.1.21
OUI version : 12.1.0.2.0
Log file location : /u01/app/oracle/product/12.1.0/db_1/cfgtoollogs/opatch/opatch2020-08-29_14-56-23PM_1.log
Invoking prereq "checkconflictagainstohwithdetail"
Prereq "checkConflictAgainstOHWithDetail" passed.
OPatch succeeded.
As we can see, we download and use the newest OPatch utility 12.2.0.1.21 to perform the patching.
4. Backup Oracle Home
Even though you have OS backups, I recommend you to backup all of Oracle software including Oracle inventory and Oracle home in this server, in case something goes wrong.
B. Installation
After a thoroughly prerequisite check, we have more confident to apply the patch.
1. Apply Patch
If there's nothing wrong with the patch, we can start to apply the patch.
[oracle@primary01 31219939]$ opatch apply
Oracle Interim Patch Installer version 12.2.0.1.21
Copyright (c) 2020, Oracle Corporation. All rights reserved.
Oracle Home : /u01/app/oracle/product/12.1.0/db_1
Central Inventory : /u01/app/oraInventory
from : /u01/app/oracle/product/12.1.0/db_1/oraInst.loc
OPatch version : 12.2.0.1.21
OUI version : 12.1.0.2.0
Log file location : /u01/app/oracle/product/12.1.0/db_1/cfgtoollogs/opatch/opatch2020-08-29_15-59-35PM_1.log
Verifying environment and performing prerequisite checks...
OPatch continues with these patches: 31219939
Do you want to proceed? [y|n]
y
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/db_1')
Is the local system ready for patching? [y|n]
y
User Responded with: Y
Backing up files...
Applying interim patch '31219939' to OH '/u01/app/oracle/product/12.1.0/db_1'
ApplySession: Optional component(s) [ oracle.sqlj, 12.1.0.2.0 ] not present in the Oracle Home or a higher version is found.
Patching component oracle.javavm.server, 12.1.0.2.0...
Patching component oracle.javavm.server.core, 12.1.0.2.0...
Patching component oracle.rdbms.dbscripts, 12.1.0.2.0...
Patching component oracle.rdbms, 12.1.0.2.0...
Patching component oracle.javavm.client, 12.1.0.2.0...
Patching component oracle.dbjava.jdbc, 12.1.0.2.0...
Patching component oracle.dbjava.ic, 12.1.0.2.0...
Patch 31219939 successfully applied.
Log file location: /u01/app/oracle/product/12.1.0/db_1/cfgtoollogs/opatch/opatch2020-08-29_15-59-35PM_1.log
OPatch succeeded.
2. Start Working Instance
[oracle@primary01 31219939]$ srvctl start instance -d compdb -i primdb1
[oracle@primary01 31219939]$ srvctl status database -d compdb
Instance primdb1 is running on node primary01
Instance primdb2 is running on node primary02
3. Apply Patch on Other Nodes
Next, you have to apply the patch on other nodes before going any further. That is to say, please make sure all nodes in the cluster are patched before post-installation.
C. Post-Installation
Post-installation means for loading SQL patch into the database.
1. Disable Cluster Database
For upgrading database only on one side, we disable the cluster database, which means only one instance is allowed at a time in the same cluster.
SQL> conn / as sysdba
Connected.
SQL> alter system set cluster_database=false scope=spfile;
System altered.
2. Restart the Instance
After that, we startup the present working instance.
[oracle@primary01 31219939]$ srvctl stop database -d compdb
[oracle@primary01 31219939]$ srvctl status database -d compdb
Instance primdb1 is not running on node primary01
Instance primdb2 is not running on node primary02
SQL> conn / as sysdba
Connected.
SQL> startup upgrade
ORACLE instance started.
We use startup upgrade to restrict accesses and allow a new release to run.
3. SQL Patch
Post-installation of patching is to load modified SQL into the database. For 11g or earlier releases, we use catbundle.sql to do SQL patch. For 12c or later releases, we use datapatch instead.
[oracle@primary01 31219939]$ datapatch -verbose
SQL Patching tool version 12.1.0.2.0 Production on Tue Aug 29 16:29:59 2020
Copyright (c) 2012, 2016, Oracle. All rights reserved.
Log file for this invocation: /u01/app/oracle/cfgtoollogs/sqlpatch/sqlpatch_41077_2020_08_25_16_29_59/sqlpatch_invocation.log
Connecting to database...OK
Bootstrapping registry and package to current versions...done
Determining current state...done
Current state of SQL patches:
Patch 31219939 (Database PSU 12.1.0.2.200714, Oracle JavaVM Component (JUL2020)):
Installed in the binary registry only
Bundle series PSU:
ID 200714 in the binary registry and ID 200714 in the SQL registry
Adding patches to installation queue and performing prereq checks...
Installation queue:
Nothing to roll back
The following patches will be applied:
31219939 (Database PSU 12.1.0.2.200714, Oracle JavaVM Component (JUL2020))
Installing patches...
Patch installation complete. Total patches installed: 1
Validating logfiles...
Patch 31219939 apply: SUCCESS
logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/31219939/23602978/31219939_apply_COMPDB_2020Aug25_16_30_37.log (no errors)
SQL Patching tool complete on Tue Aug 29 16:34:21 2020
4. Enable Cluster Database
SQL> conn / as sysdba
Connected.
SQL> alter system set cluster_database=true scope=spfile;
System altered.
5. Restart Database
SQL> shutdown
...
[oracle@primary01 31219939]$ srvctl start database -d compdb
[oracle@primary01 31219939]$ srvctl status database -d compdb
Instance primdb1 is running on node primary01
Instance primdb2 is running on node primary02
6. Recompile All Invalid Objects
SQL> conn / as sysdba
Connected.
SQL> @?/rdbms/admin/utlrp.sql
TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_BGN 2020-08-29 16:40:49
DOC> The following PL/SQL block invokes UTL_RECOMP to recompile invalid
DOC> objects in the database. Recompilation time is proportional to the
DOC> number of invalid objects in the database, so this command may take
DOC> a long time to execute on a database with a large number of invalid
DOC> objects.
DOC>
DOC> Use the following queries to track recompilation progress:
DOC>
DOC> 1. Query returning the number of invalid objects remaining. This
DOC> number should decrease with time.
DOC> SELECT COUNT(*) FROM obj$ WHERE status IN (4, 5, 6);
DOC>
DOC> 2. Query returning the number of objects compiled so far. This number
DOC> should increase with time.
DOC> SELECT COUNT(*) FROM UTL_RECOMP_COMPILED;
DOC>
DOC> This script automatically chooses serial or parallel recompilation
DOC> based on the number of CPUs available (parameter cpu_count) multiplied
DOC> by the number of threads per CPU (parameter parallel_threads_per_cpu).
DOC> On RAC, this number is added across all RAC nodes.
DOC>
DOC> UTL_RECOMP uses DBMS_SCHEDULER to create jobs for parallel
DOC> recompilation. Jobs are created without instance affinity so that they
DOC> can migrate across RAC nodes. Use the following queries to verify
DOC> whether UTL_RECOMP jobs are being created and run correctly:
DOC>
DOC> 1. Query showing jobs created by UTL_RECOMP
DOC> SELECT job_name FROM dba_scheduler_jobs
DOC> WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>
DOC> 2. Query showing UTL_RECOMP jobs that are running
DOC> SELECT job_name FROM dba_scheduler_running_jobs
DOC> WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>#
PL/SQL procedure successfully completed.
TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_END 2020-08-29 16:40:55
DOC> The following query reports the number of objects that have compiled
DOC> with errors.
DOC>
DOC> If the number is higher than expected, please examine the error
DOC> messages reported with each object (using SHOW ERRORS) to see if they
DOC> point to system misconfiguration or resource constraints that must be
DOC> fixed before attempting to recompile these objects.
DOC>#
OBJECTS WITH ERRORS
-------------------
0
DOC> The following query reports the number of errors caught during
DOC> recompilation. If this number is non-zero, please query the error
DOC> messages in the table UTL_RECOMP_ERRORS to see if any of these errors
DOC> are due to misconfiguration or resource constraints that must be
DOC> fixed before objects can compile successfully.
DOC>#
ERRORS DURING RECOMPILATION
---------------------------
0
Function created.
PL/SQL procedure successfully completed.
Function dropped.
...Database user "SYS", database schema "APEX_040200", user# "98" 16:41:57
...Compiled 0 out of 3014 objects considered, 0 failed compilation 16:41:58
...271 packages
...263 package bodies
...452 tables
...11 functions
...16 procedures
...3 sequences
...457 triggers
...1320 indexes
...211 views
...0 libraries
...6 types
...0 type bodies
...0 operators
...0 index types
...Begin key object existence check 16:41:58
...Completed key object existence check 16:41:58
...Setting DBMS Registry 16:41:58
...Setting DBMS Registry Complete 16:41:58
...Exiting validate 16:41:58
PL/SQL procedure successfully completed.
[oracle@primary01 ~]$ opatch lsinventory
...
Patch 31219939 : applied on Tue Aug 29 16:09:24 CST 2020
Unique Patch ID: 23602978
Patch description: "Database PSU 12.1.0.2.200714, Oracle JavaVM Component (JUL2020)"
Created on 22 Jun 2020, 19:59:38 hrs PST8PDT
Bugs fixed:
...