How to solve : All connections in the Universal Connection Pool are in use?

Question

I have a database server running Oracle 18.2 XE with and Apex 19.2 application served via ORDS. ORDS is behind IIS 10.

The application works properly but from time to time, it's down and I get the following error message... and I have to restart the server to make it work.

Has anyone an idea on how to solve that please ? Thanks.

    The connection pool named: |apex|| is not correctly configured, due to the following error(s):
    Exception occurred while getting connection: oracle.ucp.UniversalConnectionPoolException: 
    All connections in the Universal Connection Pool are in use

    [TE] GET /ords/f?p=myapp:homepage start: 2020-05-07T12:28:36.661Z duration: 3016ms

    ServiceUnavailableException [statusCode=503, reasons=[The connection pool named: |apex|| is not correctly configured, due to the following error(s): Exception occurred while getting connection: oracle.ucp.UniversalConnectionPoolException: All connections in the Universal Connection Pool are in use ]]
        at oracle.dbtools.http.errors.ServletResponseExceptionMapper.mapError(ServletResponseExceptionMapper.java:79)
        at oracle.dbtools.http.errors.ErrorPageRenderer.<init>(ErrorPageRenderer.java:43)
        at oracle.dbtools.http.errors.ErrorPageRenderer.<init>(ErrorPageRenderer.java:34)
        at oracle.dbtools.http.errors.ErrorPageFilter.doFilter(ErrorPageFilter.java:113)
        at oracle.dbtools.http.filters.HttpFilter.doFilter(HttpFilter.java:47)
        at oracle.dbtools.http.filters.FilterChainImpl.doFilter(FilterChainImpl.java:64)
        at oracle.dbtools.http.secure.ForceHttpsFilter.doFilter(ForceHttpsFilter.java:74)
        at oracle.dbtools.http.filters.HttpFilter.doFilter(HttpFilter.java:47)
        at oracle.dbtools.http.filters.FilterChainImpl.doFilter(FilterChainImpl.java:64)
        at oracle.dbtools.http.auth.ForceAuthFilter.doFilter(ForceAuthFilter.java:44)
        at oracle.dbtools.http.filters.HttpFilter.doFilter(HttpFilter.java:47)
        at oracle.dbtools.http.filters.FilterChainImpl.doFilter(FilterChainImpl.java:64)
        at oracle.dbtools.http.filters.Filters.filter(Filters.java:67)
        at oracle.dbtools.http.entrypoint.EntryPoint.service(EntryPoint.java:82)
        at oracle.dbtools.http.entrypoint.EntryPointServlet.service(EntryPointServlet.java:102)
        at oracle.dbtools.entrypoint.WebApplicationRequestEntryPoint.service(WebApplicationRequestEntryPoint.java:50)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:790)
        at org.eclipse.jetty.servlet.ServletHolder.handle(ServletHolder.java:873)
        at org.eclipse.jetty.servlet.ServletHandler.doHandle(ServletHandler.java:542)
        at org.eclipse.jetty.server.handler.ScopedHandler.nextHandle(ScopedHandler.java:255)
        at org.eclipse.jetty.server.session.SessionHandler.doHandle(SessionHandler.java:1700)
        at org.eclipse.jetty.server.handler.ScopedHandler.nextHandle(ScopedHandler.java:255)
        at org.eclipse.jetty.server.handler.ContextHandler.doHandle(ContextHandler.java:1345)
        at org.eclipse.jetty.server.handler.ScopedHandler.nextScope(ScopedHandler.java:203)
        at org.eclipse.jetty.servlet.ServletHandler.doScope(ServletHandler.java:480)
        at org.eclipse.jetty.server.session.SessionHandler.doScope(SessionHandler.java:1667)
        at org.eclipse.jetty.server.handler.ScopedHandler.nextScope(ScopedHandler.java:201)
        at org.eclipse.jetty.server.handler.ContextHandler.doScope(ContextHandler.java:1247)
        at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:144)
        at org.eclipse.jetty.server.handler.ContextHandlerCollection.handle(ContextHandlerCollection.java:220)
        at org.eclipse.jetty.server.handler.HandlerCollection.handle(HandlerCollection.java:152)
        at org.eclipse.jetty.server.handler.StatisticsHandler.handle(StatisticsHandler.java:174)
        at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:132)
        at org.eclipse.jetty.server.Server.handle(Server.java:505)
        at org.eclipse.jetty.server.HttpChannel.handle(HttpChannel.java:370)
        at org.eclipse.jetty.server.HttpConnection.onFillable(HttpConnection.java:267)
        at org.eclipse.jetty.io.AbstractConnection$ReadCallback.succeeded(AbstractConnection.java:305)
        at org.eclipse.jetty.io.FillInterest.fillable(FillInterest.java:103)
        at org.eclipse.jetty.io.ssl.SslConnection$DecryptedEndPoint.onFillable(SslConnection.java:427)
        at org.eclipse.jetty.io.ssl.SslConnection.onFillable(SslConnection.java:321)
        at org.eclipse.jetty.io.ssl.SslConnection$2.succeeded(SslConnection.java:159)
        at org.eclipse.jetty.io.FillInterest.fillable(FillInterest.java:103)
        at org.eclipse.jetty.io.ChannelEndPoint$2.run(ChannelEndPoint.java:117)
        at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.runTask(EatWhatYouKill.java:333)
        at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.doProduce(EatWhatYouKill.java:310)
        at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.tryProduce(EatWhatYouKill.java:168)
        at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.run(EatWhatYouKill.java:126)
        at org.eclipse.jetty.util.thread.ReservedThreadExecutor$ReservedThread.run(ReservedThreadExecutor.java:366)
        at org.eclipse.jetty.util.thread.QueuedThreadPool.runJob(QueuedThreadPool.java:698)
        at org.eclipse.jetty.util.thread.QueuedThreadPool$Runner.run(QueuedThreadPool.java:804)
        at java.lang.Thread.run(Unknown Source)
    Caused by: oracle.dbtools.url.mapping.TargetNotAvailableException: The connection pool named: |apex|| is not correctly configured, due to the following error(s): Exception occurred while getting connection: oracle.ucp.UniversalConnectionPoolException: All connections in the Universal Connection Pool are in use 
        at oracle.dbtools.url.mapping.db.PoolInjector.inject(PoolInjector.java:63)
        at oracle.dbtools.url.mapping.db.PoolInjector.inject(PoolInjector.java:48)
        at oracle.dbtools.url.mapping.db.DatabaseURLMappingBase.injectPLSQLGatewayConnection(DatabaseURLMappingBase.java:933)
        at oracle.dbtools.url.mapping.db.DatabaseURLMappingBase.addServices(DatabaseURLMappingBase.java:317)
        at oracle.dbtools.url.mapping.URLMappingBase.doFilter(URLMappingBase.java:79)
        at oracle.dbtools.url.mapping.filter.URLMappingFilter.doFilter(URLMappingFilter.java:130)
        at oracle.dbtools.http.filters.HttpFilter.doFilter(HttpFilter.java:47)
        at oracle.dbtools.http.filters.FilterChainImpl.doFilter(FilterChainImpl.java:64)
        at oracle.dbtools.http.auth.external.ExternalSessionFilter.doFilter(ExternalSessionFilter.java:59)
        at oracle.dbtools.http.filters.HttpFilter.doFilter(HttpFilter.java:47)
        at oracle.dbtools.http.filters.FilterChainImpl.doFilter(FilterChainImpl.java:64)
        at oracle.dbtools.rt.authentication.apex.ApexSessionQueryRewriteFilter.doFilter(ApexSessionQueryRewriteFilter.java:58)
        at oracle.dbtools.http.filters.HttpFilter.doFilter(HttpFilter.java:47)
        at oracle.dbtools.http.filters.FilterChainImpl.doFilter(FilterChainImpl.java:64)
        at oracle.dbtools.http.cors.CORSResponseFilter.doFilter(CORSResponseFilter.java:88)
        at oracle.dbtools.http.filters.HttpResponseFilter.doFilter(HttpResponseFilter.java:45)
        at oracle.dbtools.http.filters.FilterChainImpl.doFilter(FilterChainImpl.java:64)
        at oracle.dbtools.http.filters.AbsoluteLocationFilter.doFilter(AbsoluteLocationFilter.java:65)
        at oracle.dbtools.http.filters.HttpResponseFilter.doFilter(HttpResponseFilter.java:45)
        at oracle.dbtools.http.filters.FilterChainImpl.doFilter(FilterChainImpl.java:64)
        at oracle.dbtools.http.errors.ErrorPageFilter.doFilter(ErrorPageFilter.java:85)
        ... 47 more
    Caused by: oracle.dbtools.common.jdbc.ConnectionPoolConfigurationException: The connection pool named: |apex|| is not correctly configured, due to the following error(s): Exception occurred while getting connection: oracle.ucp.UniversalConnectionPoolException: All connections in the Universal Connection Pool are in use 
        at oracle.dbtools.common.jdbc.ConnectionPoolExceptions.from(ConnectionPoolExceptions.java:46)
        at oracle.dbtools.common.jdbc.DataSourceConnection.getPooledConnection(DataSourceConnection.java:226)
        at oracle.dbtools.common.jdbc.DataSourceConnection.getPooledConnection(DataSourceConnection.java:192)
        at oracle.dbtools.common.jdbc.DataSourceConnection.getDefaultConnection(DataSourceConnection.java:145)
        at oracle.dbtools.common.jdbc.DataSourceConnection.getConnection(DataSourceConnection.java:59)
        at oracle.dbtools.common.pools.DataSourceTargetImpl.getConnection(DataSourceTargetImpl.java:60)
        at oracle.dbtools.common.config.db.SchemaConnectionFactoryBase.inject(SchemaConnectionFactoryBase.java:84)
        at oracle.dbtools.url.mapping.db.PoolInjector.inject(PoolInjector.java:57)
        ... 67 more
    Caused by: java.sql.SQLException: Exception occurred while getting connection: oracle.ucp.UniversalConnectionPoolException: All connections in the Universal Connection Pool are in use
        at oracle.ucp.util.UCPErrorHandler.newSQLException(UCPErrorHandler.java:456)
        at oracle.ucp.util.UCPErrorHandler.throwSQLException(UCPErrorHandler.java:133)
        at oracle.ucp.jdbc.PoolDataSourceImpl.getConnection(PoolDataSourceImpl.java:2004)
        at oracle.ucp.jdbc.PoolDataSourceImpl.access$400(PoolDataSourceImpl.java:201)
        at oracle.ucp.jdbc.PoolDataSourceImpl$31.build(PoolDataSourceImpl.java:4279)
        at oracle.ucp.jdbc.PoolDataSourceImpl.getConnection(PoolDataSourceImpl.java:1917)
        at oracle.dbtools.common.config.db.PoolDataSourceThunk.getConnection(PoolDataSourceThunk.java:116)
        at oracle.dbtools.common.config.db.RefreshablePoolDataSource.getConnection(RefreshablePoolDataSource.java:107)
        at oracle.dbtools.common.config.db.PoolDataSourceThunk.getConnection(PoolDataSourceThunk.java:116)
        at oracle.dbtools.common.config.db.PoolDataSourceThunk.getConnection(PoolDataSourceThunk.java:100)
        at oracle.dbtools.common.jdbc.DataSourceConnection.getPooledConnection(DataSourceConnection.java:216)
        ... 73 more
    Caused by: oracle.ucp.UniversalConnectionPoolException: All connections in the Universal Connection Pool are in use
        at oracle.ucp.util.UCPErrorHandler.newUniversalConnectionPoolException(UCPErrorHandler.java:336)
        at oracle.ucp.util.UCPErrorHandler.throwUniversalConnectionPoolException(UCPErrorHandler.java:59)
        at oracle.ucp.util.UCPErrorHandler.throwUniversalConnectionPoolException(UCPErrorHandler.java:106)
        at oracle.ucp.common.UniversalConnectionPoolImpl.borrowConnectionWithoutCountingRequests(UniversalConnectionPoolImpl.java:289)
        at oracle.ucp.common.UniversalConnectionPoolImpl.borrowConnectionAndValidate(UniversalConnectionPoolImpl.java:153)
        at oracle.ucp.common.UniversalConnectionPoolImpl.borrowConnection(UniversalConnectionPoolImpl.java:122)
        at oracle.ucp.jdbc.JDBCConnectionPool.borrowConnection(JDBCConnectionPool.java:174)
        at oracle.ucp.jdbc.oracle.OracleJDBCConnectionPool.borrowConnection(OracleJDBCConnectionPool.java:613)
        at oracle.ucp.jdbc.oracle.OracleConnectionConnectionPool.borrowConnection(OracleConnectionConnectionPool.java:103)
        at oracle.ucp.jdbc.PoolDataSourceImpl.getConnection(PoolDataSourceImpl.java:1981)
        ... 81 more

