This article is intended to help with troubleshooting SQL Developer connection problems. It won't tell you how to set up a connection because that is already well covered online help (Start with the Getting Started topic and follow the link from there.)
The article is limited to the SQL Developer BASIC connection type. That is a simple JDBC type 4 connection with no additional authentication or security options, and no additional oracle client software.
What is a connection?
When you connect to an oracle instance using SQL Developer, you are making a TCP/IP connection to a specific host, on a specific port, and to a named instance or service. These are the parameters you supply when creating a basic connection in SQLDeveloper.- host
This can be a hostname or an IP address. If you use a hostname, including localhost, your workstation must be able to resolve it to an IP address.
- port
This is the TCP port on the host which is listening for oracle connections. It is usually 1521, but not always.
- SID
This the instance name of the database
- service
This is the service name under which the database is available. It is often the same as the SID, but may not be.
In the remainder of this article there is an assumption that you have the correct details. There is always the possibility that you have the wrong information or have mistyped something. If a test fails, re-check your information before investigating other causes.
The database host and your workstation may be the same machine — you may have SQL Developer installed on the database host, or you may have an oracle database installed on your PC — but you still need a TCP/IP connection. In this article I will use the terms host and workstation to distinguish the two.
Connection Errors
This is not a comprehensive list of oracle errors, or even all errors relevant to SQL Developer connections.See the Oracle Error messages reference manaual for all error messages.
- Client Side Errors
These are errors caused by issues on the workstation, or somewhere between the workstation and the host. If you get either of these errors, follow the troubleshooting tips at Basic Network Connectivity Below
- Status : Failure -Test failed: IO Error: The Network Adapter could not establish the connection
- An error was encountered performing the requested operation: IO Error: The Network Adapter could not establish the connection Vendor code 17002
- Host side errors
These are errors caused by issues on the host such as the database service not running. In each case you will need to contact the team responsible for the host.
- Status : Failure -Test failed: Listener refused the connection with the following error:
ORA-12514, TNS:listener does not currently know of service requested in connect descriptor.
This means that the service you have specified is not running.
- Status : Failure -Test failed: Listener refused the connection with the following error:
ORA-12505, TNS:listener does not currently know of SID given in connect descriptor
This means that the SID you have specified is not running.
- Status : Failure -Test failed: IO Error: Got minus one from a read call
This is a non-specific error, but I have seen it when the remote database is shutting down.
- Status : Failure -Test failed: Listener refused the connection with the following error:
ORA-12528, TNS:listener: all appropriate instances are blocking new connections
This can mean that either the database is still starting up, or that the database is overloaded (too many connections.)
- Status : Failure -Test failed: ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
Linux Error: 2: No such file or directory
This means that the database is down.
- Status : Failure -Test failed: ORA-01033: ORACLE initialization or shutdown in progress
This means that the database is in the process of starting or stopping.
- Status : Failure -Test failed: Listener refused the connection with the following error:
ORA-12514, TNS:listener does not currently know of service requested in connect descriptor.
- Other errors
These are not SQL Developer specific connection errors, but may mean you can't connect.
- Status : Failure -Test failed: ORA-01017: invalid username/password; logon denied
This is self-explanatory. Either your username or password is wrong.
- An error was encountered performing the requested operation:
ORA-28000: the account is locked
28000. 00000 - "the account is locked"
*Cause: The user has entered wrong password consequently for maximum
number of times specified by the user's profile parameter
FAILED_LOGIN_ATTEMPTS, or the DBA has locked the account
*Action: Wait for PASSWORD_LOCK_TIME or contact DBA
Vendor code 28000
The account you are using is locked. You will need to contact your security administrator or DBA.
- Status : Failure -Test failed: ORA-01017: invalid username/password; logon denied
Basic Network Connectivity
These tests use a windows command window and windows tools. Tools on other platforms may produce different responses.- Can you ping the host?
Q:\tests>ping mercure Ping request could not find host mercure. Please check the name and try again.
This indicates that the host "mercure" isn't known to your workstation. Typically this means that there is no DNS entry, or no HOSTS file entry.Q:\tests>ping hermes Pinging hermes.ponder-stibbons.local [192.168.1.18] with 32 bytes of data: Request timed out. Request timed out. Request timed out. Request timed out. Ping statistics for 192.168.1.18: Packets: Sent = 4, Received = 0, Lost = 4 (100% loss),
This indicates that the host "hermes" is known to your workstation, but the host itself is not accessible. The host could be down, or there could be a DNS error (i.e. wrong IP address,) or your network configuration means that your workstation cannot reach it.Q:\tests>ping mercury Pinging mercury.ponder-stibbons.local [192.168.1.5] with 32 bytes of data: Reply from 192.168.1.5: bytes=32 time<1ms TTL=64 Reply from 192.168.1.5: bytes=32 time=5ms TTL=64 Reply from 192.168.1.5: bytes=32 time<1ms TTL=64 Reply from 192.168.1.5: bytes=32 time<1ms TTL=64 Ping statistics for 192.168.1.5: Packets: Sent = 4, Received = 4, Lost = 0 (0% loss), Approximate round trip times in milli-seconds: Minimum = 0ms, Maximum = 5ms, Average = 1ms
This is a successful test, you can now proceed to the next stage. - Can you telnet to the host on the correct port?
Q:\tests>telnet mercury 1522 Connecting To mercury...Could not open connection to the host, on port 1522: Connect failed
This could mean that the listener is not running, or that a firewall is blocking the port. The firewall could be on the database host, on your workstation or anywhere in between.Q:\tests>telnet mercury 1521
If you get a blank screen here, you have successfully connected. Type ctrl-] to get the telnet command line and quit.Welcome to Microsoft Telnet Client Escape Character is 'CTRL+]' Microsoft Telnet> quit Q:\tests>
If something other than an oracle listener is using port 1521 you may still get a blank screen, or you may see a welcome message. This is unlikely if you have the correct host. - Can other oracle clients connect?
$ tnsping xe TNS Ping Utility for 64-bit Windows: Version 11.2.0.3.0 - Production on 19-OCT-2012 10:47:47 Copyright (c) 1997, 2011, Oracle. All rights reserved. Used parameter files: D:\oracle\product\11.2.0\dbhome_1\network\admin\sqlnet.ora Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = mercury.ponder-stibbons.local)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICAT ED) (SERVICE_NAME = XE))) OK (20 msec)
TODO:
- Other connection types connections (TNS,LDAP, Advanced, Local/Bequeath)
- Oracle client software interactions
- diagnostic tools
Update History
Date | Amendment |
19 Oct 2012 | Initial version |