Unable to connect to Oracle Database 11.2.0.3,11.2.0.4 and 12.1.0.2 after the Oracle Security Patch Set launched in October 2014 was installed. The October 2014 Oracle Security Patch was designed for the 12.1.0.2 version.

Description

In some situations, ArcGIS is unable to connect to Oracle after installing certain releases of Oracle or installing certain Oracle Critical Patch Updates (CPUs). ArcGIS connections may stop responding, cause an error, or crash when attempting to establish a connection.
This behavior is encountered in Oracle version 12.1.0.2, and the first CPU to report this behavior was the October 2014 patch. This bug applies to all ArcGIS releases connecting to an Oracle instance with the applied Oracle permission change. This bug also applies to ArcGIS clients connecting to an Oracle instance with or without a geodatabase, and/or using ArcSDE Application Server Services.

Cause

Oracle’s dbms_utility package’s subprogram 'get_parameter_value' returns the following error:

Error:
ORA-01031: insufficient privileges.

ArcGIS software calls the subprogram when creating a connection to the Oracle database to obtain the database parameter 'open_cursors' value. The value is required to ensure the ArcGIS session working with the Oracle instance does not exceed the maximum number of cursors available within the session.
ArcGIS holds cursors open for frequently executed SQL statements to improve performance and scalability. The impact of this issue, introduced after installing the Critical Patch Update, is that it prevents ArcGIS from successfully establishing a connection.
For additional information on Oracle's Critical Patch Update Advisory, please visit Oracle's website.

Workaround

As an alternative workaround, the system administrator can grant the SELECT_CATALOG_ROLE to each user that accesses the Oracle instance with ArcGIS.
Esri contacted Oracle Technical Support (Oracle SR 3-9775575171) and verified that there was a permission change on the PUBLIC role that was rolled into some of their releases and CPUs. Based on Oracle's instructions, the recommendation to ensure ArcGIS applications are able to successfully connect to the Oracle instance, is to explicitly grant SELECT privileges on the v$parameter view to each user or the public role.
Connect in SQL*Plus as the SYS user and grant SELECT on the v_$parameter table to each named user or the PUBLIC role.

SQL> GRANT SELECT ON v_$parameter TO PUBLIC;

Solution
If problems making connections are encountered after installing an Oracle Critical Patch Update (CPU), please verify that the appropriate ArcGIS Esri patch has been installed. The ArcGIS 'Oracle Critical Patch Update - October 2014' Connection Issue Patch does not require the elevated system privilege to allow a user to create the Oracle database connection.
See How To: Configure the geodatabase open_cursors server_config parameter for Oracle for more information on setting the open_cursors parameter within the geodatabase to match what is set in the Oracle initialization parameter for the Oracle database connection.

Steps to Reproduce