11gR2:Oracle Online Patching

-

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 – ***
h_passwd – **
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.

Comments

Post a Comment

Popular posts from this blog

Workflow Agent Listener Service WF_DEFERRED + Business Event not processing

-
Workflow Agent Listener Service WF_DEFERRED Will Not Start, Mailer May Be Down To implement the solution, please execute the following steps: 1.  Use this query to identify any remaining items in wf_deferred queue: SQL> select corrid, decode(state, 0, '0 = Ready', 1, '1 = Delayed', 2, '2 = Retained', 3, '3 = Exception', to_char(state)) State, count(*) COUNT from wf_deferred group by corrid, state; 2. Items with the APPS corrid will not dequeue from agent listener via service container.  This runs the deferred listener from the command lin e . Make sure that the listener and workflow service components - including the mailer - are down before you run this . This is not intended to be a substitute for the normal operation of this service container from the OAM console. Use this only during this cleanup operation. One will have to run this script manually: begin wf_log_pkg.wf_debug_flag := TRUE; wf_event...
Read more

Output Post Processor - EBS 12.2.4 Troubleshoot

-
Output Post Processor TroubleShooting Issue 1 Output Post Processor is Down with Actual Process is 0 And Target Process is 1 1. Shutdown Concurrent Managers 2. To ensure concurrent manager down; check there is no FNDLIBR process running. ps -ef | grep FNDLIBR 3. Run adadmin to relink FNDSVC executable. a. Invoke adadmin from command prompt b. Choose option 2 (2. Maintain Applications Files menu) c. Choose option 1 (1. Relink Applications programs ) d. Then type FND When prompted; ( Enter list of products to link (all for all products) [all] : FND ) e. Ensure adrelink is exiting with status 0 4. Start Concurrent Managers 5. Check the Output Post Processor Issue 2 Concurrent Processing  R12 Output Post Processor Service Not Coming Up Reason : If Service Manager for the node is not running.  Possible cause might be service manager definition is missing under Concurrent ->Manager ->Define form. If the Service Manager is not pr...
Read more

How to Diagnose Workflow Notification Mailer Issue

-
Outbound Notification Emails : 1. Check if the notification is present in the recipient/user's Worklist/Notifications page.If it does not exist     then it means that notification itself is not created and need to check the corresponding workflow     status.You may also query the notification from the wf_notifications table SQL> select recipient_role,notification_id,status,mail_status from wf_notifications where recipient_role like '&user_name';    The e-mail notification is sent only if all of following are true.                --Notification status is OPEN or CANCELED                --Notification mail_status is MAIL or INVALID 2. Check Recipient role has a valid e-mail address and notification preference MAIL% SELECT email_address, nvl(WF_PREF.get_pref(name, 'MAILT...
Read more