Online patching is new feature introduced in 11.1.0.6 and oracle started delivering patches with 11.2.0.2.
This patch allows you to apply patch to live database i.e we do not need to shutdown database. This feature is only available for RDBMS home and can be installed/enabled using opatch.
RDBMS Online Patching Aka Hot Patching [ID 761111.1] explains internals of how this patching works.
I tested it while working on Database corruption issue caused by Bug 10205230 ORA-600 / corruption possible during shutdown in RAC. This bug is present in 11.2.0.2 database which causes corruption when you try to use shutdown immediate/normal/transactional option to shutdown RAC database. As per MOS note 1318986.1, Data corruption occurs around shutdown one or more of the RAC instances
* One of the following ORA-600 asserts:
– ORA-600 [kclchkblk_3]
– ORA-600 [kclwcrs_6]
– ORA-600 [ktubko_1]
– ORA-600 [kcratr_scan_lostwrt]
– ORA-600[3020] on the standby database
Workaround is to perform local check point and do shutdown abort. Patch 10205230 was available for our platform and I downloaded using below command directly to server
read mos_user_id read -s h_passwd wget --http-user="$mos_user_id" --http-password="$h_passwd" "https://updates.oracle.com/Orion/Services/download/p10205230_112020_Linux-x86-64.zip?aru=13671866&patch_file=p10205230_112020_Linux-x86-64.zip" -O p10205230_112020_Linux-x86-64.zip
mos_user_id – Metalink aka Mos user id (email id)
h_passwd – Password
To query if patch is online, go to patch directory and run following
$opatch query -all online 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 : /home/oracle/product/11.2 Central Inventory : /home/oracle/oraInventory from : /etc/oraInst.loc OPatch version : 11.2.0.1.6 OUI version : 11.2.0.2.0 Log file location : /home/oracle/product/11.2/cfgtoollogs/opatch/opatch2012-06-20_16-43-21PM.log -------------------------------------------------------------------------------- Patch created on 2 Apr 2011, 12:57:14 hrs PST8PDT Need to shutdown Oracle instances: false 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: true Patch is a portal patch: false Patch is an "auto-enabled" patch: false List of platforms supported: 226: Linux x86-64 List of bugs to be fixed: 10205230: TB_X64: HIT ORA-00600: [KCLWCRS_6] This patch is a "singleton" patch. This patch belongs to the "db" product family List of executables affected: ORACLE_HOME/bin/oracle List of optional components: oracle.rdbms: 11.2.0.2.0 List of optional actions: Patch the Database instances with Online Patch hpatch/bug10205230.pch Possible XML representation of the patch: <ONEOFF REF_ID="10205230" ROLLBACK="T" XML_INV_LOC="oneoffs/10205230/" ACT_INST_VER="11.2.0.2.0" INSTALL_TIME="2012.Jun.20 16:43:21 PDT"> <DESC></DESC> <REF_LIST> <REF NAME="oracle.rdbms" VER="11.2.0.2.0" HOME_IDX="0"/> </REF_LIST> <BUG_LIST> <BUG>10205230</BUG> </BUG_LIST> <FILE_LIST/> </ONEOFF> -------------------------------------------------------------------------------- OPatch succeeded.
“Patch is an online patch: true” indicates patch can be applied to live db.
To apply the patch, we can use following syntax
Non – RAC => opatch apply online -connectString <SID>:<USERNAME>:<PASSWORD>:<NODE>
RAC => opatch apply online -connectString <SID>:<USERNAME>:<PASSWORD>:<NODE1>,<SID2>:<USERNAME>:<PASSWORD>:<NODE2>,…
Since we are patching RAC database, we will use second syntax
$ opatch apply online -connectString orcl01d1:sys:oracle123:prod03,orcl01d2:sys:oracle123:prod04 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 : /home/oracle/product/11.2 Central Inventory : /home/oracle/oraInventory from : /etc/oraInst.loc OPatch version : 11.2.0.1.6 OUI version : 11.2.0.2.0 Log file location : /home/oracle/product/11.2/cfgtoollogs/opatch/opatch2012-06-20_16-53-36PM.log The patch should be applied/rolled back in '-all_nodes' mode only. Converting the RAC mode to '-all_nodes' mode. Applying interim patch '10205230' to OH '/home/oracle/product/11.2' Verifying environment and performing prerequisite checks... Do you want to proceed? [y|n] y User Responded with: Y All checks passed. 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 'bug10205230.pch', on database 'orcl01d1'. Patching in all-node mode. Updating nodes 'prod04' Apply-related files are: FP = "/home/oracle/product/11.2/.patch_storage/10205230_Apr_2_2011_12_57_14/rac/copy_files.txt" DP = "/home/oracle/product/11.2/.patch_storage/10205230_Apr_2_2011_12_57_14/rac/copy_dirs.txt" MP = "/home/oracle/product/11.2/.patch_storage/10205230_Apr_2_2011_12_57_14/rac/make_cmds.txt" RC = "/home/oracle/product/11.2/.patch_storage/10205230_Apr_2_2011_12_57_14/rac/remote_cmds.txt" Instantiating the file "/home/oracle/product/11.2/.patch_storage/10205230_Apr_2_2011_12_57_14/rac/copy_files.txt.instantiated" by replacing $ORACLE_HOME in "/home/oracle/product/11.2/.patch_storage/10205230_Apr_2_2011_12_57_14/rac/copy_files.txt" with actual path. Propagating files to remote nodes... Instantiating the file "/home/oracle/product/11.2/.patch_storage/10205230_Apr_2_2011_12_57_14/rac/copy_dirs.txt.instantiated" by replacing $ORACLE_HOME in "/home/oracle/product/11.2/.patch_storage/10205230_Apr_2_2011_12_57_14/rac/copy_dirs.txt" with actual path. Propagating directories to remote nodes... Installing and enabling the online patch 'bug10205230.pch', on database 'orcl01d2' on node 'prod04'. Patch 10205230 successfully applied Log file location: /home/oracle/product/11.2/cfgtoollogs/opatch/opatch2012-06-20_16-53-36PM.log OPatch succeeded.
You can verify that patch is applied and enabled
SQL> oradebug patch list Patch File Name State ================ ========= bug10205230.pch ENABLED
If you have multiple ORACLE databases running out of same RDBMS Home, you can enable patch for other database using enableonlinepatch option
$ opatch util enableonlinepatch -connectString orcl02d1:sys:oracle123:prod03,orcl02d2:sys:oracle123:prod04 -id 10205230
In case of single instance home, below syntax is used
$ opatch util enableonlinepatch -connectString orcl:sys:oracle123 -id 10205230
Alert log is updated with following entries during patch application
Patch bug10205230.pch Installed - Update #1 Patch bug10205230.pch Enabled - Update #2 Thu Jun 21 02:13:17 2012 Online patch bug10205230.pch has been installed Online patch bug10205230.pch has been enabled
Online patching is very important feature introduced in 11.2.0.2 as this allows you to apply patches without any downtime. Note that if you apply patch using normal opatch apply, it will not be enabled. You can confirm this by oradebug patch list command.
Good one!