1 2 3 4 Previous Next 94 Replies Latest reply: Oct 14, 2013 5:15 AM by user13573211 Go to original post RSS
      • 15. Re: Oracle to POstgresql db link not working.....
        user13573211

        does something wrong with my LISTNER file...?

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

          This listner is exactly matching with the other listner files which are working, not sure, why this lsnrctl status LISTENER_POSTGRES is not working,

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

            pwd : /oracle/product/11.2.0/dbhome/bin

            ls -lrt dg4odbc

            -rwxr-x--x 1 oracle oinstall 0 Oct  9 12:06 dg4odbc

             

            dg4odbc exists in $ORACLE_HOME only.

             

             

            PWD: /oracle/product/11.2.0/grid/bin

            [oracle@devdb00 bin]$ ls -lrt dg4odbc

            lrwxrwxrwx 1 oracle oinstall 41 Oct  8 16:25 dg4odbc -> /oracle/product/11.2.0/dbhome/bin/dg4odbc

             

            created a link, to see even now, if LISTNER works or not, but still not working.

             

            How to make listner work...??

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

              You're again mixing Oracle_Homes.

              The listener.ora file you created is located in the grid OH /oracle/product/11.2.0/grid/

              but the lsnrctl tool you try to use is from the database home:

              [oracle@devdb00 dbhome]$ lsnrctl status LISTENER_POSTGRES

               

              When you want to use lsnrctl from the database home make sure to have a listener.ora file in the network/admin directory of the database that includes the listener_prostres entries or set the TNS_ADMIn environment variable so that it points to the location of the listener.ora you configured in the GRID OH.


              - Klaus

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

                I am checking the status of LISTENER_POSTGRES in the grid/network/admin/ only, still showing the error...

                 

                /oracle/product/11.2.0/grid/network/admin

                [oracle@devdb00 admin]$ lsnrctl status LISTENER_POSTGRES

                 

                 

                LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 09-OCT-2013 23:57:28

                 

                 

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

                 

                 

                TNS-01101: Could not find service name LISTENER_POSTGRES

                 

                AS you suggested added the EXPORT variable.....

                 

                [oracle@devdb00 ~]$ cat /home/oracle/.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

                export TNS_ADMIN=/oracle/product/11.2.0/grid/network/admin

                [oracle@devdb00 ~]$

                 

                 

                [oracle@devdb00 admin]$ lsnrctl status LISTENER_POSTGRES

                 

                 

                LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 10-OCT-2013 00:18:51

                 

                 

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

                 

                 

                TNS-01101: Could not find service name LISTENER_POSTGRES

                [oracle@devdb00 admin]$

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

                  could you please respond.....

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

                    finally finally finally finally TNSPING and LISTNER working......!!!!

                     

                    i did not make any changes...

                     

                    [oracle@devdb00 admin]$ lsnrctl status LISTENER_POSTGRES

                     

                     

                    LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 10-OCT-2013 01:26:48

                     

                     

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

                     

                     

                    Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=devdb00.office.corp)(PORT=1525)))

                    STATUS of the LISTENER

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

                    Alias                     LISTENER_POSTGRES

                    Version                   TNSLSNR for Linux: Version 11.2.0.3.0 - Production

                    Start Date                08-OCT-2013 14:12:40

                    Uptime                    1 days 11 hr. 14 min. 8 sec

                    Trace Level               off

                    Security                  ON: Local OS Authentication

                    SNMP                      OFF

                    Listener Parameter File   /oracle/product/11.2.0/grid/network/admin/listener.ora

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

                    Listening Endpoints Summary...

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

                      (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=PNPKEY)))

                    Services Summary...

                    Service "postgresql" has 1 instance(s).

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

                    The command completed successfully

                    [oracle@devdb00 admin]$

                    [oracle@devdb00 admin]$ tnsping postgresql

                     

                     

                    TNS Ping Utility for Linux: Version 11.2.0.3.0 - Production on 10-OCT-2013 01:26:58

                     

                     

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

                     

                     

                    Used parameter files:

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

                     

                     

                     

                     

                    Used TNSNAMES adapter to resolve the alias

                    Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = devdb00)(PORT = 1525)) (CONNECT_DATA = (SID = postgresql)) (HS = OK))

                    OK (0 msec)

                    [oracle@devdb00 admin]$

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

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

                        2  ;

                      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>

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

                        Did you ever start the listener LISTENER_POSTGRES?

                        lsnrctl start LISTENER_POSTGRES

                        and what is the output od:

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

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

                          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

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

                            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/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

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

                              [oracle@devdb00 admin]$ lsnrctl start LISTENER_POSTGRES

                               

                               

                              LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 10-OCT-2013 01:32:56

                               

                               

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

                               

                               

                              TNS-01106: Listener using listener name LISTENER_POSTGRES has already been started

                              [oracle@devdb00 admin]$

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

                                Oracle Dr.Klaus:


                                TNSPING working, iSQL working, lsnrctl start LISTENER_POSTGRES is working...but getting the below, can you please please help me solve this....


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

                                  2  ;

                                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

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

                                  Fine.

                                  When looking again at the listener.ora file I see a mismatch in the LD_LIBRARY_PATH.

                                  It should be:

                                     

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

                                  instead of

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

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

                                    LATEST LISTENER FILE

                                     

                                    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/product/11.2.0/grid)

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

                                            (ENV = "LD_LIBRARY_PATH=/oracle/product/11.2.0/dbhome/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

                                    1 2 3 4 Previous Next