Oracle Database 12c: PSUs vs database proactive bundle patches

Simon Pane

June 14, 2016

Tags: Oracle, Patching, Technical Track, 12C, Database 12C

Background

When it comes to performing quarterly patches to the Oracle database the options used to be pretty straight forward:

Apply either the Patch Set Update "PSU" (or Cumulative Patch Update "CPU") to Oracle database homes on UNIX/Linux.
Apply the "Windows Bundle Patch" to Oracle homes on Windows.

However, Oracle made things a little more confusing as of Oracle 12c with the introduction of " Database Proactive Bundle Patches". Especially with recent changes to the description of these Database Proactive Bundle Patches as of the April 2016 patch cycle. Hence DBAs may have some logical questions:

What exactly is a Database Proactive Bundle Patch and how does it related to the PSU?
Is the Database Proactive Bundle Patch a complete superset of the PSU?
Which should I use for my 12c Oracle homes, especially if I don't use Engineered Systems or Database In-Memory?
Can I change my mind and switch which one I'd like to apply?

Those are all good questions which we'll try to address in this article as they require a bit of investigation to answer completely from the various Oracle documentation sources and My Oracle Support (MOS) notes. For simplicity I'll focus only on a simple and typical configuration of RDBMS homes on Oracle Linux 6 and exclude RAC and GI configurations.

"Database Proactive Bundle Patch" = DBBP

First of all, it's prudent to get some terminology straight. My Oracle Support (MOS) documents usually refer to this new patching mechanism as "Database Proactive Bundle Patch" and sometimes just "Database Proactive Patch". Hence one might think that the appropriate acronym is "DPBP" however as we'll see later, the actual acronym used within the database is "DBBP". Hence for simplicity the acronym DBBP will be used from this point onwards. Further, it's very important to realize that these new 12c DBBPs are not related to the "Windows Bundle Patch" (BP) that we're already familiar with. With Oracle 12c the Windows Bundle Patches continue – DBBPs are not related to the Windows BPs and instead are new and apply to the OSs such as Linux, Solaris, AIX, etc. Finally, MOS Note 12.1.0.2 Database Proactive Bundle Patches / Bundle Patches for Engineered Systems and DB In-Memory - List of Fixes in each Bundle (Doc ID 1937782.1) clearly states that:

The name of these bundle patches was changed to "Database Proactive Bundle Patch" in April 2016.
 
 The patches include fixes for both Engineered Systems and for DB In-Memory.
 They can be used on both Exadata and non-Exadata systems, and can be used for both RAC and non-RAC configurations.

Hence it now appears like the DBBP is a new generic high level quarterly patch that's no longer limited to Engineered Systems or Database In-Memory (DBIM). That same document also states:

Each bundle patch includes the following component patches in separate subdirectories:
 * Clusterware component the same as GI PSU
 * ACFS component the same as GI PSU
 
 A database bundle component that includes all DB PSU fixes along with additional fixes specifically for Engineered Systems and DB In-Memory.

The important text there is " includes all DB PSU fixes". And also " along with additional fixes specifically for Engineered Systems and DB In-Memory". But questions remain such as whether the actual PSU or the bugs fixed is a subset of the DBBP and whether there are other "proactive" fixes in the DBBP that apply to all databases regardless of Engineered Systems or the use of DBIM?

Existing References

At this point it's worthwhile to familiarize ourselves with some relevant references. The following two MOS notes are handy references and a starting point for all database patching activities: Quick Reference to Patch Numbers for Database PSU, SPU(CPU), Bundle Patches and Patchsets (Doc ID 1454618.1) Oracle Recommended Patches -- Oracle Database (Doc ID 756671.1) Mike Dietrich's Oracle Upgrade blog is also a great reference and has some interesting articles already such as: Oracle April 2016 PSU and Proactive BPs are there Oracle Database BP April16 applied successfully Can I apply a BP on top of a PSU? Or vice versa? In that last article he already answers one of the initial questions about whether we can switch strategies and start using DBBPs instead of PSUs or vice versa. From his article (and testing to verify), the answer is "NO". A DBBP cannot be applied on top of a previous quarter's PSU and vice versa. If you want to switch techniques and for example start applying DBBPs instead of PSUs you'll need to un-install the the old conflicting PSU patches first. The process to do so is pretty straight forward but will require a longer outage window and more downtime due to the additional steps:

