3 Replies Latest reply: Dec 5, 2012 11:55 PM by Kgronau-Oracle RSS

    NET8 timeout with ODBC Gateway

    tx103108
      Local:
      Database: Oracle XE 11gR2
      Oracle ODBC Gateway 11g
      Windows 7
      Sybase SQL Anyhwere 12 client

      Remote:
      Database: Sybase SQL Anywhere 10
      Windows Server 2008 R2
      ====================

      SQL:
      select * from mytable@dblink:

      Error returned:
      ERROR at line 1:
      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 dblink

      Files:
      ---------------
      initdlink.ora
      ---------------

      # 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 = dblink
      #HS_FDS_TRACE_LEVEL = 255

      HS_LANGUAGE=american_america.we8iso8859p1
      HS_NLS_NCHAR=UTF-8

      ----------------
      listener.ora
      ----------------

      SID_LIST_LISTENER =
      (SID_LIST =
      (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = C:\oraclexe\app\oracle\product\11.2.0\server)
      (PROGRAM = extproc)
      )
      (SID_DESC =
      (SID_NAME = CLRExtProc)
      (ORACLE_HOME = C:\oraclexe\app\oracle\product\11.2.0\server)
      (PROGRAM = extproc)
      )
      (SID_DESC =
      (SID_NAME = XE)
      (ORACLE_HOME = C:\oraclexe\app\oracle\product\11.2.0\server)
      )
      (SID_DESC =
      (SID_NAME = dblink)
      (ORACLE_HOME = C:\oraclexe\app\oracle\product\11.2.0\gtw)
      (PROGRAM = dg4odbc)
      )
      )

      LISTENER =
      (DESCRIPTION_LIST =
      (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
      )
      )

      DEFAULT_SERVICE_LISTENER = (XE)

      ------------------
      tnsnames.ora
      ------------------

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

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

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

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

      ==========================
      (1) Similar setup works ok on Linux connecting to remote Sybase on Windows. But get error below when running on Windows 7.
      (2) Similar setup from Windows7 to Windows 2008 R2 works fine when ODBC Gateway on remote Windows 2008 server.
      (3) ODBC connectivity has been verified as ok to remote Sybase database on Windows 2008 server.
      (4) tnsping dblink verified ok.

      Problems started when moving ODBC Gateway to local system where Oracle target database resides.

      Please advise.

      Added to initdblink.ora the following (no change in results):

      HS_KEEP_REMOTE_COLUMN_SIZE = ALL
      HS_NLS_LENGTH_SEMANTICS = CHAR

      Edited by: user601798 on Dec 4, 2012 11:59 AM
        • 1. Re: NET8 timeout with ODBC Gateway
          Kgronau-Oracle
          You're mixing different OH's in your listener. The gateway is located in C:\oraclexe\app\oracle\product\11.2.0\gtw, but the database and the listener in: C:\oraclexe\app\oracle\product\11.2.0\server

          When you're using DG4ODBC you can use the gateway which is automatically installed in the database home -> you'll find there as well a HS directory and the dg4odbc executable. The other option is to use separate listeners for both installations. For example using the Oracle database listener on port 1521 for the database connections and 1522 for the gateway.

          If you really want to use one listener (which is not the preferred solution), then you need also to specify the location to the executable, similar to:


          <space>(SID_DESC =
          <space><space>(SID_NAME = dblink)
          <space><space>(ORACLE_HOME = C:\oraclexe\app\oracle\product\11.2.0\gtw)
          <space><space>(PROGRAM = C:\oraclexe\app\oracle\product\11.2.0\gtw\dg4odbc)
          <space>)

          After changing the listener.ora file, please STOP and STAT the listener using lsnrctl utility and provide its output. Also provide the output of tnsping dblink.


          - Klaus
          • 2. Re: NET8 timeout with ODBC Gateway
            tx103108
            Thanks. I used one listener and your suggestions and I am up and running. BTW - does using separate listeners - one for the GTW and one for the instance - increase performance?
            • 3. Re: NET8 timeout with ODBC Gateway
              Kgronau-Oracle
              As the listener is only spawning the gateway executable and then not longer used, there's no performance increase when using 2 listeners. It is just easier to maintain as you can shut down separately the database and/or gateway listener.

              And the other reason is that Oracle is downwards compatible, not upwards. This means that you can use for example an 11.2.0.2 listener to spawn an 11.2.0.1 process, but there's no guarantee that it can also correctly spawn an 11.2.0.3 process. When you now just update the gateway OH to for example 11.2.0.3 but keep the atabase at lower level like 11.2.0.2 there's no guarantee that the listner is correctly spawning the gateway process.