Keeping your Oracle database software up to date is
a critical and time-consuming task for DBAs. For many years now,
Oracle has been releasing Critical Patch Updates on a quarterly
basis. These patches, as the name implies, contain critical updates
to the software, often released in response to a newly found security
vulnerability. More recently, Oracle has also been releasing Patch
Set Updates on a quarterly basis. These also contain important fixes
to the Oracle software. However, there is confusion about the
difference between the two and more importantly, confusion about which one
needs to be applied. So whats the difference and which one should you
apply?
According to Oracle Support article ID
1446582.1: Frequently Asked Questions (FAQ) Patching Oracle Database
Server:
“A PSU is a collection of proactive, stabilizing
cumulative patches for a particular product version (base release or patch
set). PSUs are cumulative and include all of the security fixes from
CPU patches, plus additional fixes. Critical Patch Updates are the
primary means of releasing security fixes for Oracle products. CPUs are
cumulative with respect to prior CPUs and generally contain only security
fixes.” Read the patch types to know the difference between the types of oracle Database patches.
So, there you have it. CPUs are smaller
and more focused than PSU and mostly deal with security issues. PSUs
contain bug fixes AND they contain the security fixes from the CPU. When
you download a PSU, it will tell you which CPU it contains. PSUs are on
the same quarterly schedule as the Critical Patch Updates (CPU), specifically
the Tuesday closest to the 17th of January, April, July, and October. One
thing to keep in mind, however, is that once a PSU has been installed, the
recommended way to get future security content is to apply subsequent
PSUs. Reverting from PSU back to CPU, while possible, would require
significant effort and so is not advised. So with this in mind, why would
someone choose to apply a CPU rather than a PSU? I suppose for folks who
are concerned only with security fixes and not functionality fixes, a CPU-only
approach may be best. It does seem to be the more conservative approach
as a CPU is (in theory) less like to cause trouble than a PSU, simply because
it has less code changes in it.
5-Number Versions
Oracle version number looks like this: 11.2.0.4.0 or 12.1.0.1.1. What are these numbers and what do they represent?- The first 3 numbers are the release number (or base release, or base version) which represents the version itself. For example: 10gR2 is 10.2.0, 11g is 11.1.0 and 12c is 12.1.0.
- The 4th number is the patchset, which is a full patch of the software. Until 11.1, patchset was a package that we downloaded and installed on an existing Oracle Home. From 11.2 onwards, the patchset comes as a full installation into a new Oracle Home.
- We will talk about the 5th number in a minute…
Patch Types
In Oracle there are quite a few different types of patches. Let’s review them quickly:- Patchset – as we saw above, it is a large and significant patch (the 4th number of the full version). The patchset is installed using the familiar Universal Installer.
- One-off Patch – this is a small patch for fixing a single specific bug. The One-off patch is installed using opatch, a tool for patch installation which exists in every Oracle Home.
- CPU (Critical Patch Update) or SPU (Security Patch Update) – this specific patch is released every quarter and includes security fixes. The CPU (SPU) is also installed using the opatch tool.
- PSU (Patch Set Update) in UNIX/Linux or PB (Patch Bundle) in Windows
– a package of One-off patches that was built to make the patching
process easier and eliminate conflicts between different one-off
patches. The last number of the full version (as I promised to explain)
is the PSU or PB level. The PSU and PB are also installed using the
opatch tool.
Note that the PSU and PB are cumulative, meaning that PSU 11.2.0.3.8 includes all the fixes from 11.2.0.3.1 to 11.2.0.3.7. On top of that, they include all the CPU (or SPU) up to the PSU release date.
Behavior Changes
After upgrading and patching the database, we often run into different behavior changes or problems with the new version. So how does patching influence our database?- Patchset in this context is a bit risky. Patchsets rarely contain new features, but they might (bringing bugs with them sometimes). However, behavior changes of specific components (such as the optimizer) are more frequent, as well as changes in parameters’ default values, new parameters and other general changes that can influence how the database behaves.
- All the other patches (PSU, PB, CPU, One-off patch) aren’t supposed to change the database behavior (unless a specific behavior itself was a bug and the fix changed it). The fixes here are bug specific; new features, new parameters and new default values should not be introduced.
What’s new?
11.2
As I wrote before, starting with 11.2 (for those of you who don’t know), the patchset is not an installation on top of an existing Oracle Home, but as a full installation package. If you needed to install 10.2.0.5, you installed 10.2.0.1 base release, then downloaded and installed 10.2.0.5 patchset to the same Oracle Home. To install 11.2.0.4, all we need to do is to download 11.2.0.4 installation from MOS (My Oracle Support, f.k.a. metalink) and install it into a new Oracle Home. There is still an option to install a patchset into an existing Oracle Home, but this is not that common.12.1
Starting with 12.1, Oracle probably decided that there are too many patch types (and I guess they are right…), so they decided to cancel the CPU (SPU). From now on, the quarterly patch is the PSU and the PB and they will contain bug fixes including the quarterly security bug fixes.
My personal preference is to apply PSUs and not
worry about CPUS.
The patch may be CPU or PSU but the following steps are same for everything.
First fully read the readme.txt of your patch to know if any pre-requisite patch is needed or not.
1. Ensure that the Oracle Database on which you are installing the patch or from which you are rolling back the patch is Oracle Database 11g Release 11.2.0.3.0.
2. Oracle recommends you to use the latest version of OPatch.
If you do not have the latest version, then follow the instructions outlined in the My Oracle Support note 224346.1 available at: https://support.oracle.com/CSP/main/article?cmd=show&type=NOT&id=224346.1
3. Ensure that you set the ORACLE_HOME environment variable to the Oracle home of the Oracle Database.
4. Ensure that you set the PATH environment variable to include the location of the unzip executable, and the <ORACLE_HOME>/bin and the <ORACLE_HOME>/OPatch directories present in the Oracle home of the Oracle Database.
5. Ensure that you verify the Oracle Inventory because OPatch accesses it to install the patches. To verify the inventory, run the following command. If the command displays some errors, then contact Oracle Support and resolve the issue.
$ opatch lsinventory
6. Ensure that you shut down all the services running from the Oracle home.
Note:
- For a Non-RAC environment, shut down all the services running from the Oracle home.
- For a RAC environment, shut down all the services (database, ASM, listeners, nodeapps, and CRS daemons) running from the Oracle home of the node you want to patch. After you patch this node, start the services on this node.Repeat this process for each of the other nodes of the Oracle RAC system. OPatch is used on only one node at a time.
7. shutdown
(2) Installation
To install the patch, follow these steps:
Note:
- In case of an Oracle RAC environment, perform these steps on each of the nodes.
1. Maintain a location for storing the contents of the patch ZIP file. In the rest of the document, this location (absolute path) is referred to as <PATCH_TOP_DIR>.
2. Extract the contents of the patch ZIP file to the location you created in Step (1). To do so, run the following command:
$ unzip -d <PATCH_TOP_DIR> p14013094_112030_Generic.zip
3. Navigate to the <PATCH_TOP_DIR>/14013094 directory:
$ cd <PATCH_TOP_DIR>/14013094
4. Install the patch by running the following command:
$ opatch apply
Note:
When OPatch starts, it validates the patch and ensures that there are no conflicts with the software already installed in the ORACLE_HOME of the Oracle Database. OPatch categorizes conflicts into the following types:
- Conflicts with a patch already applied to the ORACLE_HOME - In this case, stop the patch installation and contact Oracle Support Services.
- Conflicts with a patch already applied to the ORACLE_HOME that is a subset of the patch you are trying to apply - In this case, continue with the patch installationbecause the new patch contains all the fixes from the existing patch in the ORACLE_HOME. The subset patch will automatically be rolled back prior to the installation of the new patch.
5. Start the services from the Oracle home.
(3) Post Installation
After you install the patch, reload the packages into the Oracle Database.
startup
conn / as sysdba
execute @prvtstas.plb @prvtstai.plb @prvtstat.plb in same order
shutdown;
startup
Note:
- In case of an Oracle RAC environment, reload the packages on one of the nodes.
(4) De-installation
To deinstall the patch, follow these steps:
Note:
- In case of an Oracle RAC environment, perform these steps on each of the nodes.
1. Navigate to the <PATCH_TOP_DIR>/14013094 directory:
$ cd <PATCH_TOP_DIR>/14013094
2. Deinstall the patch by running the following command:
$ opatch rollback -id 14013094
3. Start the services from the Oracle home.
(5)Post de-installation
After you de-install the patch, reload the packages into the Oracle Database.
startup
conn / as sysdba
execute @prvtstas.plb @prvtstai.plb @prvtstat.plb in same order
shutdown;
startup
Note:
- In case of an Oracle RAC environment, reload the packages on one of the nodes.
