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

I got ” ORA-28545: error diagnosed by Net8 when connecting to an agent ”  error in Oracle database.

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

Details of error are as follows.

ORA-28545: 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.

You are using DG4MSQL to connect to SQL*Server and a connection gives the following error -



SQL> select * from emp@testlink;
select * from emp@testlink
*
Error at line1:
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 TESTLINK.


A listener log file shows the following errors -

TNS-12518: TNS:listener could not hand off client connection
TNS-12560: TNS:protocol adapter error
TNS-00530: Protocol adapter error
32-bit Windows Error: 2: No such file or directory

error diagnosed by Net8 when connecting to an agent

This ORA-28545 error is related to the 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.

The cause of this problem is that there is an ORACLE_HOME environment variable in the session starting the listener that is pointing to a 10.2 ORACLE_HOME . This is preventing the 11g listener finding the gateway executable file in the 11g directory.

The problem can be fixed by adding the full path name of the gateway executable to the listener.ora –

(PROGRAM=C:\OracleGateways\bin\dg4msql)

which allows the gateway connection to work. However, this is not the best solution for this problem as there may be future installs of other versions which will also have the same problem.

This error can also happen if the listener being used for the Gateway is in a different ORACLE_HOME from where the Gateway is installed.

For example,  the listener being used for the Gateway is run from the directory –

D:\oracle\product\12.1.0\dbhome_1\network\admin

but the Gateway is installed in –

d:\app\oracle\product\12.1.0\tghome_3

For the first cause the solution is –
The permanent and correct solution for this problem to prevent future problems with other Oracle version installs is to remove any ORACLE_HOME environment variable settings from the session starting the 11g listener.
The ORACLE_HOME parameter is not required on Windows operating systems.

When the Gateway is in a different ORACLE_HOME from the listener ORACLE_HOME then the solution is to create and use a listener from the Gateway ORACLE_HOME –

d:\app\oracle\product\12.1.0\tghome_3\network\admin

Another solution is to try Set global_names=false.

How to configure HSODBC
———————————
(This section assumes that everything is done in the Oracle user account
that starts the listener!)

In general the following things must be configured:
1) listener
2) tnsnames
3) init<SID>.ora of the hs subsystem
4) environment
5) Oracle database

1) The listener needs a new SID entry like the following:
(SID_DESC =
(SID_NAME = hsodbc)
(ORACLE_HOME = /home/oracle/server/10.2.0/)
(PROGRAM = hsodbc)
(ENVS=LD_LIBRARY_PATH=/home/oracle/server/10.2.0/lib:/home/odbc/dd/lib)
)

Please correct the ORACLE_HOME entry and the ENVS entry according to your installation.
If the odbc driver requires the foreign data store client libraries (like the Progress ODBC driver), the LD_LIBRARY_PATH must contain this library path as well:
…
(ENVS=LD_LIBRARY_PATH=/home/oracle/server/10.2.0/lib:/home/odbc/dd/lib:
/progress/dcl/lib)
…

ORACLE_HOME must point to your ORACLE_HOME directory and the ENVS string contains entries for the LD_LIBRARY_PATH.

The minimum of the LD_LIBRARY_PATH setting must contain the Oracle library and the odbc library path; both 32 bit.
HSODBC is a 32 bit libray and thus it needs a 32 bit ODBC driver.

A correct setting of the path can be verified by typing

hsodbc

then pressing <ENTER> at the console. If the LD_LIBRARY_PATH contains the correct libraries, the version number of HSODBC should be displayed.

So a listener.ora file can look like:
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = hsodbc)
(ORACLE_HOME = /home/oracle/server/10.2.0/)
(PROGRAM = hsodbc)
(ENVS=LD_LIBRARY_PATH=/home/oracle/server/10.2.0/lib:/home/odbc/dd/lib)
)
)

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = <hostname of the Oracle Server>)
(PORT = 1921))
)
)
)

The listener must be restarted (use stop and start) after changing the listener.ora file!

2) The tnsnames.ora needs an entry for the HSODBC alias:
HSODBC.DE.ORACLE.COM =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = tcp)(HOST = <hostname of the Oracle Server)(PORT = 1521))
)
(CONNECT_DATA =
(SID = hsodbc)
)
(HS=OK)
)

The domain of the tns alias can differ from the one used above (de.oracle.com), depending on the parameter in the sqlnet.ora:  NAMES.DEFAULT_DOMAIN = de.oracle.com

But the important entry is the (HS=)or (HS=OK) key word. This key word mustbe added manually and opening the Net Configuration Assistants will remove this entries from your tnsnames.ora file! The (HS=OK) parameter must be outside the SID section and specifies that this  connector uses the Oracle Heterogeneous Service Option.

After adding the tnsnames alias and restarting the listener, a connectivity check is to use tnsping <alias>.
tnsping hsodbc should come back with a successfull message.

3) init.ora of the gateway:
The SID to use HS functionality is called in this example hsodbc. There are some restrictions how to name the SID (described in the Net Administrators Guide in detail).

At this place only a short note: don’t use dots in the SID and keep it short!

The SID is also relevant for the init.ora file of the gateway. The name of the file is init<SID>.ora. In this example it is called inithsodbc.ora. The file is located at $ORACLE_HOME/hs/admin.

It should contain the following entries:
#
# HS init parameters
#
HS_FDS_CONNECT_INFO = mssql
HS_FDS_TRACE_LEVEL = off
HS_FDS_SHAREABLE_NAME = /home/odbc/dd/lib/libodbc.so
#
# ODBC specific environment variables
#
set ODBCINI=/home/odbc/dd/odbc.ini
#
# Environment variables required for the non-Oracle system
#
#set <envvar>=<value>

Short explanation of the parameters:

HS_FDS_CONNECT_INFO points to the ODBC DSN configured in PART I of this note.

HS_FDS_SHAREABLE_NAME points to the ODBC Driver Manager library at $ODBC_HOME/lib/<ODBC_Driver_MANAGER_LIB>.

For the Data Direct Technologies odbc driver the generic odbc library on Linux is called libodbc.so. This library checks the ODBC DSN configuration and loads the driver to the foreign database server. The name of this library may differ
from odbc vendor to vendor. Please check out the driver documentation to figure out the generic odbc library.

Also some ODBC driver vendors do not require an ODBC Driver Manager; then the ODBC driver library itself can be
specified here. To determine if an ODBC Driver Manager is required, please contact the ODBC driver vendor.
(As not each ODBC Driver vendor documents its ODBC Driver Manager library and the library name might differ from Driver Manager to Driver Manager a possible way to figure out the Driver Manager library name could be to check for the existence of SQLAllocConnect ODBC function within this library:

strings <library name> |grep -i sqlalloc
)

The set ODBCINI=/home/odbc/dd/odbc.ini points to the location of an odbc.ini file you want to use with this hsodbc configuration.

4) Configuring the environment:
Normally there is nothing to configure anymore. But to test the odbc connectivity for the Oracle user the following should be performed:

Set the ODBCINI and ODBC_HOME environment variable and add the $ODBC_HOME/lib directory to the $LD_LIBRARY_PATH.

(The details how to do it are described in Part I.)

Now execute as the ORACLE User (who starts the listener) the demoodbc program:

$ODBC_HOME/demo/demoodbc -uid sa -pwd sa mssql

A similar output should be generated:
DataDirect Technologies, Inc. ODBC Sample Application. will connect to data source ‘mssql’ as user ‘sa/sa’.

First Name Last Name Hire Date Salary Dept
———- ——— ——— —— —-
Tyler Bennett 1977-01-06 00:00:00.000 32000.0 D101
George Woltman 1982-07-08 00:00:00.000 53500.0 D101
Rich Holcomb 1983-01-06 00:00:00.000 49500.0 D202
Richard Potter 1986-12-04 00:00:00.000 15900.0 D101
David Motsinger 1985-05-05 00:00:00.000 19250.0 D202
Tim Sampair 1987-02-12 00:00:00.000 27000.0 D101
SQLFetch returns: SQL_NO_DATA_FOUND

5)Configuring the Oracle database
The only thing that must be done here is to create a database link: connect with the username/password that has sufficient rights to create a database link (i.e. system). The syntax is:

create [public] database link <name>
connect to <UID> identified by <pwd> using ‘<tnsalias>’;

In other words, to connect to the MS SQL Server configured in the last steps, the syntax must be:
CREATE DATABASE LINK sqlserver
CONNECT TO “sa” IDENTIFIED BY “sa” USING ‘hsodbc’;

The db link name is sqlserver. Username and password must be in double quotes, because the username and password are case sensitive. ‘hsodbc’ points to the alias in the tnsnames.ora file that calls the HS subsystem.

If everything is configured well, a select of the EMP table -created for the demoodbc program- should be successful:

select * from “EMP”@sqlserver;
…

(Side note: The EMP table at the MS SQL Server is in capital letters. Because the MS SQL Server is case sensitive the EMP table must be surrounded by double quotes). @sqlserver points to the name of the database link to the
MS SQL Server.

Do you want to learn Oracle Database for Beginners, then read the following articles.

Oracle Tutorial | Oracle Database Tutorials for Beginners ( Junior Oracle DBA )