Hi,
I have Oracle heterogeneous gateways and mysql connector 5.1 installed on a Gateways server. From isql test it works fine but when query from sqlplus after a dblink was created i got error below. Please help.
Thank you very much,
SQL> select count(*) from DBASEC.LOGIN_INFO_AUDIT@mysqldbarepo;
select count(*) from DBASEC.LOGIN_INFO_AUDIT@mysqldbarepo
*
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 MYSQLDBAREPO
Here is my odbcinst.ini, odbc.ini, listener.ora, initmysqldbarepo.ora and tnsnames.ora
12cR2_home> cat /etc/odbcinst.ini
# Example driver definitions
# Driver from the postgresql-odbc package
# Setup from the unixODBC package
[PostgreSQL]
Description = ODBC for PostgreSQL
Driver = /usr/pgsql-11/lib/psqlodbc.so
#Driver = /usr/lib/psqlodbc.so
#Setup = /usr/lib/libodbcpsqlS.so
#Driver64 = /usr/lib64/psqlodbc.so
#Setup64 = /usr/lib64/libodbcpsqlS.so
FileUsage = 1
# Driver from the mysql-connector-odbc package
# Setup from the unixODBC package
[MySQL]
Description = ODBC for MySQL
#Driver = /usr/lib/libmyodbc5.so
#Setup = /usr/lib/libodbcmyS.so
Driver = /usr/lib64/libmyodbc5.so
Setup = /usr/lib64/libodbcmyS.so
FileUsage = 1
(GC92@bmw157:/users/gc92)
12cR2_home> cat /etc/odbc.ini
[pgipollnew]
Description = PostgreSQL connection to pgipollnew
Driver = /usr/pgsql-11/lib/psqlodbc.so
Database = pgipollnew
Servername = pgipollnew.domain
UserName = dblinker
Password = dblinkerPWD
Port = 5432
Protocol = 10.6
ReadOnly = No
RowVersioning = No
ShowSystemTables = No
ConnSettings =
[mysqldbarepo]
Description = Connector/ODBC 5.1 Driver
Driver = /usr/lib64/libmyodbc5.so
Database = mysqldbarepo
SERVER = mysqldbarepo.domain
PORT = 3306
USER = dblinker
Password = dblinkerPWD
OPTION = 0
SOCKET =
(ORACLE@bmw157:/u01/app/oracle/product/12.2.0/gateway/network/admin)
ODBC_Gateway> cat listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/12.2.0/db/network/admin/listener.ora
# Generated by Oracle configuration tools.
ADR_BASE_LISTENER = /u01/app/oracle/product/12.2.0/gateway
INBOUND_CONNECT_TIMEOUT_= 0
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = bmw157.domain)(PORT = 1521))
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = pgipollnew)
(ORACLE_HOME = /u01/app/oracle/product/12.2.0/gateway)
(ENVS = "LD_LIBRARY_PATH=/usr/lib64:/usr/pgsql-11/lib:/u01/app/oracle/product/12.2.0/gateway/lib")
(PROGRAM = dg4odbc)
)
(SID_DESC =
(SID_NAME = mysqldbarepo)
(ORACLE_HOME = /u01/app/oracle/product/12.2.0/gateway)
(ENVS = "LD_LIBRARY_PATH=/usr/lib64:/u01/app/oracle/product/12.2.0/gateway/lib")
(PROGRAM = dg4odbc)
)
)
(ORACLE@bmw157:/u01/app/oracle/product/12.2.0/gateway/hs/admin)
ODBC_Gateway> cat initmysqldbarepo.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 = mysqldbarepo
HS_FDS_TRACE_LEVEL = 0
HS_FDS_SHAREABLE_NAME = /usr/lib64/libodbc.so
#HS_LANGUAGE=AMERICAN_AMERICA.WE8ISO8859P1
#
# ODBC specific environment variables
#
set ODBCINI=/etc/odbc.ini
#
# Environment variables required for the non-Oracle system
#
#set <envvar>=<value>
(ORACLE@bmw156:/u01/app/oracle/product/12.2.0/db/network/admin)
boomitst> cat tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/12.2.0/db/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
LISTENER_BOOMITST =
(ADDRESS = (PROTOCOL = TCP)(HOST = bmw156.domain)(PORT = 1521))
PGIPOLLNEW =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = bmw167.domain)(PORT = 1521))
(CONNECT_DATA = (SID = pgipollnew))
(HS = OK)
)
MYSQLDBAREPO =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = bmw167.domain)(PORT = 1521))
(CONNECT_DATA = (SID = mysqldbarepo))
(HS = OK)
)
(ORACLE@bmw157:/u01/app/oracle/product/12.2.0/gateway/hs/admin)
ODBC_Gateway> lsnrctl status
LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 07-MAY-2019 11:30:51
Copyright (c) 1991, 2016, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=bmw157.domain)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 12.2.0.1.0 - Production
Start Date 07-MAY-2019 11:25:17
Uptime 0 days 0 hr. 5 min. 34 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/12.2.0/gateway/network/admin/listener.ora
Listener Log File /u01/app/oracle/product/12.2.0/gateway/diag/tnslsnr/bmw157/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=bmw157.domain)(PORT=1521)))
Services Summary...
Service "mysqldbarepo" has 1 instance(s).
Instance "mysqldbarepo", status UNKNOWN, has 1 handler(s) for this service...
Service "pgipollnew" has 1 instance(s).
Instance "pgipollnew", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
(ORACLE@bmw157:/u01/app/oracle/product/12.2.0/gateway/hs/admin)
ODBC_Gateway> isql -v mysqldbarepo
+---------------------------------------+
| Connected! |
| |
| sql-statement |
| help [tablename] |
| quit |
| |
+---------------------------------------+
SQL> select count(*) from DBASEC.LOGIN_INFO_AUDIT
+---------------------+
| count(*) |
+---------------------+
| 8469 |
+---------------------+
SQLRowCount returns 1
1 rows fetched