Online Hot Patching

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 http://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!