Unresolvable intermittent hang.

[dba01]

Hi folks,

i'm an Oracle DBA troubleshooting for a client of ours who is running MS Access via Oracle ODBC against our 11g Oracle database in our data center halfway across the U.S. They are performing data pulls, executing various queries.

My company has been working on this issue for countless hours. We've checked firewalls, done client-side tracing in the Oracle SQLNet layer, tons of monitoring in the network, database, etc.

With tracing I've performed, when the customer encounters a hang state, both the ODBC client and the database server are waiting, expecting to hear back. This condition can occur for large queries, or smaller ones. It does not consistently hang after a certain number of minutes either.

I happen to believe there has to be an issue with their setup. They migrated to a new server (new hardware,software etc.) then moved their apps over. At the same time, we migrated them to a different database server and performed an upgrade from Oracle 10g to 11g.

Because this issue is intermittent, and "they haven't changed their queries", they are insistent the issue is on our end.

I know very little about MS Access. I'm looking for help on how to diagnose this issue - where to look, and how to troubleshoot.



Thanks for any help you can provide.

[RuralGuy]

It sounds to me like a Dropped Packet and both sides are waiting for a response. I hope there is no wireless links involved.

[dba01]

yes, the tracing suggests both sides are waiting for the other to respond. I'm wondering how I could use the client application to troubleshoot. For example, testing the MS Access database (I have a copy) from my own work station.

This would potentially permit me to test a different set of ODBC drivers, different network segments, etc. The customer has limited skills - they just run the thing, didn't build it.

The MS Access queries generate all kinds of database calls to the system catalog - it's not just a single SQL statement to the database.

[RuralGuy]

There are a lot of variables here. Did you make sure there are no wireless links yet?

[RuralGuy]

Do you have a copy of MS Access on your machine. Is it the same version as the client's?

[dba01]

The MS Access database runs on a Windows 2012 Server along with a SQL Server DB. They also make ADO.Net calls to the same Oracle prod database for SSIS, and it too has hangs. The common denominator is both ADO.NET and MS Access run off the same server, and utilize the same Oracle Net client for their respective drivers.

So I'm assuming by wireless links you mean wireless devices on workstations?

The customer at one point also had the ability to run the MS Access database on her workstation. I have a copy of that database on my workstation. Same version. She sent it to me. But I am not skilled in MS Access.

[RuralGuy]

In order to run an MS Access db your need either MS Access or the run time on your machine. In order to duplicate the problem you will probably need whatever the client has. Even then you may not be able to duplicate the issue.

[dba01]

The issue is resolved. The customer performed tests by-passing a router in their data center, and did not experience the hangs. The router was replaced.

[RuralGuy]

Thanks for the update. Glad they found the problem.