4 Replies Latest reply on Oct 10, 2017 6:18 PM by mxallen-Oracle

    ORA-28500: connection from ORACLE to a non-Oracle system returned this message

    3438750

      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.

       

      1. 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)

      )

       

      1. 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.