2

I'm seeing a lot of queries from a .Net data access server I maintain timing out. It appears to be totally random with no relationship to data or data locks. For example, the following query timed out!

 SELECT NULL FROM DUAL 

The system logs show that when it happened CPU was at 20%, memory 42%, Disk 3%. What is going on?

The DB is version 10.2.0.3.0 on HPUX. The ODP driver is 2.111.6.20 (11g driver)

I checked out the sqlnet.log and found a large number of these error messages:

***********************************************************************
Fatal NI connect error 12170.

  VERSION INFORMATION:
        TNS for HPUX: Version 10.2.0.3.0 - Production
        Oracle Bequeath NT Protocol Adapter for HPUX: Version 10.2.0.3.0 - Production
        TCP/IP NT Protocol Adapter for HPUX: Version 10.2.0.3.0 - Production
  Time: 29-JUN-2009 06:42:04
  Tracing not turned on.
  Tns error struct:
    ns main err code: 12535
    TNS-12535: TNS:operation timed out
    ns secondary err code: 12606
    nt main err code: 0
    nt secondary err code: 0
1
  • I have heard that slow network could be the cause? Is that a viable possibility?
    – Jeff
    Jun 29, 2009 at 12:47

8 Answers 8

3

The particular error and the fact that it happens to all applications running against the database would strongly point to a network hiccup as the source of the problem.

  • How are TNS aliases resolved? Are you using a local tnsnames.ora file?

  • Assuming you are using a local tnsnames.ora file, is the TNS alias for the database using an IP address or a host name? Using an IP address eliminates the need to hit DNS, so it may be worthwhile to try that in case the problem is that your DNS server is briefly going nuts.

  • You may also try configuring a backup listener and adding a failover option to the TNS alias. If the problem is that the network hiccups and loses the packets from the client to the listener communication randomly, having a failover option that can be tried may resolve the vast majority of issues without needing to figure out what piece of the network is going flaky. Of course, that assumes that the problem corrects itself quickly enough that the next connection attempt succeeds, but that may well be a reasonable assumption. If adding a backup listener resolves the problem, you can be all but certain that it's a network issue.
2

I've found that turning off connection pooling is more reliable. I ran into several situations like you describe.

It turned out that the connections are tested BEFORE they are placed back into the pool, instead of as they're being pulled for subsequent use.

On a good day, you'll get an error of some sort. On a bad day, you'll just hang waiting to use a connection that is completely messed up and will never work.

If you're going to use pooling, I read recommendations that you execute an alter command against the session. (No, I don't know of a good one. Oracle didn't make it any clearer in the manual)

Brad

2
  • Pooling is already off. I also ran into some other problems with it months ago.
    – Jeff
    Jun 19, 2009 at 19:59
  • 1
    You can solve this by adding "Validate Connection=True" to the connection string. You should also call ClearPool if you get an invalid connection - see download.oracle.com/docs/cd/B28359_01/win.111/b28375/….
    – crb
    Jun 19, 2009 at 23:15
2

I had this issue when the following conditions were true: - JDBC Client was running on a machine with IP ZZ.ZZ.ZZ.ZZ - Database Server had two NICs - one with IP XX.XX.XX.XX and another with YY.YY.YY.YY - JDBC client URL was pointing to IP XX.XX.XX.XX, port 1521 - Using routing table, the client ZZ.ZZ.ZZ.ZZ was able to reach XX.XX.XX.XX - The default "LISTENER" was listening on YY.YY.YY.YY port 1521, (hostname resolves to this IP) - The LOCAL_LISTENER parametyer in SPFILE was NULL -- that is it was never set

I resolved this by doing the following: - Stopped the listener (lsnrctl stop) - Changed LISTENER.ORA to listen on XX.XX.XX.XX (instead of the hostname default of YY.YY.YY.YY) - Added an entry in TNSNAMES.ORA to set the local listener (LISTENER = (...)), essentially a copy of the entry used in LISTENER.ORA) - Added a parameter LOCAL_LISTENER = LISTENER in spfile (ALTER SYSTEM SET ... SCOPE=SPFILE) - Restarted the LISTENER (lsnrctl start) - Restarted the database

1

There's not really enough information to come up with a solution, but here are a few things to try.

Are you able to reproduce this outside the app? Do you ever see failures with tnsping? Can you ping the server reliably?

Is the app running multiple connections in parallel? Are there limits to the number of connections?

What about the network - is there a firewall between the app and the db?

4
  • There are multiple connections. The server allows 400. At any given time we typically run 160. I've checked at near peak times and we don't seem to be going much above that ever.
    – Jeff
    Jun 29, 2009 at 12:34
  • There is evidence to believe that this is happening to all apps running against this DB.
    – Jeff
    Jun 29, 2009 at 12:46
  • I have not seen tnsping failures. However the errors are so rare (2 or 3 times a day) that it's unlikely that it would happen during manual testing. Same with pinging the server. Every time I check it seems to be ok--but again for something that seems to happen perhaps 15 - 30 minutes out of a 24 hour period it's unlikely to occur during testing. I may set up an automated script and see what that turns up.
    – Jeff
    Jun 29, 2009 at 12:49
  • If it's happening to all apps, not just one, then that would seem to indicate a problem on the db side. These types of intermittent failures can be difficult and frustrating to track down - good luck.
    – chris
    Jun 29, 2009 at 19:51
1

Although this is treating the symptom rather than whatever network stack disease you have, you could try increasing the inbound_connect_timeout to see if the problem goes away.

To check what you're running now, invoke LSNRCTL on the database host and issue the command:

show inbound_connect_timeout

You have to change this in your db host's sqlnet.ora and listener.ora files:

sqlnet.ora: SQLNET.INBOUND_CONNECT_TIMEOUT = 100 (assuming 100 is greater than your current timeout)

listener.ora: INBOUND_CONNECT_TIMEOUT_yourLIstenerNameGoesHere = 100

1

Check also your firewall. Inbound port 1521 on the database server should be open (if you are connecting to the DB server from another server) .

1

I have just managed to resolve this issue, I have figured out that if you are running on DHCP server, make sure your host file has the current correct IP address, C:\windows\system32\drivers\etc\host (open it using notepad) & this will fix your problem.

1

Additional Tips: Please check your server/PC firewall settings. On some windows operating system such as Vista/Win7/Win Server; firewall is enable for SOHO network computers. Make sure to test all network groups private/office/public. Then stop the firewall service and test for the connection.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .