Re: "select" query to remote database fails to retrieve data.
- From: Bill Ferguson <wbfergus@xxxxxxxxx>
- To: gidhin@xxxxxxxxx
- Date: Wed, 12 May 2010 12:47:14 -0600
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
