5 Replies Latest reply: Oct 2, 2013 5:58 AM by Mkirtley-Oracle RSS

    dblink oracle to postgres with dg4odbc | ORA-12154: TNS:could not resolve the connect identifier specified

    DanielHP

      Hi, i'm trying to create database link from a database Oracle 11g to PostgreSQL with DG4ODBC, and unixODBC

       

      my configured to /etc/odbc.ini

      [PostgreSQL]

      Description = Test to Postgres

      Driver = psqlodbc

      Trace = Yes

      TraceFile = /tmp/sql.log

      Database = danieldb

      Servername =

      UserName = SA

      Password = password

      Port = 5432

      Protocol = 6.4

      ReadOnly = No

      RowVersioning = No

      ShowSystemTables = No

      ShowOidColumn = No

      FakeOidIndex = No

       

      my configured to /etc/odbcinst.ini

      [ODBC]

      CommLog=1

      Debug=1

      FileUsage=1

      Pooling=No

      Trace=1

      [psqlodbc]

      Description=PostgreSQL ODBC driver

      Driver=/usr/lib64/psqlodbcw.so

      CommLog=1

      Debug=0

      FileUsage=1

       

      my configured to /u01/app/oracle/product/11.2.0/xe/hs/admin/initPostgreSQL.ora

      HS_FDS_CONNECT_INFO = PostgreSQL

      HS_FDS_TRACE_LEVEL = 0

      HS_FDS_SHAREABLE_NAME = /usr/lib64/libodbcpsql.so

      set ODBCINI=/etc/odbc.ini

       

      my configured to /u01/app/oracle/product/11.2.0/xe/network/admin/listener.ora

      Listener =

      (ADDRESS = (PROTOCOL = TCP)(HOST = oracle-poc)(PORT = 1521))

       

       

      SID_LIST_LISTENER =

              (SID_LIST =

                      (SID_DESC=

                              (SID_NAME=PostgreSQL)

                              (ORACLE_HOME=/u01/app/oracle/product/11.2.0/xe)

                              (PROGRAM=dg4odbc)

                              (ENVS="LD_LIBRARY_PATH=/usr/lib64:/u01/app/oracle/product/11.2.0/xe/lib")

                      )

              )

       

      my configured to /u01/app/oracle/product/11.2.0/xe/network/admin/tnsname.ora

      PostgreSQL=

              (DESCRIPTION =

                      (ADDRESS = (PROTOCOL = TCP)(HOST = oracle-poc)(PORT = 1521))

                      (CONNECT_DATA =

                              (SID = PostgreSQL)

                      )

                      (HS = OK)

              )

       

      i'm try to created public database link :

      CREATE PUBLIC DATABASE LINK "orapos" CONNECT TO "SA" IDENTIFIED BY "password" USING 'PosgreSQL';

       

      when i used tnsping

      [root@oracle-poc admin]# tnsping PostgreSQL

       

       

      TNS Ping Utility for Linux: Version 11.2.0.2.0 - Production on 16-MAY-2013 20:34:19

       

       

      Copyright (c) 1997, 2011, Oracle.  All rights reserved.

       

       

      Used parameter files:

      /u01/app/oracle/product/11.2.0/xe/network/admin/sqlnet.ora

       

       

       

       

      Used TNSNAMES adapter to resolve the alias

      Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = oracle-poc)(PORT = 1521)) (CONNECT_DATA = (SID = PostgreSQL)) (HS = OK))

      OK (0 msec)

       

      and last i try to use the database link :

      SQL> select * from "tabel2"@orapos;

      select * from "tabel2"@orapos

                             *

      ERROR at line 1:

      ORA-12154: TNS:could not resolve the connect identifier specified

       

       

      Whats wrong with my configuration??

       

      Thank you and best regards,

      Daniel