Tuesday, October 23, 2012

Troubleshooting SQL: Developer Connection Problems

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.

These details should be available from your DBA, or from when you created a database.

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.

  1. 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
  2. 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.

  3. 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.

Basic Network Connectivity

These tests use a windows command window and windows tools. Tools on other platforms may produce different responses.
  1. 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.
  2. 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.
  3. 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

1 comment:

Anonymous said...

el problema son los parametros de conexion, el host el puerto o el SID para saber los datos correctos ir a la instalacion de Oracle en mi caso es C:\oraclexe\app\oracle\product\11.2.0\server\network\ADMIN donde server puede ser variar segun tu instalacion, abres el archivo tnsnames la primer cadena ejemplo:
XE =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = server)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = XE)
)
)
te dice los datos de tu conexion en este ejemplo host:server puerto 1521 y SID:XE por default la instalacion en HOST es localhost por eso no permite la conexion. ojo los parametros varian segun la instalacion.