This discussion is archived
2 Replies Latest reply: Oct 1, 2012 11:34 AM by KillyGonzales RSS

PostgreSQL TNS-12547

KillyGonzales Newbie
Currently Being Moderated
Hi,
I've problem with connecting from 11g to PostgreSQL database.
First I configure initpostgresql.ora
 HS_FDS_CONNECT_INFO = postgresql
 HS_FDS_TRACE_LEVEL = 4
 HS_FDS_TRACE_FILE_NAME = /u01/app/oracle/product/11.1.0/db_1/hs/log/hsodbc.trc
 #HS_FDS_TRACE_FILE_NAME = /tmp/oracle_trace_host_143
 HS_FDS_SHAREABLE_NAME = /usr/lib64/libodbc.so

 HS_FDS_SUPPORT_STATISTICS = FALSE
 #
 # ODBC specific environment variables
 #
 set ODBCINI=/etc/odbc.ini
then listener.ora
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = VBoxCentOS)(PORT = 1521))
    )
  )

ADR_BASE_LISTENER = /u01/app/oracle

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME =/u01/app/oracle/product/11.1.0/db_1)
      (PROGRAM = extproc)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = orcl)
      (ORACLE_HOME = /u01/app/oracle/product/11.1.0/db_1)
      (SID_NAME = orcl)
    )
   (SID_DESC=
      (SID_NAME=postgresql)
      (ORACLE_HOME=/u01/app/oracle/product/11.1.0/db_1)
      (PROGRAM=dg4odbc)
      (ENV="LD_LIBRARY_PATH=/usr/lib64:/u01/app/oracle/product/11.1.0/db_1/lib")
    )
  )
and tnsnames.ora
pgsql = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = vboxxp)(PORT = 5432) (CONNECT_DATA = (SID = postgresql))(HS = OK)))
after listener restart when I try tnsping pgsql I got message

Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = vboxxp)(PORT = 5432) (CONNECT_DATA = (SID = postgresql))(HS = OK)))
TNS-12547: TNS:lost contact

Can somebody tell me what might be wrong with this configuration?
  • 1. Re: PostgreSQL TNS-12547
    kgronau Guru
    Currently Being Moderated
    The Oracle database needs to talk to the Oracle Listener that spawns the gateway process.

    You have configured the Oracle Gateway listener to listen on (HOST = VBoxCentOS)(PORT = 1521)), but in your tnsnames.ora you connect directly to the PostGres database:
    pgsql = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = vboxxp)(PORT = 5432)...
    In addition some brackets are incorrectly placed.

    So please correct the tnsnames.ora entry to:
    pgsql = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP) (HOST = VBoxCentOS)(PORT = 1521)) (CONNECT_DATA = (SID = postgresql))(HS = OK))
  • 2. Re: PostgreSQL TNS-12547
    KillyGonzales Newbie
    Currently Being Moderated
    Thanks for help kgronau. Your sollution worked.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points