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

1
votes

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
3
oracleoracle-apex
If this was a .NET question, I would suggest that you make sure all the database connection objects are disposed of as soon as they have been used. It looks like that is a Java thing too: How to Close JDBC Resources Properly – Every Time. - Andrew Morton
@AndrewMorton it's not related to .Net. It's related to Apex - Thomas Carlton
I saw "oracle.dbtools.common.jdbc" in the error message and thought that could be relevant - does Apex disallow your access to the internal goings on? - Andrew Morton
@AndrewMorton yes. it's a kind of black box. Thanks though - Thomas Carlton

3 Answers

2
votes

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

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

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.

0
votes

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.