Sunday, July 1, 2012

Applying Rolling Patch on RAC 11g R2

Last week I have got this task to apply Rolling patch 6 on Oracle RAC 11g R2 11.2.0.3 on Windows 2008 64 bit

Environment: 11gRAC/DB (11.2.0.3) , Windows Server 2008 R2 x64 Edition

Patch:13965211 - 11.2.0.3 Patch 6 for Windows 64 bit, download patch from metalink.
This patch is RAC rolling upgradable for GRID, and RDBMS homes.
You should administer one Oracle product, one Oracle home at a time, for its particular Bundle Patch Update.
DB 11.2.0.3 Patch 6 includes all bugs fixed in 11.2.0.3 Patch 1 to Patch 5. It must be applied on top of the 11.2.0.3 base release or on top of any earlier bundle patch from 11.2.0.3 Patch 1 to Patch 5.

The order of patching in RAC install is GRID_HOME, then RDBMS_HOME

OPatch Info:
Oracle recommends that you use OPatch utility release 11.2.0.1.9 or later. You can download it from My Oracle Support with patch 6880880.

Patch Installation Instructions
Patching GRID Home

1. Before Patching GRID_HOME, stop OCR related resources, Oracle HASD Services (OHASD) Services and all Oracle Services:


> srvctl stop service -d  -s  -i

> srvctl stop instance -d DBNAME -i INSTANCE -o abort

> crsctl stop crs

make the backup of the Oracle Home being patached (eg; d:\app...)

  1. To verify all resources have been oved to the other nodes, run the following command from another node with GRID running:

3.  > crsctl status resource -t


  1. Then use the Computer Management Services GUI or the net stop command to stop ALL Oracle services running on that node:
        net stop OracleService
        net stop OracleTNSListenerLISTENER_
        net stop OracleTNSListenerLISTENER_SCANn
        net stop OracleDBConsole
        net stop OracleASMService
        net stop "Oracle VSS Writer Service"
        net stop OracleREMExecService
        net stop OracleOHService
        net stop "Oracle Object Service"
        net stop OracleClusterVolumeService
        net stop ocfs (required only for OCFS installation)
        net stop orafenceservice
        Note: An explicit 'srvctl start listener -n ' should be issued after patching
  2. Set the ORACLE_HOME environment variable properly
    D:\Sources\11.2.0.3\Patches\p6880880_112000_MSWIN-x86-64_optach\OPatch>set ORACLE_HOME=D:\app\11.2.0.3\grid
  3. Go to the directory where you downloaded the patch:
    D:\Sources\11.2.0.3\Patches\p6880880_112000_MSWIN-x86-64_optach\OPatch>cd D:\Sources\11.2.0.3\Patches\p13965211_112030_MSWIN-x86-64
    First check the inventory for the existing patches
    D:\Sources\11.2.0.3\Patches\p13965211_112030_MSWIN-x86-64>D:\Sources\11.2.0.3\Patches\p6880880_112000_MSWIN-x86-64_optach\OPatch\opatch lsinventory
Oracle Interim Patch Installer version 11.2.0.3.0
Copyright (c) 2012, Oracle Corporation.  All rights reserved.

Oracle Home       : D:\app\11.2.0.3\grid
Central Inventory : C:\Program Files\Oracle\Inventory
from           : n/a
OPatch version    : 11.2.0.3.0
OUI version       : 11.2.0.3.0
Log file location : D:\app\11.2.0.3\grid\cfgtoollogs\opatch\opatch2012-06-30_11-55-13AM_1.log

Lsinventory Output file location : D:\app\11.2.0.3\grid\cfgtoollogs\opatch\lsinv\lsinventory2012-06-30_11-55-13AM.txt
--------------------------------------------------------------------------------
Installed Top-level Products (1):

Oracle Grid Infrastructure                                           11.2.0.3.0
There are 1 products installed in this Oracle Home.

There are no Interim patches installed in this Oracle Home.
Rac system comprising of multiple nodes
  Local node = NODE1
  Remote node = NODE2

--------------------------------------------------------------------------------
OPatch succeeded.

7. D:\Sources\11.2.0.3\Patches\p13965211_112030_MSWIN-x86-64>cd 13965211

8. D:\Sources\11.2.0.3\Patches\p13965211_112030_MSWIN-x86-64\13965211>D:\Sources\11.2.0.3\Patches\p6880880_112000_MSWIN-x86-64_optach\OPatch\opatch apply -local
Oracle Interim Patch Installer version 11.2.0.3.0
Copyright (c) 2012, Oracle Corporation.  All rights reserved.


