As all of you know that I do not blog very often. Maybe, I do not have much to say. With all the confusion lately about Oracle's CPU (stands for Critical Patch Updates), OPEN_CURSORS parameter and ArcMap crashing, lots of calls I have received and questions from my colleagues in Support team, I have decided to write about it. In this blog post, I will attempt to clarify them in the hope that it will help others.
I will start with a small history lesson to clarify the timeline. Oracle CPUs are released every quarter (4 times a year) and the idea behind this is that Oracle proactively patches issues, closes security holes, resolves bugs, etc. The periodic release of the patches helps DBAs to schedule the maintenance window, etc. With the release of Oct 2014 CPU, ESRI and consecutively our software was impacted. Following below are the details of how and why...in case you are interested. It is not merely academic as the knowledge of the problem always helps us understand it's resolution.
Prior to Oracle's Oct 2014 CPU patch, anyone could access (select) system parameter values. In this case the value of parameter OPEN_CURSORS. ESRI reads this parameter when it opens an ArcMap/ArcCatalog session to know what the value is set to. I am not sure what they use the value for, but can make an educated guess. I would explain my guess, but it is not relevant for the purposes of this post. Suffice it to say if the client session could not read this parameter, ArcMap/ArcCatalog session would close (without much explanation) resulting in what looked like ArcMap/ArcCatalog crashing.
Oracle figured that any user being able to read system parameters without explicit access to them posed a security risk. I can see their point and concur. You can do some damage if you know what all the parameters were. In an effort to close this security hole, Oracle disallowed select access to system parameters, to all regular users. If a user wanted to access system parameters, they would have to be given explicit access privileges. This security hole was closed in the October 2014 CPU patch (and every CPU patch following Oct 2014 CPU as Oracle CPU patches are cumulative).
As soon as our customers applied Oracle's October 2014 CPU patch, ArcMap/ArcCatalog sessions crashed, as they could not read the OPEN_CURSORS value and got an Oracle error "ORA-01031: insufficient privileges." This error was not readily visible from ArcMap/ArcCatalog GUI.
Clients complained to ESRI and after early diagnostics, finger pointing, etc. a workaround was devised. You can read all about the workaround in ESRI KB article 43293 . ESRI knew that this workaround would not be sufficient in the long run for security conscious DBAs and had to provide a different solution. Near the close of 2014, ESRI provided a patch ArcGIS 'Oracle Critical Patch Update - October 2014' Connection Issue Patch . You can read about this ArcGIS patch in the link provided. In essence what this patch did was that instead of looking for OPEN_CURSORS parameter in Oracle's system table, it looked for this value in SDE's SERVER_CONFIG table. Since this table belonged to SDE and ESRI had control of it, it did not have to depend on Oracle's security changes. To actually copy the value of OPEN_CURSORS from Oracle's system table to SDE's SERVER_CONFIG table, ESRI developed a KB article 43377 .
"All of this seems clear, so where is the confusion?" you ask.
I had to explain all the above and one important point, to point out all the confusion. If you read the KB 43377 (referenced above) closely, you will notice one critical point. I am going to put that in verbatim from the article so that I do not paraphrase.
"In summary, when an ArcGIS session connects to an Oracle instance, ArcGIS executes the dbms_utility.get_parameter_value subprogram, if an error is encountered (because the user connecting lacks privileges) ArcGIS checks if the open_cursors parameter exists in the geodatabase server_config table and when not present, automatically sets the default to a value of 300."
This means that if you apply the ArcGIS connection issue patch (after you have applied the Oracle's Oct 2014 CPU patch or later), but do not follow the steps discussed in KB 43377 you could end up with an issue. Let me explain with an example, because to me an example always helps.
1) I am a GIS Administrator of Company XYZ. I have an Oracle database 11g and ArcGIS 10.2.X. Everything is fine in my land and my GIS users are happy. Life is good.
2) Let us assume that my Oracle OPEN_CURSORS parameter is set to 2000.
3) My Oracle DBA comes to me one day (in Jan 2015) and says that he is going to apply Oracle CPU to make the database current with security patches. The patch is installed on Friday evening.
4) Monday morning, none of the GIS users can connect and I am in panic. I hop online and remember what my DBA was going to do last weekend. I figure out the issue and apply ESRI's patch to resolve the issue (after testing). In the meantime I put in the workaround (with the help of my DBA) so that my GIS users can work.
5) I test and then install the patch in Production, but forget about KB article 43377 or the process is not successful due to permission issues.
6) Now I have an issue where my OPEN_CURSORS parameter for Oracle is set to 2000, but to ESRI it is set to 300. This is due to combination of step 5 and the important point from KB 43377 mentioned above.
I start getting intermittent issues, sometimes ArcMap session crashes, but I have no idea why. The process is not repeatable, but happens when I have many layers in my stored display or during reconcile/post/compress process. In effect, every time where more than 300 cursors are opened. Oracle does not give me an error (specifically an ORA- error) as I am still under the Oracle's limit. I am lost...
Hope this makes the picture clear.
To avoid issues like these, please make sure that after you have applied the ArcGIS patch and properly followed the procedure in KB 43377, you have OPEN_CUSRORS parameter present in SDE's SERVER_CONFIG table and it's value matches the value of OPEN_CURSORS parameter in Oracle.
I hope this blog post helps. Please let me know if you have any questions.