Environment
- Oracle server v19c
- Using jdbc driver and ucp from `com.oracle.database.jdbc:ojdbc10-production:19.29.0.0`
- Tomcat10/Java17/SpringBoot3.5 application
- JNDI Datasource defined in tomcat10 context.xml
<Resource name="jdbc/bo"
auth="Container"
type="oracle.ucp.jdbc.PoolDataSource"
factory="oracle.ucp.jdbc.PoolDataSourceImpl"
connectionFactoryClassName="oracle.jdbc.pool.OracleDataSource"
url="jdbc:oracle:thin:@NNN:1521:mapps"
user="nnn"
password="nnn"
inactiveConnectionTimeout="20"
maxConnectionReuseTime="1800"
abandonedConnectionTimeout="60"
minPoolSize="2"
maxPoolSize="50"
connectionPoolName="UCPPool0"
validateConnectionOnBorrow="true"
sqlForValidateConnection="select 1 from DUAL"
connectionProperties="defaultNChar=true"
/>
- UCP logging enabled with `oracle.ucp.level = FINEST`
- There is a 3600 seconds timeout on firewall level between app server and db server.
Problem
When the tomcat server has been idle for some time (more than 60 mins?), it seems that both connections in the pool has become stale.
I see 2 x, what i believe to be validation query timeouts in the UCP log:
15-Nov-2025 08:19:43.462 FINEST [ajp-nio-10.1.240.221-8009-exec-6] oracle.ucp.logging.ClioSupport._log :::throwing: null : [
oracle.ucp.util.UCPTaskManagerImpl$5.get(UCPTaskManagerImpl.java:263),
oracle.ucp.common.UniversalPooledConnectionImpl.isValidHelper(UniversalPooledConnectionImpl.java:361),
oracle.ucp.common.UniversalPooledConnectionImpl.isValid(UniversalPooledConnectionImpl.java:323),
oracle.ucp.common.FailoverableUniversalPooledConnectionBase.isValid(FailoverableUniversalPooledConnectionBase.java:17),
oracle.ucp.jdbc.oracle.OracleUniversalPooledConnection.isValid(OracleUniversalPooledConnection.java:242),
oracle.ucp.common.UniversalConnectionPoolImpl.borrowConnectionAndValidateHelper(UniversalConnectionPoolImpl.java:202),
oracle.ucp.common.UniversalConnectionPoolImpl.borrowConnectionAndValidate(UniversalConnectionPoolImpl.java:154),
oracle.ucp.common.UniversalConnectionPoolImpl.borrowConnection(UniversalConnectionPoolImpl.java:127),
oracle.ucp.jdbc.JDBCConnectionPool.borrowConnection(JDBCConnectionPool.java:174),
oracle.ucp.jdbc.oracle.OracleJDBCConnectionPool.borrowConnection(OracleJDBCConnectionPool.java:624),
oracle.ucp.jdbc.oracle.OracleConnectionConnectionPool.borrowConnection(OracleConnectionConnectionPool.java:106),
oracle.ucp.jdbc.PoolDataSourceImpl.getConnection(PoolDataSourceImpl.java:2037),
oracle.ucp.jdbc.PoolDataSourceImpl.access$500(PoolDataSourceImpl.java:206),
oracle.ucp.jdbc.PoolDataSourceImpl$31.build(PoolDataSourceImpl.java:4368),
oracle.ucp.jdbc.PoolDataSourceImpl.getConnection(PoolDataSourceImpl.java:1969),
oracle.ucp.jdbc.PoolDataSourceImpl.getConnection(PoolDataSourceImpl.java:1932),
oracle.ucp.jdbc.PoolDataSourceImpl.getConnection(PoolDataSourceImpl.java:1917),
org.hibernate.engine.jdbc.connections.internal.DatasourceConnectionProviderImpl.getConnection(DatasourceConnectionProviderImpl.java:126),
org.hibernate.internal.NonContextualJdbcConnectionAccess.obtainConnection(NonContextualJdbcConnectionAccess.java:46),
org.hibernate.resource.jdbc.internal.LogicalConnectionManagedImpl.acquireConnectionIfNeeded(LogicalConnectionManagedImpl.java:126),
org.hibernate.resource.jdbc.internal.LogicalConnectionManagedImpl.getPhysicalConnection(LogicalConnectionManagedImpl.java:156),
org.springframework.orm.jpa.vendor.HibernateJpaDialect.beginTransaction(HibernateJpaDialect.java:164),
org.springframework.orm.jpa.JpaTransactionManager.doBegin(JpaTransactionManager.java:421),
...
]
According to ucp guide on stale connections https://docs.oracle.com/en/database/oracle/oracle-database/19/jjucp/stale-ucp-connections.html , `maxConnectionReuseTime` is exactly relevant in this situation:
This feature is typically used when a firewall exists between the pool tier and the database tier and is setup to block connections based on time restrictions. The blocked connections remain in the pool even though they are unusable. In such scenarios, the connection reuse time is set to a smaller value than the firewall timeout policy.
As I understand it, this should close and remove connections from the pool that have existed for more than the specified time (1800s here).
But I experience no such behaviour and don't see any log entries indicating that connections are closed or removed from the pool due to maxConnectionReuseTime.
Is there something I have misunderstood or misconfigured?
I'm starting to suspect that maxConnectionReuseTime is not affecting the 2 connections fulfilling the minPoolSize setting?
I realize that I could probably just set the minPoolSize to 0, but I would like to understand the intended behaviour of maxConnectionReuseTime better first. Or if any other settings could help with the situation. (and i am a little stubborn)