메뉴 건너뛰기

Korea Oracle User Group

Guru's Articles

출처 : http://askdba.org/weblog/2012/06/11gr2oracle-online-patching/

 

11gR2:Oracle Online Patching

 

 / 22 June, 2012

 

 

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.

번호 제목 글쓴이 날짜 조회 수
공지 Guru's Article 게시판 용도 ecrossoug 2015.11.18 598
24 How to Recover Data (Without a Backup!) 명품관 2016.05.11 1354
23 On ROWNUM and Limiting Results (오라클 매거진 : AskTom) 명품관 2016.04.28 384
22 DDL Logging - 12c (조나단 루이스) 명품관 2016.04.26 1497
» 11.1.0.6 부터 지원하는 Oracle Online Patching New Feature 명품관 2016.04.22 519
20 Quick tip on Function Based Indexes 명품관 2016.04.19 191
19 Oracle Linux 6.7 with Oracle 11.2.0.4 RAC 명품관 2016.04.15 9598
18 Parameter Recommendations for Oracle Database 12c - Part II 명품관 2016.03.18 352
17 Parameter Recommendations for Oracle Database 12c - Part I 명품관 2016.03.07 832
16 Hybrid Columnar Compression Common Questions 명품관 2016.03.04 191
15 What is an In-Memory Compression Unit (IMCU)? 명품관 2016.02.24 181
14 Why You Can Get ORA-00942 Errors with Flashback Query 명품관 2016.02.01 447
13 How do I capture a 10053 trace for a SQL statement called in a PL/SQL package? 명품관 2016.01.06 262
12 (유투브) KISS series on Analytics: Dealing with null - Connor McDonald 명품관 2016.01.05 155
11 Upgrade a Pluggable Database in Oracle 12c 명품관 2015.12.30 160
10 Oracle 12c SQL – Using JSON 명품관 2015.12.29 2560
9 Oracle Enterprise Manager Cloud Control 13c Release 1 (13.1.0.0) Installation on Oracle Linux 6 and 7 명품관 2015.12.23 198
8 Top 5 SQL Monitor Features file 명품관 2015.12.01 777
7 On Invokers and Outs file 명품관 2015.11.23 102
6 Parallel Execution 12c New Features Overview file 명품관 2015.11.23 109
5 How many checkpoints in Oracle database ? [1] file 명품관 2015.11.20 282
위로