Cant make unixODBC work on HP-UX: ORA-28545

[mrsanmaru]

Hi everyone,

I am successfully using unixODBC on RedHat to retrieve data from a remote QL Server to my local Oracle development server. I am trying to do the same on my production server running Oracle over HP-UX. UnixODBC is correctly configured, as I can use isql (the unixODBC SQL application) to retrieve data from the remote SQL Server DB. I have created an initMSSQLIISDB.ora in $ORACLE_HOME/hs/admin and I have edited listener.ora and tnsnames.ora (atached below). I restarted the listener and created the dblink to the SQL Server DB as follows:

create public database link MSSQLIISDB
connect to
identified by
using 'MSSQLIISDB';



But when I try to to use this link I get the following error:



select count(*) from SDC_Cuerpo@MSSQLIISDB

ORA-28545: error diagnosticado por Net8 al conectar a un agente
Unable to retrieve text of NETWORK/NCR message 65535

I have been trying to find out what's wrong and I had a look to HS_FDS_INST view. It is supposed that there should be a reference to the MSSQLIISDB instance. As far as I know, the service should autoregister, but I think that this is not happening. Any suggestion?

Thanks in advance and best regards.

Rene.


initMSSQLIISDB.ora


#Fichero de configuracion del servicio HS para conexion entre
#base de datos oracle y la base de datos SQL Server MSSQLIISDB
#(IISDB en serverdbeduca)
#
# HS init parameters
#

HS_FDS_CONNECT_INFO = MSSQLIISDB
HS_FDS_TRACE_LEVEL = 4
HS_FDS_TRACE_FILE_NAME = MSSQLIISDB.log
HS_LANGUAGE = SPANISH_SPAIN.WE8ISO8859P1
HS_FDS_SHAREABLE_NAME = /usr/local/unixODBC/lib/libodbc.sl
HS_AUTOREGISTER = true

#
# ODBC specific environment variables
#
set ODBCINI = /usr/local/unixODBC/etc/odbc.ini


#
# Environment variables required for the non-Oracle system
#
#set



listener.ora


# listener.ora Network Configuration File: /u/oracle/oracle/product/10.2.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /u/oracle/oracle/product/10.2.0/db_1)
(PROGRAM = extproc)
)
(SID_DESC=
(SID_NAME = gipd)
(ORACLE_HOME = /u/oracle/oracle/product/10.2.0/db_1)
)
(SID_DESC =
(SID_NAME = MSSQLIISDB)
(ORACLE_HOME = /u/oracle/oracle/product/10.2.0/db_1)
(PROGRAM = hsodbc)
)
)

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = rigel)(PORT = 1521))
)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
)
)



tnsnames.ora


# tnsnames.ora Network Configuration File: /u/oracle/oracle/product/10.2.0/db_1/
network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

GIPD =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = rigel)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = gipd)
)
)

GIPDVM =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = oracle10desavm.educacion.org)(PORT = 152
1))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = gipdvm)
)
)

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

# SID para la conexion con un odbc.
MSSQLIISDB =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST =rigel)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = MSSQLIISDB)
)
(HS=OK)
)

[dbasan]

HS_FDS_CONNECT_INFO = MSSQLIISDB
(SID_DESC =
(SID_NAME = MSSQLIISDB)
(ORACLE_HOME = /u/oracle/oracle/product/10.2.0/db_1)
(PROGRAM = hsodbc)
)
)

AFAIK, the service would not auto register, you have to configure like setting the init parameters for an instance. Make the below changes and try the connection. Check the HS trace file and turn off the trace.

HS_FDS_CONNECT_INFO ="servername;databasename"

(PROGRAM = MSSQLIISDB)

[mrsanmaru]

Hi again,

The solution dbasan provided me didn´t work, as the parameters I entered where correct, but thanks anyway. The problem was related to a library problem. I tried hsodbc in the OS command line and I got the folowwing error:

/usr/lib/dld.sl: Bad magic number for shared library: /u/oracle/oracle/product/1
0.2.0/db_1/lib/libclntsh.sl.10.1


So I investigated for a bit and find out that the problem was that I needed 32 bits oracle libraries instead of the 64 bits ones.

I set:

export SHLIB_PATH=$ORACLE_HOME/lib32

and restarted the listener.

After that I querried the dblink and I got the long-awaited data.

Now I am wondering if there will be a significant performance lost, but al least the application analyst won't be putting pressure on me for a while.

Bye.