The credit for the patch apply in the post goes to a new friend that wishes to remain anonymous, except that her initials start with KA, and who happens to work at a company in Memphis, TN. Coincidentally, that is where I had been liv. During a recent EM12c project at her company, we were going through the steps to create a 2-node RAC environment. After registering it with Enterprise Manager, and adding the relevant targets (Oracle Homes, Databases, Listeners etc) we tested the connectivity (specifically checking the tablespace usage) to one of the databases and receive an “ORA-01000 maximum open cursors” message.
The open_cursors parameter in the respective database was set to 5000, and there were hardly any connections on it at the time which could explain it. We attempted a local login (from the respective host) as well as a listener based login, both of which were successful. After a quick search on MOS, it turns out there’s a bug which causes queries from Enterprise Manager on 11.2.0.4 Databases: “EM 12c: Querying a List of tablespaces for an 11.2 Oracle Database Results in ORA-1000 Error in Enterprise Manager 12.1.0.3 Cloud Control (Doc ID 1618684.1)”. The solution, according to the MOS note, was to apply Patch 17897511 on the RDBMS home where the 11.2.0.4 instance resides.
Since I’d never tested the online patching process, it seemed like a good time to try it out. I’d recommend reading Jason Arniel’s post here on the subtle “ism’s” of online patching – I found it to be quite useful.
Let’s begin.
d-oratest11-db01.planets.com:/u01/oracle/software/patch/17897511:oratst111> /u01/oracle/product/11.2.0/db_1/OPatch/opatch apply online -connectString oratst111:sys:password:d-oratest11-db01,oratst112:sys:password:d-oratest11-db02 Oracle Interim Patch Installer version 11.2.0.3.4 Copyright (c) 2012, Oracle Corporation. All rights reserved. Oracle Home : /u01/oracle/product/11.2.0/db_1 Central Inventory : /u01/oracle/oraInventory from : /u01/oracle/product/11.2.0/db_1/oraInst.loc OPatch version : 11.2.0.3.4 OUI version : 11.2.0.4.0 Log file location : /u01/oracle/product/11.2.0/db_1/cfgtoollogs/opatch/17897511_Feb_14_2014_12_28_57/apply2014-02-14_12-28-56PM_1.log The patch should be applied/rolled back in '-all_nodes' mode only. Converting the RAC mode to '-all_nodes' mode. Applying interim patch '17897511' to OH '/u01/oracle/product/11.2.0/db_1' Verifying environment and performing prerequisite checks... All checks passed. Backing up files... Patching component oracle.rdbms, 11.2.0.4.0... Installing and enabling the online patch 'bug17897511.pch', on database 'oratst111'. Verifying the update... Patching in all-node mode. Updating nodes 'd-oratest11-db02' Apply-related files are: FP = "/u01/oracle/product/11.2.0/db_1/.patch_storage/17897511_Jan_28_2014_07_45_26/rac/copy_files.txt" DP = "/u01/oracle/product/11.2.0/db_1/.patch_storage/17897511_Jan_28_2014_07_45_26/rac/copy_dirs.txt" MP = "/u01/oracle/product/11.2.0/db_1/.patch_storage/17897511_Jan_28_2014_07_45_26/rac/make_cmds.txt" RC = "/u01/oracle/product/11.2.0/db_1/.patch_storage/17897511_Jan_28_2014_07_45_26/rac/remote_cmds.txt" Instantiating the file "/u01/oracle/product/11.2.0/db_1/.patch_storage/17897511_Jan_28_2014_07_45_26/rac/copy_files.txt.instantiated" by replacing $ORACLE_HOME in "/u01/oracle/product/11.2.0/db_1/.patch_storage/17897511_Jan_28_2014_07_45_26/rac/copy_files.txt" with actual path. Propagating files to remote nodes... Instantiating the file "/u01/oracle/product/11.2.0/db_1/.patch_storage/17897511_Jan_28_2014_07_45_26/rac/copy_dirs.txt.instantiated" by replacing $ORACLE_HOME in "/u01/oracle/product/11.2.0/db_1/.patch_storage/17897511_Jan_28_2014_07_45_26/rac/copy_dirs.txt" with actual path. Propagating directories to remote nodes... Installing and enabling the online patch 'bug17897511.pch', on database 'oratst112' on node 'd-oratest11-db02'. Patch 17897511 successfully applied Log file location: /u01/oracle/product/11.2.0/db_1/cfgtoollogs/opatch/17897511_Feb_14_2014_12_28_57/apply2014-02-14_12-28-56PM_1.log OPatch succeeded.Great! Let’s just make sure that it worked.
d-oratest11-db01.planets.com:/u01/oracle/software/patch/17897511:oratst111> /u01/oracle/product/11.2.0/db_1/OPatch/opatch lsinventory Oracle Interim Patch Installer version 11.2.0.3.4 Copyright (c) 2012, Oracle Corporation. All rights reserved. Oracle Home : /u01/oracle/product/11.2.0/db_1 Central Inventory : /u01/oracle/oraInventory from : /u01/oracle/product/11.2.0/db_1/oraInst.loc OPatch version : 11.2.0.3.4 OUI version : 11.2.0.4.0 Log file location : /u01/oracle/product/11.2.0/db_1/cfgtoollogs/opatch/opatch2014-02-14_12-29-37PM_1.log Lsinventory Output file location : /u01/oracle/product/11.2.0/db_1/cfgtoollogs/opatch/lsinv/lsinventory2014-02-14_12-29-37PM.txt -------------------------------------------------------------------------------- Installed Top-level Products (1): Oracle Database 11g 11.2.0.4.0 There are 1 products installed in this Oracle Home. Interim patches (1) : Patch (online) 17897511: applied on Fri Feb 14 12:29:01 CST 2014 Unique Patch ID: 17213015 Created on 28 Jan 2014, 07:45:26 hrs PST8PDT Bugs fixed: 17897511 Rac system comprising of multiple nodes Local node = d-oratest11-db01 Remote node = d-oratest11-db02 -------------------------------------------------------------------------------- OPatch succeeded.And, thats it. After the patch application, we were able to query the tablespace information for all databases running from this home successfully via Enterprise Manager.
Cheers!
Maaz Anjum's Blog 
Patch 17897511 is superseded by patch 18235390.
Thanks for the update Larry!