1 2 3 Previous Next 40 Replies Latest reply: Oct 10, 2013 12:51 AM by user13573211 RSS

    ORACLE to POSTGRES db link not working

    user13573211

      cat /home/oracle/temp/psqlodbc-09.02.0100/test/odbc.ini

       

      [postgresql]

      Description             = psqlodbc regression test DSN

      Driver                          = /home/oracle/temp/psqlodbc-09.02.0100/.libs/psqlodbcw.so

      Trace                       = Yes

      TraceFile               = /home/oracle/tmp/sql.log

      Database                = postgresdb

      Servername              = postgresdev.office.corp

      Username                = postgres

      Password                = ***********

      Port                        = 5432

      Protocol                = 6.4

      ReadOnly                = No

      RowVersioning           = No

      ShowSystemTable         = No

      ShowOidColumn           = No

      FakeOidIndex            = No

      ConnSettings            =

       

      cat /home/oracle/temp/psqlodbc-09.02.0100/test/odbcinst.ini

      [psqlodbc]

      Description     = PostgreSQL ODBC driver (Unicode version), for regression tests

      Driver          = ../.libs/psqlodbcw.so

      Debug           = 0

      CommLog         = 1

       

      cat /oracle/database/11.2.0.3/hs/admin/initdg4odbc.ora

       

      # 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 =postgresql

      HS_FDS_TRACE_LEVEL =off

      HS_FDS_SHAREABLE_NAME = /usr/lib/libodbc.so

       

       

      #

      # ODBC specific environment variables

      #

      set ODBCINI=/home/oracle/temp/psqlodbc-09.02.0100/test/odbc.ini

      set odbcinst=/home/oracle/temp/psqlodbc-09.02.0100/test/odbcinst.ini

       

       

      cat /oracle/database/11.2.0.3/network/admin/listener.ora

       

      LISTENER_POSTGRES =

        (DESCRIPTION_LIST =

          (DESCRIPTION =

            (ADDRESS = (PROTOCOL = TCP)(HOST = scfsdb.office.corp)(PORT = 1524))

          )

        )

       

       

      SID_LIST_LISTENER_POSTGRES =

      (SID_LIST =

      (SID_DESC =

      (SID_NAME = postgresql)

      (ORACLE_HOME = /oracle/database/11.2.0.3)

      (PROGRAM = hsodbc)

      (ENVS = "LD_LIBRARY_PATH=/oracle/database/11.2.0.3/lib:/oracle/database/11.2.0.3/hs/lib:/usr/lib")

      )

      (SID_DESC =

      (SID_NAME = postgresql)

      (ORACLE_HOME = /oracle/database/11.2.0.3)

      (PROGRAM = hsodbc)

       

       

      )

      )

       

       

       

       

      #ADR_BASE_LISTENER_POSTGRES = /oracle/database

       

       

      LOGGING_LISTENER_POSTGRES = OFF

       

      cat /oracle/database/11.2.0.3/network/admin/tnsnames.ora

       

      postgresql =

      (DESCRIPTION =

      (ADDRESS = (PROTOCOL = TCP)(HOST = xxx.xx.x.xx)(PORT = 1524))

      (CONNECT_DATA =

      (SID = postgresql)

      )(HS = OK)

      )

       

       

       

      This is not working, could you please let me know, where I should make the change. Thank you so much for all your help in Advance..!!! Guru's, please respond on this.

        • 1. Re: ORACLE to POSTGRES db link not working
          Kgronau-Oracle

          in Oracle 11 the executable for the gateway configured in the listener.ora  is called dg4odbc, not hsodbc. So please correct:

          (PROGRAM = hsodbc) to (PROGRAM = dg4odbc)

          Once done, please stop and start the listener LISTENER_POSTGRES

           

          - Klaus

          • 2. Re: ORACLE to POSTGRES db link not working
            user13573211

            Thank you sooo much KGRONAU..i am waiting for you only..i read many of your earlier replies...in fact  i have already changed that .and no issues with listener....

            But isql is not working...please help me to make the ISQL working...

             

            [oracle@scfxxxx001 psqlodbc-09.02.0100]$ isql postgresql

            [ISQL]ERROR: Could not SQLConnect

             

            new listener file::

            SID_LIST_LISTENER_POSTGRES =

            (SID_LIST =

              (SID_DESC =

              (SID_NAME = postgresql)

              (ORACLE_HOME = /oracle/database/11.2.0.3)

              (PROGRAM = /oracle/database/11.2.0.3/hs/lib/dg4odbc.o)

              (ENVS = "LD_LIBRARY_PATH=/oracle/database/11.2.0.3/lib:/oracle/database/11.2.0.3/hs/lib:/usr/lib")

              )

            )

             

             

            #ADR_BASE_LISTENER_POSTGRES = /oracle/database

             

             

            LOGGING_LISTENER_POSTGRES = ON

            • 3. Re: ORACLE to POSTGRES db link not working
              Kgronau-Oracle

              First of all the listener is still not correct, just use (PROGRAM=dg4odbc) and stop and start the listener. The listener will then automatically check out the $ORACLE_HOME/bin directory and load the dg4odbc executable.

               

               

               

              Regarding isql, please provide the env you have set before starting isql and then use isql -v so that we get more details:

              1. type: env

              2. type: isql -v [postgresql

              Provide the output of each command.

              - Klaus

              • 4. Re: ORACLE to POSTGRES db link not working
                user13573211

                Thank you sooooo much again sir...Changed the listener as below and it is working


                LISTENER_POSTGRES =

                  (DESCRIPTION_LIST =

                    (DESCRIPTION =

                      (ADDRESS = (PROTOCOL = TCP)(HOST = scxxxx001)(PORT = 1525))

                   (ADDRESS = (PROTOCOL = IPC)(KEY = PNPKEY))

                    )

                  )

                 

                 

                SID_LIST_LISTENER_POSTGRES =

                (SID_LIST =

                  (SID_DESC =

                  (SID_NAME = postgresql)

                  (ORACLE_HOME = /oracle/database/11.2.0.3)

                  (PROGRAM = dg4odbc)

                  (ENVS = "LD_LIBRARY_PATH=/oracle/database/11.2.0.3/lib:/oracle/database/11.2.0.3/hs/lib:/usr/lib")

                  )

                )

                 

                 

                #ADR_BASE_LISTENER_POSTGRES = /oracle/database

                 

                 

                LOGGING_LISTENER_POSTGRES = ON

                • 5. Re: ORACLE to POSTGRES db link not working
                  user13573211

                  i did not set up any ENV, except some thing in LISTENER file, could you please review the below and suggest, what I should do...eagerly waiting for your reply..many thanks...

                   

                  [oracle@xxxxxx admin]$ env

                  HOSTNAME=scflsdb001

                  TERM=vt100

                  SHELL=/bin/bash

                  HISTSIZE=1000

                  NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P1

                  SSH_CLIENT=172.17.200.79 60780 22

                  SSH_TTY=/dev/pts/4

                  USER=oracle

                  LD_LIBRARY_PATH=/oracle/database/11.2.0.3/lib

                  LS_COLORS=no=00:fi=00:di=01;34:ln=01;36:pi=40;33:so=01;35:bd=40;33;01:cd=40;33;01:or=01;05;37;41:mi=01;05;37;41:ex=01;32:*.cmd=01;32:*.exe=01;32:*.com=01;32:*.btm=01;32:*.bat=01;32:*.sh=01;32:*.csh=01;32:*.tar=01;31:*.tgz=01;31:*.arj=01;31:*.taz=01;31:*.lzh=01;31:*.zip=01;31:*.z=01;31:*.Z=01;31:*.gz=01;31:*.bz2=01;31:*.bz=01;31:*.tz=01;31:*.rpm=01;31:*.cpio=01;31:*.jpg=01;35:*.gif=01;35:*.bmp=01;35:*.xbm=01;35:*.xpm=01;35:*.png=01;35:*.tif=01;35:

                  ORACLE_SID=mywlddv2

                  ORACLE_BASE=/oracle/database

                  LIBPATH=/oracle/database/11.2.0.3/lib

                  MAIL=/var/spool/mail/oracle

                  PATH=/oracle/database/11.2.0.3/bin:/usr/kerberos/bin:/usr/local/bin:/bin:/usr/bin:/home/oracle/bin

                  INPUTRC=/etc/inputrc

                  PWD=/oracle/database/11.2.0.3/network/admin

                  LANG=en_US.UTF-8

                  SSH_ASKPASS=/usr/libexec/openssh/gnome-ssh-askpass

                  SHLVL=1

                  HOME=/home/oracle

                  LOGNAME=oracle

                  SSH_CONNECTION=172.17.200.79 60780 172.17.1.80 22

                  LESSOPEN=|/usr/bin/lesspipe.sh %s

                  ORA_NLS33=/oracle/database/11.2.0.3/ocommon/nls/admin/data

                  ORACLE_HOME=/oracle/database/11.2.0.3

                  NLS_DATE_FORMAT=DD-MON-YYYY

                  G_BROKEN_FILENAMES=1

                  _=/bin/env

                  OLDPWD=/oracle/database/11.2.0.3/hs/admin

                   

                   

                  --------------------------------------------------------------------------

                   

                   

                  [oracle@xxxxxxxx admin]$ isql -v postgresql

                  [IM002][unixODBC][Driver Manager]Data source name not found, and no default driver specified

                  [ISQL]ERROR: Could not SQLConnect

                  • 6. Re: ORACLE to POSTGRES db link not working
                    Kgronau-Oracle

                    The error message is clear - isql can't find the ODBC DSN and the root cause is because you didn't set the ODBCINI environment variable.

                     

                    isql needs to know the ODBC details,like the ODBC parameter ODBCINI which contains your ODBC DSN.

                    So before calling isql set the env:

                    export ODBCINI=/home/oracle/temp/psqlodbc-09.02.0100/test/odbc.ini

                    export ODBCINSTINI=/home/oracle/temp/psqlodbc-09.02.0100/test/odbcinst.ini

                    export LD_LIBRARY_PATH=/home/oracle/temp/psqlodbc-09.02.0100/libs:/usr/lib/:$LD_LIBRARY_PATH

                    SIDE NOTE: Please crosscheck that the patches I specified above match your setting.

                     

                    Now call again isql: isql -v postgresql

                    • 7. Re: ORACLE to POSTGRES db link not working
                      Kgronau-Oracle

                      when you can connect with isql successfully you can check out DG4ODBC.

                      First I would check the word size of the ODBC Driver, the Driver manager and DG4ODBC. They all should match. Use these commands:

                      file /usr/lib/libodbc.so

                      file  /home/oracle/temp/psqlodbc-09.02.0100/libs/psqlodbcw.so

                      file /oracle/database/11.2.0.3/bin/dg4odbc.

                       

                      When they match, then correct again the listener.ora, this time the LD_LIBRARY_PATH to:

                        (ENVS = "LD_LIBRARY_PATH=/oracle/database/11.2.0.3/lib:/home/oracle/temp/psqlodbc-09.02.0100/libs:/usr/lib")

                       

                      Then stop and start the listener and give DG4ODBC a try

                      • 8. Re: ORACLE to POSTGRES db link not working
                        user13573211

                        many many thanks Kgronau for your reply. I did all the steps as you suggested, some where I am missing. Could you PLEASE help me to solve the problem.

                         

                        Error that I am getting as below:

                         

                        SQL> select *from temp@postgresql;

                        select *from temp@postgresql

                                          *

                        ERROR at line 1:

                        ORA-02019: connection description for remote database not found

                         

                        Please see the outputs of the  file commands :

                         

                        [oracle@scflsdb001 lib]$ file /usr/lib/libodbc.so

                        /usr/lib/libodbc.so: symbolic link to `libodbc.so.1.0.0'

                        [oracle@scflsdb001 lib]$ file /usr/lib/libodbc.so.1.0.0

                        /usr/lib/libodbc.so.1.0.0: ELF 32-bit LSB shared object, Intel 80386, version 1 (SYSV), stripped

                        [oracle@scflsdb001 lib]$ file  /home/oracle/temp/psqlodbc-09.02.0100/.libs/psqlodbcw.so

                        /home/oracle/temp/psqlodbc-09.02.0100/.libs/psqlodbcw.so: ELF 64-bit LSB shared object, AMD x86-64, version 1 (SYSV), not stripped

                        [oracle@scflsdb001 lib]$ file /oracle/database/11.2.0.3/bin/dg4odbc

                        /oracle/database/11.2.0.3/bin/dg4odbc: ELF 64-bit LSB executable, AMD x86-64, version 1 (SYSV), for GNU/Linux 2.6.9, dynamically linked (uses shared libs), not stripped

                         

                        Updated the listener as suggested:

                         

                        LISTENER_POSTGRES =

                          (DESCRIPTION_LIST =

                            (DESCRIPTION =

                              (ADDRESS = (PROTOCOL = TCP)(HOST = scflsdb001)(PORT = 1525))

                           (ADDRESS = (PROTOCOL = IPC)(KEY = PNPKEY))

                            )

                          )

                         

                         

                        SID_LIST_LISTENER_POSTGRES =

                        (SID_LIST =

                          (SID_DESC =

                          (SID_NAME = postgresql)

                          (ORACLE_HOME = /oracle/database/11.2.0.3)

                          (PROGRAM = dg4odbc)

                          (ENVS = "LD_LIBRARY_PATH=/oracle/database/11.2.0.3/lib:/home/oracle/temp/psqlodbc-09.02.0100/.libs:/usr/lib")

                          )

                        )

                         

                         

                        #ADR_BASE_LISTENER_POSTGRES = /oracle/database

                         

                         

                        LOGGING_LISTENER_POSTGRES = ON

                         

                        I am little bit doubtful about my TNS file also, could you please check this also:

                         

                        LISTENER_POSTGRES.OFFICE.CORP =

                                (DESCRIPTION =

                                        (ADDRESS = (PROTOCOL = TCP)(HOST = postgresDEV.office.corp)(PORT = 5432))

                                        (CONNECT_DATA =

                                                (SID = postgresql)

                                        )(HS = OK)

                                )

                        • 9. Re: ORACLE to POSTGRES db link not working
                          Kgronau-Oracle

                          There are 2 issues. First the error message you get:

                          ORA-02019: connection description for remote database not found just means that you did not create a database link called postgresql in the Oracle database. The syntax would be:

                          create <public> database link postgresql connect to "<PostGres UID>" identified by <PostGres PWD>" using 'postgresql';

                           

                          But even when you have created the database link there is a major issue with your libs.

                          Check out the file output - DG4ODBC and PostgreSQL ODBC driver report 64bit libs but your unixODBC Driver manager is still a 32bit application. That does not work. Please get a 64bit Driver Manager (for example from www.unixODBC.ora and compile the source code from scratch).

                          • 10. Re: ORACLE to POSTGRES db link not working
                            user13573211

                            Thank you so much Mr.Kgronau for your reply.

                             

                            1. I have created a DB Link earlier but used a different name in the query, but now I used the right one, and I have got the below error. Could you please let me know, if my TNS Names.ora is not correct.

                            SQL> select *from "temp"@pg_link;

                            select *from "temp"@pg_link

                                                *

                            ERROR at line 1:

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

                             

                             

                             

                            2. Should I have to do all this set-up again from scratch. Or Should I have to download 64 bit driver from, www.unixODBC.ora, and install only that..?? And once if I am done downloading that and making sure that, newly installed driver is 64(using file command).. Should the above error(ORA-12154) dis-appear.

                            Is it enough if I just chose a driver which is 64bit, or should I have to make sure any other issues..??


                            Many Many Many Thanks..!!! I wish I would have knowledge like one day..!

                            • 11. Re: ORACLE to POSTGRES db link not working
                              Kgronau-Oracle

                              The error ORA-12154: TNS:could not resolve the connect identifier specified means that the TNS alias you specified in your database using clause is not found in the database tnsnames.ora file.

                               

                              At the beginning you posted a tnsnames.ora file with the entry

                              postgresql =

                                (DESCRIPTION =

                                (ADDRESS = (PROTOCOL = TCP)(HOST = xxx.xx.x.xx)(PORT = 1524))

                                (CONNECT_DATA =

                                 (SID = postgresql))

                                 (HS = OK)

                              )

                              Make sure that this is the tnsnames.ora file used by the Oracle database and then verify the db link if you created it with the tns alias postgresql in the using clause:

                              create <public> database link pg_link; connect to "<PostGres UID>" identified by <PostGres PWD>" using 'postgresql';

                               

                              Regarding the ODBC Driver Manager. You only have to install the 64bit ODBC Driver Manager and then change the LD_LIBRARY_PATH setting to the location where the 64bit ODBC Driver Manager is located. In addition in the gateway init file refer to the new, 64bit libodbc.so file.

                               

                              - Klaus

                              • 12. Re: ORACLE to POSTGRES db link not working
                                user13573211

                                Hello Sir:

                                 

                                Please see the below latest TNS entry. In the HOST section of the TNS file, should I have to provide the postgres HOST name, or ORACLE host name.

                                 

                                postgresql =

                                  (DESCRIPTION =

                                  (ADDRESS = (PROTOCOL = TCP)(HOST = devpgres1001.office.corp)(PORT = 5432))

                                  (CONNECT_DATA =

                                  (SID = postgresql)

                                  )(HS = OK)

                                  )

                                 

                                 

                                I have created DB link properly. Please see the below, but I am still getting the same TNS error.

                                 

                                SQL> create public database link pg_link1 connect to "postgres" identified by "*********" using 'postgresql';

                                 

                                 

                                Database link created.

                                 

                                 

                                SQL> select * from "temp"@pg_link1;

                                select * from "temp"@pg_link1

                                                     *

                                ERROR at line 1:

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

                                 

                                 

                                 

                                 

                                SQL>

                                 

                                Sorry for giving you trouble, every second of your time, would be very much appreciated, and I make sure to add your score as much as I can.

                                 

                                Thank you Again.

                                • 13. Re: ORACLE to POSTGRES db link not working
                                  Kgronau-Oracle

                                  12154 still means the alias 'postgresql' is not found in your tnsnames.ora. You first have to make sure that the Oracle database tnsnames,ora file has this entry present.

                                  Easy check is to log into the machine using the Oracle account and to soucre the database environment, Then use tnsping to check: tnsping postgresql


                                  Regarding the host and the port - the information in the tnsnames.ora must be the host & port of the Oracle Listener that spawns the gateway connection. Oracle database uses SQL*Net so it needs to talk to an Oracle Listener and the listener then starts the DG4ODBC process. DG4ODBC will then load the ODBC driver which connects to the foreign database.



                                  • 14. Re: ORACLE to POSTGRES db link not working
                                    user13573211

                                    Can you please help me to resolve this TNSPING thing.  I have copied LISTENER file and TNS file as it is here, but still TNSPING postgresql

                                    I tried multiple options, but no one worked.

                                     

                                    Location:/oracle/database/11.2.0.3/network/admin

                                     

                                     

                                    File Name: tnsnames.ora

                                     

                                     

                                     

                                    postgresql =

                                            (DESCRIPTION =

                                                    (ADDRESS = (PROTOCOL = TCP)(HOST = scflsdb001)(PORT = 1525))

                                                    (CONNECT_DATA =

                                                            (SID = postgresql)

                                                    )(HS = OK)

                                            )

                                     

                                     

                                     

                                    File Name: listener.ora

                                     

                                    LISTENER_POSTGRES =

                                      (DESCRIPTION_LIST =

                                        (DESCRIPTION =

                                          (ADDRESS = (PROTOCOL = TCP)(HOST = scflsdb001)(PORT = 1525))

                                              (ADDRESS = (PROTOCOL = IPC)(KEY = PNPKEY))

                                        )

                                      )

                                     

                                     

                                    SID_LIST_LISTENER_POSTGRES =

                                    (SID_LIST =

                                            (SID_DESC =

                                                    (SID_NAME = postgresql)

                                                    (ORACLE_HOME = /oracle/database/11.2.0.3)

                                                    (PROGRAM = dg4odbc)

                                                    (ENVS = "LD_LIBRARY_PATH=/oracle/database/11.2.0.3/lib:/home/oracle/temp/psqlodbc-09.02.0100/.libs:/usr/lib64")

                                            )

                                    )

                                     

                                     

                                    #ADR_BASE_LISTENER_POSTGRES = /oracle/database

                                     

                                     

                                    LOGGING_LISTENER_POSTGRES = ON

                                     

                                     

                                     

                                    STEP : FAILED

                                     

                                    [oracle@scflsdb001 admin]$ tnsping postgresql

                                     

                                     

                                    TNS Ping Utility for Linux: Version 11.2.0.3.0 - Production on 27-SEP-2013 02:28:04

                                     

                                     

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

                                     

                                     

                                    Used parameter files:

                                    /oracle/database/11.2.0.3/network/admin/sqlnet.ora

                                     

                                     

                                    TNS-03505: Failed to resolve name

                                    1 2 3 Previous Next