RDBMS Online Patching

If you support Oracle RDBMS 11.2.0.2 and want to zero downtime applying
patches to databases then it is time to have a look at new possibility
of Online Patching delivered with 11.2.0.2 version and described here
RDBMS Online Patching Aka Hot Patching [ID 761111.1]

The syntax is “opatch apply online -connectString … ” but I could not find description
of the online option (only -connectString) using opatch -help (version 11.2.0.1.6)
although based on the note it is available since 11.1.0.6 version of opatch

And while going through README for the patch I found the blog
about online patching – Applying online patch on 11gr2
which helped me greatly to get proper syntax for online patching.

I had test RAC database with applied PSU3 and checked if there
were any online patches available on MOS. I found quite a few and downloaded
several of them to check what information “opatch query” will show.

Surprisingly, “opatch query” did not show any of them to be applicable online
but all of them had online directory with .pch file underneath and all
of them had “Online Installable Patch” in their README
for example 10040035: AROLTP-D: INSTANCE DIED WITH ORA-00600 [KCLPDC_21]

[oracle@r1 10040035]$ /u01/app/oracle/product/11.2.0/dbhome_1/OPatch/opatch query .
Invoking OPatch 11.2.0.1.6
...
 Need to shutdown Oracle instances: true
 Patch is roll-backable: true
 Patch is a "Patchset Update": false
 Patch is a rolling patch: true
 Patch has sql related actions: false
 Patch is an online patch: false
 Patch is a portal patch: false
 Patch is an "auto-enabled" patch: false

 List of platforms supported:
   226: Linux x86-64

 List of prereq patches:
   12419331

 List of overlay patches:
   12419331

 List of bugs to be fixed:
   10040035: AROLTP-D: INSTANCE DIED WITH ORA-00600 [KCLPDC_21]
...
OPatch succeeded.
[oracle@r1 10040035]$ /u01/app/oracle/product/11.2.0/dbhome_1/OPatch/opatch query -is_online_patch .
Invoking OPatch 11.2.0.1.6

Oracle Interim Patch Installer version 11.2.0.1.6
Copyright (c) 2011, Oracle Corporation.  All rights reserved.

Oracle Home       : /u01/app/oracle/product/11.2.0/dbhome_1
Central Inventory : /u01/app/oraInventory
   from           : /etc/oraInst.loc
OPatch version    : 11.2.0.1.6
OUI version       : 11.2.0.2.0
Log file location : /u01/app/oracle/product/11.2.0/dbhome_1/cfgtoollogs/opatch/opatch2011-08-29_01-41-03AM.log

--------------------------------------------------------------------------------
 Patch is an online patch: false

