This discussion is archived
1 2 3 Previous Next 40 Replies Latest reply: Oct 9, 2013 10:51 PM by user13573211 Go to original post RSS
  • 15. Re: ORACLE to POSTGRES db link not working
    user13573211 Newbie
    Currently Being Moderated

    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 Guru
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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 Guru
    Currently Being Moderated

    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 Guru
    Currently Being Moderated

    then check Dg4ODBC using your select.

  • 20. Re: ORACLE to POSTGRES db link not working
    user13573211 Newbie
    Currently Being Moderated

    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 Guru
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    [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 Newbie
    Currently Being Moderated

    [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 Guru
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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 Guru
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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.??

Legend

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