Patches are a small collection of files copied over to an existing installation. They are associated with particular versions of Oracle products.
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.
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_HOMEOpatchopatch lsinventory –invPtrLoc “location of oraInst.loc file”
$ORACLE_HOMEOPatchopatch 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
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
Click to Check Existing Oracle Database Patch Status
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.
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
- – 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:
About Patching in Oracle
How to Apply Critical Patch Update on RAC
Apply patches on 126.96.36.199.0 to upgrade 188.8.131.52.0