2 Replies Latest reply: Oct 1, 2012 1:34 PM by KillyGonzales RSS

    PostgreSQL TNS-12547

    KillyGonzales
      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-Oracle
          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
            Thanks for help kgronau. Your sollution worked.