What could be wrong: ping works fine but tnsping works intermittently

Question

We have oracle 10g running on windows server 2003. A machine which runs an application using that database has as of a few weeks ago suddenly started having connectivity problems. Today we ran the automatic updates for windows server and the problem has only gotten worse. I realize this isn't enough information for anyone to diagnose the problem but perhaps you can get me pointed in the right direction with the following more specific scenario:

From this machine we can ping the server with absolutely no problem and, being physically close and on an intranet the return is very fast.

However, when we run tnsping I have seen 3 different results within a few minutes of each other.

1. tnsping returns just fine and in a reasonable amount of time
2. tnsping returns but only after a real long time (several seconds)
3. tnsping results in an ora-12560 protocol adapter error

At the same time I can tnsping the server from my machine with no problem.

Can anyone point me in the right direction?

Answer 1

Make sure there is no other machine on the network with the same IP address. A method would be unplug your machine from the network and see if you can still ping it. If you can then this is the problem.

Answer 2

To help eliminate DNS issues from the equation, specify the host's IP address in the TNSNAMES.ora file for your connection instead of a hostname. Are you using DHCP?

Have you eliminated hardware as the problem - have you tried a different NIC?

Answer 3

I'd try to check the following:

• do traceroute from the app server and from your machine check for anything abnormal
• check tnsping from various other machine and try to identify a pattern
• try a tcp/ip sniffer to see what is going on at both ends of the connection
• get oracle support involved

Answer 4

If the server doesn't have a domain-name setup at a dns server, then add it's ip address and name to the host file on the server; this (the server not being able to find itself in dns) has been known to cause tns timeouts.

Answer 5

Before calling Oracle, I would create a trace file for a Fail case.

TNSPING.TRACE_LEVEL

Purpose

Use the parameter TNSPING.TRACE_LEVEL to turn TNSPING utility tracing on, at a specific level, or off.

Default

off

Values * off: for no trace output * user: for user trace information * admin: for administration trace information * support: for Oracle Support Services trace information

Example

TNSPING.TRACE_LEVEL=admin

Answer 6

Before involving oracle in this issue, get some help from your network administrator for the following test. First enable verbose logging on the database in the listener. Enable logging on the client via sqlnet. Go to the machine that is having trouble with tnsping, have the network administrator run a network tool to trace tcp packets from there. Perform the tnsping and see if what packet are being sent, what dns lookup are being made, what route is being taken. On the database see if the listener actually receives a ping from the client. If not then see where along the network to the database the problem is. Is it nameserver resolution? Is it a bad network cable, bad switch port, etc. Your network admin is your best friend for this problem. Do the same test via sqlplus with a simple connection and see what the client is logging.