Oracle RDBMS 11.2 Online patch

    Today I had a colleague asking if he can install a opatch online or should he do it in a traditional fashion and any risk involved with it. To answer this I had to give some explanation and examples, which I thought would be useful for others if posted in a blog.

   A normal patch comprises of one or more object (.o) files and/or libraries (.a files). Installation requires shutting down the RDBMS instance, re-linking the oracle binary, and restarting the instance. Whereas the online patch can be applied to a live RDBMS instance.

    An online patch contains a single shared library; installing an online patch does not require shutting down the instance or relinking the oracle binary. An online patch can be installed/un-installed using Opatch (which uses oradebug commands internally).Since this is done with oradebug if you have multiple databases running from a single oracle home you have push them across to the databases you require.

  

  Is patching online always recommended if we have the option to do so ? I would say use the online option only in cases of a quick fix required and a downtime cannot be borrowed immediately. Else stay away from the online option. Online patches as mentioned earlier are share libraries and require additional memory since the modified functions have to be in the memory. Oracle defines the overhead calculation as below:

Unix : memory overhead = ( # of processes +1) x size of ( .pch file)

 In my example the overhead is as below:
 
 processes parameter is set to 200, and the .pch file size is 1.67 MB

 Memory overhead = (200 + 1) * 1.67 MB = 335 MB
 
 When we have multiple online patches like these the overhead would go up, so even if we apply online patches it is recommended to remove them and install it in the normal fashion during a downtime acquired later.

 Here we will take a example of applying patch 17018214 to a database in 11.2.0.3 in AIX, to see the actual steps and how it gets loaded.

 
How to determine a patch can be applied online:

1. It should be mentioned in the readme and patch should have a online directory and under its subdirectories a .pch file

2. cod to the patch directory and run : opatch query -all online
   The output should contain the text "Patch is an online patch: true"
   
How to apply the patch online:

syntax 
   
$ORACLE_HOME/OPatch/opatch apply online -connectString :::,:::

I am using a single node command since I am using a single node command as below:

$ORACLE_HOME/OPatch/opatch apply online -connectString TST:sys:oracle

The output should contain text as below after successful application

Patching component oracle.rdbms, 11.2.0.3.0...
Installing and enabling the online patch 'bug17018214.pch', on database 'TST'.

Verifying the update...
Patch 17018214 successfully applied

How to verify the patch is applied

 Login via sqlplus as a sysdba and query vie oradebug, similarly enabling and disabling the patch can be done via oradebug, but when you want to remove the online patch it should be done via opatch. You can also check via the procmap command for the pmon process to see the shared libraries being loaded, you can use pmap for solaris and Linux.

SQL> oradebug patch list

Patch File Name                                   State
================                                =========
bug17018214.pch                                  ENABLED

I had issues with procmap, hence writing down a sample pmap output of the pmon process after the onilne patch is installed:

00002abecb53c000       8 r-x-- 000000000c64e000 008:00002 oracle
00002abecb53e000    5052 r-x-- 00000000000bd000 008:00002 oracle
00002abecba2d000     140 r-x-- 0000000000000000 008:00002 bug17018214.so
00002abecba50000    1024 ----- 0000000000023000 008:00002 bug17018214.so
00002abecbb50000       8 rwx-- 0000000000023000 008:00002 bug17018214.so
00007fff3342d000      84 rwx-- 00007ffffffea000 000:00000   [ stack ]
ffffffffff600000    8192 ----- 0000000000000000 000:00000   [ anon ]

Also you can enable and disable the patch with the below commands.

SQL> oradebug patch disable bug17018214.pch
Statement processed.
SQL> oradebug patch list

Patch File Name                                   State
================                                =========
bug17018214.pch                                  DISABLED

SQL> oradebug patch enable bug17018214.pch
Statement processed.
SQL> oradebug patch list

Patch File Name                                   State
================                                =========
bug17018214.pch                                  ENABLED

 If you are wondering where the patch information is stored, its stored in $ORACLE_HOME/hpatch 
  
$ ls
bug17018214.pch            bug17018214.pchIFPT.fixup  bug17018214.so             orapatchIFPT.cfg

How to rollback the online patch

syntax 

opatch rollback -id -connectString  :::

The command in my single node database as below/;

$ORACLE_HOME/OPatch/opatch rollback -id 17018214 -connectString TST:sys:oracle

The output should contain text as below after succesfully revoking

The patch will be removed from database instances.
Disabling and removing online patch 'bug17018214.pch', on database 'TST'

RollbackSession removing interim patch '17018214' from inventory

Reference:
MoS Note Doc ID 761111.1 - RDBMS Online Patching Aka Hot Patching