10 Replies Latest reply on Dec 6, 2019 2:39 PM by jmarc

ORDS startup and database startup best practice

jmarc Nov 4, 2019 9:31 AM

hello,

i work with ords 19.2 standalone .

if i start ords , the database needs to be started before.

if i stop the database for 1 hour, do i need to shutdown ords also ?

do the ORDS and database need to have the same state ?

regards

1. Re: ORDS startup and database startup best practice

thatJeffSmith-Oracle Nov 4, 2019 6:06 PM (in response to jmarc)

the db doesn't have to be up when you start ORDS

if it's not, that pool won't create, and we'll sleep for a few moments, and then when someone tries to call a service from that pool, we'll try to re-establish the connections again

if that fails, we sleep the pool again, until that period expires, and then on the next request, it repeats...until the db is actually up and the connection pools can be established

most people never shutdown their databases except for major maintenance work like upgrades or patches
Like Show 0 Likes(0)

Actions
2. Re: ORDS startup and database startup best practice

jmarc Nov 5, 2019 9:29 AM (in response to thatJeffSmith-Oracle)

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)
Like Show 0 Likes(0)

Actions
3. Re: ORDS startup and database startup best practice

jmarc Nov 5, 2019 9:52 AM (in response to jmarc)

it seems that i have to use

<entry key="db.invalidPoolTimeout">1m</entry>
Like Show 0 Likes(0)

Actions
4. Re: ORDS startup and database startup best practice

jmarc Nov 29, 2019 2:43 PM (in response to jmarc)

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
Like Show 0 Likes(0)

Actions
5. Re: ORDS startup and database startup best practice

jmarc Dec 4, 2019 10:54 AM (in response to jmarc)

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
Like Show 0 Likes(0)

Actions
6. Re: ORDS startup and database startup best practice

MatthiasHoys Dec 6, 2019 12:24 PM (in response to jmarc)

Hi,
It looks like I'm having a similar issue with APEX 5.1.4 and ORDS 19.2 but I have not found the root cause yet.
See ORDS 19.2 error: The database user for the connection pool named |apex|rt|, is not authorized to proxy to the schema named APEX_PUBLIC_USER

Let me know if you find the solution.

Thanks,
Matthias
Like Show 0 Likes(0)

Actions
7. Re: ORDS startup and database startup best practice

MatthiasHoys Dec 6, 2019 12:29 PM (in response to jmarc)

I tried the Legend Plug-in in APEX 5.1.4 and it works fine with ORDS 19.2:
Like Show 0 Likes(0)

Actions
8. Re: ORDS startup and database startup best practice

jmarc Dec 6, 2019 2:21 PM (in response to MatthiasHoys)

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
Like Show 0 Likes(0)

Actions
9. Re: ORDS startup and database startup best practice

MatthiasHoys Dec 6, 2019 2:30 PM (in response to jmarc)

Hmm, there was indeed maintenance on one of the databases and the application server was not stopped before the maintenance. But I believe this should be no issue as the connections are reestablished when the database comes back online.
I now restarted Tomcat to be sure and will monitor the logs to see if the error returns.

Thanks
Matthias
Like Show 0 Likes(0)

Actions
10. Re: ORDS startup and database startup best practice

jmarc Dec 6, 2019 2:39 PM (in response to MatthiasHoys)

the issue is partial , APEX seems to work but not for the plugin files stored in database.
regards
jm
Like Show 0 Likes(0)

Actions

Go to original post

Join the world’s largest interactive community dedicated to Oracle technologies.