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

    ORDS startup and database startup best practice

    jmarc

      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

      jm

        • 1. Re: ORDS startup and database startup best practice
          thatJeffSmith-Oracle

          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

          • 2. Re: ORDS startup and database startup best practice
            jmarc

            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)

            • 3. Re: ORDS startup and database startup best practice
              jmarc

              it seems that i have to use

               

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

              • 4. Re: ORDS startup and database startup best practice
                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

                • 5. Re: ORDS startup and database startup best practice
                  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

                  • 6. Re: ORDS startup and database startup best practice
                    MatthiasHoys

                    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

                    • 7. Re: ORDS startup and database startup best practice
                      MatthiasHoys

                      I tried the Legend Plug-in in APEX 5.1.4 and it works fine with ORDS 19.2:

                       

                      • 8. Re: ORDS startup and database startup best practice
                        jmarc

                        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

                        • 9. Re: ORDS startup and database startup best practice
                          MatthiasHoys

                          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

                          • 10. Re: ORDS startup and database startup best practice
                            jmarc

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

                            regards

                            jm