1 2 3 Previous Next 94 Replies Latest reply: Oct 14, 2013 5:15 AM by user13573211 RSS

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

    user13573211

      [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

          [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

            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

              /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

                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

                  [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

                    [oracle@devdb00 ~]$ isql -v postgresql

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

                    | Connected!                            |

                    |                                       |

                    | sql-statement                         |

                    | help [tablename]                      |

                    | quit                                  |

                    |                                       |

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

                    SQL>

                    • 7. Re: Oracle to POstgresql db link not working.....
                      user13573211

                      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

                        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

                          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

                            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

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

                              • 12. Re: Oracle to POstgresql db link not working.....
                                Kgronau-Oracle

                                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

                                  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

                                    [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 Previous Next