ORDS startup and database startup best practice

1) i start ords first and database is stopped

i have the warning

Nov 05, 2019 9:51:41 AM

WARNING: The pool named: |apex|| is invalid and will be ignored: The connection pool named: |apex|| is not correctly configured, due to the following error(s): Listener refused the connection with the following error:

ORA-12505, TNS:listener does not currently know of SID given in connect descriptor

Nov 05, 2019 9:51:41 AM

WARNING: The pool named: |apex|pu| is invalid and will be ignored: The connection pool named: |apex|pu| is not correctly configured, due to the following error(s): Listener refused the connection with the following error:

ORA-12505, TNS:listener does not currently know of SID given in connect descriptor

Nov 05, 2019 9:51:41 AM

WARNING: The pool named: |apex|al| is invalid and will be ignored: The connection pool named: |apex|al| is not correctly configured, due to the following error(s): Listener refused the connection with the following error:

ORA-12505, TNS:listener does not currently know of SID given in connect descriptor

Nov 05, 2019 9:51:41 AM

WARNING: The pool named: |apex|rt| is invalid and will be ignored: The connection pool named: |apex|rt| is not correctly configured, due to the following error(s): Listener refused the connection with the following error:

ORA-12505, TNS:listener does not currently know of SID given in connect descriptor

2) i start database oracle

3)i try to connect with browser http://xx.xx.xx.xx:8585/apex/f?p=4550

and in the output ords log

2019-11-05 10:26:03.007:INFO:oejsh.ContextHandler:main: Started o.e.j.s.ServletContextHandler@6c49835d{/apex,null,AVAILABLE}

2019-11-05 10:26:03.008:INFO:oejsh.ContextHandler:main: Started o.e.j.s.h.ContextHandler@76f2b07d{/i,null,AVAILABLE}

2019-11-05 10:26:03.041:INFO:oejs.AbstractConnector:main: Started ServerConnector@1a38c59b{HTTP/1.1,[http/1.1, h2c]}{0.0.0.0:8585}

2019-11-05 10:26:03.041:INFO:oejs.Server:main: Started @7129ms

Nov 05, 2019 10:26:58 AM

WARNING: The connection pool named: |apex|| is not correctly configured, due to the following error(s): Listener refused the connection with the following error:

ORA-12505, TNS:listener does not currently know of SID given in connect descriptor

oracle.dbtools.common.jdbc.ConnectionPoolConfigurationException: The connection pool named: |apex|| is not correctly configured, due to the following error(s): Listener refused the connection with the following error:

ORA-12505, TNS:listener does not currently know of SID given in connect descriptor

        at oracle.dbtools.common.jdbc.ConnectionPoolConfigurationException.duplicate(ConnectionPoolConfigurationException.java:50)

it seems that i have to use

<entry key="db.invalidPoolTimeout">1m</entry>

i have a second issue with plugin file.

i have ORDS 19.2 and APEX 19.2

ORDS is started as standalone

with the defaults.xml file

<?xml version="1.0" encoding="UTF-8" standalone="no"?>

<!DOCTYPE properties SYSTEM "http://java.sun.com/dtd/properties.dtd">

<properties>

<comment>Saved on Tue Nov 05 14:09:36 CET 2019</comment>

<entry key="db.hostname">localhost</entry>

<entry key="db.port">1521</entry>

<entry key="db.sid">orc19c</entry>

<entry key="security.requestValidationFunction">wwv_flow_epg_include_modules.authorize</entry>

<entry key="security.validationFunctionType">plsql</entry>

<entry key="jdbc.InitialLimit">10</entry>

<entry key="jdbc.MinLimit">10</entry>

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

<entry key="db.invalidPoolTimeout">1m</entry>

</properties>

i make a shutdown/statup database.

i i run the "sample charts" page 26 (legend plugin).

i have the following issue for the jetLegend.js file plugin

regards

jm

when the error occurs ,i have in the ORDS log

Nov 29, 2019 3:44:41 PM

WARNING: The database user for the connection pool named |apex|rt|, is not authorized to proxy to the schema named APEX_PUBLIC_USER