Run "opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir <patch dir>" to detect the conflicts
Rollback the conflicting patches using "opatch rollback -id <patch number>"
Apply the new patch using "opatch apply"
Run "datapatch -verbose" which will handle un-installing the old patch and installing the new one in the catalog all at once.

To answer and prove the remaining questions, we need to do a little digging.

Downloading the Necessary Patches

MOS notes 1454618.1 and/or 756671.1 (both linked to above) are great starting points when preparing to apply quarterly database patches. From either of those documents we can see that (for April 2016) the PSU patch number is 22291127 (12.1.0.2.160419) and the DBBP patch number is 22899531. And of course, whenever patching we need to update OPatch first using patch number 6880880. To make downloading patches directly to the server as simple as possible, I like to use Maris Elsins' getMOSPatch utility when I already know the patch numbers (if not, remember that the patch download screen from MOS can also build a wget based shell script):

$ # Update OPatch
 $ ./getMOSPatch.sh patch=6880880 regexp=".*121.*"
 Oracle Support Userid: pane@pythian.com
 Oracle Support Password:
 
 Getting list of files for patch 6880880 for "Linux x86-64"
 Files to download:
  p6880880_121010_Linux-x86-64.zip
 
 Downloading the patches:
 Downloading file p6880880_121010_Linux-x86-64.zip ...
  % Total % Received % Xferd Average Speed Time Time Time Current
  Dload Upload Total Spent Left Speed
 100 120M 100 120M 0 0 1639k 0 0:01:15 0:01:15 --:--:-- 2675k
 p6880880_121010_Linux-x86-64.zip completed with status: 0
 
 $
 $ # Download APR2016 Patches
 $
 $ ./getMOSPatch.sh patch=22291127
 Oracle Support Userid: pane@pythian.com
 Oracle Support Password:
 
 Getting list of files for patch 22291127 for "Linux x86-64"
 Files to download:
  p22291127_121020_Linux-x86-64.zip
 
 Downloading the patches:
 Downloading file p22291127_121020_Linux-x86-64.zip ...
  % Total % Received % Xferd Average Speed Time Time Time Current
  Dload Upload Total Spent Left Speed
 100 172M 100 172M 0 0 1361k 0 0:02:09 0:02:09 --:--:-- 2386k
 p22291127_121020_Linux-x86-64.zip completed with status: 0
 
 $
 $ ./getMOSPatch.sh patch=22899531
 Oracle Support Userid: pane@pythian.com
 Oracle Support Password:
 
 Getting list of files for patch 22899531 for "Linux x86-64"
 Files to download:
  p22899531_121020_Linux-x86-64.zip
 
 Downloading the patches:
 Downloading file p22899531_121020_Linux-x86-64.zip ...
  % Total % Received % Xferd Average Speed Time Time Time Current
  Dload Upload Total Spent Left Speed
 100 1762M 100 1762M 0 0 1635k 0 0:18:23 0:18:23 --:--:-- 1260k
 p22899531_121020_Linux-x86-64.zip completed with status: 0
 
 $

Right away it's apparent that the DBBP is considerably larger (~10x) however as it states in the documentation, it also includes GI and OCW fixes whereas the DB PSU does not. After unzipping the downloads, a simple way to compare the contents of each is using the tree command:

