Oracle Remote server don't allow user to disconnect
Massimiliano Saba
I have a problem with proxy-table and user disconnection that happens very
rare. The most of the time I do not have any issue but sometimes I do.
I run a Sybase Anywhere Server 9.0.2.3527 and I use a proxy table on a
remote server (Oracle 10g). I am using the Oracle wire ODBC driver 10
version 5.10.00.124. I use a READ ONLY table from the remote server and my
software run its query (SELECT * FROM ...) without ANY TRANSACTION.
The problem happens when the user disconnect from the DB, sometime it is
unable to do that and the properties stuck in:
ReqType: Disconnect
ReqStatus: Executing
If I manually drop the connection the Engine die and I have to restart it.
It seems the Oracle connection remain "pending" and I do not have any tool
to drive it from Sybase.
Has anybody seen that? Any Suggestion? How I can overcome the situation
without dropping the connection (killing the engine)?
Kind Regards
Max
Unknown
c:\windows\temp\sa_dump.dmp
file?
If it did, collect that and open up a support ticket so
someone in engineering can drill into this more deeply.
You should be able to get some partial relieve by
increasing the -gn setting (default 20). Remote
connections can double the number of threads
tied up so once you hit 10 connections (with just
1 remote server) you are at risk of running out of
threads and starving the server of server threads.
FWIW, to me it sounds like either the ODBC driver
is not getting the disconnect notice or ORACLE is
not releasing the connections. It could be the ORA
client layer failing us. Also make sure you have grabbed
the latest ORACLE [from the latest 10.0.1 EBF; I believe]
Do check out your ORA client libraries and if there
are any fixes available for them. Noting the version
may be important to technical support/engineering
as well.
You should be able to force the existing connections
to disconnect/drop using an
ALTER SERVER <your_ora_remote_srvr>
CONNECTION CLOSE [ALL | connection-id];
from any other connection. [after increasing -gn]
"Massimiliano Saba" <m...@automationpartnership.com> wrote in message
news:47b5a132$1@forums-1-dub...
Massimiliano Saba
I have just checked out and I can't find the file, so I assume the engine is
not generating it.
Now I have updated the -gn setting to 40 and we will see if it fix or no.
Unfortunately this issue it is not periodic/systematic (or we are unable to
identify wich sequence of events induces the error). I am keep monitoring
and let you know. Thank you for your help.
Regards
Max
"Nick Elson" <@@@nick@@@.@@@elson@sybase@@@.@@@com@@@> wrote in message
news:47bb0fbd$1@forums-1-dub...
Massimiliano Saba
the only further detail I have noticed is the memory occupation of the
engine 4 time the standard value (200 MB).
Any suggestion?
Regards
Max
"Massimiliano Saba" <m...@automationpartnership.com> wrote in message
news:47b5a132$1@forums-1-dub...
Unknown
Trying other ODBC drivers may help diagnose/stabilize
the situation. You have another Oracle driver supplied
with the version 9.0.x installation. Since you are an Oracle
shop you should also have access to their ODBC driver.
Of course there are contingencies with those but if any of
those are O\W stable enough for your purposes, you may
be able to get at the root cause of this or even a stable
production situation.
At one level it seems the disconnect is not being reported
back to the database server (for whatever reason) and I do
not see an exact cause or know of a way to affect that.
Capturing what is happening at the ODBC API layer (either
with ODBC tracing or setting the option CIS_OPTION=6)
may tell you more.
About the only other thing I could think of trying next is to
check into V10.0.1 and the Oracle ODBC driver available
with that. You will also need to have the Oracle Client
software installed for that.
"Massimiliano Saba" <m...@automationpartnership.com> wrote in message
news:47be8535@forums-1-dub...