oracle.dbtools.common.jdbc.ConnectionPoolConfigurationException: The database user for the connection pool named |apex|rt|, is not authorized to proxy to the schema named APEX_PUBLIC_USER

        at oracle.dbtools.common.jdbc.ConnectionPoolExceptions.from(ConnectionPoolExceptions.java:46)

        at oracle.dbtools.common.jdbc.ConnectionPoolExceptions.from(ConnectionPoolExceptions.java:53)

        at oracle.dbtools.common.jdbc.DataSourceConnection.getPooledConnection(DataSourceConnection.java:233)

        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)

        at oracle.dbtools.url.mapping.db.DatabaseURLMappingBase.addServices(DatabaseURLMappingBase.java:307)

        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)

        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.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(Thread.java:748)

Caused by: oracle.dbtools.common.ucp.ConnectionLabelingException: Error occurred when attempting to configure url: unknown with labels: {oracle.dbtools.jdbc.label.schema=APEX_PUBLIC_USER}

        at oracle.dbtools.common.ucp.LabelingCallback.handle(LabelingCallback.java:147)

        at oracle.dbtools.common.ucp.LabelingCallback.proxyToSchema(LabelingCallback.java:210)

        at oracle.dbtools.common.ucp.LabelingCallback.configure(LabelingCallback.java:76)

        at oracle.ucp.common.UniversalConnectionPoolImpl.getAvailableConnectionHelper(UniversalConnectionPoolImpl.java:711)

        at oracle.ucp.common.UniversalConnectionPoolImpl.getAvailableConnection(UniversalConnectionPoolImpl.java:650)

        at oracle.ucp.common.UniversalConnectionPoolImpl.borrowConnectionWithoutCountingRequests(UniversalConnectionPoolImpl.java:261)

        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)

        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)

        ... 67 more

Caused by: java.lang.NullPointerException

        at oracle.net.ns.NIOPacket.writeToSocketChannel(NIOPacket.java:331)

        at oracle.net.ns.NIONSDataChannel.writeDataToSocketChannel(NIONSDataChannel.java:199)

        at oracle.net.ns.NIONSDataChannel.writeDataToSocketChannel(NIONSDataChannel.java:144)

        at oracle.jdbc.driver.T4CMAREngineNIO.flush(T4CMAREngineNIO.java:743)

        at oracle.jdbc.driver.T4CMAREngineNIO.prepareForUnmarshall(T4CMAREngineNIO.java:756)

        at oracle.jdbc.driver.T4CMAREngineNIO.unmarshalUB1(T4CMAREngineNIO.java:429)

        at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:407)

        at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:268)

        at oracle.jdbc.driver.T4C7Ocommoncall.doOLOGOFF(T4C7Ocommoncall.java:64)

        at oracle.jdbc.driver.T4CConnection.closeProxySession(T4CConnection.java:2242)

        at oracle.jdbc.driver.PhysicalConnection.openProxySession(PhysicalConnection.java:2711)

        at oracle.dbtools.common.ucp.LabelingCallback$ProxySchemaTask.call(LabelingCallback.java:283)

        at oracle.dbtools.common.ucp.LabelingCallback$ProxySchemaTask.call(LabelingCallback.java:271)

        at oracle.dbtools.common.concurrent.RetryStrategy.execute(RetryStrategy.java:45)

        at oracle.dbtools.common.ucp.LabelingCallback.proxyToSchema(LabelingCallback.java:206)

        ... 85 more

i have the issue if i make a shutdown/startup database while ORDS is still running.

A workaround is to restart ORDS after startup database

i  opened a Oracle Service Request

regards

jm

the issue is partial , APEX seems to work but not for the plugin files stored in database.

regards

jm

hello,

the following answer from oracle support :

"There’s no need to restart ORDS when the database is restarted.

ORDS will retry but only after 15 minutes has elapsed since the connection was found to be invalid.

When a request comes into ORDS for a database that can not be connected to then ORDS will consider that connection pool invalid.

No matter how many subsequent requests are received, ORDS will not attempt to make a connection until 15 minutes has elapsed."

if i understand correctly, when database is restarted, the end user who observes that the page is not correctly displayed  have to wait 15 minutes to have no issue.

it would be difficult to explain to end users

regards

jm