OPatch succeeded.
[oracle@r1 10040035]$ tree .
.
|-- README.txt
|-- etc
|   |-- config
|   |   |-- actions.xml
|   |   |-- deploy.xml
|   |   `-- inventory.xml
|   `-- xml
|       |-- GenericActions.xml
|       `-- ShiphomeDirectoryStructure.xml
|-- files
|   `-- lib
|       `-- libserver11.a
|           `-- kcl.o
`-- online
    |-- etc
    |   |-- config
    |   |   |-- actions.xml
    |   |   |-- deploy.xml
    |   |   `-- inventory.xml
    |   `-- xml
    |       |-- GenericActions.xml
    |       `-- ShiphomeDirectoryStructure.xml
    `-- files
        `-- hpatch
            `-- bug10040035.pch

I have been able to apply all of them without any downtime and all
of them appeared to be ENABLED on both instances:

r1221> oradebug patch list

Patch File Name                                   State
================                                =========
bug9795214.pch                                   ENABLED
bug10040035.pch                                  ENABLED
bug10222480.pch                                  ENABLED
bug11670161.pch                                  ENABLED
bug11853815.pch                                  ENABLED
bug9829397.pch                                   ENABLED

Interesting enough that either for RAC or for standalone database
“opatch apply online” was converted to RAC mode “-all_nodes”
although patches were applied properly for both clustered and non-clustered
environments.
RAC database:

[oracle@r1 10040035]$ /u01/app/oracle/product/11.2.0/dbhome_1/OPatch/opatch apply \
online -connectString r1221:sys:pwd:r1,r1222:sys:pwd:r2
Invoking OPatch 11.2.0.1.6

Oracle Interim Patch Installer version 11.2.0.1.6
Copyright (c) 2011, Oracle Corporation.  All rights reserved.

Oracle Home       : /u01/app/oracle/product/11.2.0/dbhome_1
Central Inventory : /u01/app/oraInventory
   from           : /etc/oraInst.loc
OPatch version    : 11.2.0.1.6
OUI version       : 11.2.0.2.0
Log file location : /u01/app/oracle/product/11.2.0/dbhome_1/cfgtoollogs/opatch/opatch2011-08-29_02-07-19AM.log

The patch should be applied/rolled back in '-all_nodes' mode only.
Converting the RAC mode to '-all_nodes' mode.
Applying interim patch '10040035' to OH '/u01/app/oracle/product/11.2.0/dbhome_1'
Verifying environment and performing prerequisite checks...

Do you want to proceed? [y|n]
y
User Responded with: Y
All checks passed.
Provide your email address to be informed of security issues, install and
initiate Oracle Configuration Manager. Easier for you if you use your My
Oracle Support Email address/User Name.
Visit https://www.oracle.com/support/policies.html for details.
Email address/User Name:

You have not provided an email address for notification of security issues.
Do you wish to remain uninformed of security issues ([Y]es, [N]o) [N]:  Y
Backing up files...

Patching component oracle.rdbms, 11.2.0.2.0...
The patch will be installed on active database instances.
Installing and enabling the online patch 'bug10040035.pch', on database 'r1221'.

Patching in all-node mode.

Updating nodes 'r2'
   Apply-related files are:
     FP = "/u01/app/oracle/product/11.2.0/dbhome_1/.patch_storage/10040035_Jul_25_2011_06_02_21/rac/copy_files.txt"
     DP = "/u01/app/oracle/product/11.2.0/dbhome_1/.patch_storage/10040035_Jul_25_2011_06_02_21/rac/copy_dirs.txt"
     MP = "/u01/app/oracle/product/11.2.0/dbhome_1/.patch_storage/10040035_Jul_25_2011_06_02_21/rac/make_cmds.txt"
     RC = "/u01/app/oracle/product/11.2.0/dbhome_1/.patch_storage/10040035_Jul_25_2011_06_02_21/rac/remote_cmds.txt"

Instantiating the file "/u01/app/oracle/product/11.2.0/dbhome_1/.patch_storage/10040035_Jul_25_2011_06_02_21/rac/
copy_files.txt.instantiated" by replacing $ORACLE_HOME
in "/u01/app/oracle/product/11.2.0/dbhome_1/.patch_storage/10040035_Jul_25_2011_06_02_21/rac/copy_files.txt" 
with actual path.
Propagating files to remote nodes...
Instantiating the file "/u01/app/oracle/product/11.2.0/dbhome_1/.patch_storage/10040035_Jul_25_2011_06_02_21/rac/
copy_dirs.txt.instantiated" by replacing $ORACLE_HOME 
in "/u01/app/oracle/product/11.2.0/dbhome_1/.patch_storage/10040035_Jul_25_2011_06_02_21/rac/copy_dirs.txt" 
with actual path.
Propagating directories to remote nodes...
Installing and enabling the online patch 'bug10040035.pch', on database 'r1222' on node 'r2'.

Patch 10040035 successfully applied
Log file location: /u01/app/oracle/product/11.2.0/dbhome_1/cfgtoollogs/opatch/opatch2011-08-29_02-07-19AM.log

OPatch succeeded.

non-RAC database

[oracle@oel5 11800959]$ /u02/app/oracle/product/11.2.0/dbhome_1/OPatch/opatch apply online -connectString d122:sys:pwd:
Invoking OPatch 11.2.0.1.1

Oracle Interim Patch Installer version 11.2.0.1.1
Copyright (c) 2009, Oracle Corporation.  All rights reserved.

Oracle Home       : /u02/app/oracle/product/11.2.0/dbhome_1
Central Inventory : /u01/app/oracle/oraInventory
   from           : /etc/oraInst.loc
OPatch version    : 11.2.0.1.1
OUI version       : 11.2.0.2.0
OUI location      : /u02/app/oracle/product/11.2.0/dbhome_1/oui
Log file location : /u02/app/oracle/product/11.2.0/dbhome_1/cfgtoollogs/opatch/opatch2011-08-29_15-27-30PM.log

Patch history file: /u02/app/oracle/product/11.2.0/dbhome_1/cfgtoollogs/opatch/opatch_history.txt

The patch should be applied/rolled back in '-all_nodes' mode only.
Converting the RAC mode to '-all_nodes' mode.
ApplySession applying interim patch '11800959' to OH '/u02/app/oracle/product/11.2.0/dbhome_1'

Running prerequisite checks...

OPatch detected non-cluster Oracle Home from the inventory and will patch the local system only.

Backing up files and inventory (not for auto-rollback) for the Oracle Home
Backing up files affected by the patch '11800959' for restore. This might take a while...
Backing up files affected by the patch '11800959' for rollback. This might take a while...

Patching component oracle.rdbms, 11.2.0.2.0...
The patch will be installed on active database instances.
Installing and enabling the online patch 'bug11800959.pch', on database 'd122'.

ApplySession adding interim patch '11800959' to inventory

Verifying the update...
Inventory check OK: Patch ID 11800959 is registered in Oracle Home inventory with proper meta-data.
Files check OK: Files from Patch ID 11800959 are present in Oracle Home.

OPatch succeeded.

I think that conversion a little bit misleading for RAC database patching
because credentials should be entered for all instances of clustered database
and if you do not type it for one of them, there would not be any errors,
execution will be finished but patch will be applied only for nodes which credentials
are provided.

Online patches can be easily managed by oradebug and can be turned
on and off at each instance of RAC database or for the whole non-RAC database.
However “opatch lsinventory” does not show status of online patch
only if it is applied.

d122> oradebug patch list

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

d122> oradebug patch disable bug11800959.pch
Statement processed.
d122> oradebug patch list

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

d122> oradebug patch enable bug11800959.pch
Statement processed.
d122> oradebug patch list

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

d122> oradebug patch disable bug11800959.pch term
Statement processed.
d122> oradebug patch list

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

[oracle@oel5 u01]$ /u02/app/oracle/product/11.2.0/dbhome_1/OPatch/opatch lsinventory
Invoking OPatch 11.2.0.1.1

Oracle Interim Patch Installer version 11.2.0.1.1
Copyright (c) 2009, Oracle Corporation.  All rights reserved.

Oracle Home       : /u02/app/oracle/product/11.2.0/dbhome_1
Central Inventory : /u01/app/oracle/oraInventory
   from           : /etc/oraInst.loc
OPatch version    : 11.2.0.1.1
OUI version       : 11.2.0.2.0
OUI location      : /u02/app/oracle/product/11.2.0/dbhome_1/oui
Log file location : /u02/app/oracle/product/11.2.0/dbhome_1/cfgtoollogs/opatch/opatch2011-08-30_09-36-16AM.log

Patch history file: /u02/app/oracle/product/11.2.0/dbhome_1/cfgtoollogs/opatch/opatch_history.txt

Lsinventory Output file location : /u02/app/oracle/product/11.2.0/dbhome_1/cfgtoollogs/opatch/lsinv/lsinventory2011-08-30_09-36-16AM.txt

--------------------------------------------------------------------------------
Installed Top-level Products (1):

Oracle Database 11g                                                  11.2.0.2.0
There are 1 products installed in this Oracle Home.

Interim patches (1) :

Patch (online) 11800959: applied on Mon Aug 29 15:27:55 EST 2011
Unique Patch ID:  13914499
   Created on 8 Jul 2011, 03:51:56 hrs PST8PDT
   Bugs fixed:
     11800959

--------------------------------------------------------------------------------

d122> oradebug patch enable bug11800959.pch
Statement processed.
d122> oradebug patch list

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

Oracle Support encourages to apply online patches only in cases when
downtime can not be scheduled immediately but strongly recommends to replace
them with normal patches that requires shutdown of instances.

And all my tests were done on test databases so I have not
taken any risk yet to do it on production. But the feature is very useful
and number of patches being steadily increased over last releases.

Have a good day!