How to troubleshoot Oracle remote database connection


Many Oracle DBAs, developers and just end-users often encounter an issue when they can not connect remotely to an Oracle database. There can be different reasons of the connection problems. I give below a short cookbook on resolving those database connection issues. But first letís explore a bit Oracle connectivity concepts and terminology.

Oracle NET Client Listener tnsnames
Oracle NET Client Server configuration

An Oracle database is represented to clients as a service; that is, the database performs work on behalf of clients. The service name is included in the connect data part of the connect descriptor. To connect to a database service, clients use a connect descriptor that provides the location of the database and the name of the database service. The address portion of the connect descriptor is actually the protocol address of the listener. To connect to a database service, clients first contact a listener process that typically resides on the database server. The listener receives incoming client connection requests and hands these requests to the database server. Once the connection is established, the client and database server communicate directly.

Ping database host IP
ping 11.222.333.44

If it works, go to the next step;
If not, check the server availability

Ping database hostname

if it works, go to the next step;
if not, something wrong with DNS /ACTIVE directory => try using hosts file

Test if listener port is reachable/opened for your remote connection
For that use telnet utility which is available on Unix and can be enabled on Windows.

  1. Linux example:
    $ telnet 1522
    Connected to (12.222.333.44).
    Escape character is ‘^]’.
    Connection closed by foreign host.
  2. Windows example

1) First enable telnet on Windows 7 if itís not there:

pkgmgr /iu:”TelnetClient”

2) Check the database port

telnet 1523
=> no output in case of a port opened; in case of a failure ñ see below Oracle error:
Could not open connection to the host, on port 1523: Connect failed

In case the database listener port is not reachable you face potentially a firewall issue. There are at least two solutions of this problems.
Both described in one of my presentations on SlideShare and in Demos on DaDBm YouTube channel.

Test Oracle Net connectivity ñ tnsping
The Oracle Net Listener is the gateway to the Oracle instance for all nonlocal user connections. A single listener can service multiple database instances and thousands of client connections. tnsping is the Oracle Net equivalent of the TCP/IP ping utility. It offers a quick test to verify that the network path to a destination is good. The utility validates that the host name, port, and protocol reach a listener. It does not actually check whether the listener handles the service name or a database is up and running

tnsping orcl

1) If it works, you will get a following message:

OK (10 msec)

2) In case of issues or errors, verify that the database listener is configured properly and/or troubleshoot the client side (see the chapter below)

Test database connection
1) With EZCONNECT bypassing tnsnames.ora (you can omit default port 1521)
sqlplus [email protected]’//’
sqlplus [email protected]’//’

2) With TNS alias using tnsnames.ora file:

sqlplus [email protected]

Troubleshoot the client side
Before trying to solve a particular Oracle error on client side, ensure the following on client side:

ñ Your Oracle client is installed and configured properly
ñ Identify your Oracle environment
ñ Identify current ORACLE_HOME
ñ Identify a location of tnsname.ora file (if used)
ñ Verify that you have correctly entered the service name of the database that you want to reach
ñ If you are connecting from a login dialog box, verify that you are not placing an at symbol (@) before your connection service name.

You can use Oracle Universal Installer (OUI) and OS commands to achieve all above steps. For example, on Windows following sqlplus commands can be useful in identifying your Oracle environment:

sqlplus /nolog

The following error codes are related to problems on the client side
ORA-12154: TNS:could not resolve the connect identifier specified

Couse and Action:
Usually this error indicate that a connect identifier / tns alias you use in your connection can not be recognized or found somewhere. Cross check your tnsnames.ora if it exists there.

ORA-12198: ìTNS:could not find path to destinationî and
ORA-12203: ìTNS:unable to connect to destinationî

Cause: The client cannot find the desired database.

  1. Verify that the service name ADDRESS parameters in the connect descriptor of your TNSNAMES.ORA file are correct.
  2. Verify that the listener on the remote node has started and is running. If not, start the listener by using the Listener Control utility.

ORA-12533: ìTNS:illegal ADDRESS parametersî

Cause: The protocol-specific parameters in the ADDRESS section of the designated
connect descriptor in your tnsnames.ora file are incorrect.
Action: For more information about protocol-specific keywords, refer to the Oracle
operating system documentation for your platform.

TNS-12541: TNS:no listener

Cause: The listener on the remote node cannot be contacted.
Action: Verify that the listener on the remote node has been started. You can check its status with the STATUS command of the Listener Control utility and start it with the START command if necessary. Verify that the database listener is configured properly using the following commands:
tnslsnr status
tnslsnr status
tnslsnr services

tnsnames.ora file example