11gR2:Oracle Online Patching

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.

One thought on “11gR2:Oracle Online Patching

Leave a Reply