8 Replies Latest reply: Jul 24, 2013 4:29 AM by ManishS137 Branched to a new discussion. RSS

    Hetrogeneous link Error

    ManishS137

      Hi All,

       

      I have created a Heterogeneous link on windows ,

      I am able to tnsping the SQL server DNS.

       

      but when I am selecting table from SQL server Table ,

       

      SQL> select * from  sysusers@SODBL

        2  ;

      select * from  sysusers@SODBL

                              *

      ERROR at line 1:

      ORA-12518: TNS:listener could not hand off client connection

       

       

      ==========================================================Error in alert file===========================================================================

      Fatal NI connect error 12518, connecting to:

      (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.60.146)(PORT=1522))(CONNECT_DATA=(SID=SQLSRVRDBLNK)(HS=OK)(CID=(PROGRAM=c:\oraclexe\app\oracle\product\11.2.0\server\bin\ORACLE.EXE)(HOST=TESTENVIROMENT)(USER=Administrator))))

       

        VERSION INFORMATION:

          TNS for 32-bit Windows: Version 11.2.0.2.0 - Production

          Oracle Bequeath NT Protocol Adapter for 32-bit Windows: Version 11.2.0.2.0 - Production

          Windows NT TCP/IP NT Protocol Adapter for 32-bit Windows: Version 11.2.0.2.0 - Production

        Time: 23-JUL-2013 13:29:07

        Tracing not turned on.

        Tns error struct:

          ns main err code: 12564

         

      TNS-12564: TNS:connection refused

          ns secondary err code: 0

          nt main err code: 0

          nt secondary err code: 0

          nt OS err code: 0

      =======================================================================================================================================================

       

      Please suggest me way out ,

      Thanks ,

       

      Manish

        • 1. Re: Hetrogeneous link Error
          Kgronau-Oracle

          Hi Mansih,

          please post your listener.ora file and the listener status of the listener you configured to spawn the gateway connection.

          Thanks,

          Klaus

          • 2. Re: Hetrogeneous link Error
            Mkirtley-Oracle

            Manish,

                 Could you also load the tnsnames.ora you used to create the database link ?

             

            Regards,

            Mike

            • 3. Re: Hetrogeneous link Error
              ManishS137

              Hi Klaus ,

               

              My Listenr file is below

               

              SID_LIST_LISTENER =

                (SID_LIST =

                  (SID_DESC =

                    (SID_NAME = PLSExtProc)

                    (ORACLE_HOME = C:\oraclexe\app\oracle\product\11.2.0\server)

                    (PROGRAM = extproc)

                  )

                  (SID_DESC =

                    (SID_NAME = CLRExtProc)

                    (ORACLE_HOME = C:\oraclexe\app\oracle\product\11.2.0\server)

                    (PROGRAM = extproc)

                  )

                  (SID_DESC =

                     (SID_NAME = SQLSRVRDBLNK)

                     (ORACLE_HOME = C:\oraclexe\app\oracle\product\11.2.0\server)

                     (PROGRAM = hsodbc)

                     (ENVS = "EXTPROC_DLLS=ONLY:C:\oraclexe\app\oracle\product\11.2.0\server\bin\OraClr11.dll")

                    )

                 )

                

              LISTENER =

                (DESCRIPTION_LIST =

                  (DESCRIPTION =

                   (ADDRESS_LIST=

                    (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))

                    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.60.146)(PORT = 1521))

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

                    (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.60.146)(PORT=1522)))

                  )

                )

               

              -manish

              • 4. Re: Hetrogeneous link Error
                ManishS137

                my TNS ENTRY

                 

                 

                 

                SQLSRVRDBLNK =(DESCRIPTION=(ADDRESS=  (PROTOCOL= TCP)(HOST=192.168.60.146)(PORT=1522))(CONNECT_DATA=(SID=SQLSRVRDBLNK)(HS=OK)))

                 

                DB LINK CREATION SCRIPT :

                create public database link SODBL connect to "SA" identified by  "SA" using  'SQLSRVRDBLNK';

                 

                =================================================================================================================

                 

                LSNRCTL STATUS

                 

                 

                C:\Documents and Settings\Administrator>lsnrctl

                 

                LSNRCTL for 32-bit Windows: Version 11.2.0.2.0 - Production on 24-JUL-2013 12:18:45

                 

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

                 

                Welcome to LSNRCTL, type "help" for information.

                 

                LSNRCTL> status

                Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))

                STATUS of the LISTENER

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

                Alias                     LISTENER

                Version                   TNSLSNR for 32-bit Windows: Version 11.2.0.2.0 - Production

                Start Date                24-JUL-2013 11:54:55

                Uptime                    0 days 0 hr. 23 min. 55 sec

                Trace Level               off

                Security                  ON: Local OS Authentication

                SNMP                      OFF

                Listener Parameter File   C:\oraclexe\app\oracle\product\11.2.0\server\network\admin\listener.ora

                Listener Log File         C:\oraclexe\app\oracle\diag\tnslsnr\testenviroment\listener\alert\log.xml

                Listening Endpoints Summary...

                  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1ipc)))

                  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.60.146)(PORT=1521)))

                  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\PNPKEYipc)))

                  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.60.146)(PORT=1522)))

                  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=testenviroment)(PORT=8080))(Presentation=HTTP)(Session=RAW))

                Services Summary...

                Service "CLRExtProc" has 1 instance(s).

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

                Service "PLSExtProc" has 1 instance(s).

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

                Service "SQLSRVRDBLNK" has 1 instance(s).

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

                Service "XEXDB" has 1 instance(s).

                  Instance "xe", status READY, has 1 handler(s) for this service...

                Service "xe" has 1 instance(s).

                  Instance "xe", status READY, has 1 handler(s) for this service...

                The command completed successfully

                LSNRCTL>

                 

                =======================================================================================================

                 

                My Oracle(XE) and SQl(server 2005) server on same machine , Windows XP ,

                 

                 

                -thanks

                Manish

                • 5. Re: Hetrogeneous link Error
                  Kgronau-Oracle

                  Manish,

                  in 11g the executable is called dg4odbc, not hsodbc. Also please remove the ENVS parameter, it is not needed for listener.ora

                   

                  So please change your listener.ora file SID

                      (SID_DESC =

                         (SID_NAME = SQLSRVRDBLNK)

                         (ORACLE_HOME = C:\oraclexe\app\oracle\product\11.2.0\server)

                         (PROGRAM = dg4odbc)

                        )

                     )

                   

                  Once done, please stop and start the listener.

                   

                  - Klaus

                  • 6. Re: Hetrogeneous link Error
                    ManishS137

                    @mike , please find tnsnames file in above 

                    • 7. Re: Hetrogeneous link Error
                      Kgronau-Oracle

                      There's an issue with the brackets...

                      You have:

                      SQLSRVRDBLNK =(DESCRIPTION=(ADDRESS=  (PROTOCOL= TCP)(HOST=192.168.60.146)(PORT=1522))(CONNECT_DATA=(SID=SQLSRVRDBLNK)(HS=OK)))

                      But (HS=OK) needs to be outside of the Connect Data section, so please change it to:

                      SQLSRVRDBLNK =(DESCRIPTION=(ADDRESS=  (PROTOCOL= TCP)(HOST=192.168.60.146)(PORT=1522))(CONNECT_DATA=(SID=SQLSRVRDBLNK))(HS=OK))

                       

                      - Klaus

                      • 8. Re: Hetrogeneous link Error
                        ManishS137

                        yes Klaus ,

                         

                        I have noticed and changed as per suggested,

                         

                        Now i am getting error ,

                         

                        SQL> select * from dbo.spt_monitor@SODBL;

                        select * from dbo.spt_monitor@SODBL

                                                      *

                        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 SODBL

                         

                         

                        thanks ,

                        Manish