Oracle Home       : D:\app\11.2.0.3\grid
Central Inventory : C:\Program Files\Oracle\Inventory
   from           : n/a
OPatch version    : 11.2.0.3.0
OUI version       : 11.2.0.3.0
Log file location : D:\app\11.2.0.3\grid\cfgtoollogs\opatch\13965211_May_26_2012_13_32_19\apply2012-06-30_13-32-18PM_1.l
og

Applying interim patch '13965211' to OH 'D:\app\11.2.0.3\grid'
Verifying environment and performing prerequisite checks...
Patch 13965211: Optional component(s) missing : [ oracle.precomp.common, 11.2.0.3.0 ] , [ oracle.precomp.lang, 11.2.0.3.
0 ] , [ oracle.ntoledb.odp_net_2, 11.2.0.3.0 ] , [ oracle.rdbms.tg4msql, 11.2.0.3.0 ] , [ oracle.rdbms.oci, 11.2.0.3.0 ]
 , [ oracle.ntoramts, 11.2.0.3.0 ] , [ oracle.rdbms.ic, 11.2.0.3.0 ] , [ oracle.ntoledb, 11.2.0.3.0 ] , [ oracle.ctx, 11
.2.0.3.0 ] , [ oracle.odbc.ic, 11.2.0.3.0 ] , [ oracle.sysman.console.db, 11.2.0.3.0 ] , [ oracle.sysman.agent, 10.2.0.4
.3 ]
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

This node is part of an Oracle Real Application Cluster.
Remote nodes: 'NODE2'
Local node: 'NODE1'
Please shutdown Oracle instances running out of this ORACLE_HOME on the local system.
(Oracle Home = 'D:\app\11.2.0.3\grid')

Is the local system ready for patching? [y|n]
y
User Responded with: Y
Backing up files...
Patching component oracle.rdbms, 11.2.0.3.0...

Patching component oracle.rdbms.rsf, 11.2.0.3.0...
Patching component oracle.rdbms.plsql, 11.2.0.3.0...
Patching component oracle.network.rsf, 11.2.0.3.0...
Patching component oracle.rdbms.rman, 11.2.0.3.0...
Patching component oracle.rdbms.util, 11.2.0.3.0...
Patching component oracle.rdbms.dbscripts, 11.2.0.3.0...
Patching component oracle.xdk.rsf, 11.2.0.3.0...
Patching component oracle.rdbms.rsf.ic, 11.2.0.3.0...
Patching component oracle.has.common.cvu, 11.2.0.3.0...
Patching component oracle.has.deconfig, 11.2.0.3.0...
Patching component oracle.has.rsf, 11.2.0.3.0...
Patching component oracle.swd.oui, 11.2.0.3.0...

Patching component oracle.has.common, 11.2.0.3.0...
Patching component oracle.has.cvu, 11.2.0.3.0...
Patching component oracle.has.db, 11.2.0.3.0...
Patching component oracle.has.crs, 11.2.0.3.0...
Patching component oracle.usm, 11.2.0.3.0...
Patching component oracle.usm.deconfig, 11.2.0.3.0...
Patching component oracle.network.listener, 11.2.0.3.0...
Patching component oracle.ldap.rsf.ic, 11.2.0.3.0...
Verifying the update...
The local system has been patched.  You can restart Oracle instances on it.

9. opatch will attempt to apply the patch on all the nodes in the cluster sequentially. However will prompt for user input to start patching other nodes. At this time ensure that the Oracle services on target node are stopped before proceeding to patch. Once the opatch session completes patching this node, start the services and proceed applying patch to the next node.

10. Once the opatch session completes patching GRID_HOME on a node, run the following before proceeding to patch GRID_HOME on the next node. %ORACLE_HOME%\bin\acfsroot installD:\Sources\11.2.0.3\Patches\p13965211_112030_MSWIN-x86-64\13965211>%ORACLE_HOME%\bin\acfsroot install
ACFS-9300: ADVM/ACFS distribution files found.
ACFS-9307: Installing requested ADVM/ACFS software.
ACFS-9308: Loading installed ADVM/ACFS drivers.
ACFS-9327: Verifying ADVM/ACFS devices.
ACFS-9157: Detecting driver 'oracle oks'.
ACFS-9157: Detecting driver 'oracle advm'.
ACFS-9157: Detecting driver 'oracle acfs'.
ACFS-9309: ADVM/ACFS installation correctness verified.


