geometrix - Fotolia

Problem solve Get help with specific problems with your technologies, process and projects.

Battle through the recurring Oracle TNS mistakes admins make

TNS often poses problems for IT admins and can be difficult to navigate -- especially when it comes to an Oracle database. Here's how to avoid those common errors.

All too often, I see questions about some very common TNS errors relating to connecting to an Oracle database.

With that being said, I'm going to explain how the user's tnsnames.ora configuration file works hand in hand with the database's Listener configuration. Once that relationship is understood, we can easily see how to fix the most common Oracle TNS errors encountered.

It may interest the readers to know that TNS is short for Transparent Network Substrate, which is what Oracle called part of its network stack back when that stack was referred to as SQL*Net. Now, the network stack is called Oracle Net, but the TNS abbreviation still persists.

After installing the Oracle Client software on a workstation, typically, the next task is to add an entry in the ORACLE_HOME/network/admin/tnsnames.ora configuration file. This entry is simply an alias, which defines all the pertinent information to connect to an Oracle database. An example of such an entry is as follows:





                 (Host = myhost01)(Port = 1521)))



The alias is called ORCLDB. We can call the alias anything we want for our application software. The Oracle database never uses this alias. The application will say "connect to ORCLDB," and the Oracle Client software will look in the tnsnames.ora file for the details of that alias. The alias defines the protocol in use, the Transmission Control Protocol (TCP), the host where the database resides (myhost), the port of the listener (1521) and the service name of the database (orcl).

Now, let's see how the database server's Listener is configured. On the database server, I issued "lsnrctl services."

[oracle@myhost01 ~]$ lsnrctl services

LSNRCTL for Linux: Version - Production on 18-JUL-2017 10:40:33

Copyright (c) 1991, 2014, Oracle.  All rights reserved.

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))

Services Summary...

Service "" has 1 instance(s).

  Instance "orcl", status READY, has 1 handler(s) for this service...


      "DEDICATED" established:10077 refused:0 state:ready


We can see that the Listener is configured for the TCP. The host entry in the output is blank, but we know the database host from the command prompt. The port is 1521, and the service name is

Oracle TNS errors occur when we have the wrong host, protocol, port and service name in our alias in the tnsnames.ora configuration file. For example, I will use SQL*Plus to connect to the database defined by the orcldb alias. I receive the ORA-12514 error. (Side note: The errors in this article can also have the TNS prefix. So, you may see TNS-12514 where one would also have an ORA-12514 error. The prefix is determined by the Oracle code issuing the error.)

C:\>sqlplus peasland@orcldb

SQL*Plus: Release Production on Tue Jul 18 10:43:30 2017

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Enter password:


ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

The error is telling us that the Listener does not know of our service name. In the tnsnames.ora file, the service name is defined as orcl. The Listener knows of the service named as we saw from the "lsnrctl services" output. The services do not match, letter for letter. The Listener has the domain name appended to the service, and the tnsnames.ora alias does not. This is a simple fix. In the tnsnames.ora, add the domain name.


With the ORA-12514 error, the Oracle Client software was able to contact the Listener on the specified port with the matching protocol, but the Listener did not match that final piece of information -- the service name. In the next example, we have a different issue.

C:\>sqlplus peasland@orcldb

SQL*Plus: Release Production on Tue Jul 18 10:49:16 2017

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Enter password:


ORA-12545: Connect failed because target host or object does not exist

Here, the ORA-12545 is telling us that the Listener cannot be reached. In this case, it is because the host and/or port is incorrect. I have this error because my alias has the following:

(Host = myhsot)

The host name is incorrect. Once I fixed the host name in the alias, the connection succeeded. For ORA-12545 errors (TNS-12545 as well), double-check the host and port.

If the protocol is incorrect in the alias definition, you may receive errors such as:

ORA-12538: TNS:no such protocol adapter

The solution is to use the proper protocol, which is normally TCP.

To recap, whenever I receive Oracle TNS  errors, I look at the details of the alias in the tnsnames.ora configuration file, and I look at the Listener definition. If I receive TNS(ORA)-12514 errors, I know the service names are not matching. If I receive TNS(ORA)-12545 errors, I know the host/port combination is not correct. If I receive TNS(ORA)-12538 errors, the protocol is not valid.

Next Steps

Prevent an Oracle TNS Listener attack

Are single users bounded by a TNS?

A look into a TNS zero-day workaround

Dig Deeper on Oracle database security

Join the conversation


Send me notifications when other members comment.

Please create a username to comment.

What Oracle TNS errors have you encountered lately? How did you fix them?
It is important to also note that Oracle default port number "1521" should not be used in configuring the Listener.Ora file for an Oracle database considered to be warehousing important data of an organization. Remember your baseline configuration requirement for general information security...... "Change all vendor default parameters". This applies to Oracle, AIX, Solaris, Windows, Linux, VMs platforms.

Change it to something else other than 1521 and reflect same in Listener.ora and TNSname.Ora files. Its safer and make more sense so that it will become a little bit more difficult for a hacker to sniff/listen on the Oracle database as default port makes it so so easier for them. Make database compromise a little bit difficult and unattractive and reduce the surface area of attack.