13 Replies Latest reply on Nov 22, 2018 7:43 PM by thatJeffSmith-Oracle

    ORDS to connect to database using TNSNAMES not working?

    Denis Savenko

      CentOS Linux 7.5

      Oracle Database XE 18c

      ORDS 18.3

      APEX 18.2

       

      My ORDS instance works perfectly fine if I use `basic` type of connection in my `defaults.xml`:

      <?xml version="1.0" encoding="UTF-8" standalone="no"?>
      <!DOCTYPE properties SYSTEM "http://java.sun.com/dtd/properties.dtd">
      <properties>
      <entry key="db.connectionType">basic</entry>
      <entry key="db.hostname">localhost</entry>
      <entry key="db.port">1521</entry>
      <entry key="db.servicename">XEPDB1</entry>
      <entry key="security.requestValidationFunction">wwv_flow_epg_include_modules.authorize</entry>
      <entry key="security.validationFunctionType">plsql</entry>
      </properties>
      

       

      However, if I change the connection type property to `tns` like this:

      <?xml version="1.0" encoding="UTF-8" standalone="no"?>
      <!DOCTYPE properties SYSTEM "http://java.sun.com/dtd/properties.dtd">
      <properties>
      <entry key="db.connectionType">tns</entry>
      <entry key="db.tnsDirectory">/opt/oracle/product/18c/dbhomeXE/network/admin</entry>
      <entry key="db.tnsAliasName">pdb1</entry>
      <entry key="security.requestValidationFunction">wwv_flow_epg_include_modules.authorize</entry>
      <entry key="security.validationFunctionType">plsql</entry>
      </properties>
      

       

      And my tnsnames.ora looks as following:

      # tnsnames.ora Network Configuration File: /opt/oracle/product/18c/dbhomeXE/network/admin/tnsnames.ora
      # Generated by Oracle configuration tools.
      
      
      XE =
        (DESCRIPTION =
          (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
          (CONNECT_DATA =
            (SERVER = DEDICATED)
            (SERVICE_NAME = XE)
          )
        )
      
      
      PDB1 =
        (DESCRIPTION =
          (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
          (CONNECT_DATA =
            (SERVER = DEDICATED)
            (SERVICE_NAME = XEPDB1)
          )
        )
      
      
      LISTENER_XE =
        (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
      

       

      ORDS won't resolve the descriptor and generates this exception:

      IO Error: could not resolve the connect identifier "pdb1"

      In my browser window:

      screenshot.png

       

      ----

       

      Is there anything I am doing wrong or is it a known ORDS issue?

       

      ----

       

      P.S. My tnsnames entry works fine in other places, in example with sqlplus:

       

      # sqlplus sys@pdb1 as sysdba
      
      
      SQL*Plus: Release 18.0.0.0.0 - Production on Tue Nov 20 00:01:06 2018
      Version 18.4.0.0.0
      
      
      Copyright (c) 1982, 2018, Oracle.  All rights reserved.
      
      Enter password:
      
      
      Connected to:
      Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production
      Version 18.4.0.0.0
      
      
      SQL>
      

       

       

        • 3. Re: ORDS to connect to database using TNSNAMES not working?
          Denis Savenko

          Hi, Jeff!

           

          Unfortunately not. Already tried it.

          • 4. Re: ORDS to connect to database using TNSNAMES not working?
            thatJeffSmith-Oracle

            and if instead of localhost you had an IP?

            • 5. Re: ORDS to connect to database using TNSNAMES not working?
              Denis Savenko

              tnsnames.ora:

              # tnsnames.ora Network Configuration File: /opt/oracle/product/18c/dbhomeXE/network/admin/tnsnames.ora
              # Generated by Oracle configuration tools.
              
              
              XE =
                (DESCRIPTION =
                  (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
                  (CONNECT_DATA =
                    (SERVER = DEDICATED)
                    (SERVICE_NAME = XE)
                  )
                )
              
              
              PDB1 =
                (DESCRIPTION =
                  (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))
                  (CONNECT_DATA =
                    (SERVER = DEDICATED)
                    (SERVICE_NAME = XEPDB1)
                  )
                )
              
              
              LISTENER_XE =
                (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
              

               

              sqlplus:

              # sqlplus sys@pdb1 as sysdba
              
              
              SQL*Plus: Release 18.0.0.0.0 - Production on Tue Nov 20 00:07:24 2018
              Version 18.4.0.0.0
              
              
              Copyright (c) 1982, 2018, Oracle.  All rights reserved.
              
              
              Enter password:
              
              
              Connected to:
              Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production
              Version 18.4.0.0.0
              
              
              SQL>
              

               

              ORDS defaults.xml:

              <?xml version="1.0" encoding="UTF-8" standalone="no"?>
              <!DOCTYPE properties SYSTEM "http://java.sun.com/dtd/properties.dtd">
              <properties>
              <entry key="db.connectionType">tns</entry>
              <entry key="db.tnsDirectory">/opt/oracle/product/18c/dbhomeXE/network/admin</entry>
              <entry key="db.tnsAliasName">PDB1</entry>
              <entry key="security.requestValidationFunction">wwv_flow_epg_include_modules.authorize</entry>
              <entry key="security.validationFunctionType">plsql</entry>
              </properties>
              

               

              ORDS output:

              screenshot.png

              • 6. Re: ORDS to connect to database using TNSNAMES not working?
                thatJeffSmith-Oracle

                turn debug to true, and let's see the call stack

                • 7. Re: ORDS to connect to database using TNSNAMES not working?
                  Denis Savenko

                  Here you are:

                  ServiceUnavailableException [statusCode=503, reasons=[The connection pool named: |apex|| is not correctly configured, due to the following error(s): IO Error: could not resolve the connect identifier  "PDB1" ]]
                  at oracle.dbtools.url.mapping.URLMappingBase.doFilter(URLMappingBase.java:109)
                  at oracle.dbtools.url.mapping.filter.URLMappingFilter.doFilter(URLMappingFilter.java:127)
                  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:83)
                  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:731)
                  at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:303)
                  at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
                  at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
                  at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)
                  at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
                  at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:218)
                  at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:110)
                  at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:506)
                  at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:169)
                  at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:103)
                  at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:962)
                  at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:116)
                  at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:445)
                  at org.apache.coyote.ajp.AjpProcessor.process(AjpProcessor.java:190)
                  at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:637)
                  at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:316)
                  at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
                  at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
                  at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
                  at java.lang.Thread.run(Thread.java:748)
                  Caused by: oracle.dbtools.url.mapping.TargetNotAvailableException: The connection pool named: |apex|| is not correctly configured, due to the following error(s): IO Error: could not resolve the connect identifier  "PDB1"
                  at oracle.dbtools.url.mapping.db.PoolInjector.inject(PoolInjector.java:60)
                  at oracle.dbtools.url.mapping.db.PoolInjector.inject(PoolInjector.java:46)
                  at oracle.dbtools.url.mapping.db.DatabaseURLMappingImpl.injectPLSQLGatewayConnection(DatabaseURLMappingImpl.java:772)
                  at oracle.dbtools.url.mapping.db.DatabaseURLMappingImpl.addServices(DatabaseURLMappingImpl.java:320)
                  at oracle.dbtools.url.mapping.URLMappingBase.doFilter(URLMappingBase.java:80)
                  ... 49 more
                  Caused by: oracle.dbtools.common.jdbc.ConnectionPoolConfigurationException: The connection pool named: |apex|| is not correctly configured, due to the following error(s): IO Error: could not resolve the connect identifier  "PDB1"
                  at oracle.dbtools.common.jdbc.ConnectionPoolConfigurationException.duplicate(ConnectionPoolConfigurationException.java:49)
                  at oracle.dbtools.common.config.db.DatabasePools.dataSource(DatabasePools.java:89)
                  at oracle.dbtools.common.pools.DataSourceTargetImpl.pool(DataSourceTargetImpl.java:75)
                  at oracle.dbtools.common.pools.DataSourceTargetImpl.dataSource(DataSourceTargetImpl.java:131)
                  at oracle.dbtools.common.config.db.SchemaConnectionFactoryBase.inject(SchemaConnectionFactoryBase.java:52)
                  at oracle.dbtools.url.mapping.db.PoolInjector.inject(PoolInjector.java:54)
                  ... 53 more
                  Caused by: java.sql.SQLRecoverableException: IO Error: could not resolve the connect identifier  "PDB1"
                  at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:801)
                  at oracle.jdbc.driver.PhysicalConnection.connect(PhysicalConnection.java:782)
                  at oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:39)
                  at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:704)
                  at oracle.jdbc.pool.OracleDataSource.getPhysicalConnection(OracleDataSource.java:390)
                  at oracle.jdbc.pool.OracleDataSource.getConnection(OracleDataSource.java:279)
                  at oracle.jdbc.pool.OracleDataSource.getConnection(OracleDataSource.java:202)
                  at oracle.jdbc.pool.OracleDataSource.getConnection(OracleDataSource.java:180)
                  at oracle.dbtools.common.config.db.ConnectionFactory$OracleConnectionFactory._connection(ConnectionFactory.java:322)
                  at oracle.dbtools.common.config.db.ConnectionFactory.getConnection(ConnectionFactory.java:51)
                  at oracle.dbtools.common.config.db.ValidatedConnectionFactory.from(ValidatedConnectionFactory.java:83)
                  at oracle.dbtools.common.config.db.DatabasePools.validateConnection(DatabasePools.java:336)
                  at oracle.dbtools.common.config.db.DatabasePools.access$400(DatabasePools.java:61)
                  at oracle.dbtools.common.config.db.DatabasePools$DatabasePoolLoader.call(DatabasePools.java:417)
                  at oracle.dbtools.common.config.db.DatabasePools$DatabasePoolLoader.call(DatabasePools.java:402)
                  at oracle.dbtools.common.functions.Functions$3.get(Functions.java:41)
                  at oracle.dbtools.common.functions.MaybeValidImpl.load(MaybeValidImpl.java:169)
                  at oracle.dbtools.common.functions.MaybeValidImpl.<init>(MaybeValidImpl.java:20)
                  at oracle.dbtools.common.functions.MaybeValidImpl.<init>(MaybeValidImpl.java:14)
                  at oracle.dbtools.common.functions.MaybeValidImpl$LoaderImpl.load(MaybeValidImpl.java:262)
                  at oracle.dbtools.common.functions.MaybeValidCache$MaybeValidCacheLoader.call(MaybeValidCache.java:297)
                  at oracle.dbtools.common.functions.MaybeValidCache$MaybeValidCacheLoader.call(MaybeValidCache.java:286)
                  at com.google.common.cache.LocalCache$LocalManualCache$1.load(LocalCache.java:4876)
                  at com.google.common.cache.LocalCache$LoadingValueReference.loadFuture(LocalCache.java:3528)
                  at com.google.common.cache.LocalCache$Segment.loadSync(LocalCache.java:2277)
                  at com.google.common.cache.LocalCache$Segment.lockedGetOrLoad(LocalCache.java:2154)
                  at com.google.common.cache.LocalCache$Segment.get(LocalCache.java:2044)
                  at com.google.common.cache.LocalCache.get(LocalCache.java:3952)
                  at com.google.common.cache.LocalCache$LocalManualCache.get(LocalCache.java:4871)
                  at oracle.dbtools.common.functions.MaybeValidCache.get(MaybeValidCache.java:84)
                  at oracle.dbtools.common.config.db.DatabasePools.pool(DatabasePools.java:264)
                  at oracle.dbtools.common.config.db.DatabasePools.validity(DatabasePools.java:240)
                  at oracle.dbtools.common.config.db.DataSourceIdentifiers.lambda$validity$0(DataSourceIdentifiers.java:36)
                  at oracle.dbtools.common.functions.MaybeValidImpl.load(MaybeValidImpl.java:169)
                  at oracle.dbtools.common.functions.MaybeValidImpl.<init>(MaybeValidImpl.java:20)
                  at oracle.dbtools.common.functions.MaybeValidImpl.<init>(MaybeValidImpl.java:14)
                  at oracle.dbtools.common.functions.MaybeValidImpl$LoaderImpl.load(MaybeValidImpl.java:262)
                  at oracle.dbtools.common.functions.MaybeValidImpl$LoaderImpl.load(MaybeValidImpl.java:232)
                  at oracle.dbtools.common.config.db.DataSourceIdentifiers.validity(DataSourceIdentifiers.java:53)
                  at oracle.dbtools.common.config.db.DataSourceValidation.startup(DataSourceValidation.java:44)
                  at oracle.dbtools.common.app.PluginLifeCycles.startup(PluginLifeCycles.java:39)
                  at oracle.dbtools.common.app.ApplicationContext.<init>(ApplicationContext.java:83)
                  at oracle.dbtools.common.app.ApplicationContext.<init>(ApplicationContext.java:51)
                  at oracle.dbtools.entrypoint.WebApplicationEntryPoint.contextInitialized(WebApplicationEntryPoint.java:88)
                  at org.apache.catalina.core.StandardContext.listenerStart(StandardContext.java:5118)
                  at org.apache.catalina.core.StandardContext.startInternal(StandardContext.java:5634)
                  at org.apache.catalina.util.LifecycleBase.start(LifecycleBase.java:145)
                  at org.apache.catalina.core.ContainerBase.addChildInternal(ContainerBase.java:899)
                  at org.apache.catalina.core.ContainerBase.addChild(ContainerBase.java:875)
                  at org.apache.catalina.core.StandardHost.addChild(StandardHost.java:652)
                  at org.apache.catalina.startup.HostConfig.deployWAR(HostConfig.java:1092)
                  at org.apache.catalina.startup.HostConfig$DeployWar.run(HostConfig.java:1984)
                  at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
                  at java.util.concurrent.FutureTask.run(FutureTask.java:266)
                  at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
                  at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
                  ... 1 more
                  Caused by: oracle.net.ns.NetException: could not resolve the connect identifier  "PDB1"
                  at oracle.net.resolver.NameResolver.resolveName(NameResolver.java:180)
                  at oracle.net.resolver.AddrResolution.resolveAndExecute(AddrResolution.java:490)
                  at oracle.net.ns.NSProtocol.establishConnection(NSProtocol.java:660)
                  at oracle.net.ns.NSProtocol.connect(NSProtocol.java:287)
                  at oracle.jdbc.driver.T4CConnection.connect(T4CConnection.java:1481)
                  at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:540)
                  ... 56 more
                  
                  • 8. Re: ORDS to connect to database using TNSNAMES not working?
                    thatJeffSmith-Oracle

                    ok, stepping back a level

                     

                    Can you just confirm that this is the right directory

                    /opt/oracle/product/18c/dbhomeXE/network/admin

                     

                    and that that directory is reachable and your ords process OS user has read access to the tns* files?

                     

                     

                    1 person found this helpful
                    • 9. Re: ORDS to connect to database using TNSNAMES not working?
                      Denis Savenko

                      Thank you, Jeff! I think that is it.

                       

                      The /opt/oracle/product/18c/dbhomeXE/network/admin directory was correct (it is the default directory).

                       

                      But it is true I forgot to check the permissions on my `tnsnames.ora` file, and it was a good idea since ORDS runs under tomcat system user.

                       

                      By default `tnsnames.ora` has 0640 permissions, which means that it could be read only by owner (oracle) and owner group (oinstall). So for ORDS to be able to read the file, it was needed to either add tomcat user to oinstall group or permit to read the `tnsnames.ora` file to others (which I finally did).

                       

                      I think it would be great to add this piece of information to the documentation that it needs tweaking to make it working by default.

                      • 10. Re: ORDS to connect to database using TNSNAMES not working?
                        thatJeffSmith-Oracle

                        Add to the docs - make sure ORDS can find and read your file?

                        • 11. Re: ORDS to connect to database using TNSNAMES not working?
                          Denis Savenko

                          thatJeffSmith-Oracle wrote:

                           

                          Add to the docs - make sure ORDS can find and read your file?

                          Right. A note that by default `tnsnames.ora` file can be read only by the Oracle Database owner and the owner group after installation of DBMS (oracle and oinstall respectively in 18c), so the one needs to check and grant read permission to the ORDS user (tomcat in case it is run under tomcat application server) or add this user to the owner group.

                          • 12. Re: ORDS to connect to database using TNSNAMES not working?
                            Denis Savenko

                            Or even a better suggestion - if ORDS raised a more meaningful exception instead of `could not resolve the connect identifier` in such a case, which pointed at the actual reason of the issue (for instance, `could not read file '/path/to/tnsnames.ora' or file does not exist`), it would be even greater - and in this case there would not be the need in corresponding docs.

                            • 13. Re: ORDS to connect to database using TNSNAMES not working?
                              thatJeffSmith-Oracle

                              Yeah, debug set to true should have trapped that