Patching Oracle Databases

By Ahmed Baraka

Oracle Database Administration from Zero to Hero- a course by Ahmed Baraka

 Objectives
In this lecture, you will learn how to perform the following:
• Describe Patching Maintenance Types
• Describe types of patches on 12c R1 and prior
• Describe Oracle's Critical Patch Update (CPU) Program
• Describe Patching Strategies (Methods) on Oracle database 12c R1 and prior
• Describe Oracle database version numbers on 12c R1 and prior
• Describe types of patches on Oracle database 12c R2 and after
• Describe Oracle database version numbers on 12c R2 and after
• Understand RU and RUR projection timeline
• Obtain the latest patches for Oracle database
• Apply patches general procedure on non-RAC non-ASM databases
• Use OPatch to apply patches
• Query patch inventory from SQL
Oracle Database Administration from Zero to Hero- a course by Ahmed Baraka
 The Target of Database Patching

Bugs/ Security Optimizer

Regression Threats Issues

Patching

Oracle Database Administration from Zero to Hero- a course by Ahmed Baraka

 Patching Maintenance Types

Oracle Database Administration from Zero to Hero- a course by Ahmed Baraka

 Patching Maintenance Types
• Reactive: patches applied as a reaction to fix specific issues
- Usually delivered as “Interim Patches” (historically known as “one-off”)
- Patches provided on demand for a given “defect, version, platform”
- Go through basic sanity tests
- Usually included in the next patch set release

• Proactive: patches applied on the scheduled maintenance window

- May contain: bug fixes, security issue fixes, optimizer fixes, and new
features
- Go through extra level of testing
- Naming convention and version number format have been changing along
Oracle database development history

Oracle Database Administration from Zero to Hero- a course by Ahmed Baraka

Oracle Database Administration from Zero to Hero- a course by Ahmed Baraka
 12cR1 and older: Types of Patches
• Security Patch Update (SPU)
- a cumulative collection of security fixes
- delivered on pre-defined quarterly schedule

• Patch Set Update (PSU)

- a cumulative collection of fixes for bugs encountered in the field and
security fixes
- guaranteed not to contain any changes to the optimizer or fixes
- delivered on pre-defined quarterly schedule
BP
• Bundle Patch (BP)
PSU
- a cumulative collection of fixes to address
bugs, security fixes, optimizer, and may span SPU
multiple stack components: DB and Grid
Oracle Database Administration from Zero to Hero- a course by Ahmed Baraka
 Oracle's Critical Patch Update (CPU) Program
• A program to release patches to cover security vulnerabilities in Oracle
product families (currently every quarter)
• Released quarterly on the third Tuesday of Jan, Apr, Jul, and Oct
• For Oracle database:
- Prior to [Link], it is included in the SPUs
- On [Link] and onward, it is included in the Release Update Revision
patches (RUR)
• If a critical security flaw is discovered between the quarterly Critical
Patch Updates, a Security Alert is released (with a possible interim fix)
• To be notified of the release of Critical Patch Updates and Security
Alerts, follow the instructions in MOS note 2813763.1
Oracle Database Administration from Zero to Hero- a course by Ahmed Baraka
 12cR1 and older: Patching Strategies (Methods)
• SPU (CPU) only, SPU/PSU (recommended), or BP
• SPU/CPU Only:
- Covers security issues
- Requires basic testing

• PSU requires further testing

• It is not advisable to switch from PSU method to SPU method
• From database BP to SPU is not allowed
• From PSU to database BP is allowed
- Use the latest version of OPatch utility

Oracle Database Administration from Zero to Hero- a course by Ahmed Baraka

 The Challenge of Following SPU-Only Method

PSU 1 is
SPU 1 SPU 2 PSU 1 SPU 3 SPU 4 needed?

• If applying a PSU is needed, you might need to rollback some SPUs

and the interim patches released after the required PSU (if any)

Oracle Database Administration from Zero to Hero- a course by Ahmed Baraka

 Version Numbers: 12R1 and Prior

11 . 2 . 0 . 4 . 160419

12 . 1 . 0 . 2 . 220719

Major release Update release date

(YYMMDD)

Patch Release

Oracle Database Administration from Zero to Hero- a course by Ahmed Baraka

 12cR2 and later: Types of Patches
• Release Updates (RU)
- Bug fixes, functional, and security
- Released every quarter
• Release Update Revisions (RUR)
- New security fixes
- Come out as part of the CPU every quarter
- 2 RUR are released for each RU

Oracle Database Administration from Zero to Hero- a course by Ahmed Baraka

 Version Numbers: 12R2 and Later

18 . 1 . 0 . 0 . 160419

19 . 3 . 0 . 0 . 220719

Release Update Update release date

Revision (RUR) Ver. (YYMMDD)
Database
Release Reserved for
Release Update future use
(RU) Version

Oracle Database Administration from Zero to Hero- a course by Ahmed Baraka

 RU and RUR Projection Timeline Example

