1 2 Previous Next 17 Replies Latest reply on Dec 12, 2019 3:16 PM by MatthiasHoys

    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

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

                              Ok, I don't seem to have the plugin issue on APEX 5.1.4 with ORDS 19.2. After I restarted Tomcat, I also don't see the error anymore in the logs, so it could indeed be related to a restart of the database without restarting ORDS...

                               

                              Did you try to run the ORDS validation? This will restore the proxy grants if they should be missing or incomplete.

                               

                              $ java -jar ords.war validate

                               

                              HTH

                              Matthias

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

                                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

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

                                  that 15 minutes is configurable

                                   

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

                                   

                                   

                                  The values you can pass are along this pattern...

                                     

                                  • 1m
                                  • 15s
                                  • 2hr

                                     

                                  So when I start up ORDS with the above, I'm telling it to sleep on a pool for 3 minutes before trying to create it again.

                                   

                                  Don't go crazy with this setting. It's not 'cheap' to make a connection attempt and re-establish pools. So if your DB is going to be down for awhile, you don't want ORDS spinning it's wheels trying to get back in.

                                   

                                  Your users will feel better with redundancy built in, failover/rac/data guard with multiple ORDS front ended with a load balancer. 

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

                                    And what about the issue with that jetLegend.js plugin? Is this solved now or is the connectivity to the plugin restored after 1 minute waiting?

                                     

                                    Thanks

                                    Matthias

                                    1 2 Previous Next