Investigating Weblogic JDBC Issues : Datasource/Pooling

ResourceException during JDBCDataSource.getConnection() (weblogic.common.ResourceException: No resources available)

ResourceExceptionsare thrown when a getConnection()request via a DataSourceto a JDBC connection pool cannot be satisfied because either no connection in the pool or no thread is available to handle the connection request. Cause of the missing resource could be either one of:

• Connection leak in the application.
  Connections used from the JDBC pool need to be closed after usage by the application code. If close() is not called, connections are not freed and not available for reuse. A possible symptom for a connection leak for Oracle JDBC connection pools can be an error message:
  
  ORA-00020 - maximum number of processes (%s) exceeded
  Enabling WLS JDBC trace logging is a good way to identify potential leaks. Messages such as the following get logged when the WebLogic pool has been watching its connections in use, and this connection is currently reserved, but has not been used at all for the inactive-connection-timeout period.
  
  <Warning><Common><BEA-000620><Forcibly releasing inactive resource "weblogic.jdbc.common.internal.ConnectionEnv" back in the pool "mypool"
  This typically means the application leaked the connection by losing the reference to the connection without/before closing it. The WebLogic logging will include a full stacktrace of where the connection was reserved. This information should help in reviewing how this connection was managed (lost, missed or unused) without closing.
• Too few threads or too few connections (MaxCapacity) configured to allow enough concurrently active database connections as needed by the application.
  
  A general rule of thumb here is to configure the maximum number of database connections in the JDBC pool (MaxCapacity) equal to the number of execute threads. This value then limits the number of database connections that can be active (enlisted in a transaction) in parallel.
• Connection peaks occur (a lot of connections are created) although during the same time the application gets ResourceExceptions which implies that no connections are available in the pool.

Additional Information

• If InitialCapacityis smaller than MaxCapacityand fewer than MaxCapacityconnections are currently open, every connection request that comes in will open CapacityIncrementnew connections, until the maximum number of allowed connections in the pool is reached. (This can lead to a connection peak effect as there may be more connections opened than actually are simultaneously used.)
• If the maximum number of connections are open already, ResourceExceptionswill be thrown.
• Set the property TestConnectionOnReserveto true. This ensures that every connection that is requested from the pool will be tested before it is forwarded to the application code. If the test fails, it will be automatically reopened.

Bad performance of the RDBMS or the network, so that connection requests to the underlying database lead to very long startup times for the WebLogic Server

During WebLogic Server startup, the property InitialCapacityin the JDBCConnectionPooldefines the number of connections that are created immediately. If the creation and initialization of the underlying physical connections to the database takes a long time, the startup of the WebLogic Server instance will also take a long time.
If connection requests to your database take generally a long time and you would not like WebLogic Server to take this long time during startup, the alternative would be to set InitialCapacityto 0. This will create a pool with no physical connections during startup. If you would like to have all connections created during the first connection request, set CapacityIncrementto the full number of connections available in the pool. The first request will take a very long time, but after that, the connection pool will be fully operable.
Errors or Exceptions during database connectivity creation due to JDBC driver configuration problems
As mentioned above, WebLogic Server will try to create InitialCapacityphysical connections during startup. If the JDBC pool is not configured correctly or the database is not available during WebLogic Server startup, the initialization of the physical connections will not succeed. The workaround here is to configure a JDBC pool that does not open any connections during start (InitialCapacity="0"). The configuration can be changed while WebLogic Server is still running. After the pool is configured correctly or the database is back up, the connections in the pool can be created and the pool can be used in the application.
Information on how to configure Database Connectivity with different drivers is available at Using JDBC Driver with WebLogic Server
Error messages are typically very explicit about what is incorrect. For example:

• When a message similar to the following appears, verify that the database name is correct:
  
  <Warning><JDBC><BEA-001129><Received exception while creating connection for pool "myPool" E/A-Exception: Connection refused(DESCRIPTION=(TMP=)(VSNNUM=153092352)(ERR=12505)(ERROR_STACK=(ERROR=(CODE=12505)(EMFI=4))))>
  <Error><JDBC><BEA-001150><Connection Pool "myPool" deployment failed with the following error:
  0:Could not create pool connection. The DBMS driver exception was: E/A-Exception: Connection refused(DESCRIPTION=(TMP=)(VSNNUM=153092352)(ERR=12505)(ERROR_STACK=(ERROR=(CODE=12505)(EMFI=4)))).>
  In this case, the TNS entry or environment settings such as PATH or LD_LIBRARY_PATH need to be checked. On Windows, PATH needs to point to the client and OCI libraries. Under Unix environments, LD_LIBRARY_PATH needs to point to the directories where the client installation copied the client and OCI libraries. The relevant WebLogic JDBC error would include the following exception.
  
  java.sql.SQLException: Io exception: The Network Adapter could not establish the connection
• If the tnsnames.orafile or ORACLE_HOMEis not set correctly, the following Oracle error will be thrown:
  
  ORA-12154: TNS could not resolve service name
  Make sure that the ORACLE_HOME environment variable points to the correct directory and the tnsnames.ora file is stored in the correct directory. Please verify via sql-plus if a connection to this database is successful. 
• Incorrect locale settings may lead to error messages similar to:
  
  weblogic.management.DeploymentException: Error creating connection pool myConnectionPool:&
  0:Unable to load locale categories
  In this case the locale environment needs to be checked by starting a database client in the same environment as WebLogic Server and checking if the locale works there.

Connection refresh/reconnect problems after the database was down

If the database was intermittently down, a connection reset or refresh will happen if the property TestConnectionsOnReserveis set to true and the connection test query fails. You will find the related messages in the WebLogic Server log file, similar to: ORA-03113 end-of-file on communication channeland/or ORA-01012 not logged on:

<Jan 31, 2009 2:20:17 PM PST> <Info> <JDBC Pool oraclePool> <null> <This connection will now be refreshed.>
<Jan 31, 2009 2:20:18 PM PST> <Info> <JDBC> <001067> <Connection for pool "oraclePool" refreshed.>
<Jan 31, 2009 2:20:18 PM PST> <Info> <JDBC Pool oraclePool> <null> <A connection from pool oraclePool was tested during reserve with a select count(*) from dual and failed:>
<Jan 31, 2009 2:20:18 PM PST> <Info> <JDBC Pool oraclePool> <null>
<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.execute(Statement.java:534)
  at weblogic.jdbc.common.internal.ConnectionEnv.test(ConnectionEnv.java:961)
  at weblogic.jdbc.common.internal.ResourceAllocator.reserve(ResourceAllocator.java:651)
  at weblogic.jdbc.common.internal.ResourceAllocator.reserveUnused(ResourceAllocator.java:575)
  at weblogic.jdbc.common.internal.ResourceAllocator.trigger(ResourceAllocator.java:1296)
  at weblogic.time.common.internal.ScheduledTrigger$1.run(ScheduledTrigger.java:171)
  at weblogic.security.service.SecurityServiceManager.runAs(SecurityServiceManager.java:807)
  at weblogic.time.common.internal.ScheduledTrigger.executeLocally(ScheduledTrigger.java:168)
  at weblogic.time.common.internal.ScheduledTrigger.execute(ScheduledTrigger.java:158)
  at weblogic.time.server.ScheduledTrigger.execute(ScheduledTrigger.java:38)
  at weblogic.kernel.ExecuteThread.execute(ExecuteThread.java:156)
  at weblogic.kernel.ExecuteThread.run(ExecuteThread.java:137)

This message is informational only and, as long as the connection could be created successfully afterwards, you should not be concerned.