TNS SQL .NET Connectivity Issues – Troubleshooting and Diagnosing Tips

TNS SQL .NET Connectivity Issues – Troubleshooting and Diagnosing Tips

A number of network connectivity issues exist that Database admins are familiar with in their lifetime careers. Some of the common TNS error messages related to connectivity issues are:

• ORA-12154: TNS: Could not resolve service name
• ORA-12157: TNS Internal network communication error 
• TNS-12545: Connect failed because target host or object does not exist

Any expert in Oracle*Net system understands that there is a nesting of transport layers just like the layers of an onion. And, the lowest of these layers (or levels) is the TCP/IP (or sometimes other protocols), where the database packets are transported among servers.

Troubleshooting steps for checking Oracle connectivity:

For troubleshooting and diagnosing the problems related to Oracle connectivity, here are the steps to check the issues of the very first level:

1 – Use the IP address to check with the “ping.” (ping IP: 198.11.34.43)

2 – Now, use the DNS name to check with the “ping.” (for instance: tnsping princeton)

3 – Check telnet to the respective IP using the port no. 1521 (telnet 1.2.3.4 1521)

4 – Now using the TNS name check with the “tnsping.”

5 – Call upon SQL*Plus from the operating system command line “sqlplus barney@rubble.” If it does not work, monitor to make sure that the receiver has defined the rubble service.

6 – Now, sign in to the SQL*Plus and link with TNS service name (connect barney/rubble@service_name_)

7 – In SQL*Plus, try selecting from table@remote_db_link

Now, move in further to take a look at the next level/layer for troubleshooting the issue.

TNS connectivity layer at server level

When the Oracle database admins generate their tnsnames.ora files for defining remote databases, they frequently describe the host name related to the foreign server that has the Oracle database within it. For instance, an entry made in the tnsnames.ora file for database present remotely may look something like this:

stockholm =
   (DESCRIPTION =
     (ADDRESS_LIST =
         (ADDRESS =
           (COMMUNITY = TCP)
           (PROTOCOL = TCP)
          (HOST = hum)
           (PORT = 1521)
          )
      )
     (CONNECT_DATA = (SID = claus))
    )

Here, we can see a TNS defined by the service name stockholm, which specifies a link to a server present remotely with host name hum that has the Oracle database name claus. When there is a request made by the UNIX server from the remote connection, the /etc/host file can be accessed for getting the IP address of the host server named hum. 

As seen in the listing mentioned below, we can see that the hum server is present at the IP address 192.133.13.12. In some connection servers, the /etc/host file is present for isolating the IP address from its particular tnsnames.ora file. If, however, there ever is a change in the PI address, the UNIX server system will then only alter the IP address present at one place.

root> cat /etc/hosts
192.133.13.22  hum    hum.com 
192.144.13.22  dopey  dopey.com