11. After this you can start the CRS or go for the RDBMS home patch without starting it, I started.
D:\app\11.2.0.3\grid\BIN>crsctl start crs
CRS-4123: Oracle High Availability Services has been started.

D:\app\11.2.0.3\grid\BIN>crsctl check crs
CRS-4638: Oracle High Availability Services is online
CRS-4537: Cluster Ready Services is online
CRS-4529: Cluster Synchronization Services is online
CRS-4533: Event Manager is online



12. Verify the patch installation
# opatch lsinventory

D:\Sources\11.2.0.3\Patches\p13965211_112030_MSWIN-x86-64\13965211>set ORACLE_HOME=D:\app\11.2.0.3\grid
D:\Sources\11.2.0.3\Patches\p13965211_112030_MSWIN-x86-64\13965211>D:\Sources\11.2.0.3\Patches\p6880880_112000_MSWIN-x86
-64_optach\OPatch\opatch lsinventory
Oracle Interim Patch Installer version 11.2.0.3.0
Copyright (c) 2012, Oracle Corporation.  All rights reserved.


Oracle Home       : D:\app\11.2.0.3\grid
Central Inventory : C:\Program Files\Oracle\Inventory
   from           : n/a
OPatch version    : 11.2.0.3.0
OUI version       : 11.2.0.3.0
Log file location : D:\app\11.2.0.3\grid\cfgtoollogs\opatch\opatch2012-06-30_13-52-31PM_1.log

Lsinventory Output file location : D:\app\11.2.0.3\grid\cfgtoollogs\opatch\lsinv\lsinventory2012-06-30_13-52-31PM.txt
--------------------------------------------------------------------------------
Installed Top-level Products (1):

Oracle Grid Infrastructure                                           11.2.0.3.0
There are 1 products installed in this Oracle Home.


Interim patches (1) :
Patch  13965211     : applied on Sat Jun 30 13:39:18 AST 2012
Unique Patch ID:  14860516
Patch description:  "ORACLE 11G 11.2.0.3 PATCH 6 BUG FOR WINDOWS (64-BIT AMD64 AND INTEL EM64)"
   Created on 21 May 2012, 00:06:51 hrs PST8PDT
   Bugs fixed:
     13965211, 12424121, 13478060, 13786778, 13579992, 13705338, 13603613
     13476583, 12745662, 9190186, 13530646, 13520452, 9019231, 13610777
     12905053, 10263668, 13550185, 13385346, 13448206, 12772404, 13911711
     11072246, 13104881, 13863326, 12889054, 13775960, 13777823, 13464002
     11824898, 13535622, 13916709, 12880299, 12918338, 12529945, 13811209
     14058362, 13724808, 13885389, 13649031, 13350245, 13553883, 13388104
     13635347, 13404129, 13031118, 12530140, 13023541, 13767921, 13787482
     13641076, 13807411, 13724992, 12965899, 13791443, 12879027, 13584130
     13495307, 12594032, 13709220, 13247965, 13243072, 13683125, 13652493
     12957127, 10215977, 13014128, 13040331, 12857222, 12977501, 12664456
     12405931, 13525554, 13492863, 13573521, 13873471, 13366268, 13352423
     13460353, 12709476, 13652088, 13886023, 13942723, 12919564, 12894807
     12829021, 12612118, 11063191, 13503598, 13482688, 13354082, 13484963
     13395403, 13542159, 12983611, 13718476, 12846562, 9659614, 13326736
     11846902, 11665727, 13257247, 13588248, 13544396, 13566938, 13615517
     13425727, 13037709, 12730342, 12349553, 13502441, 13258062, 13251796
     13247273, 12659561, 12639013, 12594616, 11772838, 13570057, 12403721
     12585543, 12784549, 12834800, 12975771, 13040171, 13058950, 13063120
     13077335, 13365700, 13382280, 13384397, 13440516, 13454210, 13457537
     13477790, 13496250, 13501787, 13505390, 13506110, 13524899, 13572659
     13594712, 13617627, 13326289, 13555974, 13035360, 13420224, 13419660
     13036331, 13332439, 13420174, 12583826, 13358781, 11883969, 13524237
     12867713, 13328193, 13516727, 11865420, 13502183, 13259364, 13371153
     12998795, 13413168, 12897902, 13044108, 12971242, 12834027, 12620823
     13420516, 12849377, 12975811, 12646784, 12829429, 12538907, 12950823
     12848480, 12823838, 13366202, 13002015, 12834777, 13001901, 13017428
     13082238, 12965049, 11840910, 12656535, 12617123, 13073340, 12925041
     12938841, 13023632, 13066371, 12942119, 11877623, 12810890, 12995950
     12765467, 12934171, 13070939, 12535346, 12985184, 13103913, 12765868
     12622441, 12876314, 12820045, 13038806, 13090686, 12923168, 13362079
     12591252, 12718090, 12873909, 10350832, 12795861, 13039908, 12976590
     13017584, 12627504, 13263435, 12847466, 12797765, 12758736, 12878750
     12861463, 13024624, 12764337, 12662040, 13068077, 12914824, 13074261
     12668341, 12597906, 12932852, 13045518, 12897651, 12960925, 12728585
     13019958, 12902661, 12886827, 12913474, 3522216, 12678920, 12885323
     13334158, 12947871, 12896850, 13001955, 12784559, 12827166, 9703627
     12772345, 12905058, 13345868, 10357727, 12827493, 13004894, 12780983
     12842804, 13146719, 12655301, 12960302, 13085732, 12979199, 12638117
     13357509, 12401111, 12857064, 8631856, 13035804, 13355963, 12845115
     12695029, 12990582, 12971775, 12867511, 12917230, 12582664, 12849688
     12950644, 12588744, 13011520, 12899169, 12823042


Rac system comprising of multiple nodes
  Local node = NODE1
  Remote node = NODE2

--------------------------------------------------------------------------------
OPatch succeeded.
D:\Sources\11.2.0.3\Patches\p13965211_112030_MSWIN-x86-64\13965211>


Patching RDBMS Home
  1. Before Patching RDBMS_HOME, the GRID and ASM services can be running.
    Stop OCR related resources and Oracle Services on the current node as listed below:
    > srvctl stop
    service -d -s -i > srvctl stop instance -d -i -o abort
    D:\app\11.2.0.3\grid\BIN>srvctl stop instance -d rac -i rac1
  2. Set ORACLE_HOME
    D:\Sources\11.2.0.3\Patches\p13965211_112030_MSWIN-x86-64\13965211>set ORACLE_HOME=D:\app\Sami\product\11.2.0.3\dbhome_1
 Apply Patch now,
D:\Sources\11.2.0.3\Patches\p13965211_112030_MSWIN-x86-64\13965211>D:\Sources\11.2.0.3\Patches\p6880880_112000_MSWIN-x86-64_optach\OPatch\opatch apply

Oracle Interim Patch Installer version 11.2.0.3.0
Copyright (c) 2012, Oracle Corporation.  All rights reserved.


Oracle Home       : D:\app\Sami\product\11.2.0.3\dbhome_1
Central Inventory : C:\Program Files\Oracle\Inventory
   from           : n/a
OPatch version    : 11.2.0.3.0
OUI version       : 11.2.0.3.0
Log file location : D:\app\Sami\product\11.2.0.3\dbhome_1\cfgtoollogs\opatch\13965211_Jun_30_2012_15_21_30\apply2012-06-
30_15-21-29PM_1.log
Applying interim patch '13965211' to OH 'D:\app\Sami\product\11.2.0.3\dbhome_1'
Verifying environment and performing prerequisite checks...
Patch 13965211: Optional component(s) missing : [ oracle.precomp.lang, 11.2.0.3.0 ] , [ oracle.rdbms.tg4msql, 11.2.0.3.0
 ] , [ oracle.rdbms.ic, 11.2.0.3.0 ] , [ oracle.has.cvu, 11.2.0.3.0 ] , [ oracle.has.crs, 11.2.0.3.0 ] , [ oracle.usm, 1
1.2.0.3.0 ]
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
This node is part of an Oracle Real Application Cluster.
Remote nodes: 'node2'
Local node: 'node1'
Please shutdown Oracle instances running out of this ORACLE_HOME on the local system.
(Oracle Home = 'D:\app\Sami\product\11.2.0.3\dbhome_1')

