5 Replies Latest reply: Aug 24, 2013 10:18 AM by sb92075 RSS

    dblink is giving error at tns creation.

    947771

      hi,

      I am trying to make dblink, in 11g express.winodws 2003, to sqlserver 2005 on same machine.

      i have made odbc connection to sqlserver and the name is dg4odbc.

      my home is

      F:\oraclexe\app\oracle\product\11.2.0\server

       

      i have initdg4odbc.ora in

      F:\oraclexe\app\oracle\product\11.2.0\server\hs\admin

      content of the file is

      HS_FDS_CONNECT_INFO = dg4odbc

      HS_FDS_TRACE_LEVEL = OFF

       

       

      listener.ora file ha

      SID_LIST_LISTENER =

        (SID_LIST =

          (SID_DESC =

            (SID_NAME = PLSExtProc)

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

            (PROGRAM = extproc)

          )

          (SID_DESC =

            (SID_NAME = CLRExtProc)

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

            (PROGRAM = extproc)

          )

      (SID_DESC =

                (SID_NAME = dg4odbc)

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

                (program = dg4odbc)

            )

       

        )

       

      LISTENER =

        (DESCRIPTION_LIST =

          (DESCRIPTION =

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

            (ADDRESS = (PROTOCOL = TCP)(HOST = wlt-vr7lj49kz0h)(PORT = 1521))

          )

        )

       

      DEFAULT_SERVICE_LISTENER = (XE)

       

      tnslistener.ora

      has

       

       

      dg4odbc =

                (DESCRIPTION =

                        (ADDRESS = (protocol=tcp)(host=wlt-vr7lj49kz0h)(port=1521))

                                (connect_data = (sid=dg4odbc))

                                (hs=ok)

                )

      XE =

        (DESCRIPTION =

          (ADDRESS = (PROTOCOL = TCP)(HOST = wlt-vr7lj49kz0h)(PORT = 1521))

          (CONNECT_DATA =

            (SERVER = DEDICATED)

            (SERVICE_NAME = XE)

          )

        )

       

      EXTPROC_CONNECTION_DATA =

        (DESCRIPTION =

          (ADDRESS_LIST =

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

          )

          (CONNECT_DATA =

            (SID = PLSExtProc)

            (PRESENTATION = RO)

          )

        )

       

      ORACLR_CONNECTION_DATA =

        (DESCRIPTION =

          (ADDRESS_LIST =

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

          )

          (CONNECT_DATA =

            (SID = CLRExtProc)

            (PRESENTATION = RO)

          )

        )

       

      i am getting following error

       

      F:\Documents and Settings\Administrator>Lsnrctl reload

       

      LSNRCTL for 32-bit Windows: Version 11.2.0.2.0 - Production on 18-AUG-2013 08:00

      :02

       

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

       

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

      TNS-12541: TNS:no listener

      TNS-12560: TNS:protocol adapter error

        TNS-00511: No listener

         32-bit Windows Error: 2: No such file or directory

      Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=wlt-vr7lj49kz0h)(PORT=15

      21)))

      TNS-12541: TNS:no listener

      TNS-12560: TNS:protocol adapter error

        TNS-00511: No listener

         32-bit Windows Error: 61: Unknown error

       

       

      yours sincerely

        • 1. Re: dblink is giving error at tns creation.
          sb92075

          >tnslistener.ora

          HUH?

           

          please clarify the details concerning the remote DB to which you desire to establish a connection.

           

          NOTHING in the post showed anything directly involving an DBLINK.

           

          CREATE DATABASE LINK ....

           

          How do I ask a question on the forums?

          https://forums.oracle.com/message/9362002#9362002

          • 2. Re: dblink is giving error at tns creation.
            Solomon Yakobson

            Why are you using dg4odbc? Are you trying to link to non-Oracle database?

             

            SY.

            • 3. Re: dblink is giving error at tns creation.
              947771

              I have sqlserver 2005 on same machine. i have also made odbc connection to sqlserver on same machine and the name is dg4odbc.

               

              and from internet i learned that after doing above configuration.

               

               

              i can test the listener

              Lsnrctl reload

               

              which is giving error.

               

              other steps are there to create dblink but they are after above step.

               

              2) Is any gateway is required.

               

              yours sincerely

              • 4. Re: dblink is giving error at tns creation.
                947771

                I have sqlserver 2005 on same machine. i have also made odbc connection to sqlserver on same machine and the name is dg4odbc.

                 

                and from internet i learned that after doing above configuration.

                 

                 

                i can test the listener

                Lsnrctl reload

                 

                which is giving error.

                 

                other steps are there to create dblink but they are after above step.

                 

                2) Is any gateway is required.

                 

                yours sincerely

                • 5. Re: dblink is giving error at tns creation.
                  sb92075

                  947771 wrote:

                   

                  I have sqlserver 2005 on same machine. i have also made odbc connection to sqlserver on same machine and the name is dg4odbc.

                   

                  and from internet i learned that after doing above configuration.

                   

                   

                  i can test the listener

                  Lsnrctl reload

                   

                  which is giving error.

                   

                  other steps are there to create dblink but they are after above step.

                   

                  2) Is any gateway is required.

                   

                  yours sincerely

                   

                  using ODBC to connect to SQL Server only shows that SQL Server is working as expected.

                  It shows NOTHING regarding Oracle configuration; which appears to be not correct at the OS level as evidenced by "32-bit Windows Error: 61: Unknown error"