Tridion CMS & Oracle: ORA-01000: maximum open cursors exceeded

Question

hopefully someone can help me here.

We use Tridon CMS to manage a website hosted on jBoss and Apache.

We've been using SDL Tridion 5.3 for 5 years now and suddenly we have encountered an error with the Oracle database behind it. Most of our content is served as normal jsp pages from the file system but we have some components which are served via a call to Tridion's Java API which returns an html snippet from the Oracle database.
Recently we noticed that some of these html snippets weren't being served and on inspection of the server log files we found that the Oracle error ORA-01000: maximum open cursors exceeded was being generated.
Our max cursors was set to 300 so we increased it to 350 to see if it helped but it didn't.
Monitoring the active Oracle sessions, we could see that a number of sessions had reached the max cursors so we restarted the app server. This appeared to help, but only temporarily. The number of sessions reaching the max cursors threshold is gradually increasing again. Yesterday afternoon soon after the restart there were around 30 sessions with max cursors, this morning it's 150.

Obviously something has changed recently to cause this, but we're not sure what. The Oracle database is not something we generally have access to and certainly not something we make changes to directly - all day-to-day database operations happen via the Tridion API. We haven't done anything out of the ordinary as far as Tridion development and publishing is concerned so nothing different to what we've been doing for the past few years there. Traffic volumes to our website are relatively low at the moment (and have been significantly higher in the past) so we're pretty sure there's not an issue there.

One thing I have just been told that may or may not be connected - shortly before the issue arose we failed over one of our internal firewalls, but we can't think of a way this could cause the issue we're seeing. Other than the firewall failover we can't find any other changes to the connectivity between the app server and the database.

Does anyone have any suggestions of where we might look for a solution here? We've just opened a support ticket with SDL but at the moment they're just as bemused as us.

Thanks.

Answer 1

We have experienced similar issue in the past with IBM WebSphere. In our scenario, the root cause of the problem is that the firewall time out for idle or stale connections and dropping connections between the application server and the DB because the timeout at the firewall is lower than the AppServer connection cleanup timeout.

Its may be worthwhile to check this first and make sure this is not the root cause, it took a while to figure this out for us since we are spending lot of time analyzing the application logs and Tridion api etc. I assume that jBoss has similar setting like WebSphere.

The solution we have implemented is to set the Unused Timeout lower than the timeout setting on the firewall. This allowed WebSphere Application Server can clean up its unused connections before the firewall drops them.

Answer 2

I'm unsure about SDL Tridion R5.3, but on 2011 SP1 there is a known issue that using JNDI (in combination with WebSphere I believe) when ResultSets are not explicitly closed, it will leave the cursors open.

The solution is simply as Oracle documentation states, increase the number of allowed cursors, or if you keep running into problems as you say, you might consider not use JNDI. If possible you could check out your own code for properly closing ResultSets (which according to the JavaDoc for ResultSet:: it isn't necessary, although this seems to be only true when not using JNDI; when using JNDI it seems to leave the cursors open).