Re: "select" query to remote database fails to retrieve data.
Date: Mon, 2 Aug 2010 14:07:05 +0530
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 220.127.116.11 (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