Getting Stale Connection using OracleDataSource with OCI driver

I am getting stale connection error when there is no requests to the database from my java application for couple of hours.

Its a simple java application runned on Linux box with OCI (type driver). Dont ask me why OCI, why not thin. I am using OracleDataSource and OracleConnectionCacheManager for maintaining the cache of connection objects. Here is the code snippet:

import java.sql.Connection; 
import java.sql.SQLException; 
import java.util.Properties; 

import oracle.jdbc.pool.OracleConnectionCacheManager; 
import oracle.jdbc.pool.OracleDataSource; 

import org.apache.log4j.Logger; 

import com.exception.DataException; 

public class ConnectionManager { 
private static OracleDataSource poolDataSource = null; 
private final static String CACHE_NAME = "CONNECTION_POOL_CACHE"; 
private static OracleConnectionCacheManager occm = null; 

public static void init(String url,String userId,String password) throws PCTDataException{ 
Properties cacheProps = null; 
try { 
poolDataSource = new OracleDataSource(); 
poolDataSource.setURL(url); 
poolDataSource.setUser(userId); 
poolDataSource.setPassword(password); 

cacheProps = new Properties(); 
cacheProps.setProperty("MinLimit", "1"); 
cacheProps.setProperty("Ma开发者_如何学JAVAxLimit", "5"); 
cacheProps.setProperty("InitialLimit", "1"); 
cacheProps.setProperty("ValidateConnection", "true"); 

poolDataSource.setConnectionCachingEnabled(true); 
occm = OracleConnectionCacheManager.getConnectionCacheManagerInstance(); 
occm.createCache(CACHE_NAME, poolDataSource, cacheProps); 
occm.enableCache(CACHE_NAME); 
} catch (SQLException se) { 
throw new DataException("SQL Exception while initializing connection pool"); 
}catch(Exception e){ 
throw new DataException("Exception while initializing connection pool"); 
} 
} 

public static Connection getConnection() throws PCTDataException { 
try{ 
if (poolDataSource == null) { 
throw new SQLException("OracleDataSource is null."); 
} 
occm.refreshCache(CACHE_NAME, OracleConnectionCacheManager.REFRESH_INVALID_CONNECTIONS); 
Connection connection = poolDataSource.getConnection(); 
return connection; 
}catch(SQLException se){ 
se.printStackTrace(); 
throw new DataException("Exception while getting Connection object"); 
}catch(Exception e){ 
e.printStackTrace(); 
throw new DataException("Exception while getting Connection object"); 
} 
} 

public static void closePooledConnections() { 
try{ 
if (poolDataSource != null) { 
poolDataSource.close(); 
} 
}catch(SQLException se){ 
}catch(Exception e){ 
} 

} 
} 

The error is as follows:

ConnectionManager.java:getConnection:87 - Exception while getting Connection object: 
java.sql.SQLException: Invalid or Stale Connection found in the Connection Cache 
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112) 
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:146) 
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:208) 
at oracle.jdbc.pool.OracleImplicitConnectionCache.getConnection(OracleImplicitConnectionCache.java:390) 
at oracle.jdbc.pool.OracleDataSource.getConnection(OracleDataSource.java:404) 
at oracle.jdbc.pool.OracleDataSource.getConnection(OracleDataSource.java:189) 

What am I missing?

Maybe you need to set keep alives on? what this does is periodically, when not in use it send a ping to the database server basically saying I am still here and don't close me out. This is not that fun to try to debug though. The problem could be a setting on your database server where there is a max connection age, or a time to kill idle connections. There also could be some settings in your pool that you could use to that would check for this and then just tell it to get a new one when this happens. I wish I could be more help but I have not worked with oracle.

Instead of using OracleDataSource + OracleConnectionCacheManager, I would recommend using the OracleOCIConnectionPool, which was specifically designed for caching OCI connections.

It is a drop in replacement for OracleDataSource, except the PoolConfig properties for OracleDataSource and the OracleOCIConnectionPool are a bit different.

You will get the "Invalid or Stale Connection" error when you have a connection in the connection pool which is no longer connected to the Database actively. Below are few scenarios which can lead to this

1. Connection is manually aborted from the database by a dba. For example, if the connection was killed using "ALTER SYSTEM KILL SESSION"
2. When a connection exists in the connection pool without being used for a long time and is disconnected due to the timeouts enforced by the database (idle_time)
3. A database restart
4. A network event has caused the connection to drop, probably because the network has become unavailable or a firewall has dropped a connection which has been open for too long.

Run the below query to determine the IDLE_TIME enforced by the Database

select * from dba_profiles dp, dba_users du
where dp.profile = du.profile and du.username ='YOUR_JDBC_USER_NAME';

Now try with the below configuration

Properties cacheProps = new Properties(); 
cacheProps.setProperty("MinLimit", "0"); 
cacheProps.setProperty("MaxLimit", "5"); 
cacheProps.setProperty("InitialLimit", "1"); 
cacheProps.setProperty("ValidateConnection", "true");
cacheProps.setProperty("InactivityTimeout", "17000"); //something lower than the DB IDLE_TIME
cacheProps.setProperty("PropertyCheckInterval", "16000") /*something lower than the inactivity timeout
 - to make sure that connections which were inactive for more than InactivityTimeout
 are always removed from the pool*/