Re: "select" query to remote database fails to retrieve data.
- From: Niall Litchfield <niall.litchfield@xxxxxxxxx>
- To: "gidhin K. joy" <gidhin@xxxxxxxxx>
- Date: Mon, 2 Aug 2010 13:10:16 +0100
Thanks for the interesting feedback - did it require something like
wireshark in the end to diagnose?
On Mon, Aug 2, 2010 at 9:37 AM, gidhin K. joy <gidhin@xxxxxxxxx> wrote:
>
> This issue was resolved, Just thought to share so that it will be helpful
> for someone else.
>
> Apparently all the trouble was due to a new juniper firewall installed
> which would fragment and drop the traffic that would too big to handle. We
> have configured SDU to lesser size - 2048 at client side and all the
> queries works just fine with a minor performance hit.
>
> I am not sure what was the default SDU size, however looks like oracle
> connection negotiates and selects the lower SDU size among the server and
> client.
> ~Gidhin
>
> On 13 May 2010 00:17, Bill Ferguson <wbfergus@xxxxxxxxx> wrote:
>
>> I had a somewhat similar experience a few months ago. Without getting
>> actual confirmation from Oracle, it somewhat seemed like the security
>> on SQL Net traffic was tightened up some with 11g, whereby if
>> firewalls aren't specifically allowed to pass SQL Net traffic, they
>> seem to do some packet sniffing and this winds up in the traffic not
>> getting completely sent to the client (even though the server did send
>> it all), so everything gives the appearance of hanging simply because
>> the server is waiting on a confirmation from the client and the client
>> is still waiting for the data.
>>
>> However, my problem was the server is running 11.1.0.7 (on Windows),
>> while the clients that were experiencing problems were running the
>> 10.2 drivers. Even with the newer 11g drivers the problem still
>> persisted, but clients using 9.x drivers did not have the problem.
>> After several weeks of going around and around with the network folks
>> and sending them the Oracle notes from MOS stating exactly what to
>> look for and exactly what to do to fix it, I finally had to take a
>> computer over to their office and show them the exact error on my
>> machine, then let them watch the firewall traffic. After about 30
>> minutes of them fiddling around, one guy finally clicked on a screen
>> in the firewall configuration menu that showed the exact setting
>> Oracle said need to be changed, they allowed SQL Net traffic, and
>> suddenly all of the problems disappeared.
>>
>> One of the "symptoms" of the incorrect firewall setting was that
>> something as simple as "select * from table_name" had the appearance
>> of hanging, while a similar connection doing a "describe table_name"
>> worked.
>>
>> I forget which MOS documents I found that explained the exact cuase
>> and solution to the problem, but I found them by researching the
>> TNS-12535 error that I found in my listener's log file.
>>
>> Hope this helps some.
>>
>> --
>> -- Bill Ferguson
>>
>
>
--
Niall Litchfield
Oracle DBA
http://www.orawin.info
Other related posts:
