JDBC Recommendations

JDBC connection pools for production environments

Configuration of JDBC connection pools for production systems is a critical and important task to ensure stability and performance. Some general recommendations may help as a starting point for administrators:

·         set InitialCapacity = MaxCapacity: This will ensure that all connections are opened during WebLogic Server start. As creation of a physical database connection is very expensive, all needed connections should be opened immediately and kept open.

·         disable shrinking by setting ShrinkingEnabled to false: As mentioned above, creation of physical database connections is expensive, so connections should be established once and kept during the complete lifetime of the WebLogic Server instance.

·         turn off refresh functionality if it is not needed

·         set TestConnectionsOnReserve to true. This will ensure that connections are tested before they go to the application and are reopened if needed.

·         set TestConnectionsOnRelease to false: As connection testing is an overhead that should be avoided where possible, connection testing for connections that an application gives back to the pool is an unnecessary effort. As long as connections are tested during a getConnection, this is not needed.

·         set the number of connections in the JDBC pool equal to the number of execute threads that use the connections. This helps to avoid ResourceExceptions. 

  

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 TestConnectionsOnReserve is 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 channel and/or ORA-01012 not logged on:

   <Jan 31, 2002 2:20:17 PM PST> <Info> <JDBC Pool oraclePool> <null> <This connection will now be refreshed.>
<Jan 31, 2002 2:20:18 PM PST> <Info> <JDBC> <001067> <Connection for pool "oraclePool" refreshed.>
<Jan 31, 2002 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, 2002 2:20:18 PM PST> <Info> <JDBC Pool oraclePool> <null>
<java.sql.SQL
Exception: ORA-03113: end-of-file on communication channel
  at weblogic.db.oci.OciCursor.getCDAException(OciCursor.java:240)

 

 

Decide whether  you need Refresh Functionality in JDBC

The refresh functionality is intended to test all currently unused connections in the pool using the test table, and refresh connections if needed (if the test fails). This functionality is turned on by defining a test table and the property RefreshMinutes in the JDBCConnectionPool.

 

Refresh runs asynchronous to any client application code, and will temporarily reserve all currently unused pool connections to test. It will reserve all those connections during the complete test time. If new application requests for a connection come in during this time, the following will happen:

·         if InitialCapacity is smaller than MaxCapacity and less then MaxCapacity connections are currently open, every connection request that comes in will open CapacityIncrement new 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 maximum number of connections are open already, ResourceExceptions will be thrown.

You should carefully consider if the refresh functionality is actually needed for your JDBC pool. Alternative options for testing and refreshing connections if needed are:

·         Set the property TestConnectionsOnReserve to 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.

·         If the database was temporarily unavailable or down, the connection pool can be completely refreshed by using weblogic.Admin RESET_POOL. This ensures that all connections are refreshed, whereas the refresh functionality only refreshes unused connections.

To Summarize :

When Oracle is stopped and restarted (either in a controlled fashion or due to a database
failure), there different ways to make WebLogic detect this and rebuild its JDBC pool :
– Use the TestConnectionsOnReserve parameter in the JDBC definition and provide the Test Table Name

– Use  Connection Creation Retry Frequency  Greater than 0 (>0)  

– Enabled  “Inactive Connection Timeout” to reclaim idle connections back to pool, even if not closed by the application code. Suggested is 30 sec

– Execute the weblogic.Admin command line utility with the Reset_Pool option

– Stop and restart WebLogic 

Database changes need for Sybase To Reconnect.

  As weblogic connecting  to master database on connection retry instead of dbo. Below are the  changes made on database side.

Create a stored procedure

Assigned the stored procedure for global login trigger.

Stored Procedure Task : it checks for each login and its corresponding default database, if the default database is in recovery mode the login will not be successful and exit out of server. If the database is available, then the login will be a success.

1> sp_logintrigger

2> go

Global login trigger    Status

----------------------- -------------

master.dbo.sp__dbstatus Enabled