Common Oracle TNS Errors

In this post , I am trying to cover common TNS errors and possible corrective measures.

ORA-12154: TNS:could not resolve service name

SQL> connect SYSTEM/manager@DEV
ORA-12154: TNS:could not resolve service name

The most common TNS error mostly due to multiple ORACLE HOME, tnsnames.ora and sqlnet.ora conflicts.

  • Check that the instance or database name is being typed in correctly. Check ORACLE_SID,ORACLE_HOME
  • Make sure listener is up and running
  • lsnrctl > status           if down then

    lsnrctl > start

  • PING the server to see if network is up
  • ping

  • TNSPING the server and then try connecting
  • tnsping DEV

  • Check the tns names.ora file to validate that the instance name has been entered correctly. Make sure that all entries are properly terminated with parenthesis.
  • Validate the domain name entry in sqlnet.ora is not conflicting with the full database name resolution.
  • If nothings works, delete and recreate the entry to eliminate any character or control character issues.

For more details on ORA-12154 causes and actions


TNS-03505: Failed to resolve name

Message 3511 not found; product=network; facility=TNS
TNS-03505: Failed to resolve name

This is quite similar to the above error so you have to again check the environmental variables like ORACLE_HOME and PATH and make sure their values are set correctly.
The issue usually occurs when the tns entiries we provide has some unwanted characters or spaces etc.Please also check the tns entry(alias) is pointing to the correct database with sid hostname.

For more details on TNS-03505 reasons and remedy actions

ORA-12537: TNS: connection closed error

It happens if the connection has been closed most likely due to the following reasons:

  • Firewall has disconnected idle connections
  • Configuration problem in sqlnet.ora or listener.ora
  • Time out occurred on client connection
  • Path name that is too long for the Oracle TNS client on windows

Try the following to fix this issue:

  1. Install the exe in a shorter named directory.
  2. Modify the TNS Names entry so that it is shorter.
  3. Replace domain names with IP Addresses if that shortens and vice versa.
  4. Remove any parameters from the entry that are not needed.
  5. Patch the Database Listener to the most recent version

For more details on ORA-12537 reasons and remedy actions


Troubleshooting Tips from the Field for Oracle Net Services

Here are some tips you may find helpful when you are having difficulty diagnosing network problems from Oracle documentation:

  • Use the node or network address during configuration instead of the name of the server computer

    This eliminates any internal lookup problems and make the connection slightly faster.

  • If you are using TCP/IP addresses, use the IP address rather than host name

    For example, change the (HOST=server_name) line in the tnsnames.ora file with the internet address, for example (HOST=

  • Perform a loopback test

    Perform a loopback test on the server as described in “Testing Configuration on the Database Server”. If the test passes, ftp the tnsnames.ora and sqlnet.ora files to the client.

  • Check what is between you and the server

    If it is a wide area network (WAN), identify any intermediate systems that may not work correctly. If all computers are fine, the problem may be a timing issue.

  • Verify whether or not there is a timing issue

    Timing issues are associated with an ORA-12535 error in the client log files.

    To resolve this, try speeding up the connection by using exact addresses instead of names and increase the INBOUND_CONNECT_TIMEOUT_listener_name parameter in the listener.ora file. The default value for this parameter is 10 seconds.

  • Determine which Oracle applications are failing

    SQL*Plus may work, but CASE tools may not. If you determine the problem is a data volume issue, try to transfer a large (5 MB) file with the base connectivity.


Resolving most common Error messages for Oracle Net Services



3 responses to this post.

  1. Posted by Amin Anwar on January 21, 2011 at 12:18 pm

    what is streaming in oracle?


    • Posted by mohibalvi on February 10, 2011 at 1:15 am

      Oracle Streams, a built-in feature of the Oracle database, is a data replication and integration feature. It provides a flexible infrastructure that meets a wide variety of information sharing needs. Oracle Streams enables the propagation of data, transactions and events in a data stream either within a database, or from one database to another.

      You can also check this post on my blog for Streams


  2. […] Common Oracle TNS Errors […]


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: