ORA-28500: connection from ORACLE to a non-Oracle system returned this message
Hi Team,
I am experiencing the above issue when i tried to connect from oracle to postgresql using database link.
I have followed the below steps as part of configuring the database link:
Step 1:
As root user i have given the entries in /etc/odbc.ini file
[PG]
Debug = 1
CommLog = 0
ReadOnly = 1
Driver = /usr/pgsql-9.6/lib/psqlodbcw.so
Servername = testdb.test.com
Username = dblinkuser
Password = dblinkpass
Port = 5432
Database = test
Trace = 1
TraceFile = /tmp/sql.log
Step 2:
Below are the entries in /etc/odbcinst.ini file
# Example driver definitions
# Driver from the postgresql-odbc package
# Setup from the unixODBC package
[PostgreSQL]
Description = ODBC for PostgreSQL
Driver = /usr/pgsql-9.6/lib/psqlodbc.so
#Setup = /usr/pgsql-9.6/lib/psqlodbc.so
#Driver64 = /usr/local/unixODBC/lib/libodbc.so.2
#Setup64 = /usr/local/unixODBC/lib/libodbc.so.2
FileUsage = 1
After the above entries i checked the connectivity using isql command as shown below:
[root@ testdb etc]# isql -v PG
+---------------------------------------+
| Connected! |
| |
| sql-statement |
| help [tablename] |
| quit |
| |
+---------------------------------------+
Even , I tried the same with username and password too.
[root@testdb etc]# isql -v PG dblinkuser dblinkpass
+---------------------------------------+
| Connected! |
| |
| sql-statement |
| help [tablename] |
| quit |
| |
+---------------------------------------+
Above approaches worked fine.
Step3:
Below are the entries in the $ORALCE_HOME/hs/admin in initPG.ora file.
- initPG.ora file entries
# 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 = PG
HS_FDS_TRACE_LEVEL = 0
#HS_FDS_SHAREABLE_NAME = /usr/lib64/libodbc.so
HS_FDS_SHAREABLE_NAME = /usr/lib64/libodbc.so
HS_LANGUAGE = AMERICAN_AMERICA.AL32UTF8
#
# ODBC specific environment variables
#
set ODBCINI=/etc/odbc.ini
#
# Environment variables required for the non-Oracle system
#
#set <envvar>=<value>
***************************************************************
Step4:
Below are the entries in tnsnames.ora and listener.ora files
PG =
(DESCRIPTION=
(ADDRESS=(PROTOCOL = TCP)(HOST = testdb.test.com)(PORT = 1524))
(CONNECT_DATA=(SID = PG))
(HS=OK)
)
- Listener.ora
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = testdb.test.com)(PORT = 1524))
)
)
)
(SID_DESC =
(ORACLE_HOME = /u01/app/oracle/product/12.1.0.2.160419se2)
(SID_NAME = PG)
(PROGRAM = dg4odbc)
(ENVS="LD_LIBRARY_PATH =/usr/pgsql-9.6/lib:/u01/app/oracle/product/12.1.0.2.160419se2/lib")
)
)
Finally reloaded the listener, stopped and started the listener. It worked fine.
Service "PG" has 1 instance(s).
Instance "PG", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
Tnsping output below:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION= (ADDRESS=(PROTOCOL = TCP)(HOST = testdb.test.com)(PORT = 1524)) (CONNECT_DATA=(SID = PG)) (HS=OK))
OK (0 msec)
Step5:
Connected to ORALCE SID and created database link with the below command by connecting to the Oracle SCHEMA.
create database link PG1 connect to "dblinkuser" identified by "dblinkpass" using 'PG';
Step6:
When I tried to check the connectivity with the below sql query, experienced the below issue:
SQL> select count(*) from address@PG1;
select count(*) from address@PG1
*
ERROR at line 1:
ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
[
Could you please provide your suggestions to overcome the above issue.
I tried with multiple workarounds but no luck.
Also, please let me know if more information needed from my end.
Regards,
Vijay.
