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:

ORCLDB =

  (DESCRIPTION=

    (ADDRESS_LIST =

      (ADDRESS = (COMMUNITY = TCP)(PROTOCOL = TCP)

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

    (CONNECT_DATA = (SERVICE_NAME = orcl))

  )

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 12.1.0.2.0 - 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 "orcl.acme.com" has 1 instance(s).

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

    Handler(s):

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

         LOCAL SERVER

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 orcl.acme.com.

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 11.2.0.4.0 Production on Tue Jul 18 10:43:30 2017

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

Enter password:

ERROR:

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 orcl.acme.com 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.

(CONNECT_DATA = (SERVICE_NAME = orcl.acme.com))

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 11.2.0.4.0 Production on Tue Jul 18 10:49:16 2017

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

Enter password:

ERROR:

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

This was last published in September 2017

Dig Deeper on Oracle database security

PRO+

Content

Find more PRO+ content and other member only offers, here.

Join the conversation

1 comment

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

What Oracle TNS errors have you encountered lately? How did you fix them?
Cancel

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close