Investigate JDBC Problems (Continued)

Database Dilemmas
If the configured amount of allowed open cursors in an Oracle database is exceeded, this error message will be thrown:

java.sql.SQLException: ORA-01000: 
  maximum open cursors exceeded

There can be several causes for this error. In one case, the prepared statement cache is configured to be too large. Check the configuration of your JDBC pool regarding the prepared statement cache. Every prepared statement will use one open cursor in the Oracle database. The statement cache holds prepared statements on a connection basis, which means that the Oracle database will use up to:

(StatementCacheSize) x (MaxCapacity) 

open cursors for every configured pool. Since open cursors will be used for other objects also (for example, stored procedures or result sets) the number of open cursors needs to be configured high enough to hold all the statements in the statement cache. The setting for OPEN_CURSORS is per session/connection. (See Resources for additional information on statement cache configuration.)

Some versions of Oracle drivers (thin or oci) have a cursor leak in the XA driver class (oracle.jdbc.xa.client.OracleXADataSource) that leads to the ORA-01000 error message after some time. Ensure that on the database side the DBA_PENDING_TRANSACTION view has the correct permissions:

grant all on 
  DBA_PENDING_TRANSACTIONS to public
  grant all on DBA_PENDING_NEIGHBORS 
  to public grant all on
  DBA_2PC_PENDING to public

The cursor leak is fixed in Oracle version 9.2.0.5 and 10g. This known issue is described in Oracle Metalink Case 3151681 (see Resources).

If you have configured a firewall between the database and WebLogic Server, and this firewall closes idle connections after a certain amount of time, the JDBC pool refresh functionality can be used to ensure that connections from the pool are not closed by the firewall. Here is a common error message thrown after such a closed connection:

java.sql.SQLException: ORA-03113: 
  end-of-file on communication 
  channel
  at weblogic.db.oci.OciCursor.
    getCDAException(
    OciCursor.java:240)
  at weblogic.jdbc.oci.Statement.
    executeQuery(Statement.java:916) 
  at ...

This error occurs because the socket connection is considered okay from both the WebLogic Server and the database side; both may try to write into this socket connection and fail because it has been closed by the firewall without notification or error message to the participating parties. Use the refresh functionality to ensure that the connections are not idle long enough for the firewall to close them.

You can configure refresh functionality by setting the RefreshMinutes or TestFrequencySeconds property so that connections are tested at least one time during the idle period configured for the firewall. To enable the refresh functionality, the TestTableName property also has to be set (see Resources for more information).

However, every Java Message Service (JMS) server takes one connection from the JDBC pool if a JDBC store is defined. This connection is considered as reserved by the pool so that the refresh functionality will not test and refresh those connections. Here is a typical error message:

JMSServer "myJMSServer", store 
  failure while writing message for 
  queue myQueue, java.io.IOException

This kind of situation can be solved either by sending at least one dummy JMS message during the idle period so that the firewall will not close the connection, disabling the connection closure by the firewall, or defining a separate JDBC pool that will be used as a JDBC store for JMS servers and use weblogic.Admin RESET_POOL to reopen the connections at least one time during the idle period.