Answer 1

If seeing UCP-45064 with Oracle 23 JDBC drivers (e.g. com.oracle.database.jdbc:ojdbc11-production:23.5.0.24.07), you may need to set the following system property ...

java -Doracle.ucp.createConnectionInBorrowThread=true

or in code ...

System.setProperty("oracle.ucp.createConnectionInBorrowThread", "true");

The error is likely related to https://docs.oracle.com/en/database/oracle/oracle-database/23/jjuar/oracle/ucp/jdbc/PoolDataSource.html#SYSTEM_PROPERTY_CREATE_CONNECTION_IN_BORROW_THREAD

Since 23.4, for backward compatibility: 2 ways to grow a pool. If set to true, pool grows with a borrow thread. If set to false, pool grows with a background executor. This property is "false" by default.

import oracle.ucp.jdbc.PoolDataSource;
import oracle.ucp.jdbc.PoolDataSourceFactory;

System.setProperty("oracle.ucp.createConnectionInBorrowThread", "true"); // workaround for UCP-45064

PoolDataSource pds = PoolDataSourceFactory.getPoolDataSource();
pds.setConnectionFactoryClassName("oracle.jdbc.pool.OracleDataSource");
pds.setURL(jdbcUrl);
pds.setUser(dbUser);
pds.setPassword(dbPassword);
pds.setInitialPoolSize(initialPoolSize);
pds.setMaxPoolSize(maxPoolSize);

Answer 2

check out the open documentation:

https://docs.oracle.com/cd/E56351_01/doc.30/e87809/about-REST-configuration-files.htm#AELIG7162

You might want to add or modify the following entries in the defaults.xml file:

<entry key="jdbc.InitialLimit">3</entry>
<entry key="jdbc.MinLimit">1</entry>
<entry key="jdbc.MaxLimit">10</entry>

you can check out the rest of the parameters for a better experience.

Answer 3

Try to find a file named apex.xml and to increase:

<entry key="jdbc.MaxLimit">...</entry>

Answer 4

My Oracle support Doc ID 1592840.1 (All Connections In The Universal Connection Pool Are In Use) describes it. If you have access to MOS, have a look. I can't (i.e. I'm not allowed to) post that document's contents here, but - shortly, if it helps, download & install the latest ORDS version from the OTN((here).

There's another "solution" but it requires you to set this and modify that, which is something I can't copy/paste over here, and is too much to type to rephrase it.