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?

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

                                  I've republished my latest SQLNET files if you;d like to take a look.

                                   

                                  Re: Need Debugging help Oracle to MYSQL local single instance install

                                  1 2 上一个 下一个