This discussion is archived
1 2 3 7 Previous Next 94 Replies Latest reply: Oct 14, 2013 3:15 AM by user13573211 RSS

Oracle to POstgresql db link not working (error diagnosed by Net8)

user13573211 Newbie
Currently Being Moderated

[oracle@devdb00 ~]$ cat /home/oracle/temp/psqlodbc-09.02.0100/test/odbc.ini

[postgresql]

Description             = psqlodbc regression test DSN

Driver                                  = /usr/lib64/psqlodbcw.so

Trace                       = Yes

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

Database                = mywlduser

Servername              = devpgres.office.corp

Username                = postgres

Password                = ********

Port                    = 5432

Protocol                = 6.4

ReadOnly                = No

RowVersioning           = No

ShowSystemTable         = No

ShowOidColumn           = No

FakeOidIndex            = No

ConnSettings            =

 

 

[oracle@devdb00 ~]$

  • 1. Re: Oracle to POstgresql db link not working.....
    user13573211 Newbie
    Currently Being Moderated

    [oracle@devdb00 ~]$ cat /home/oracle/temp/psqlodbc-09.02.0100/test/odbcinst.ini

    [psqlodbc]

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

    Driver          =  /usr/lib64/psqlodbcw.so

    Debug           = 0

    CommLog         = 1

    [oracle@devdb00 ~]$

  • 2. Re: Oracle to POstgresql db link not working.....
    user13573211 Newbie
    Currently Being Moderated

    CAT  /oracle/product/11.2.0/grid/network/admin/listener.ora

     

     

    LISTENER_POSTGRES =

      (DESCRIPTION_LIST =

        (DESCRIPTION =

          (ADDRESS = (PROTOCOL = TCP)(HOST = devdb00.office.corp)(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 = /oracle/product/11.2.0/grid/bin/dg4odbc)

            (ENV = "LD_LIBRARY_PATH=/oracle/product/11.2.0/grid/lib:/usr/lib64")

            )

    )

     

     

    #ADR_BASE_LISTENER_POSTGRES = /oracle/database

     

     

    LOGGING_LISTENER_POSTGRES = ON

    ADR_BASE_LISTENER = /oracle

    ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER = ON

    TRACE_LEVEL_LISTENER = OFF

  • 3. Re: Oracle to POstgresql db link not working.....
    user13573211 Newbie
    Currently Being Moderated

    /oracle/product/11.2.0/grid/network/admin/tnsnames.ora

     

    postgresql.office.corp =

      (DESCRIPTION =

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

       (CONNECT_DATA =

       (SID = postgresql)

        )

       (HS = OK)

            )

  • 4. Re: Oracle to POstgresql db link not working.....
    user13573211 Newbie
    Currently Being Moderated

    cat /oracle/product/11.2.0/grid/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 = DEBUG

    HS_FDS_SHAREABLE_NAME = /usr/lib64/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

     

     

     

     

    #

    # Environment variables required for the non-Oracle system

    #

     

     

    [oracle@devdb00 admin]$

  • 5. Re: Oracle to POstgresql db link not working.....
    user13573211 Newbie
    Currently Being Moderated

    [oracle@devdb00 ~]$ pwd

    /home/oracle

     

     

    [oracle@devdb00 ~]$ cat .bash_profile

    # .bash_profile

     

     

    # Get the aliases and functions

    if [ -f ~/.bashrc ]; then

            . ~/.bashrc

    fi

     

     

    # User specific environment and startup programs

     

     

    PATH=$PATH:$HOME/bin

    export ORACLE_SID=mwdev001

    export PATH

     

     

    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=/oracle/product/11.2.0/grid/lib:/usr/lib64:$LD_LIBRARY_PATH

  • 6. Re: Oracle to POstgresql db link not working.....
    user13573211 Newbie
    Currently Being Moderated

    [oracle@devdb00 ~]$ isql -v postgresql

    +---------------------------------------+

    | Connected!                            |

    |                                       |

    | sql-statement                         |

    | help [tablename]                      |

    | quit                                  |

    |                                       |

    +---------------------------------------+

    SQL>

  • 7. Re: Oracle to POstgresql db link not working.....
    user13573211 Newbie
    Currently Being Moderated

    SQL> select count(*) from "usr_acct"@pg_link;

    select count(*) from "usr_acct"@pg_link

                                    *

    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_LINK

     

     

     

     

    SQL>

  • 8. Re: Oracle to POstgresql db link not working.....
    mkirtley-Oracle Expert
    Currently Being Moderated

    Hi,

    To begin with there is a problem with your listener.ora.

    You have -

     

    SID_LIST_LISTENER_POSTGRES =

    (SID_LIST =

            (SID_DESC =

                    (SID_NAME = postgresql)

                    (ORACLE_HOME = /oracle/database/11.2.0.3)

                    (PROGRAM = /oracle/product/11.2.0/grid/bin/dg4odbc)

            (ENV = "LD_LIBRARY_PATH=/oracle/product/11.2.0/grid/lib:/usr/lib64")

            )

    )

     

    but the ORACLE_HOME directory and the path used for the PROGRAM do not match.

    Where did you install the gateway software ?
    If it was in /oracle/product/11.2.0/grid then change the listener to -

     

    SID_LIST_LISTENER_POSTGRES =

    (SID_LIST =

            (SID_DESC =

                    (SID_NAME = postgresql)

                    (ORACLE_HOME = /oracle/product/11.2.0/grid)

                    (PROGRAM =dg4odbc)

            (ENV = "LD_LIBRARY_PATH=/oracle/product/11.2.0/grid/lib:/usr/lib64")

            )

    )

     

    You do not need to add the path to the gateway excutable and doing so cause problems.
    If you installed the gateway software in /oracle/database/11.2.0.3 then you need to have a listener.ora in the directory -

    /oracle/database/11.2.0.3/network/admin

    and start the listener using the lsnrctl executable in /oracle/database/11.2.0.3/bin.

    The gateway init<sid>.ora file should then be in /oracle/database/11.2.0.3/hs/admin.

     

    Regards,

    Mike

  • 9. Re: Oracle to POstgresql db link not working.....
    user13573211 Newbie
    Currently Being Moderated

    I changed my LISTENER file, But I am still getting the same error. Please find the latest listener file as below.

     

    cat /oracle/product/11.2.0/grid/network/admin/listener.ora

     

    SID_LIST_LISTENER_POSTGRES =

    (SID_LIST =

            (SID_DESC =

                    (SID_NAME = postgresql)

                    (ORACLE_HOME = /oracle/database/11.2.0/grid)

                    (PROGRAM = dg4odbc)

            (ENV = "LD_LIBRARY_PATH=/oracle/product/11.2.0/grid/lib:/usr/lib64")

            )

    )

     

     

    #ADR_BASE_LISTENER_POSTGRES = /oracle/database

     

     

    LOGGING_LISTENER_POSTGRES = ON

    ADR_BASE_LISTENER = /oracle

    ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER = ON

    TRACE_LEVEL_LISTENER = OFF

    [oracle@devdb00 bin]$

  • 10. Re: Oracle to POstgresql db link not working.....
    user13573211 Newbie
    Currently Being Moderated

    error that I am still getting is as below.

     

    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_LINK

  • 11. Re: Oracle to POstgresql db link not working.....
    user13573211 Newbie
    Currently Being Moderated

    can some expert on heterogeneous services respond on this please...!!!!

  • 12. Re: Oracle to POstgresql db link not working.....
    kgronau Guru
    Currently Being Moderated

    The error 28545 is a configuration mismatch.

     

    Looking at the listener I wonder why you've set the ORACLE_HOME to a grid OH

                (ORACLE_HOME = /oracle/database/11.2.0/grid)

    - does your grid ORACLE_HOME really contain in the bin directory a dg4odbc executable?

     

     

    So what's the ORACLE_HOME of your database? Please replace (ORACLE_HOME = /oracle/database/11.2.0/grid) with the setting of your ORACLE_HOME belonging to the Oracle database - then STOP and START the listener. Then test DG4ODBC again and just in case it continues to fail please post the lsnrctl status LISTENER_POSTGRES and the output of tnsping postgresql.office.corp

     

    - Klaus

  • 13. Re: Oracle to POstgresql db link not working.....
    user13573211 Newbie
    Currently Being Moderated

    changed the LISTNER file like below.

     

    /oracle/product/11.2.0/grid/network/admin/listener.ora

     

    LISTENER_POSTGRES =

      (DESCRIPTION_LIST =

        (DESCRIPTION =

          (ADDRESS = (PROTOCOL = TCP)(HOST = devdb.office.corp)(PORT = 1525))

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

        )

      )

     

     

    SID_LIST_LISTENER_POSTGRES =

    (SID_LIST =

            (SID_DESC =

                    (SID_NAME = postgresql)

                    (ORACLE_HOME = /oracle/product/11.2.0/dbhome)

                    (PROGRAM = dg4odbc)

            (ENV = "LD_LIBRARY_PATH=/oracle/product/11.2.0/grid/lib:/usr/lib64")

            )

    )

     

     

    #ADR_BASE_LISTENER_POSTGRES = /oracle/database

     

     

    LOGGING_LISTENER_POSTGRES = ON

    ADR_BASE_LISTENER = /oracle

    ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER = ON

    TRACE_LEVEL_LISTENER = OFF

  • 14. Re: Oracle to POstgresql db link not working.....
    user13573211 Newbie
    Currently Being Moderated

    [oracle@devdb00 dbhome]$ tnsping postgresql.office.corp

     

     

    TNS Ping Utility for Linux: Version 11.2.0.3.0 - Production on 09-OCT-2013 22:24:15

     

     

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

     

     

    Used parameter files:

    /oracle/product/11.2.0/dbhome/network/admin/sqlnet.ora

     

     

     

     

    Used TNSNAMES adapter to resolve the alias

    Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = devdb.office.corp)(PORT = 1525)) (CONNECT_DATA = (SID = postgresql))(HS = OK))

    OK (0 msec)

    [oracle@devdb00 dbhome]$

     

     

    [oracle@devdb00 dbhome]$ lsnrctl status LISTENER_POSTGRES

     

     

    LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 09-OCT-2013 22:24:30

     

     

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

     

     

    TNS-01101: Could not find service name LISTENER_POSTGRES

1 2 3 7 Previous Next

Legend

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