$ tree -d -L 5 |grep [0-9] |grep -v oc4j
 +-- p22291127_121020_Linux-x86-64
 | +-- 22291127
 | +-- 19769480
 | +-- 20299023
 | +-- 20831110
 | +-- 21359755
 | +-- 21948354
 | +-- 22291127
 +-- p22899531_121020_Linux-x86-64
  +-- 22899531
  +-- 21436941
  +-- 22502518
  +-- 22806133
  | +-- 20243804
  | +-- 20415006
  | +-- 20594149
  | +-- 20788771
  | +-- 20950328
  | +-- 21125181
  | +-- 21359749
  | +-- 21527488
  | +-- 21694919
  | +-- 21949015
  | +-- 22806133
  +-- 23006522

From the above we can see that the sub-patches are non-overlapping. In other words, the list of sub-patches under the PSU (22291127) and under the DBBP (22899531) are mutually exclusive. However the README for the DBBP patch ( 22899531) states:

The Database Proactive Patch patches are cumulative and include the Database PSU and associated CPU program security content.

This statement is a little perplexing as based on that, one may expect to see the PSU patch (22291127) listed under the DBBP patch (22899531), however the tree command above shows that it clearly is not. Therefore I would consider the phrase " include the Database PSU" a little misleading. (Also we need to take note from that same README that patch 22806133 is the sub-patch (from the main DBBP patch 22899531) which applies to non-RAC RDBMS homes.) All of the bugs fixed are listed in the official documentation for each patch: For PSU: 12.1.0.2 Patch Set Updates - List of Fixes in each PSU (Doc ID 1924126.1) For DBBP: 12.1.0.2 Database Proactive Bundle Patches / Bundle Patches for Engineered Systems and DB In-Memory - List of Fixes in each Bundle (Doc ID 1937782.1) However since there are so many bugs, comparing them all manually is tedious. Instead it's a little easier to work with by quickly installing both into the same Oracle homes in two identical VMs (one cloned from the other) and comparing the results.

After Installing both Patches

Installing both PSUs and DBBPs are pretty straight forward. For non-RAC it's as simple as " opatch apply" and " datapatch –verbose" for both. Remembering that the test environments are non-RAC and running in Oracle VirtualBox (and not on an Engineered System of any kind), it's easy to list what patches are actually installed into the Oracle homes using commands such as:

$ opatch lsinventory -bugs_fixed | grep "^[0-9]" | awk '{print $1}' | sort –u
 $ opatch lsinventory -bugs_fixed | grep "^[0-9]" | awk '{print $1 " " $2}' | sort -u

Comparing the results between a home patched with the APR2016 PSU and a home patched with the APR2016 DBBP shows that:

The PSU includes a total of 315 bug fixes.
The DBBP includes a total of 1239 bug fixes.
All of the 315 bugs fixed in the PSU are part of the 1239 bugs fixed in the DBBP.

Hence a proof that the DBBP bug fixes is indeed a superset of the PSU bug fixes even though the parent patch numbers are not correlated. Oracle develops different patches for each but either way, the PSU bugs are included somewhere in the DBBP patches. Looking how each appears inside the catalog: For the PSU:

SQL> select patch_id, patch_uid, version, status, bundle_series, description
  2 from dba_registry_sqlpatch;
 
  PATCH_ID PATCH_UID VERSION STATUS BUNDLE_SERIES
 ---------- ---------- -------------------- --------------- ------------------------------
 DESCRIPTION
 ----------------------------------------------------------------------------------------------------
  21555660 19361790 12.1.0.2 SUCCESS
 Database PSU 12.1.0.2.5, Oracle JavaVM Component (Oct2015)
 
  22291127 19694308 12.1.0.2 SUCCESS PSU
 Database Patch Set Update : 12.1.0.2.160419 (22291127)
 
 SQL>

And or the DBBP:

SQL> select patch_id, patch_uid, version, status, bundle_series, description
  2 from dba_registry_sqlpatch;
 
  PATCH_ID PATCH_UID VERSION STATUS BUNDLE_SERIES
 ---------- ---------- -------------------- --------------- ------------------------------
 DESCRIPTION
 ----------------------------------------------------------------------------------------------------
  21555660 19361790 12.1.0.2 SUCCESS
 Database PSU 12.1.0.2.5, Oracle JavaVM Component (Oct2015)
 
  22806133 19983161 12.1.0.2 SUCCESS DBBP
 DATABASE BUNDLE PATCH: 12.1.0.2.160419 (22806133)
 
 SQL>