Is the local system ready for patching? [y|n]
y
User Responded with: Y
Backing up files...
Patching component oracle.rdbms, 11.2.0.3.0...
Patching component oracle.rdbms.rsf, 11.2.0.3.0...
Patching component oracle.rdbms.plsql, 11.2.0.3.0...
Patching component oracle.network.rsf, 11.2.0.3.0...
Patching component oracle.precomp.common, 11.2.0.3.0...
Patching component oracle.rdbms.rman, 11.2.0.3.0...
Patching component oracle.rdbms.util, 11.2.0.3.0...
Patching component oracle.ntoledb.odp_net_2, 11.2.0.3.0...
Patching component oracle.rdbms.dbscripts, 11.2.0.3.0...
Patching component oracle.xdk.rsf, 11.2.0.3.0...
Patching component oracle.rdbms.rsf.ic, 11.2.0.3.0...
Patching component oracle.rdbms.oci, 11.2.0.3.0...
Patching component oracle.ntoramts, 11.2.0.3.0...
Patching component oracle.ntoledb, 11.2.0.3.0...
Patching component oracle.ctx, 11.2.0.3.0...
Patching component oracle.odbc.ic, 11.2.0.3.0...
Patching component oracle.has.common.cvu, 11.2.0.3.0...
Patching component oracle.has.deconfig, 11.2.0.3.0...
Patching component oracle.has.rsf, 11.2.0.3.0...
Patching component oracle.swd.oui, 11.2.0.3.0...
Patching component oracle.has.common, 11.2.0.3.0...
Patching component oracle.has.db, 11.2.0.3.0...
Patching component oracle.usm.deconfig, 11.2.0.3.0...
Patching component oracle.sysman.console.db, 11.2.0.3.0...
Patching component oracle.network.listener, 11.2.0.3.0...
Patching component oracle.sysman.agent, 10.2.0.4.3...
Patching component oracle.ldap.rsf.ic, 11.2.0.3.0...
Verifying the update...
The local system has been patched.  You can restart Oracle instances on it.
4. Now start the Database instance,
D:\app\11.2.0.3\grid\BIN>srvctl start instance -d rac -i rac1

Post installation steps
After installing the patch, perform the following actions:
For each database instance running out of the ORACLE_HOME being patched, connect to the database using SQL*Plus as SYSDBA and run catwinbundle.sql as follows:


> cd %ORACLE_HOME%\Bundle\Patch6
> sqlplus /nolog
SQL> CONNECT / AS SYSDBA
SQL> STARTUP
SQL> @catwinbundle.sql
SQL> QUIT

C:\Users\sami>cd D:\app\Sami\product\11.2.0.3\dbhome_1\bundle\patch6
D:\app\Sami\product\11.2.0.3\dbhome_1\bundle\patch6>sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Sat Jun 30 15
:30:19 2012
Copyright (c) 1982, 2011, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, Oracle Label Security,
OLAP, Data Mining and Real Application Testing options
SQL>
SQL> @catwinbundle.sql
...
..
...
SQL> ALTER SESSION SET current_schema = SYS;
Session altered.
SQL> PROMPT Updating registry...
Updating registry...
SQL> INSERT INTO registry$history
  2    (action_time, action,
  3     namespace, version, id,
  4     bundle_series, comments)
  5  VALUES
  6    (SYSTIMESTAMP, 'APPLY',
  7     SYS_CONTEXT('REGISTRY$CTX','NAMESPACE'),
  8     '11.2.0.3',
  9     6,
 10     'WINBUNDLE',
 11     '11.2.0.3 BP 6');
1 row created.
SQL> COMMIT;
Commit complete.
SQL> SPOOL off
SQL> SET echo off
Check the following log file for errors:
D:\app\Sami\cfgtoollogs\catbundle\catbundle_WINBUNDLE_RAC_APPLY_2012Jun30_15_31_37.log
SQL>


Check all dependent objects are valid and if not then compile them by using utlrp.sql script.
SQL> select count(*) from dba_objects where status ='INVALID';
  COUNT(*)
----------
     20083

Note:
You must execute the steps in "Post Installation Steps” for any new database that was created by any of the following methods:
  • Using DBCA (Database Configuration Assistant) to select a sample database (General, Data Warehouse, Transaction Processing)
  • Using a script that was created by DBCA that creates a database from a sample database
  • Cloning a database that was created by either of the two preceding methods, and if "Post Installation Steps”  was not executed after Bundle Patch6 was applied
Start Patching the next node:
Finally check the status of all the services
>crsctl status resource -t
Once all is well, you can start the same steps mentioned above for the next node.

>>>Wish you all a happy patching.<<<

For any further queries on RAC and Grid Infrastructure, please don't hesitate to email me on samiora@gmail.com