1 2 3 Previous Next 40 Replies Latest reply: Oct 10, 2013 12:51 AM by user13573211 Go to original post RSS
      • 15. Re: ORACLE to POSTGRES db link not working
        user13573211

        Installed the UNIX ODBC64 version.

         

        After that steps followed:

         

        1. Changed the .bash_profile file like below.

        export LD_LIBRARY_PATH=/oracle/database/11.2.0.3/lib:/oracle/database/11.2.0.3/hs/lib:/usr/lib64:$LD_LIBRARY_PATH

         

        2. Changed the oracle gateway file like below:

        HS_FDS_SHAREABLE_NAME = /usr/lib64/libodbc.so

         

        3. Made sure that "file" commands of all the libs are matcing.

         

        4. Changed a line in LISTENER file like below:

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


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


        I have strictly followed all your guidelines, could you PLEASE help me to move forward with a successful connection Mr.Kgronau...!!

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

          can you please post also the content of the file:

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

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

            Content of the sqlnet.ora

             

            [oracle@scflsdb001 ~]$ cat /oracle/database/11.2.0.3/network/admin/sqlnet.ora

            # sqlnet.ora Network Configuration File: /oracle/11g/network/admin/sqlnet.ora

            # Generated by Oracle configuration tools.

             

             

            NAMES.DEFAULT_DOMAIN = office.corp

             

             

            ##SQLNET.AUTHENTICATION_SERVICES= (ALL)

             

             

            NAMES.DIRECTORY_PATH= (TNSNAMES, ONAMES, HOSTNAME)

             

             

            #ADR_BASE = /oracle/database

             

             

            [oracle@scflsdb001 ~]$

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

              You have set NAMES.DEFAULT_DOMAIN = office.corp

              So the alias in the tnsnames.ora must be called "postgresql.office.corp".

               

              Change again your tnsnames.ora file to:

              postgresql.office.corp =

                      (DESCRIPTION =

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

                              (CONNECT_DATA =

                                      (SID = postgresql)

                              )(HS = OK)

                      )

              Then check again tnsping:

              tnsping postgresql.office.corp

              tnsping postgresql

               

              Both should return a successful message.

               

              Then check out DG4ODBC and test another select.

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

                then check Dg4ODBC using your select.

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

                  When I run the query, I have got the some other error...hopefully this is the last error Mr.

                   

                  SQL> select * from "shopping_list"@pg_link1;

                  select * from "shopping_list"@pg_link1

                                                *

                  ERROR at line 1:

                  ORA-28545: error diagnosed by Net8 when connecting to an agent

                  Unable to retrieve text of NETWORK/NCR message 65535

                  ORA-02063: preceding 2 lines from PG_LINK1

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

                    28545 indicates a configuration issue.

                    Please post the listener status:

                    lsnrctl status LISTENER_POSTGRES

                    and the output of: /oracle/database/11.2.0.3/bin/dg4odbc

                    (both commands are executed from the shell).

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

                      [oracle@scflsdb001 admin]$ lsnrctl status LISTENER_POSTGRES

                       

                       

                      LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 27-SEP-2013 03:16:52

                       

                       

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

                       

                       

                      Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=scflsdb001)(PORT=1525)))

                      STATUS of the LISTENER

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

                      Alias                     listener_postgres

                      Version                   TNSLSNR for Linux: Version 11.2.0.3.0 - Production

                      Start Date                25-SEP-2013 16:44:35

                      Uptime                    1 days 10 hr. 32 min. 17 sec

                      Trace Level               off

                      Security                  ON: Local OS Authentication

                      SNMP                      OFF

                      Listener Parameter File   /oracle/database/11.2.0.3/network/admin/listener.ora

                      Listener Log File         /oracle/database/diag/tnslsnr/scflsdb001/listener_postgres/alert/log.xml

                      Listening Endpoints Summary...

                        (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=scflsdb001)(PORT=1525)))

                      Services Summary...

                      Service "postgresql" has 1 instance(s).

                        Instance "postgresql", status UNKNOWN, has 1 handler(s) for this service...

                      The command completed successfully

                      [oracle@scflsdb001 admin]$

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

                        [oracle@scflsdb001 ~]$ /oracle/database/11.2.0.3/bin/dg4odbc

                         

                         

                         

                         

                        Oracle Corporation --- FRIDAY    SEP 27 2013 03:19:24.888

                         

                         

                        Heterogeneous Agent Release 11.2.0.3.0 - 64bit Production  Built with

                           Oracle Database Gateway for ODBC

                         

                         

                         

                         

                         

                        [oracle@scflsdb001 ~]$

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

                          the listener is up for 1 day although you changed the listener.ora file recently and modified the LD_LIBRARY_PATH. That won't work. As we have to stop and start the listner, let's also change the parameter ENVS to the new syntax ENV.

                          So the line looks like:

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

                          Once done, stop and start the listener:

                          lsnrctl stop LISTENER_POSTGRES

                          lsnrctl start LISTENER_POSTGRES

                           

                          The modification is just to follow the new syntax and to avoid issues with the netca just in case you will ever call it.

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

                            STATUS of the LISTENER

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

                            Alias                     LISTENER_POSTGRES

                            Version                   TNSLSNR for Linux: Version 11.2.0.3.0 - Production

                            Start Date                27-SEP-2013 03:38:55

                            Uptime                    0 days 0 hr. 0 min. 0 sec

                            Trace Level               off

                             

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

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

                             

                            ERROR AGAIN:

                             

                             

                             

                            SQL> select * from "shopping_list"@pg_link;

                            select * from "shopping_list"@pg_link

                                                          *

                            ERROR at line 1:

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

                            ORA-02063: preceding line from PG_LINK

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

                              ERROR AGAIN:

                               

                               

                               

                              SQL> select * from "shopping_list"@pg_link;

                              select * from "shopping_list"@pg_link

                                                            *

                              ERROR at line 1:

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

                              ORA-02063: preceding line from PG_LINK

                               

                               

                               

                               

                              SQL>

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

                                OK - the generic configuration (SQL*Net) is now working. ORA-28500 is a gateway related error message. Let's enable gateway tracing by setting in the gateway init file

                                HS_FDS_TRACE_LEVEL=DEBUG

                                then open a new SQLPlus session and execute again your select "select * from "shopping_list"@pg_link;". Once you get the error again, please check out the ORACLE_HOME/hs/log directory. It shpuld contain a trace file with the current timestamp. Please attach it to the thread or upload it to a public file server like DropBox and provide the link.

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

                                  ERROR is like below, pretty much in every TRACE file::

                                   

                                  Unable to open init file initpostgresql.ora in directory /oracle/database/11.2.0.3/hs/admin

                                   

                                   

                                  HS Gateway:  NULL connection context at exit

                                   

                                  I don't think I have specified initpostgresql.ora any where...?? Could it be issue with ODBC.INI file OR odbcinst.ini files.??