So both contain the JavaVM PSU for Oct2015 (even though I just installed PSU and not the DB/JavaVM PSU combo patch) but the BUNDLE_SERIES column now displays "DBBP". Therefore queries that check the catalog for patches applied based on the predicate " BUNDLE_SERIES='PSU'" will need to be updated. That aside it's interesting to see if the "Proactive" DBBP includes bug fixes that are not included in the PSU which affect the RDBMS when not using Engineered Systems or DBIM. A quick random spot check leads to some random bugs such as:

Bug 20212067 - ORA-600 [kzaxpsqbnd:ENCD-lxgcnv] on CLOB update with XML, EXTENDED audit (Doc ID 20212067.8)
 Bug 22212940 : ORACLE LISTENERS KEEP CRASHING AND DISPATCHERS REFUSES CONNECTIONS
 Bug 20445031 - ORA-600 [723] can occur with PDB (kxftSetResult memory) (Doc ID 20445031.8)
 Bug 21977392 ORA-600 [4193] ORA-600 [ktbair1] ORA-600 [1234] ORA-600 [6856] block type 'ktu undo block' on recovery of encrypt datafile

Hence it seems easy to find bugs that are fixed in the DBBP and not the PSU which are not at all related to Engineered Systems, DBIM, GI, etc. Or patches that Oracle considers "proactive" and not worth including in the PSU even though many of them can potentially lead to ORA-00600 and other serious errors. And of course that's just a random sampling. A detailed analysis can expect to find hundreds of other generic bug fixes in the DBBP but not the PSU.

What about Standard Edition?

The official README documentation for the APR2016 Database Proactive Bundle patch 22899531 states:

In this document Oracle Database Home refers to Enterprise Edition. Standard Edition Database software installs should install Database PSU.

However, testing installation of the APR2016 DBBP into a 12.1.0.2 Standard Edition 2 Oracle home succeeds without any issues at all.

$ opatch apply
Oracle Interim Patch Installer version 12.1.0.1.12
Copyright (c) 2016, Oracle Corporation. All rights reserved.

Oracle Home : /u01/app/oracle/product/12.1.0/dbhome_2
Central Inventory : /u01/app/oraInventory
from : /u01/app/oracle/product/12.1.0/dbhome_2/oraInst.loc
OPatch version : 12.1.0.1.12
OUI version : 12.1.0.2.0
Log file location : /u01/app/oracle/product/12.1.0/dbhome_2/cfgtoollogs/opatch/opatch2016-06-06_14-44-17PM_1.log

Verifying environment and performing prerequisite checks...
OPatch continues with these patches: 20243804 20415006 20594149 20788771 20950328 21125181 21359749 21527488 21694919 21949015 22806133

Do you want to proceed? [y|n]
y
User Responded with: Y
All checks passed.
y

Problem with accessing response file of "/u01/app/oracle/product/12.1.0/dbhome_2/ccr/bin/setupCCR".

Please shutdown Oracle instances running out of this ORACLE_HOME on the local system.
(Oracle Home = '/u01/app/oracle/product/12.1.0/dbhome_2')

Is the local system ready for patching? [y|n] y
User Responded with: Y
Backing up files...
Applying sub-patch '20243804' to OH '/u01/app/oracle/product/12.1.0/dbhome_2'
ApplySession: Optional component(s) [ oracle.has.crs, 12.1.0.2.0 ] , [ oracle.oraolap, 12.1.0.2.0 ] not present in the Oracle Home or a higher version is found.

Patching component oracle.rdbms.deconfig, 12.1.0.2.0...

Patching component oracle.tfa, 12.1.0.2.0...

....

