This week's book giveaway is in the Java in General forum.
We're giving away four copies of Event Streams in Action and have Alexander Dean & Valentin Crettaz on-line!
See this thread for details.
Win a copy of Event Streams in Action this week in the Java in General forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Devaka Cooray
  • Liutauras Vilda
  • Jeanne Boyarsky
  • Bear Bibeault
Sheriffs:
  • Paul Clapham
  • Knute Snortum
  • Rob Spoor
Saloon Keepers:
  • Tim Moores
  • Ron McLeod
  • Piet Souris
  • Stephan van Hulst
  • Carey Brown
Bartenders:
  • Tim Holloway
  • Frits Walraven
  • Ganesh Patekar

Tomcat8.5 w/Oracle 12.2 maximum open cursors exceeded

 
Greenhorn
Posts: 15
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hello Code Ranch! I have multiple Tomcat applications configured with various versions of Oracle and the Oracle Cursors rarely go above 50.  We have since Upgraded to Oracle 12.2 RAC in one of the env's and our cursors are exceeding 5000+ and do not go back down until we restart Tomcat.  Looking in Oracle we can see the Cursors are coming from our Tomcat DB user.  After multiple calls and SR's with Oracle they are telling us it is our application running on Tomcat that is the issue.

My question is if there are any config changes or lib's etc that need to be changed when upgrading from Oracle 12c to 12.2 RAC with ASM? Do I need to set maxAge or another variable to kill the stale cursors?

 
Quincy Schmidt
Greenhorn
Posts: 15
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I upgraded the ojdbc7.jar in the tomcat\lib directory to ojdbc8.jar to see if that helps.
 
Bartender
Posts: 20933
127
Android Eclipse IDE Tomcat Server Redhat Java Linux
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Try turning on the "orphan" connection detection option in your JDBC connection pool definition for Tomcat. That will cause a stack checkpoint to be taken when a Connection is checked out of the pool. If that Connection isn't closed (returned to the pool) within a reasonable amount of time, the saved stacktrace will be dumped out to the Tomcat log so that you can look for reasons why the Connection didn't close - and thus neither did the cursor.

That's assuming that Connection.close() for a pool Connection closes Statements, ResultSets and similar things attached to it - which I hadn't really considered before - except that putting a Connection back in a pool with active bits hanging off it would be trouble in its own right.

Generally, orphan connections come from sloppy programming (it only takes one mistake if you run enough connections long enough!), having an Exception thrown past the close() statement ("finally" is your friend!) or improper practices like trying to pass a Connection from one Http service request to the next (Connections are NOT Serializable/session-safe!).

 
Quincy Schmidt
Greenhorn
Posts: 15
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Tim Holloway wrote:Try turning on the "orphan" connection detection option in your JDBC connection pool definition for Tomcat. That will cause a stack checkpoint to be taken when a Connection is checked out of the pool. If that Connection isn't closed (returned to the pool) within a reasonable amount of time, the saved stacktrace will be dumped out to the Tomcat log so that you can look for reasons why the Connection didn't close - and thus neither did the cursor.

That's assuming that Connection.close() for a pool Connection closes Statements, ResultSets and similar things attached to it - which I hadn't really considered before - except that putting a Connection back in a pool with active bits hanging off it would be trouble in its own right.

Generally, orphan connections come from sloppy programming (it only takes one mistake if you run enough connections long enough!), having an Exception thrown past the close() statement ("finally" is your friend!) or improper practices like trying to pass a Connection from one Http service request to the next (Connections are NOT Serializable/session-safe!).



Thank you for the reply!  Is the orphan connection option configured in the server.xml or in the tomcatw.exe gui under the java tab?  Or better yet can you point me to a doc or url that explains the setup parameters?

 
Greenhorn
Posts: 18
1
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Look at your application and make sure you are properly closing all open connections in a finally statement.  Also, generally a database pool in tomcat has a maximum number of connections specified.  Your application should borrow a connection from the tomcat pool and then return it.  If you are piling up database pool connections like it sounds your are, then there is something wrong with how your application is written and freeing up resources.
 
Tim Holloway
Bartender
Posts: 20933
127
Android Eclipse IDE Tomcat Server Redhat Java Linux
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Connection Pools are usually specified in the Context xml for a single webapp, although pools can be shared with multiple webapps, in which case they'd be defined in TOMCAT_HOME/conf/server.xml

https://tomcat.apache.org/tomcat-8.0-doc/jndi-datasource-examples-howto.html

The magic word is "abandoned", and the specific option (xml attribute) for Tomcat 8 is logAbandoned="true". Other versions of Tomcat operate similarly.
 
Quincy Schmidt
Greenhorn
Posts: 15
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Matthew Keller wrote:Look at your application and make sure you are properly closing all open connections in a finally statement.  Also, generally a database pool in tomcat has a maximum number of connections specified.  Your application should borrow a connection from the tomcat pool and then return it.  If you are piling up database pool connections like it sounds your are, then there is something wrong with how your application is written and freeing up resources.



Thank you for the reply. I do not believe it is the app as we have the exact same application in multiple environments with the only difference being the version of Oracle.
 
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!