This discussion is archived
4 Replies Latest reply: Apr 2, 2013 11:10 PM by Sandeep Tanjore RSS

Cannot create any connection with SQL Developer

Sandeep Tanjore Newbie
Currently Being Moderated
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 Pro
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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
    Sandeep Tanjore Newbie
    Currently Being Moderated
    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
    Sandeep Tanjore Newbie
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points