Patching component oracle.hadoopcore, 12.1.0.2.0...
Composite patch 22806133 successfully applied.
Log file location: /u01/app/oracle/product/12.1.0/dbhome_2/cfgtoollogs/opatch/opatch2016-06-06_14-44-17PM_1.log

OPatch succeeded.
$

Log file for this invocation: /u01/app/oracle/cfgtoollogs/sqlpatch/sqlpatch_18910_2016_06_06_15_30_18/sqlpatch_invocation.log

Connecting to database...OK
Bootstrapping registry and package to current versions...done
Determining current state...done

Current state of SQL patches:
Bundle series DBBP:
ID 160419 in the binary registry and not installed 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:
22806133 (DATABASE BUNDLE PATCH: 12.1.0.2.160419 (22806133))

Installing patches...
Patch installation complete. Total patches installed: 1

Validating logfiles...
Patch 22806133 apply: SUCCESS
logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/22806133/19983161/22806133_apply_ORCLSE2_2016Jun06_15_31_28.log (no errors)
SQL Patching tool complete on Mon Jun 6 15:33:16 2016
$

And within the SE2 database, again it appears as the DBBP has been successfully applied:

SQL> select patch_id, patch_uid, version, status, bundle_series, description
  2 from dba_registry_sqlpatch;
 
  PATCH_ID PATCH_UID VERSION STATUS BUNDLE_SERIES
 ---------- ---------- -------------------- --------------- ------------------------------
 DESCRIPTION
 ----------------------------------------------------------------------------------------------------
  22806133 19983161 12.1.0.2 SUCCESS DBBP
 DATABASE BUNDLE PATCH: 12.1.0.2.160419 (22806133)
 
 SQL>

So no difference at all. (And recall from earlier that 22806133 is simply the sub-patch that applies to non-RAC RDBMS homes from the main DBBP 22899531.) Further, checking the alert log of the SE2 database upon startup lists all of the DBBP bugs fixed with no related errors or warnings. Hence the use of the word " should" instead of " must" in the documentation. I wouldn't recommend going against Oracle's recommendation (which is that the PSU should be used for SE2) however it's interesting to know that if the DBBP is accidentally applied to a SE2 home, it doesn't appear to cause any issues.

Conclusions

A little research through the patch README and MOS documentation combined with a little experimentation helps clarify the difference between the PSUs and DBBPs for non-Engineered Systems and databases not using DBIM. The key findings are:

The new 12c "Database Proactive Bundle Patches" (DBBP) are not the same as the traditional "Windows Bundle Patches" (BP).
The bugs fixed in the DBBP is a complete superset of the bugs fixed in the PSU however the supporting patch numbers do not correlate.
In some places, the documentation is misleading: the DBBP doesn't "contain the PSU" but does contain the same bug fixes as the PSU.
The DBBP contains approximate 4x the number of bug fixes overall. The difference must be ones Oracle considers "proactive".
It's easy to find many bugs which are not related to Engineered Systems or DBIM (appear generic) that are included in the DBBP but not the PSU.
DBBPs must be applied upon previous DBBPs and vice versa. You cannot apply a DBBP on-top of a DB PSU or a DB PSU on-top of a DBBP without un-installing the old patches first.

And a few other interesting observations include:

Even though the documentation recommends against it, the DBBP can technically be applied to a Standard Edition 2 RDBMS home.
Queries against DBA_REGISTRY_SQLPATCH with the predicate "bundle_patch='PSU'" will need to be adjusted to also include "DBBP".

Which is best to use on a go-forward basis will depend on your comfort level balancing the risk of exposure to bugs vs. risk of problems introduced by patches and bug fixes. Personally I've found that RDBMS bug fixes are usually quite reliable (and don't introduce further errors or issues) and hence to minimize exposure to possible bugs I'm personally inclined to switch to the new Database Proactive Bundle Patches for all Oracle 12c EE homes as a standard regardless of use of Engineered Systems, or DBIM.

Insight and analysis of technology and business strategy