A technical troubleshooting blog about Oracle with other Databases & Cloud Technologies.

Patching !!

7 min read

In which months oracle release CPU patches?


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.

Is it possible to 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 without or zero downtime 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).

When you moved oracle binary files from one ORACLE_HOME server to another server then which oracle utility will be used to make this new ORACLE_HOME usable?

Relink all.

You have collection of patch (nearly 100 patches) or patchset. How can you apply only one patch from it?

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:

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.
opatch util napply <patch_location> -id 9 -skip_subset -skip_duplicate

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. 

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 to Download Patches, Patchset or Opatch from metalink?

If you are using latest support.oracle.com then after login to metalink Dashboard

- Click on "Patches & Updates" tab

- On the left sidebar click on "Latest Patchsets" under "Oracle Server/Tools".

- A new window will appear.

- Just mouseover on your product in the "Latest Oracle Server/Tools Patchsets" page.

- Corresponding oracle platform version will appear. Then simply choose the patchset version and click on that.

- You will go the download page. From the download page you can also change your platform and patchset version.

What is OPatch?

It is the utility to apply the patch.

How to Apply Opatch in Oracle?

1. You MUST read the Readme.txt file included in opatch file, look for any prereq. steps/ post installation steps or and DB related changes. Also, make sure that you have the correct opatch version required by this patch.

2.Make sure you have a good backup of database.

3. Make a note of all Invalid objects in the database prior to the patch.

4. Shutdown All the Oracle Processes running from that Oracle Home , including the Listener and Database instance, Management agent etc.

5. You MUST Backup your oracle Home and Inventory

tar -cvf $ORACLE_HOME $ORACLE_HOME/oraInventory | gzip > Backup_Software_Version.tar.gz

6. Unzip the patch in $ORACLE_HOME/patches

7. cd to the patch direcory and do opatch -apply to apply the patch.

8. Read the output/log file to make sure there were no errors.

Patching Oracle Software with OPatch ?

opatch napply <patch_location> -skip_subset -skip_duplicate

OPatch skips duplicate patches and subset patches (patches under <patch_location> that are subsets of patches installed in the Oracle home).

What is Opatch in Oracle?

OPATCH Utility (Oracle RDBMS Patching)

1. Download the required Patch from Metalink based on OS Bit Version and DB Version.

2. Need to down the database before applying patch.

3. Unzip and Apply the Patch using ”opatch apply” command.On successfully applied of patch you will see successful message “OPatch succeeded.“, Crosscheck your patch is applied by using “opatch lsinventory” command .

4. Each patch has a unique ID, the command to rollback a patch is “opatch rollback -id  <patch no.>” command.On successfully applied of patch you will see successful message “OPatch succeeded.“, Crosscheck your patch is applied by using “opatch lsinventory” command .

5. Patch file format will be like, “p<patch no.>_<db version>_<os>.zip”

6. We can check the opatch version using “opatch -version” command.

7. Generally, takes 2 minutes to apply a patch.

8. To get latest Opatch version download “patch 6880880 - latest opatch tool”, it contains OPatch directory.

9. Contents of downloaded patches will be like “etc,files directories and a README file”

10. Log file for Opatch utility can be found at $ORACLE_HOME/cfgtoollogs/opatch

11. OPatch also maintains an index of the commands executed with OPatch and the log files associated with it in the history.txt file located in the <ORACLE_HOME>/cfgtoollogs/opatch directory.

12. Starting with the patch set, Oracle Database patch sets are full installations of the Oracle Database software. This means that you do not need to install Oracle Database 11g Release 2 ( before installing Oracle Database 11g Release 2 (

13. Direct upgrade to Oracle 10g is only supported if your database is running one of the following releases: 8.0.6, 8.1.7, 9.0.1, or 9.2.0. If not, you will have to upgrade the database to one of these releases or use a different upgrade option (like export/ import).

14.Direct upgrades to 11g are possible from existing databases with versions, or Upgrades from other versions are supported only via intermediate upgrades to a supported upgrade version.

Oracle version what does each number refers to?

Oracle version number refers:
10 – Major database release number
 2 – Database Maintenance release number
 0 – Application server release number
 4 – Component Specific release number
 0 – Platform specific release number

What is rolling upgrade?

It is a new ASM feature from Database 11g.ASM instances in Oracle database 11g release(from 11.1) can be upgraded or patched using rolling upgrade feature. This enables us to patch or upgrade ASM nodes in a clustered environment without affecting database availability.During a rolling upgrade we can maintain a functional cluster while one or more of the nodes in the cluster are running in different software versions.Rolling upgrade can be used only for Oracle database 11g releases(from 11.1).

Steps to Upgrade in Oracle ?

Manual upgrade which involves the following steps:

1.Backup the database.

2.In UNIX/Linux environments, set the $ORACLE_HOME and $PATH variables to point to the new 11g Oracle home.

3.Analyze the existing instance using the "$ORACLE_HOME/rdbms/admin/utlu111i.sql" script.

4.Start the original database using the STARTUP UPGRADE command and proceed with the upgrade by running the "$ORACLE_HOME/rdbms/admin/catupgrd.sql" script.

5.Recompile invalid objects.

6.Restart the database.

7.Run the "$ORACLE_HOME/rdbms/admin/utlu111s.sql" script and check the result of the upgrade.

8.Troubleshoot any issues or abort the upgrade.

What happens when you give “STARTUP UPGRADE”?

$sqlplus "/as sysdba"

The UPGRADE keyword enables you to open a database based on an earlier Oracle Database release. It also restricts logons to AS SYSDBAsessions, disables system triggers, and performs additional operations that prepare the environment for the upgrade.

You might be required to use the PFILE option to specify the location of your initialization parameter file.
Once the database is started in upgrade mode, only queries on fixed views execute without errors until after the catupgrd.sql script is run. Before running catupgrd.sql, queries on any other view or the use of PL/SQL returns an error.

What is the difference between startup Upgrade and Migrate ?

startup migrate:
Used to upgrade a database till 9i.

Startup Upgrade
From 10G  we are using startup upgrade to upgrade database.

What is the difference between database upgradation and database migration?

Today, let us see about the difference between upgrade and migration.
First of all let me give you a brief definition of both


Process of changing version of database from lower release to major release is called UPGRADE. For example, moving from to to, to etc..

Usually, we will upgrade the database without changing the physical server. Means, in the existing server itself we will upgrade the DB to new release.

Reasons for upgrade:

1. To continue support with Oracle (because Oracle will not support if you have a license of unspported version. for example, Oracle stopped support for 10g now, so if you have 10g database you cannot get any help from Oracle support to raise SR’s, to fix any bugs etc)
2. There is a serious bug which got fixed only in new release.
3. Client has license for new release
4. Application will work better with new version of database etc


Process of changing OS platform for a database server. For example, moving a database from Solaris server to Linux server, windows to Solaris etc.

Ideally in most of the migration projects, there will be a change in physical server i.e If we have a server with Solaris 5.10 and now client decided to move to latest server with RHEL 6, we call it as migration.

Some of the reasons why client need this…

1. the existing server became old and doesn’t have expanding capability (like we cannot extend memory, disk space or CPU etc)
2. existing OS license got expired and client want now to use cheaper OS (like Linux)
3. moving from one data center to another to reduce cost of maintenance etc

Many a times, migration also involves upgrade. usually, out of 100% migration projects, 80-90% will involve the task for upgrade database along with migration.

For example, there is a database running on X server with Solaris 5.10 and client decided to move to database on Y server with RHEL 6. In this case, it involves both migration (because you are changing the platform) and upgrade (because database release is changing).