2019 2020 2021

April July October Jan April July Oct Jan
19.2.0

Oracle Database Administration from Zero to Hero- a course by Ahmed Baraka

 RU and RUR Projection Timeline Example

2019 2020 2021

April July October Jan April July Oct Jan
19.2.0 19.3.0
19.2.1

Oracle Database Administration from Zero to Hero- a course by Ahmed Baraka

 RU and RUR Projection Timeline Example

2019 2020 2021

April July October Jan April July Oct Jan
19.2.0 19.3.0 19.4.0
19.2.1 19.3.1
19.2.2

Oracle Database Administration from Zero to Hero- a course by Ahmed Baraka

 RU and RUR Projection Timeline Example

2019 2020 2021

April July October Jan April July Oct Jan
19.2.0 19.3.0 19.4.0 19.5.0
19.2.1 19.3.1 19.4.1
19.2.2 19.3.2

Oracle Database Administration from Zero to Hero- a course by Ahmed Baraka

 RU and RUR Projection Timeline Example

2019 2020 2021

April July October Jan April July Oct Jan
19.2.0 19.3.0 19.4.0 19.5.0 19.6.0
19.2.1 19.3.1 19.4.1 19.5.1
19.2.2 19.3.2 19.4.2

Oracle Database Administration from Zero to Hero- a course by Ahmed Baraka

 RU and RUR Projection Timeline Example

2019 2020 2021

April July October Jan April July Oct Jan ..
19.2.0 19.3.0 19.4.0 19.5.0 19.6.0 .. .. .. ..
19.2.1 19.3.1 19.4.1 19.5.1 .. .. .. ..
19.2.2 19.3.2 19.4.2 .. .. .. ..
.. .. .. ..
.. .. ..
.. ..

Oracle Database Administration from Zero to Hero- a course by Ahmed Baraka

 Recommended Patching Strategy

2019 2020 2021

April July October Jan April July Oct Jan ..
19.2.0 19.3.0 19.4.0 19.5.0 19.6.0 .. .. .. ..
19.2.1 19.3.1 19.4.1 19.5.1 .. .. .. ..
19.2.2 19.3.2 19.4.2 .. .. .. ..
.. .. .. ..
.. .. ..
.. ..

Apply the next RU each quarter

Oracle Database Administration from Zero to Hero- a course by Ahmed Baraka

 Alternative Patching Strategy

2019 2020 2021

April July October Jan April July Oct Jan ..
19.2.0 19.3.0 19.4.0 19.5.0 19.6.0 .. .. .. ..
19.2.1 19.3.1 19.4.1 19.5.1 .. .. .. ..
19.2.2 19.3.2 19.4.2 .. .. .. ..
.. .. .. ..
.. .. ..
.. ..

Apply the first RUR, the second RUR, then the RUs

Oracle Database Administration from Zero to Hero- a course by Ahmed Baraka

 Alternative Patching Strategy

2019 2020 2021

April July October Jan April July Oct Jan ..
19.2.0 19.3.0 19.4.0 19.5.0 19.6.0 .. .. .. ..
19.2.1 19.3.1 19.4.1 19.5.1 .. .. .. ..
19.2.2 19.3.2 19.4.2 .. .. .. ..
.. .. .. ..
.. .. ..
.. ..

Apply the second RUR2 only

Oracle Database Administration from Zero to Hero- a course by Ahmed Baraka

 Patch Product/Component Targets
• Patch possible targets:
- Database
- OJVM
- GI (Grid Infrastructure)
• OJVM could be added to DB or GI in one patch (Combo)
• OJVM patches can be applied on database that are patched to at least
October 2014 DB PSU (or equivalent SPU or Database Patch for
Exadata)
• In Windows, rollback the old OJVM patch, apply the latest bundle
patch, then apply the latest OJVM patch

Oracle Database Administration from Zero to Hero- a course by Ahmed Baraka

 Obtaining the Latest Patches for Oracle Database
• You need an account in Oracle Support to obtain them
- Linked to a CSI
• Assistant: Download Reference for Oracle Database/GI Update,
Revision, PSU, SPU(CPU), Bundle Patches, Patchsets and Base
Releases (Doc ID 2118136.2)
• Primary Note for Database Proactive Patch Program (Doc ID 888.1)
- Oracle Database 19c Important Recommended One-off Patches (Doc ID
555.1)
• For obtaining Windows Database Bundle Patches, (Doc ID 161549.2)
• Patches for Oracle database 12.2 and older are password protected

Oracle Database Administration from Zero to Hero- a course by Ahmed Baraka

 Applying Patches General Procedure on non-RAC
