This discussion is archived
3 Replies Latest reply: Dec 5, 2012 9:55 PM by kgronau RSS

NET8 timeout with ODBC Gateway

tx103108 Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points