Oracle Database Patch

Oracle Database Patches:

Patch is required for any bug fix or some additional features. Databse patches are mainly applied for security fixes, Priority fixes and patchset is used for upgradation like 10.2.0.1 to 10.2.0.4.

Critical Patch Update(CPU): These are the patches for security fixes and released each quarter.

Patch Set Update(PSU): These are cumulative patches that include both security fixes and priority fixes. PSU are the minor version upgrades (e.g., 11.2.0.1.1 to 11.2.0.1.2).

Once PSU is applied, PSU has to be applied each quarter till we upgrade our database to new base version.

How do we apply Oracle database patches?

Patches are downloaded from metalink (My Oracle Support) and it has readme.txt file with the patching instructions and we can follow as suggested by oracle with read me file.

Basic steps to follow for applying database patch:

• Check the patch you are going to apply with opatch lsinventry.
• Download the patch from metalink.
• Unzip the patch and read the instructions from read me file.
• Shutdown database and listner.
• Go to patch directory : Opatch apply
• Start listener and database.
• Run sql script catcpu.sql (from $ORACLE_HOME/rdbms/admin)
• Verify the status of patch applied 
• Run SQL script utlrp.sql (compile invalid objects if any generated)

Log location of Opatch:

$ORACLE_HOME/cfgtoollogs/opatch

Significance of utlrp.sql and catcpu.sql:

Utlrp.sql :- location of utlrp.sql is $ORACLE_HOME/rdbms/admin. This script is used to compile the invalid objects.

Catcpuu.sql :- catcpu.sql calls a script called catbundle.sql, which is located at $ORACLE_HOME/rdbms/admin.

This script will load sql file in database by looking into bundledata_cpu.xml for file information for a particular patch. It will be creating a dynamic apply sql file and run it.