Re: "select" query to remote database fails to retrieve data.
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_at_gmail.com> 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
> On 13 May 2010 00:17, Bill Ferguson <wbfergus_at_gmail.com> 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 18.104.22.168 (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"
>> 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 -- http://www.freelists.org/webpage/oracle-lReceived on Mon Aug 02 2010 - 07:10:16 CDT