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

ping orcl.dadbm.com
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 orcl1.dadbm.com 1522
Trying 10.126.247.11...
Connected to orcl.dadbm.com (12.222.333.44).
Escape character is '^]'.
get
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 orcl1.dadbm.com 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.dadbm.com:1521/orclor
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 user@'//orcl.dadbm.com:1521/orcl'
sqlplus user@'//orcl.dadbm.com/orcl'


2) With TNS alias using tnsnames.ora file:
sqlplus user@orcl

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
@%ORACLE_HOME%
@%TNS_ADMIN%

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.
Action:
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


ORCL1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = orcl1.dadbm.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ORCL1)
)
)
ORCL2 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = orcl2.dadbm.com)(PORT = 1522))
(CONNECT_DATA = (SERVICE_NAME = ORCL2))
)
ORCL3 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = orcl3.dadbm.com)(PORT = 1523))
)
(CONNECT_DATA = (SID = ORCL3))
)