1 2 Previous Next 16 Replies Latest reply on Oct 18, 2010 3:12 PM by clcarter

    ODBC dblink ORA-28545: error diagnosed by Net8 when connecting to an agent

    SchneiderIS
      I have two servers:
      - server A that is running 10g XE in Windows 7 64bit
      - server B that is running SQL Server 2008 on Win Server 2003 32bit

      Server A has an ODBC connection to Server B. The connection has been tested from the ODBC administrator screen and passes.

      Server A has the TNSNAMES.ORA, LISTENER.ORA, and initMYSQLSERVER.ORA files configured to make the link. The listener starts up fine and the creation of the link happens without error (the user name and password defined in the link are the same as used for the ODBC configuration) but when I go to run a query I get the following error:

      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 MYSQLSERVERLINK
      +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.+
      Error at Line: 1 Column: 27


      I have been trying to find on the web any description of what is happening here and cannot seem to find anything that applies. Can anyone help me with this?
        • 1. Re: ODBC dblink ORA-28545: error diagnosed by Net8 when connecting to an agent
          SchneiderIS
          Has no one any experience or ideas on this one?
          • 2. Re: ODBC dblink ORA-28545: error diagnosed by Net8 when connecting to an agent
            Lubiez Jean-Valentin
            Hello,


            Please, may you post the listener.ora and tnsnames.ora files ?

            May be something is wrong in their configuration.


            Best regards,
            Jean-Valentin
            • 3. Re: ODBC dblink ORA-28545: error diagnosed by Net8 when connecting to an agent
              SchneiderIS
              LISTENER.ORA:

              SID_LIST_LISTENER =
              +(SID_LIST =+
              +(SID_DESC =+
              +(SID_NAME = PLSExtProc)+
              +(ORACLE_HOME = C:\oraclexe\app\oracle\product\10.2.0\server)+
              +(PROGRAM = extproc)+
              +)+
              +(SID_DESC =+
              +(SID_NAME = CLRExtProc)+
              +(ORACLE_HOME = C:\oraclexe\app\oracle\product\10.2.0\server)+
              +(PROGRAM = extproc)+
              +)+
              +)+

              LISTENER =
              +(DESCRIPTION_LIST =+
              +(DESCRIPTION =+
              +(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC_FOR_XE))+
              +(ADDRESS = (PROTOCOL = TCP)(HOST = HPMedia)(PORT = 1521))+
              +)+
              +)+

              LISTENER_MYSQLSERVER =
              +(DESCRIPTION_LIST =+
              +(DESCRIPTION =+
              +(ADDRESS=(PROTOCOL = ipc)(KEY = PNPKEY))+
              +(ADDRESS=(PROTOCOL = tcp)(HOST = localhost)(PORT=1521))+
              +)+
              +)+

              SID_LIST_LISTENER_MYSQLSERVER =
              +(SID_LIST=+
              +(SID_DESC=+
              +(SID_NAME=MYSQLSERVER)+
              +(ORACLE_HOME = C:\oraclexe\app\oracle\product\10.2.0\server)+
              +(PROGRAM=hsodbc)+
              +)+
              +)+


              DEFAULT_SERVICE_LISTENER = (XE)

              -----
              TNSNAMES.ORA:


              XE =
              (DESCRIPTION =
              (ADDRESS = (PROTOCOL = TCP)(HOST = HPMedia)(PORT = 1521))
              (CONNECT_DATA =
              (SERVER = DEDICATED)
              (SERVICE_NAME = XE)
              )
              )

              EXTPROC_CONNECTION_DATA =
              (DESCRIPTION =
              (ADDRESS_LIST =
              (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC_FOR_XE))
              )
              (CONNECT_DATA =
              (SID = PLSExtProc)
              (PRESENTATION = RO)
              )
              )

              ORACLR_CONNECTION_DATA =
              (DESCRIPTION =
              (ADDRESS_LIST =
              (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC_FOR_XE))
              )
              (CONNECT_DATA =
              (SID = CLRExtProc)
              (PRESENTATION = RO)
              )
              )


              MYSQLSERVER =
              (DESCRIPTION=
              (ADDRESS_LIST =
              (ADDRESS=
              (PROTOCOL = tcp)
              (HOST = 10.0.1.56)
              (PORT = 1521)
              )
              )
              (CONNECT_DATA =
              (SID = MYSQLSERVER)
              )
              (HS = OK)
              )
              • 4. Re: ODBC dblink ORA-28545: error diagnosed by Net8 when connecting to an agent
                Lubiez Jean-Valentin
                Hello,


                What's happen if you change localhost by the name of your Server ( HPMedia ) on the line below ?
                LISTENER_MYSQLSERVER =
                ...
                (ADDRESS=(PROTOCOL = tcp)(HOST = localhost)(PORT=1521))
                Also, in the same way, are you sure about the IP Address below on the tnsnames.ora ?
                MYSQLSERVER=
                ...
                (HOST = 10.0.1.56)
                If you change it by the hostname of the server, what's the result ?


                Best regards,
                Jean-Valentin

                Edited by: Lubiez Jean-Valentin on Oct 9, 2010 5:59 PM
                • 5. Re: ODBC dblink ORA-28545: error diagnosed by Net8 when connecting to an agent
                  SchneiderIS
                  The IP address 10.0.1.56 is the same as HPMedia or localhost. I have set them all to be just HPMedia now.

                  The changes made no difference.
                  • 6. Re: ODBC dblink ORA-28545: error diagnosed by Net8 when connecting to an agent
                    SchneiderIS
                    I just tried a "listener reload LISTENER_MYSQLSERVER" and this is what I get:

                    Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
                    TNS-12541: TNS:no listener
                    TNS-12560: TNS:protocol adapter error
                    TNS-00511: No listener
                    +32-bit Windows Error: 2: No such file or directory+

                    If I run the start on the listener it says that it is already loaded. If I run the "listener stop LISTENER_MYSQLSERVER" to stop it I get the same error as shown when trying to reload. It seams to me that there is still an issue with the listener configuration that I thought was clean.

                    What file or directory could this be referring to?
                    • 7. Re: ODBC dblink ORA-28545: error diagnosed by Net8 when connecting to an agent
                      Lubiez Jean-Valentin
                      Hello,


                      You may try to check the Listener configuration with NETCA :

                      http://www.orafaq.com/wiki/NetCA
                      http://download.oracle.com/docs/cd/B19306_01/network.102/b14212/admintools.htm


                      Best regards,
                      Jean-Valentin
                      • 8. Re: ODBC dblink ORA-28545: error diagnosed by Net8 when connecting to an agent
                        SchneiderIS
                        Thanks Jean,

                        From the documentation I think I came to understand something that I was missing before. Namely that you can't add another listener that listens on the same port as the default listener. The result is that I put the SID configuration inside the main LISTENER configuration for SID's and reloaded the listener. So far I have not seen and changes that are required for the tnsnames.ora file from what I listed before.

                        Here is what my listener.ora file looks like now.

                        DEFAULT_SERVICE_LISTENER= (XE)

                        SID_LIST_LISTENER =
                        +(SID_LIST =+
                        +(SID_DESC =+
                        +(SID_NAME = PLSExtProc)+
                        +(ORACLE_HOME = C:\oraclexe\app\oracle\product\10.2.0\server)+
                        +(PROGRAM = extproc)+
                        +)+
                        +(SID_DESC =+
                        +(SID_NAME = CLRExtProc)+
                        +(ORACLE_HOME = C:\oraclexe\app\oracle\product\10.2.0\server)+
                        +(PROGRAM = extproc)+
                        +)+
                        +(SID_DESC =+
                        +(SID_NAME = MYSQLSERVER)+
                        +(ORACLE_HOME = C:\oraclexe\app\oracle\product\10.2.0\server)+
                        +(PROGRAM = hsodbc)+
                        +)+
                        +)+

                        LISTENER =
                        +(DESCRIPTION_LIST =+
                        +(DESCRIPTION =+
                        +(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC_FOR_XE))+
                        +(ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.1.70)(PORT = 1521))+
                        +)+
                        +)+


                        Now when I run a query I get the following which is more concerning in that the indication to me is that the ODBC driver from Microsoft for SQL Server does not work for Oracle.

                        ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
                        +[Generic Connectivity Using ODBC][Microsoft][ODBC Driver Manager] The specified DSN contains an architecture mismatch between the Driver and Application (SQL State: IM014; SQL Code: 0)+
                        ORA-02063: preceding 2 lines from MYSQLSERVERLINK
                        +28500. 00000 - "connection from ORACLE to a non-Oracle system returned this message:"+
                        *Cause:    The cause is explained in the forwarded message.+
                        *Action:   See the non-Oracle system's documentation of the forwarded+
                        message.
                        Error at Line: 1 Column: 31

                        Do you have any thoughts on this error? I have searched Google for it and cannot find any results.

                        Edited by: SchneiderIS on Oct 10, 2010 9:23 AM
                        • 9. Re: ODBC dblink ORA-28545: error diagnosed by Net8 when connecting to an agent
                          SchneiderIS
                          Jean,

                          One thought I had was that Oracle was hitting a 64bit SQL Server driver but I have checked and the driver is 32bit. I am also hitting a 32bit version of SQL Server and from my understanding Oracle 10g XE is also 32bit so in theory it is 32bit across the board.

                          -Peter
                          • 10. Re: ODBC dblink ORA-28545: error diagnosed by Net8 when connecting to an agent
                            SchneiderIS
                            Does anyone have any ideas on this issue?
                            • 11. Re: ODBC dblink ORA-28545: error diagnosed by Net8 when connecting to an agent
                              clcarter
                              It might be worth a try to go with the different port, and tcp instead of ipc ...
                              # listener.ora entry for a new listener
                              MYSQLSERVERLSNR = 
                                (DESCRIPTION_LIST =
                                  (DESCRIPTION =
                                    (ADDRESS = (PROTOCOL = TCP)(HOST = <hostname>)(PORT = 1522))
                                  )
                                )
                              SID_LIST_MYSQLSERVERLSNR=
                                 (SID_LIST=
                                     (SID_DESC=
                                        (SID_NAME=DSNNAME)
                                        (ORACLE_HOME=C:\oraclexe\app\oracle\product\10.2.0\server)
                                        (PROGRAM=hsodbc)
                                     )
                                  )
                              CONNECT_TIMEOUT_MYSQLSERVERLSNR = 0
                              
                              # tnsnames.ora will need the port update
                              mysqlserver =
                                ...
                                (HOST = <hostname>)
                                (PORT = 1522)
                                ...
                              
                              # And <hostname> should all be same-same ... listener is configured here, odbc dsn setting is here also
                              Start the listener ...

                              $ lsnrctl start mysqlserverlsnr
                              ...
                              $ lsnrctl status mysqlserverlsnr
                              ...

                              The initMYSQLSERVER.ora needs to be in OH/hs/admin, not network/admin ... and in some cases listener.ora and tnsname.ora entries the whitespace indentation can be significant, in places.

                              How is the DSN setup? Which driver? Windows authentication? Or is a sqlserver user set up for the connection? If its sql2005+ user names and passwords are case sensitive, from sqlplus double quotes are required in the database link setup (but not on the tns alias):
                                SQL> create database link mysqlserver connect to "username" identified by "userpasswd" using 'mysqlserver';
                                SQL> select count(*) from some_table@mysqlserver;
                              And the sqlnet.ora SQLNET.AUTHENTICATION_SERVICES = (NTS) or = (NONE) setting may also be relevant as well.
                              • 12. Re: ODBC dblink ORA-28545: error diagnosed by Net8 when connecting to an agent
                                SchneiderIS
                                Hi clcarter,

                                Thanks for the quick reply.

                                My DNS is not using Windows authentication but rather SQL Server authentication. The user name and password defined in the link are just as you described with the use of double quotes and case sensitivity in mind for the password. MY understanding is that the user name is not case sensitive though I am matching the case there as well.

                                Here is the entry in sqlnet.ora: SQLNET.AUTHENTICATION_SERVICES = (NTS)

                                When I configure the listener in the manner you describe I get the following error:
                                LSNRCTL for 32-bit Windows: Version 10.2.0.1.0 - Production on 15-OCT-2010 11:51:54
                                
                                Copyright (c) 1991, 2005, Oracle.  All rights reserved.
                                
                                Starting tnslsnr: please wait...
                                
                                TNS-12560: TNS:protocol adapter error
                                 TNS-00530: Protocol adapter error
                                This is base on the listener.ora containing:
                                MYSQLSERVERLSNR =
                                  (DESCRIPTION_LIST =
                                    (DESCRIPTION =
                                      (ADDRESS=(PROTOCOL = tcp)(HOST = HPMedia)(PORT=1522))
                                    )
                                  )
                                
                                SID_LIST_MYSQLSERVERLSNR =
                                  (SID_LIST=
                                    (SID_DESC=
                                      (SID_NAME=MYSQLSERVER)
                                      (ORACLE_HOME = C:\oraclexe\app\oracle\product\10.2.0\server)
                                      (PROGRAM=hsodbc)
                                    )
                                  )
                                This is my tnsnames.ora entry.
                                MYSQLSERVER = 
                                  (DESCRIPTION=
                                    (ADDRESS_LIST = 
                                      (ADDRESS=
                                         (PROTOCOL = tcp)
                                         (HOST = HPMedia)
                                         (PORT = 1522)
                                      )
                                    )
                                    (CONNECT_DATA = 
                                      (SID = MYSQLSERVER)
                                    )
                                    (HS = OK)
                                  )
                                The initMYSQLSERVER.ora file is in the hs/admin folder.

                                Any thoughts for this?

                                Edited by: SchneiderIS on Oct 15, 2010 11:01 AM
                                • 13. Re: ODBC dblink ORA-28545: error diagnosed by Net8 when connecting to an agent
                                  clcarter
                                  Does the hostname resolve properly in dns? and the reverse lookup on the ip?

                                  $ nslookup HPMedia
                                  $ nslookup <ip address>
                                  $ ipconfig /all
                                  ...
                                  Local Area Connection
                                  IP Addrress : <ip address>
                                  ...
                                  • 14. Re: ODBC dblink ORA-28545: error diagnosed by Net8 when connecting to an agent
                                    SchneiderIS
                                    It does not. The ipconfig shows the name and the IP address but the machine is not on a domain. The host file is showing the entries, this box is not on a Domain.
                                    1 2 Previous Next