Question with Answer on Oracle database Patches
Patches are a small collection of files copied over to an
existing installation. They are associated with particular versions of Oracle
products.
Differentiate Patcheset, CPU and PSU patch? What kind of errors usually resolved from them?
The discussion will especially help for those beginners who are preparing
for interview and inexperienced to apply the patches. In this article you will
find all those things briefly with an example. For more details please study
the oracle documentation and try to search with separate topics on this blog.
What are different Types of
Patches?
Regular Patcheset:
To upgrade to higher version we use database patchset. Please do not
confuse between regular patchests and patch set updates (PSU). Consider the
regular patchset is super set of PSU. Regular Patchset contain major bug fixes.
In comparison to regular patch PSU will not change the version of oracle
binaries such as sqlplus, import/export etc. The importance of PSU is
automatically minimized once a regular patchset is released for a given version.
It is mainly divided into two types:
Security or Critical Patch Update (CPU):
Critical patch update quarterly delivered by oracle to fix security issues.
Patch set updated (PSU): It include CPU and
bunch of other one-off patches. It is also quarterly delivered by oracle.
Interim (one-off) Patch:
It is also known as patchset exception or one-off patch or interim patch. This
is usually a single fix for single problem or enhancement. It released only
when there is need of immediate fix or enhancement that cannot wait until for next
release of patchset or bundle patch. It is applied using OPATCH utility and is
not cumulative.
Bundle Patches:
Bundle Patches includes both the quarterly security patches as well as
recommended fixes (for Windows and Exadata only). When you try to download this
patch you will find bundle of patches (different set of file) instead of single
downloaded file (usually incase patchset).
Is Opatch (utility) is also
another type of patch?
OPatch is utility from oracle corp. (Java based utility)
that helps you in applying interim patches to Oracle's software and rolling
back interim patches from Oracle's software. Opatch also able to Report already
installed interim patch and can detect conflict when already interim patch has
been applied. This program requires Java to be available on your system and
requires installation of OUI. Thus from the above discussion coming to your
question it is not ideal to say OPATCH is another patch.
When we applying single Patch,
can you use OPATCH utility?
Yes, you can use Opatch incase of single patch. The only
type of patch that cannot be used with OPatch is a patchset
When you applying Patchsets, You
can use OUI.
Yes, Patcheset uses OUI. A patch set contains a large number
of merged patches, to change the version of the product or introduce new
functionality. Patch sets are cumulative bug fixes that fix all bugs and
consume all patches since the last base release. Patch sets and the Patch Set
Assistant are usually applied through OUI-based product specific installers.
Can you Apply OPATCH without
downtime?
As you know for apply patch your database and listener must
be down. When you apply OPTACH it will update your current ORACLE_HOME. Thus
coming to your question to the point in fact it is not possible in case of
single instance but in RAC you can Apply Opatch without downtime as there will
be more separate ORACLE_HOME and more separate instances (running once instance
on each ORACLE_HOME).
You have collection of patch
(nearly 100 patches) or patchset. How can you apply only one patch from
patcheset or patch bundle at ORACLE_HOME?
With Napply itself (by providing patch location and specific patch id) you can apply only one patch from a collection of extracted patch. For more information check the opatch util NApply –help. It will give you clear picture.
With Napply itself (by providing patch location and specific patch id) you can apply only one patch from a collection of extracted patch. For more information check the opatch util NApply –help. It will give you clear picture.
For Example:
opatch util napply <patch_location> -id 9
-skip_subset -skip_duplicate
This will apply only the patch id 9 from the patch location
and will skip duplicate and subset of patch installed in your ORACLE_HOME.
How can you get minimum/detail information from inventory
about patches applied and components installed?
You can try below command for minimum and detail information
from inventory
C:\ORACLE_HOME\Opatch\opatch lsinventory –invPtrLoc “location of oraInst.loc file”
C:\ORACLE_HOME\Opatch\opatch lsinventory –invPtrLoc “location of oraInst.loc file”
$ORACLE_HOME\OPatch\opatch lsinventory -detail
-invPtrLoc “location of oraInst.loc file”
Differentiate Patcheset, CPU and PSU patch? What kind of errors usually resolved from them?
Critical Patch Update (CPU) was the original quarterly patches that were released
by oracle to target the specific security fixes in various products. CPU
is a subset of patchset updates (PSU). CPU are built on the base patchset
version where as PSU are built on the base of previous PSU
Patch Set Updates (PSUs) are also released quarterly along with CPU
patches are a superset of CPU patches in the term that PSU patch will include
CPU patches and some other bug fixes released by oracle. PSU
contain fixes for bugs that contain wrong results, Data Corruption etc but it
doe not contain fixes for bugs that that may result in: Dictionary changes, Major
Algorithm changes, Architectural changes, Optimizer plan changes
Regular patchset: Please do not confuse between
regular patchests and patch set updates (PSU). Consider the regular patchset is
super set of PSU. Regular Patchset contain major bug fixes. The importance of
PSU is minimizing once a regular patchset is released for a given version. In
comparison to regular patch PSU will not change the version of oracle binaries
such as sqlplus, import/export etc.
If both CPU and PSU are
available for given version which one, you will prefer to apply?
From the above discussion it is clear once you apply the PSU then the recommended way is to apply the next PSU only. In fact, no need to apply CPU on the top of PSU as PSU contain CPU (If you apply CPU over PSU will considered you are trying to rollback the PSU and will require more effort in fact). So if you have not decided or applied any of the patches then, I will suggest you to go to use PSU patches. For more details refer: Oracle Products [ID 1430923.1], ID 1446582.1
From the above discussion it is clear once you apply the PSU then the recommended way is to apply the next PSU only. In fact, no need to apply CPU on the top of PSU as PSU contain CPU (If you apply CPU over PSU will considered you are trying to rollback the PSU and will require more effort in fact). So if you have not decided or applied any of the patches then, I will suggest you to go to use PSU patches. For more details refer: Oracle Products [ID 1430923.1], ID 1446582.1
PSU is superset of CPU then why someone choose to apply a
CPU rather than a PSU?
CPUs are smaller and more focused than PSU and mostly deal
with security issues. It seems to be theoretically more consecutive approach
and can cause less trouble than PSU as it has less code changing in it. Thus any
one who is concerned only with security fixes and not functionality fixes, CPU
may be good approach.
How can you find the PSU
installed version?
PSU references at 5th place in the oracle version
number which makes it easier to track such as (e.g. 10.2.0.3.1). To determine
the PSU version installed, use OPATCH utility:
OPATCH lsinv -bugs_fixed | grep -i PSU
To find from the database:
Select substr(action_time,1,30) action_time,
substr(id,1,10) id, substr(action,1,10) action,substr(version,1,8) version,
substr(BUNDLE_SERIES,1,6) bundle, substr(comments,1,20) comments from
registry$history;
Note: You can find the details
from the above query if you already executed the catbundle.sql
Will Patch Application affect
System Performance?
Sometimes applying certain patch could affect Application performance of SQL statements. Thus it is recommended to collect a set of performance statistics that can serve as a baseline before we make any major changes like applying a patch to the system.
Sometimes applying certain patch could affect Application performance of SQL statements. Thus it is recommended to collect a set of performance statistics that can serve as a baseline before we make any major changes like applying a patch to the system.
Can you stop applying a patch
after applying it to a few nodes? What are the possible issues?
Yes, it is possible to stop applying a patch after applying
it to a few nodes. There is a prompt that allows you to stop applying the
patch. But, Oracle recommends that you do not do this because you cannot apply
another patch until the process is restarted and all the nodes are patched or
the partially applied patch is rolled back.
How you know impact of patch
before applying a patch?
OPATCH <option> -report
You can use the above command to know the impact of the
patch before actually applying it.
How can you run patching in
scripted mode?
opatch <option> -silent
You can use the above command to run the patches in scripted
mode.
Can you use OPATCH 10.2 to apply
10.1 patches?
No, Opatch 10.2 is not backward compatible. You can use
Opatch 10.2 only to apply 10.2 patches.
What you will do if you lost or
corrupted your Central Inventory?
In that case when you lost or corrupted your Central
Inventory and your ORACLE_HOME is safe, you just need to execute the command
with –attachHomeflag,
OUI automatically setup the Central Inventory for attached home.
What you will do if you lost
your Oracle home inventory (comps.xml)?
Oracle recommended backup your ORACLE_HOME before applying
any patchset. In that case either you can restore your ORACLE_HOME from the
backup or perform the identical installation of the ORACLE_HOME.
When I apply a patchset or an
interim patch in RAC, the patch is not propagated to some of my nodes. What do
I do in that case?
In a RAC environment, the inventory contains a list of nodes
associated with an Oracle home. It is important that during the application of
a patchset or an interim patch, the inventory is correctly populated with the
list of nodes. If the inventory is not correctly populated with values, the
patch is propagated only to some of the nodes in the cluster.
OUI allows you to update the inventory.xml with
the nodes available in the cluster using the -updateNodeList flag
in Oracle Universal Installer.
When I apply a patch, getting
the following errors:
"Opatch Session cannot load
inventory for the given Oracle Home <Home_Location> Possible causes are:
No read or write permission to ORACLE_HOME/.patch_storage; Central Inventory is
locked by another OUI instance; No read permission to Central Inventory; The
lock file exists in ORACLE_HOME/.patch_storage; The Oracle Home does not exist
in Central Inventory". What do I do?
This error may occur because of any one or more of the
following reasons:
–
The ORACLE_HOME/.patch_storage
may not have read/write permissions. Ensure that you give read/write
permissions to this folder and apply the patch again.
–
There may be another OUI instance
running. Stop it and try applying the patch again.
–
The Central Inventory
may not have read permission. Ensure that you have given read permission to the
Central Inventory and apply the patch again.
–
The ORACLE_HOME/.patch_storage
directory might be locked. If this directory is locked, you will find a file
named patch_locked
inside this directory. This may be due to a previously failed installation of a
patch. To remove the lock, restore the Oracle home and remove the patch_locked
file from the ORACLE_HOME/.patch_storage directory.
–
The Oracle home may not be present in the
Central Inventory. This may be due to a corrupted or lost
inventory or the inventory may not be registered in the Central Inventory.
We should check for the latest security patches on the
Oracle metalink website http://metalink.oracle.com/
and we can find the regular security alert at the location http://technet.oracle.com/deploy/security/alert.htm
Caution: It is not
advisable to apply the patches directly into the production server. The ideal
solution is to apply or test the patches in test server before being moved into
the production system.
For more about oracle Patch:
