Oracle Database TNS Troubleshooting with Netstat

 Oracle databases, no matter if they are in cloud or on-prem or of what version, work on client server relationship and these client could be in disparate locations. More often than not, you get various network related errors in the database in alert log or in the listener log of your database. 

How do you resolve them? How do you go about start your troubleshooting process? For network errors, its always a good idea to start looking at OS level after verifying that your TNS files and listener configuration files are valid. 

One of my favorite command to troubleshoot Oracle TNS errors on Linux (and even on Windows using gitbash or any other similar tool) is to use NETSTAT command. 

How to Use Netstat?

Just typing netstat should display a long list of information that's usually more than you want to go through at any given time. The trick to keeping the information useful is knowing what you're looking for and how to tell netstat to only display that information. 

For example, if you only want to see TCP connections, use netstat --tcp.

This shows a list of TCP connections to and from your machine. The following example shows connections to our machine on ports 993 (imaps), 143 (imap), 110 (pop3), 25 (smtp), and 22 (ssh).It also shows a connection from our machine to a remote machine on port 389 (ldap).

% netstat --tcp --numeric  

Active Internet connections (w/o servers)  

Proto Recv-Q Send-Q Local Address           Foreign Address         State       

tcp        0      0 192.168.128.152:993     192.168.128.120:3853   ESTABLISHED

tcp        0      0 192.168.128.152:143     192.168.128.194:3076   ESTABLISHED

tcp        0      0 192.168.128.152:45771   192.168.128.34:389      TIME_WAIT

tcp        0      0 192.168.128.152:110     192.168.33.123:3521     TIME_WAIT

tcp        0      0 192.168.128.152:25      192.168.231.27:44221    TIME_WAIT

tcp        0    256 192.168.128.152:22      192.168.128.78:47258   ESTABLISHED

If you want to see what (TCP) ports your machine is listening on, use netstat --tcp --listening. You can use netstat --route to display the routing table. With netstat, you can also use the --statistics flag to display networking statistics. Using this flag by itself displays all IP, TCP, UDP, and ICMP connection statistics.

NETSTAT is full of such gems, and I would highly recommend you go through man page of netstat on your server.