non-ASM Databases
1. Download the required patch file from Oracle Support and extract it into a staging directory
2. Read the associated README file
3. Validate version of current OPatch utility and upgrade it if required
4. Validate conflicting patches
5. Take the backup of $ORACLE_HOME and associated databases of same home (recommended)
6. Disable jobs in crontab and DBMS scheduler jobs, and stop applications
7. Stop all services associated to $ORACLE_HOME
8. Apply DB patch on $ORACLE_HOME
9. Validate applied patch in the inventory
10. If required, apply the OJVM patch
11. Start databases (but not the listener) associated to $ORACLE_HOME
12. If required, execute the datapatch on each database and validate
13. Start all services associated to $ORACLE_HOME
14. Enable jobs in crontab and DBMS scheduler jobs then start the applications
Oracle Database Administration from Zero to Hero- a course by Ahmed Baraka
 About OPatch
• OPatch is a Java-based utility that assists you with the process of
applying patches to Oracle software.
• OPatch is included with the Oracle database and Clusterware homes: in
a directory named as OPatch
• Can be downloaded from the Internet and upgraded separately
• Further information located in Doc ID 293369.1: Master Note For
OPatch

Oracle Database Administration from Zero to Hero- a course by Ahmed Baraka

 Downloading OPatch
• [Link]

2
The page gets refreshed and
displays the compatible
database version
1
Select the required OPatch
version or Database version

Oracle Database Administration from Zero to Hero- a course by Ahmed Baraka

 Downloading OPatch

3
Select the target platform
and architecture. Wait for
the page to get refreshed.

Oracle Database Administration from Zero to Hero- a course by Ahmed Baraka

 Preparing the Environment for Using OPatch
• Check the ORACLE_HOME environment variable
. oraevn
ORACLE_SID = [oracle] ? oradb
ORACLE_HOME = [/u01/app/oracle/product/19.0.0/dbhome_1] ?

• Upgrade OPatch to latest version (if needed)

- Download the latest OPatch version for the current database version (MOS
ID293369.1)
- Delete $ORACLE_HOME\OPatch
- Extract the latest OPatch zip files into $ORACLE_HOME
• Update the PATH environment variable to include the OPatch directory
• Change the current directory to the patch staging directory

Oracle Database Administration from Zero to Hero- a course by Ahmed Baraka

 OPatch Common Commands
• To know the current OPatch version:
opatch version

• To list the patches that are already applied to Oracle home:

opatch lsinventory
opatch lspatches

• To apply the patch (after preparing the environment and changing the
directory to the patch staging directory):
opatch apply

• Reference: OPatch User's Guide

Oracle Database Administration from Zero to Hero- a course by Ahmed Baraka

 Patch Conflict Resolution
• Conflict may rise from applying interim patches in conjunction with RU
and RUR patches
opatch prereq CheckConflictAgainstOHWithDetail -ph ./
opatch prereq CheckConflictAgainstOH -ph ./

• For information about resolving patch conflicts, see the following

documents:
- MOS Note 1941934.1: My Oracle Support Patch Conflict Checker Overview
- MOS Note 1091294.1: How to Use the My Oracle Support Conflict Checker
Tool for Patches Installed with OPatch
- MOS Note 1321267.1: Database Patch Conflict Resolution

Oracle Database Administration from Zero to Hero- a course by Ahmed Baraka

 Querying Patch Inventory from SQL
• DBMS_QOPATCH package is an interface to view the database patches
that are applied
• DBA_REGISTRY_SQLPATCH retrieves list of the SQL patches that are
applied to the database.

Oracle Database Administration from Zero to Hero- a course by Ahmed Baraka

 Patching Best Practice
• Follow the Apply-all-RUs approach in a pre-defined schedule
• Apply the patches on a testing environment first
- If not available, go for applying the RUs
• Keep the number of interim patches installed to a minimum
- Install interim patches only for specific issues that you know will apply to
your environment.
• Engineered systems (like ODA and Exadata) have their own patching
methods
• For large IT environments, you can automate the patching process
using OEM Cloud Control or a scripting language like Ansible

Oracle Database Administration from Zero to Hero- a course by Ahmed Baraka

 Mike Dietrich Blog
• Senior Director Product Management, Oracle Database Upgrade,
Migrations and Patching
• Follow his blog:
[Link]

Oracle Database Administration from Zero to Hero- a course by Ahmed Baraka

 Summary
In this lecture, you should have learnt how to perform the following:
• Describe Patching Maintenance Types
• Describe types of patches on 12c R1 and prior
• Describe Oracle's Critical Patch Update (CPU) Program
• Describe Patching Strategies (Methods) on Oracle database 12c R1 and prior
• Describe Oracle database version numbers on 12c R1 and prior
• Describe types of patches on Oracle database 12c R2 and after
• Describe Oracle database version numbers on 12c R2 and after
• Understand RU and RUR projection timeline
• Obtain the latest patches for Oracle database
• Apply patches general procedure on non-RAC non-ASM databases
• Use OPatch to apply patches
• Query patch inventory from SQL
Oracle Database Administration from Zero to Hero- a course by Ahmed Baraka