1 2 Previous Next 20 Replies Latest reply on Sep 30, 2019 10:27 PM by EdStevens

    Need help debugging a TNSNAMES issue for Oracle Gateway:

    rjsosi

      Hi,

       

       

      I created a dblink and tested it for Oracle Gateway. I got an "ORA-12154: TNS:could not resolve the connect identifier specified" error.

       

      I'm pretty sure I've got a mis-setting somewhere.

       

      I'm going to past my entire layout and anyone that wants to peruse through and spot the error(s) please feel free.:

      Service DSN

       

       

      MYSQL DB info

       

      Oracle info:

       

      ORACLE_HOME:

      C:\app\mylogin\virtual\product\12.2.0\dbhome_1

       

      Listener.ora

       

       

      1. SID_LIST_LISTENER = 
      2.   (SID_LIST = 
      3.     (SID_DESC = 
      4.       (SID_NAME = CLRExtProc) 
      5.       (ORACLE_HOME = C:\app\mylogin\virtual\product\12.2.0\dbhome_1) 
      6.       (PROGRAM = extproc) 
      7.       (ENVS = "EXTPROC_DLLS=ONLY:C:\app\mylogin\virtual\product\12.2.0\dbhome_1\bin\oraclr12.dll"
      8.     ) 
      9.     (SID_DESC = 
      10.       (GLOBAL_DBNAME = Oracle8) 
      11.       (SID_NAME = ORCL) 
      12.     ) 
      13.     (SID_DESC = 
      14.       (SID_NAME = MYSQL80w) 
      15.       (ORACLE_HOME = C:\app\mylogin\virtual\product\12.2.0\dbhome_1) 
      16.       (PROGRAM = dg4odbc) 
      17.     ) 
      18.   ) 
      19.  
      20. LISTENER = 
      21.   (DESCRIPTION_LIST = 
      22.     (DESCRIPTION = 
      23.       (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521)) 
      24.     ) 
      25.     (DESCRIPTION = 
      26.       (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

       

       

      TNSNAMES.ora

       

      1.   XE =   
      2.   (DESCRIPTION =   
      3.     (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))   
      4.     (CONNECT_DATA =   
      5.       (SERVER = DEDICATED)   
      6.       (SERVICE_NAME = XE)   
      7.     )   
      8.   )   
      9.    
      10.   ORCL_DEV = 
      11.   (DESCRIPTION = 
      12.     (ADDRESS_LIST = 
      13.       (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521)) 
      14.     ) 
      15.         (CONNECT_DATA = 
      16.       (SID = ORCL) 
      17.     ) 
      18.   ) 
      19.  
      20. # Hosts column equals DB name   ORCL is the name in the  
      21.  
      22.   MYSQL80= 
      23.    (DESCRIPTION= 
      24.       (ADDRESS= (PROTOCOL = TCP)(HOST = localhost)(PORT = 3306)) 
      25.        
      26.       (CONNECT_DATA= 
      27.          (SID=MYSQL80)) 
      28.       (HS=OK))

       

      SQLNET.ora

       

      SQLNET.KERBEROS5_CC_NAME = C:\oracle\Kerbos_credcach.txt

       

       

      SQLNET.AUTHENTICATION_SERVICES= (NTS)

       

       

      NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT, LDAP)

       

       

      SQLNET.KERBEROS5_CLOCKSKEW = 6000

       

       

      SQLNET.KERBEROS5_CONF = C:\oracle\ora10g\NETWORK\ADMIN\krb5.conf

       

       

      SQLNET.KERBEROS5_CONF_MIT = TRUE

       

       

      SQLNET.AUTHENTICATION_KERBEROS5_SERVICE = oracle

       

       

      ADR_BASE = C:\app\rscheid\virtual\product\12.2.0\dbhome_1\log

       

      initmysql80.ora

       

       

      1. # This is a sample agent init file that contains the HS parameters that are 
      2. # needed for the Database Gateway for ODBC 
      3.  
      4. # HS init parameters 
      5. HS_FDS_CONNECT_INFO = MYSQL80w 
      6.  
      7.  
      8. HS_FDS_TRACE_LEVEL = off 
      9.  
      10. # Environment variables required for the non-Oracle system 
      11. #set <envvar>=<value> 

      C:\>lsnrctl status

       

       

      1. LSNRCTL for 64-bit Windows: Version 12.2.0.1.0 - Production on 26-SEP-2019 14:26:47 
      2.  
      3.  
      4. Copyright (c) 1991, 2016, Oracle.  All rights reserved. 
      5.  
      6.  
      7. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521))) 
      8. STATUS of the LISTENER 
      9. ------------------------ 
      10. Alias                     LISTENER 
      11. Version                   TNSLSNR for 64-bit Windows: Version 12.2.0.1.0 - Production 
      12. Start Date                26-SEP-2019 13:45:57 
      13. Uptime                    0 days 0 hr. 40 min. 50 sec 
      14. Trace Level               off 
      15. Security                  ON: Local OS Authentication 
      16. SNMP                      OFF 
      17. Listener Parameter File   c:\LOCAL\network\admin\listener.ora 
      18. Listener Log File         C:\app\mylogin\virtual\product\12.2.0\diag\tnslsnr\OCLT70545153\listener\alert\log.xml 
      19. Listening Endpoints Summary... 
      20.   (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521))) 
      21.   (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1521ipc))) 
      22.   (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=OCLT70545153.office.adroot.bmogc.net)(PORT=5500))(Security=(my_wallet_directory=C:\APP\MYLOGIN\VIRTUAL\admin\orcl\xdb_wallet))(Presentation=HTTP)(Session=RAW)) 
      23. Services Summary... 
      24. Service "CLRExtProc" has 1 instance(s). 
      25.   Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service... 
      26. Service "MYSQL80w" has 1 instance(s). 
      27.   Instance "MYSQL80w", status UNKNOWN, has 1 handler(s) for this service... 
      28. Service "Oracle8" has 1 instance(s). 
      29.   Instance "ORCL", status UNKNOWN, has 1 handler(s) for this service... 
      30. Service "d767cda564dd4065aeb98259d87281ee.office.adroot.bmogc.net" has 1 instance(s). 
      31.   Instance "orcl", status READY, has 1 handler(s) for this service... 
      32. Service "orcl.office.adroot.bmogc.net" has 1 instance(s). 
      33.   Instance "orcl", status READY, has 1 handler(s) for this service... 
      34. Service "orclXDB.office.adroot.bmogc.net" has 1 instance(s). 
      35.   Instance "orcl", status READY, has 1 handler(s) for this service... 
      36. Service "orclpdb.office.adroot.bmogc.net" has 1 instance(s). 
      37.   Instance "orcl", status READY, has 1 handler(s) for this service... 
      38. The command completed successfully 

      C:\>lsnrctl services

       

       

      1. LSNRCTL for 64-bit Windows: Version 12.2.0.1.0 - Production on 26-SEP-2019 14:27:35 
      2.  
      3.  
      4. Copyright (c) 1991, 2016, Oracle.  All rights reserved. 
      5.  
      6.  
      7. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521))) 
      8. Services Summary... 
      9. Service "CLRExtProc" has 1 instance(s). 
      10.   Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service... 
      11.     Handler(s): 
      12.       "DEDICATED" established:0 refused:0 
      13.          LOCAL SERVER 
      14. Service "MYSQL80w" has 1 instance(s). 
      15.   Instance "MYSQL80w", status UNKNOWN, has 1 handler(s) for this service... 
      16.     Handler(s): 
      17.       "DEDICATED" established:0 refused:0 
      18.          LOCAL SERVER 
      19. Service "Oracle8" has 1 instance(s). 
      20.   Instance "ORCL", status UNKNOWN, has 1 handler(s) for this service... 
      21.     Handler(s): 
      22.       "DEDICATED" established:0 refused:0 
      23.          LOCAL SERVER 
      24. Service "d767cda564dd4065aeb98259d87281ee.office.adroot.bmogc.net" has 1 instance(s). 
      25.   Instance "orcl", status READY, has 1 handler(s) for this service... 
      26.     Handler(s): 
      27.       "DEDICATED" established:0 refused:0 state:ready 
      28.          LOCAL SERVER 
      29. Service "orcl.office.adroot.bmogc.net" has 1 instance(s). 
      30.   Instance "orcl", status READY, has 1 handler(s) for this service... 
      31.     Handler(s): 
      32.       "DEDICATED" established:0 refused:0 state:ready 
      33.          LOCAL SERVER 
      34. Service "orclXDB.office.adroot.bmogc.net" has 1 instance(s). 
      35.   Instance "orcl", status READY, has 1 handler(s) for this service... 
      36.     Handler(s): 
      37.       "D000" established:0 refused:0 current:0 max:1022 state:ready 
      38.          DISPATCHER <machine: OCLT70545153, pid: 3884> 
      39.          (ADDRESS=(PROTOCOL=tcp)(HOST=OCLT70545153.office.adroot.bmogc.net)(PORT=63225)) 
      40. Service "orclpdb.office.adroot.bmogc.net" has 1 instance(s). 
      41.   Instance "orcl", status READY, has 1 handler(s) for this service... 
      42.     Handler(s): 
      43.       "DEDICATED" established:0 refused:0 state:ready 
      44.          LOCAL SERVER 
      45. The command completed successfully 

      C:\>tnsping MYSQL80w

       

       

      1. TNS Ping Utility for 64-bit Windows: Version 12.2.0.1.0 - Production on 26-SEP-2019 14:28:11 
      2.  
      3. Copyright (c) 1997, 2016, Oracle.  All rights reserved. 
      4.  
      5. Used parameter files: 
      6. c:\LOCAL\network\admin\sqlnet.ora 
      7.  
      8. TNS-03505: Failed to resolve name 

       

      create public database link MYSQL80 connect to "root" identified by ",password>" using 'MYSQL80';

        • 1. Re: Need help debugging a TNSNAMES issue for Oracle Gateway:
          Adityanath Dewoolkar

          I don't see any TNS entry for MYSQL80W in your TNSNAMES.ora so "tsnping  MYSQL80W" will give you "TNS-03505: Failed to resolve name"

           

          I can see you have created LISTENER.ora with SID MYSQL80W .

           

          If above is right then TNS entru should be created for MYSQL80W  & your DB link should be created using MYSQL80W & not MYSQL80.

           

          Note: Its always good practice to keep separate listeners for HS connections.

           

          Regards,

          Adi

          • 2. Re: Need help debugging a TNSNAMES issue for Oracle Gateway:
            EdStevens

            How is this any different that your other thread: unknown

            Need Debugging help Oracle to MYSQL local single instance install

             

            Cross-posting is not helpful.

            • 3. Re: Need help debugging a TNSNAMES issue for Oracle Gateway:
              rjsosi

              Thanks Aditya,

               

              I changed the MYSQL80 listing all to MYSQL80w and that did it. I can now "tnsping MYSQL80w and it comes back with an OK!

               

              I have a quick follow up question.

               

              So my new tnsnames entry for MTSQL80w is:

               

              MYSQL80W =
                (DESCRIPTION=
                    (ADDRESS= (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
                      (CONNECT_DATA=
                      (SID=MYSQL80w))
                    (HS=OK)
                  )
              

              my listener entry is this:

               

                  (SID_DESC =
                    (SID_NAME = MYSQL80w)
                    (ORACLE_HOME = C:\app\rscheid\virtual\product\12.2.0\dbhome_1)
                    (PROGRAM = dg4odbc)
                  )
              

              So because I changed the SID=MYSQL80 in tnsname.ora to SID=MYSQL80w that fixed the issue.

               

              The played with changing just the name in the tnsnames file as well.

               

              I had both

               

              MYSQL80W =
                (DESCRIPTION=
              -
              -
              -
              

              and

               

              MYSQL80 =
                (DESCRIPTION=
              -
              -
              -
              

               

              and I was able to tnsping both with a return of "OK"

               

              so the TNSNAME is just the name you give the definition. It doesn't affect the connection between the listener and the tnsnames.ora file.

               

              On the other hand you have to have the SID from the tns entry set as SID=MYSQL80w that has to equal the SID_NAME in the SID_LIST_LISTENER of the listener file which is SID_NAME = MYSQL80w.

               

              So the listener's function is to just wait 'till a call comes in from a tnsnames file saying "I'm looking for MYSQL80w". It matches the SID with the SID_NAME and makes the connection.

               

              An additional question. Is it uisng the ORACLE_HOME setting to look for the program dg4odbc to run?

               

              Is that what the other two entries are for?

               

              Thanks Again!

              • 4. Re: Need help debugging a TNSNAMES issue for Oracle Gateway:
                Adityanath Dewoolkar

                When you run below query:

                 

                select * from dual@<DB_LINK>;

                 

                It goes to TNS entry/connection string specified in DB link definition. it understands its going for heterogeneous connection from values HS=OK.

                 

                Here it understands it needs to check for pseudo listener entry, wherein it sees below entries:

                 

                (SID_NAME = MYSQL80w) 

                (ORACLE_HOME = C:\app\rscheid\virtual\product\12.2.0\dbhome_1) 

                (PROGRAM = dg4odbc) 

                 

                Now this goes to your HS ODBC settings in you ORACLE_HOME to get actual connection details for heterogeneous DB from entry: HS_FDS_CONNECT_INFO

                 

                Hope it makes sense.

                 

                Also two entries are not needed, only one is sufficient, in your case its MYSQL80w. This should only be used for DSN creation as well for DB link creation.

                 

                Are you able to query using DB link now?

                 

                Regards,

                Adi

                • 5. Re: Need help debugging a TNSNAMES issue for Oracle Gateway:
                  L. Fernigrini

                  Regarding your last question, the answer is Yes.

                   

                  Rather than trying to establish a connection to an Oracle database, the Listener executes the "dg4odbc" program from the Oracle Home mentioned ("C:\app\rscheid\virtual\product\12.2.0\dbhome_1"), and passes "MYSQL80w" as the name of the gateway connection. With that name, the gateway knows the desired configuration and the DSN to use.

                  • 6. Re: Need help debugging a TNSNAMES issue for Oracle Gateway:
                    rjsosi

                    Hi Adi,

                     

                    I dropped and recreated the DB link as follows:

                     

                    create public database link MYSQL80w connect to "root" identified by "password1" using 'MYSQL80w';

                     

                     

                    I tried to just test the link and I got the following error message"

                     

                    ORA-28545: error diagnosed by Net8 when connecting to an agent
                    Unable to retrieve text of NETWORK/NCR message 65535
                    ORA-02063: preceding 2 lines from MYSQL80W
                    28545. 0000 -  "error diagnosed by Net8 when connecting to an agent"
                    *Cause:    An attempt to call an external procedure or to issue SQL
                               to a non-Oracle system on a Heterogeneous Services database link
                               failed at connection initialization.  The error diagnosed
                               by Net8 NCR software is reported separately.
                    *Action:   Refer to the Net8 NCRO error message.  If this isn't clear,
                               check connection administrative setup in tnsnames.ora
                               and listener.ora for the service associated with the
                               Heterogeneous Services database link being used, or with
                               'extproc_connection_data' for an external procedure call.
                    Vendor code 28545
                    

                     

                    Doing a test query gives the same message.

                     

                    Also The initmysql80w.ora file is the following:

                     

                    # This is a sample agent init file that contains the HS parameters that are

                    # needed for the Database Gateway for ODBC

                     

                    #

                    # HS init parameters

                    #

                    HS_FDS_CONNECT_INFO = MYSQL80w

                     

                     

                    HS_FDS_TRACE_LEVEL = off

                     

                     

                    #

                    # Environment variables required for the non-Oracle system

                    #

                    #set <envvar>=<value>

                    • 7. Re: Need help debugging a TNSNAMES issue for Oracle Gateway:
                      rjsosi

                      Also I'm adding that my host name resolves properly in DNS and the reverse lookup on the IP info

                       

                       

                      C:\>nslookup localhost
                      Server:  ipsrvhb1.bmogc.net
                      Address:  10.88.100.10
                      
                      Name:    localhost.mycfo.com
                      Address:  127.0.0.1
                      
                      C:\>nslookup 127.0.0.1
                      Server:  ipsrvhb1.bmogc.net
                      Address:  10.88.100.10
                      
                      Name:    localhost
                      Address:  127.0.0.1
                      
                      C:\>ipconfig /all
                      
                      
                      Windows IP Configuration
                      
                      
                        Host Name . . . . . . . . . . . . : OCLT70545153
                       -
                      -
                      -
                      
                      C:\>
                      

                       

                      I'm leaving out the rest. I just wanted you to see the Machine name that ipconfig considers as the Host Name.

                       

                      Should I be using this instead of localhost?

                      • 8. Re: Need help debugging a TNSNAMES issue for Oracle Gateway:
                        EdStevens

                        rjsosi wrote:

                         

                        Also I'm adding that my host name resolves properly in DNS and the reverse lookup on the IP info

                         

                         

                        1. C:\>nslookuplocalhost
                        2. Server:ipsrvhb1.bmogc.net
                        3. Address:10.88.100.10
                        4. Name:localhost.mycfo.com
                        5. Address:127.0.0.1
                        6. C:\>nslookup127.0.0.1
                        7. Server:ipsrvhb1.bmogc.net
                        8. Address:10.88.100.10
                        9. Name:localhost
                        10. Address:127.0.0.1
                        11. C:\>ipconfig/all
                        12. WindowsIPConfiguration
                        13. HostName............:OCLT70545153
                        14. -
                        15. -
                        16. -
                        17. C:\>

                         

                        I'm leaving out the rest. I just wanted you to see the Machine name that ipconfig considers as the Host Name.

                         

                        Should I be using this instead of localhost?

                        Consider that when I client requests a message be sent to 'localhost' (or its address 12.0.0.1) the request, by definition of 'localhost', will loopback to the same machine from which it originated.  It will never leave the requesting machine.  So while 'localhost' will work in your current situation of haveing both ends of the communication on the same machine, it will not work when you have a situation where the two ends are on different machines.  Personally, I never use 'localhost'. I always use the actual server name.  I use it in my listener.ora.  I use it in all of my tnsnames.ora.  Everywhere.  All the time.  That way I'm 100% consistent in my treatment of network communications.

                        • 9. Re: Need help debugging a TNSNAMES issue for Oracle Gateway:
                          rjsosi

                          Thanks! I'll keep the locahost setting the way it is for now.

                           

                          I have a different question regarding an ORA-28545: error

                           

                          https://knowledgebase.progress.com/articles/Article/7104

                           

                          One suggestion was that I check the ODBC driver I'm using to make sure they work across the board as far as whether I'm using a 32 bit driver or a 64 bit driver.

                           

                          Originally one of the folks either in this post or another told me to check whether my ODBC drivers can connect o MYSQL even if I use Excel. I tried to connect in Excel but got a message saying the drivers are 64 bit and Excel was 32 bit.

                           

                          So I downloaded 32 bit ODBC drivers for MYSQL and installed them.

                           

                          I created corresponding entries for the new MYSQL80w32 DSN a dn reloaded the listener and did a tnsping at the do prompt.

                           

                          It works. Also the tnsping for the original MYSQL80w DSN works as well.

                           

                          But when I created a link in Oracle and tested it I still got the same ORA-28545: error message.

                           

                          So we can eliminate that.

                           

                          What else could it be?

                          https://knowledgebase.progress.com/articles/Article/7104

                          • 10. Re: Need help debugging a TNSNAMES issue for Oracle Gateway:
                            L. Fernigrini

                            The Oracle Gateway is a 64 bit product, so it needs a 64 bit driver as you installed/configured initially.

                             

                            I suggested to use an external tool to test the ODBC connection, but always thinking of using the same connection that the Gateway would use.

                             

                            Be sure to use a 64 bit driver for the connection, you may skip testing it with another tool but the architecture (64 bit) must match between driver and "client", in this case the client is the Gateway

                            • 11. Re: Need help debugging a TNSNAMES issue for Oracle Gateway:
                              rjsosi

                              I'm 99.99% positive it's all 64 bit from Oracle through Windows to MYSQL via the original ODBC 64 bit drivers...

                               

                              Should I I get rid of the 32 bit drivers then?

                               

                              They're not going to get in the way of any of the 64 bit stuff are they?

                               

                              Also in the article from

                               

                              https://knowledgebase.progress.com/articles/Article/7104

                               

                              Did you notice for 32 bit they're still referencing the program for hsodbc?

                               

                              Now this article was written in June of 2019 although they're not telling us which version of Oracle they're using. I'm assuming at lease 12c

                              • 12. Re: Need help debugging a TNSNAMES issue for Oracle Gateway:
                                L. Fernigrini

                                The image on the original post shows the 64 bit ODBC manager, so it should be OK. you can remove the 32 bit one, just in case...

                                 

                                At least, remove the connection from the 32 bit manager, if you created it with the same name

                                • 13. Re: Need help debugging a TNSNAMES issue for Oracle Gateway:
                                  rjsosi

                                  Done. I also commented out the 32 bit entries in the tnsnames.ora log and the listener.ora log.

                                   

                                  I even deleted the 32 bit DSNs I created and I deleted the 32 bit link I created in Oracle.

                                   

                                  Can you see ANY other reason I'm getting the ORA-28545: error?

                                  1 2 Previous Next