4 Replies Latest reply: Apr 3, 2013 1:10 AM by buggleboy007 RSS

    Cannot create any connection with SQL Developer

    buggleboy007
      Hi there,

      I am trying to create a new connection using SQL Developer and I am constantly getting bogged down with an error and the error is:

      Status : Failure -Test failed: The Network Adapter could not establish the connection.

      Brief background is :

      a. Brand new machine from Dell and installed windows 7 profession 64 bit

      b. Installed successfully Oracle 11g - Release 11.2.0.1.0

      c. SQL Developer version - 3.2.20.09

      c. Able to connect to sys and scott from SQL Plus but not through SQL Developer

      d. Listener in running state and seems to be working properly

      e. ip address - 192.168.1.101 (aka host)

      Details of listener.ora is :_

      # listener.ora Network Configuration File: C:\app\BuggleBoy\product\11.2.0\dbhome_1\network\admin\listener.ora
      # Generated by Oracle configuration tools.

      SID_LIST_LISTENER =
      (SID_LIST =
      (SID_DESC =
      (SID_NAME = sandeep)
      (ORACLE_HOME = C:\app\BuggleBoy\product\11.2.0\dbhome_1)
      (PROGRAM = extproc)
      (ENVS = "EXTPROC_DLLS=ONLY:C:\app\BuggleBoy\product\11.2.0\dbhome_1\bin\oraclr11.dll")
      )
      )

      LISTENER =
      (DESCRIPTION_LIST =
      (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = 192.168.1.101))
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
      )
      )

      ADR_BASE_LISTENER = C:\app\BuggleBoy


      Details of tnsnames.ora is:_

      # tnsnames.ora Network Configuration File: C:\app\BuggleBoy\product\11.2.0\dbhome_1\network\admin\tnsnames.ora
      # Generated by Oracle configuration tools.

      ORACLR_CONNECTION_DATA =
      (DESCRIPTION =
      (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      )
      (CONNECT_DATA =
      (SID = sandeep)
      (PRESENTATION = RO)
      )
      )

      SANDEEP =
      (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.101)(PORT = 1521))
      (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = sandeep)
      )
      )

      LISTENER_SANDEEP =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.101)(PORT = 1521))


      Based on a few other threads, I did the following trouble shooting

      +1. lsnrctl stop+

      output:
      C:\Users\Buggle Boy>lsnrctl stop
      LSNRCTL for 64-bit Windows: Version 11.2.0.1.0 - Production on 02-APR-2013 00:14
      :00
      Copyright (c) 1991, 2010, Oracle. All rights reserved.
      Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=192.168.1.101)))
      TNS-01190: The user is not authorized to execute the requested listener command
      C:\Users\Buggle Boy>

      +2. lsnrctl start+

      output:

      C:\Users\Buggle Boy>lsnrctl start
      LSNRCTL for 64-bit Windows: Version 11.2.0.1.0 - Production on 02-APR-2013 00:15
      :35
      Copyright (c) 1991, 2010, Oracle. All rights reserved.
      TNS-01106: Listener using listener name LISTENER has already been started
      C:\Users\Buggle Boy>

      +3. lsnrctl status+

      output:

      LSNRCTL for 64-bit Windows: Version 11.2.0.1.0 - Production on 02-APR-2013 00:16
      :54

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

      Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=192.168.1.101)))
      STATUS of the LISTENER
      ------------------------
      Alias LISTENER
      Version TNSLSNR for 64-bit Windows: Version 11.2.0.1.0 - Produ
      ction
      Start Date 01-APR-2013 23:47:10
      Uptime 0 days 0 hr. 29 min. 45 sec
      Trace Level off
      Security ON: Local OS Authentication
      SNMP OFF
      Listener Parameter File C:\app\BuggleBoy\product\11.2.0\dbhome_1\network\admin
      \listener.ora
      Listener Log File c:\app\buggleboy\diag\tnslsnr\BuggleBoy\listener\alert
      \log.xml
      Listening Endpoints Summary...
      (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\192.168.1.101ipc)))
      (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521)))
      Services Summary...
      Service "sandeep" has 1 instance(s).
      Instance "sandeep", status UNKNOWN, has 1 handler(s) for this service...
      The command completed successfully

      C:\Users\Buggle Boy>

      +4. lsnrctl service+

      output:

      LSNRCTL for 64-bit Windows: Version 11.2.0.1.0 - Production on 02-APR-2013 00:18
      :20

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

      Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=192.168.1.101)))
      Services Summary...
      Service "sandeep" has 1 instance(s).
      Instance "sandeep", status UNKNOWN, has 1 handler(s) for this service...
      Handler(s):
      "DEDICATED" established:520 refused:0
      LOCAL SERVER
      The command completed successfully
      C:\Users\Buggle Boy>

      +5. set+

      Output:

      LSNRCTL for 64-bit Windows: Version 11.2.0.1.0 - Production on 02-APR-2013 00:18
      :20

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

      Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=192.168.1.101)))
      Services Summary...
      Service "sandeep" has 1 instance(s).
      Instance "sandeep", status UNKNOWN, has 1 handler(s) for this service...
      Handler(s):
      "DEDICATED" established:520 refused:0
      LOCAL SERVER
      The command completed successfully

      C:\Users\Buggle Boy>

      Based on all the above information given, how can I fix this issue? Please help as I need to get SQL Developer up and running.

      Many thanks in advance,
      Sandeep
        • 1. Re: Cannot create any connection with SQL Developer
          Irian
          I'm not really an expert with the listener, so I cannot check your configuration for errors, I vaguely recall that using localhost in the listener configuration is not a good idea, but I may be wrong here.

          On the SQLDeveloper side, since you are running SQLDeveloper on the same machine as the database itself you could try to use localhost or 127.0.0.1 as the hostname for the sqldeveloper basic connection, just in case there is something on yor network preventing access with the real IP address.

          You could also select the TNS connection type, and this should load your TNS file and show you the "SANDEEP" connection that is also used by SQLPlus.
          • 2. Re: Cannot create any connection with SQL Developer
            riedelme
            user11934091 wrote:
            Hi there,

            I am trying to create a new connection using SQL Developer and I am constantly getting bogged down with an error and the error is:

            Status : Failure -Test failed: The Network Adapter could not establish the connection.

            Brief background is :

            a. Brand new machine from Dell and installed windows 7 profession 64 bit

            b. Installed successfully Oracle 11g - Release 11.2.0.1.0

            c. SQL Developer version - 3.2.20.09

            c. Able to connect to sys and scott from SQL Plus but not through SQL Developer

            d. Listener in running state and seems to be working properly
            Helped a co-worker through this just yesterday. Brand new installation of XE allowed SQL*PLUS connection but SQL*Devleoper choked with the mesasge you cited. Web searches yielded 3 different forums suggesting removing (we renamed) the listener.ora file. We also had to bounce the listener. This was make more <sarcasm>fun</sarcasm> by Windows 7 refusing to let us edit LISTENER.ORA and TNSNAMES.ORA (we had to let the login user work with the file under properties/security for the network folder) AND refusing to let us shut down the listener (through command-line lsnrctl and the Services interface). We rebooted.

            So for us removing the listener.ora file allowed SQL*Developer connectivity.

            My own installation on a dell laptop similar to your works fine - did a re-install last month and have had no problems with SQL*Developer.

            Stuff we tried that did not help on the XE installation included (these might have worked if done differently)
            * changed localhost in the SQL*Developer connection setup to the box name
            * using TNS connection in SQL*Developer instead of BASIC
            * Various changes to TNSNAMES.ORA and LISTENER.ORA, mostly using the machine name or "localhost" or using SID vs SERVICE NAME
            • 3. Re: Cannot create any connection with SQL Developer
              buggleboy007
              Hiya,

              I will try both of your suggestions and see how it goes. Will update you either ways tonight :-)

              Regards,
              Sandeep

              Edited by: user11934091 on Apr 2, 2013 9:15 AM
              • 4. Re: Cannot create any connection with SQL Developer
                buggleboy007
                The issue has now been addressed. I had made a backup of listener and tnsnames files, which I copied again and made ip address corrections, after which I shut down all the Oracle services via service manager and then restarted it. After restarting it, I was able to ensure a successful Test and Connection respectively.

                Thanks again for your workarounds.

                Regards,
                Sandeep