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
ERROR: 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 168.192.1.22

• 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=198.32.3.5).

• 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

 

Oracle Network Issues Link

Network DBA

This link contains common network issues for Oracle Connectivity along with brief description of network files.

NETWORK DBA 

1. LISTENER.ORA file

It is a server side file and contains information regarding network configuration parameters. It can be found in the ORACLE_HOME/network/admin directory on the server.

Sample File

SID_LIST_LISTENER =   (SID_LIST =     (SID_DESC =       (SID_NAME = PLSExtProc)       (ORACLE_HOME = C:\oracle\product\10.2.0\db)       (PROGRAM = extproc)     )     (SID_DESC =       (GLOBAL_DBNAME = ORCL)       (ORACLE_HOME = C:\oracle\product\10.2.0\db)       (SID_NAME = ORCL)     )   ) LISTENER =   (DESCRIPTION =     (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))   )

 

If listener is modified then the listener should be restarted or reloaded to allow the new configuration to take place.  Basic commands of Listener control utility are as follows:

LSNRCTL>start   LSNRCTL>stop  LSNRCTL>reload LSNRCTL>status LSNRCTL>show

 

2.TNSNAMES.ORA file

It is a Client side file which is created on both sites SERVER/CLIENT. It contains client side network configuration parameters.It can be found in the ORACLE_HOME/network/admin. It can be found on the server if client style connections are used on the server side.

ORCL =   (DESCRIPTION =     (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))     (CONNECT_DATA =       (SERVER = DEDICATED)       (SERVICE_NAME = orcl)     )   )

3. SQLNET.ORA file

It is a Client side file which is created on both sites SERVER/CLIENT. It is use for user AUTHENTICATION purpose.

SQLNET.AUTHENTICATION_SERVICES= (NTS) NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

 

Another good link for commonly known TNS errors from

Emre Baransel – Oracle Blog