I got exactly what I was looking for.You are
simply great Tom.The ORACLE world in IT just
cannot do anything without people like you.


Many Thanks
Om

Thanks Tom, that was one of the "blind spots" I had in mind regarding connections.

By the way... When you kill a user, its status is marked KILLED in V$SESION.

I was told, or read somewhere that, from time to time, PMON wakes up to clean these entries. But I had a system running during weeks and the entries were still there.

1) Is that true ?

2) Is there any way to really remove those "dirty" entries ?

3) What is the bevahior of Dead Client Detection towards the entry in V$SESSION. Is it marked KILLED ?

Thanks again !

Hello,
I am Sun Solaris 8 , 8.1.7.0.0
Have tried getting DCD to work ; both with MTS and dedicated server mode.
Have these entries in my sqlnet.ora:

sqlnet.expire_time=1

trace_level_server=16

trace_directory_server=/disk01/oracle/product/8.1.7/network/trace


However on reastarting the listener; the following on the *.trc files shows :

grep -i dead *.trc
svr_2051.trc:niotns: Dead connection detection being turned off.

Also noticed that 'dead' client connections are not being cleaned. It is a pain ; though managable in a direct connect mode ; though a showstopper in MTS....

Is this something version specific or does DCD really work?

Thanks.

Hi Tom,

Apart from the BG process Running in my System i have got two process which mimic the BG process in the sense that the username, OSuser all are null.

But the lOGON_Time Of These sessions are Geting updated to the current time...

What are these process ???

Thanks.

Regards,
Ganesh Raja

Hi Tom,

Sorry about that ...

Should have given u more information.

OS : WIN 2000 Sp2
Oracle : 8.1.7.2

Output from v$session


SID SERIAL# LOGON_TIME PROGRAM STATUS
---------- ---------- ------------------- -------------------- --------
1 1 09.02.2002 17:05:33 ORACLE.EXE ACTIVE
2 1 09.02.2002 17:05:33 ORACLE.EXE ACTIVE
3 1 09.02.2002 17:05:34 ORACLE.EXE ACTIVE
4 1 09.02.2002 17:05:34 ORACLE.EXE ACTIVE
5 1 09.02.2002 17:05:35 ORACLE.EXE ACTIVE
6 1 09.02.2002 17:05:36 ORACLE.EXE ACTIVE
7 22345 10.02.2002 09:02:47 ACTIVE
8 22345 10.02.2002 09:02:47 ACTIVE
9 1 09.02.2002 17:05:36 ORACLE.EXE ACTIVE
10 20193 10.02.2002 08:47:13 sqlplus.exe ACTIVE


As i told u the other values like the OSUSER, TERMINAl, MACHINE and all are null..

What are these...

Again a Sorry for the previous Post !!!

Regards,
Ganesh R

A simple doubt:
Oracle Documentation (Oracle Concepts) says:
"Like SMON, PMON wakes up regularly to check whether it is needed, and can be called if another process detects the need for it."What does "regularly" mean? Every seconnd? Milisecond?

Thank you very much!!!

Sir,

I am using 8.0.4 and would i be able to set DCD for this version..

ALSO i didn't found sqlnet.ora in my server..
Only Tnsnames and listener files are present...

So how anyone can connect without sqlnet.ora?
And if i have to set DCD have i have to create sqlnet.ora?

Thanks.
atul

Tom,
We are using Oracle 9.1, on windows NT which at times when shuting down normally (using immediate option) gives waiting for active calls errors, on investigation we have concluded that this is due to oracle making calls (selects) to non-oracle systems using 'hsodbc' and if the non-oracle system is down the session hangs, since all calls to non-oracle systems start a transaction, rollback segment is used even for selects, which is not being rolled back as the session is hung state and the database cannot be brought down with the immediate option with a session in hanged state.
we have to use abort option to shutdown the database in such situations.
If we use DCD whats are the chances that the database will go down smoothly, i.e. if the database is being brought down with immediate option, will DCD be used on hanged/dead connections to rolled them back. I mean does it come in play when the database is in the process of being shutdown but can't shutdown as some sessions is hung up.
We are also using Jobs to do some batch processing, does DCD in any way affect job processes.

As far as the need to shutdown goes, We do need to shutdown the database everyday for Backup, Company policy is for cold backup and it will remain so untill the disastor and recovery using hot backups have been throughly tested by the org, which can be quite a few months

Thanks in advance

Nihal

Hi Tom,
Is there a way to detect if the DCD has been enabled, i have put the expire_time in the sqlnet .ora, but how do i asertain that what i have done is right, what i need to know is that is there a command which can be executed in sqlplus or thru lsnrctl which will tell us that expire_time is 1 minute.

The connection architecture is as follows Using jdbc to connect to Oracle which in turns connects to remote DB using hsodbc or using pl/sql to connect to Oracle which in turn connects to hsodbc, there is a client in both the cases which is not the server that will execute the sql statement, for the remote db Oracle server is the client, the lock is at the oracle database level hence if the client (Java/plsqlplus does not respond ) then oracle should rollback the process.

Dead on to what I needed. Thanks

Hi Tom,

I´ve configured DCD, but it seems to be working a litle late.

My SQLNET.EXPIRE_TIME is 30 min but in the tests I´ve done, the dead connections went away after about 50-60 min (quite after the expected).

I´m running Oracle 9iR2 on Linux Advanced Server 2.0. The tests were done in clients running on Win 98 (Oracle Client 8) and Win XP (Oracle Client 9).

My tests were just like that:

* Connect on SqlPlus
* Powerdown the client machine (direct in the power button)
* Trace the dead client session in V$SESSION every 5 minutes, until it´s gone

My guess is there is some kind of problem between Windows clients on Linux Server.

Do you have any idea? Have you seen this before?

Once again, thanks very much.

Hi ...

When we kill a session then it marked "killed" in v$session will be removed
when client will try to connect again ...

I have some doubts ...


1. After killing session ' alter system kill session '
if I abort the client application forcely .. then when will the entry be removed from
v$session.

2. Why, we need to use kill -9 after killing the session from oracle.


3. what is dead connections.


Thanks,
Js

Tom,

I believe sqlnet.expire_time is useful for identify and kill client-server connections. What if the connection is server-server via DB Link. Would sqlnet.expire_time still be useful to disconnect sessions.

Thanks

Dilip.

Tom,
Sorry for my simple question. But I want to clear my fundas.
here is the question :
PMON wakes up after 3 seconds to clean up terminated / killed process. But suppose at a moment a process got killed who was holding locks and resources. at same time other session (withing fraction of second after killed 1st session) need the locks and resources of 1st session.
But PMON will wake up after 3 seconds.
Will 2nd session waits for 3 seconds for PMON to wake up.
OR
second session will initiate the PMON to clean up the session .
OR
second session will get the message of resource busy ?


Please explain.
Thanks a lot in advance.

Kishor

Tom,
I've setup a profile to "snipe" idle sessions couple days ago and monitor sessions' activity in the database. Today I've noticed about 25 "sniped" sessions that have been idle for quite some time. I called one of the users and asked if he can try to do something within application to make his session to go away, but he said he doesn't have it open at all (TOAD). I've asked him to open TOAD again and see if his old sessions are still there, and they were not. He also told me that he clicked on "x" on the top-right corner to close his app. I have SQLNET.EXPIRE_TIME set, but looks like DCD didn't work in this case. I searched your site and found your reply to one of the people that states:
"Followup:
by closing the window, the server has no idea the connection is broken, that
there is no client (windows tcp/ip doesn't tell the server "i've gone away")"

Is this true that when "x" is used to close the app, then server doesn't know that the client is gone, and if that is, then what else can I do to detect/disconnect those sessions?

Thanks.

After dcd detection, and pmon releases the resources,
what will be the status of the session as seen in v$session

TOm:

How dod you check this parameter sql net expire time and how do you set it up? Is this why we are getting there are too many database connections message when there is no one logged in? a lot of dead sessions?

thanks

Tom:

1. Is this correct? when you have a web application using mod_plsql connecting to oracle 9i database configured with number of processes=150, every time a web user logs in (aplication authentication) and calls a page it is considered a connection. Does that mean that after calling 150 pages the number of connections will be exceeded and then system will fail? Can you explain how does that work?

2. In a web system (stateless) what does constitue a database connection is "alive" or "dead".

3. Does the parameter "processes" include a count of all dead sessions?

4. I am trying to find the best solution to resolve the error "ORA-00020: Number of Connections exceeded" in a web app.

If you read this link

http://www.experts-exchange.com/Databases/Oracle/Q_20164850.html

it suggests that the EXPIRE_TIME only works for client/server environment.

Also, ccreating a profile which sets the IDLE_TIME does not seem the best solution.

Would creating a procedure to kill sesssion and schedule it nightly be the best solution?

Create or replace procedure KillSession(idleTime in Number)
as cursor c1 is
select sid,serial#,trunc((last_call_et - trunc(last_call_et/3600,0)*3600) / 60,0) TMIN from
v$session
where status='INACTIVE' and username not in ('SYS');
VString varchar2(50);
begin
for c1rec in c1 loop
if c1rec.TMIN > idleTime then
Vstring := 'alter system kill session

'||chr(39)||c1rec.sid||','||c1rec.serial#||chr(39);
execute immediate(Vstring);
end if;
end loop;
end;


THank you,

TOm:

Can you briefly explain how:

1. what consitute a connection in a oracle web system. the mod_plsql is the client for the database.

2. What is the best solution to avoid the "Too many connections" issue in a web environment?

Thanks,

Tom:

1. My question was what is a "Connection" physcially. When i call a page in asktom do i create a connection for one second and it goes away. Do I have an allocated connection for me? when someone else calls does that create another connection?

2. You say the best solution is to avoid too many connections. What about all these solutions for deleting dead connections from DCD to IDL_TIME to procedure deleting dead session?

Is there any reference in you book for how to resolve this.

TOm:

BUt we are working in 3 tier environment and it did happen? Is there a permanent solution for it instead of "Avoiding it" which I am not sure how.

Hi Tom,

My database version is 10.2.0.1.
I have a very unusual situation.

I am listing sequence of events done -

1. One user logged in to database with sqlplus from a client.
2. He updates a table and did not commit.
3. I disconnected the client machine by removing network connection.
4. The session is still in v$session and lock are shoing in v$lock/dba_locks.
5. This had taken about 2.5 hours to release.Client machine is still disconnected.

But my question is should or should not PMON have cleared this quickly(3 secs/1 min)..at least released resources(locks).I know about dead client detection(with sqlnet.ora) but I could not use it here.I also tried to use 'oradebug wakeup <pid of pmon>', but still it didn't help.

I am eagerly waiting for your inputs.

Thanks
Subhasis

Hi Tom,

Thanks a lot for your valuable inputs!!!!

Now, continuing more in this regard, we have observed if the connecting client is Redhat linux AS-4(in place of windows XP), then the connection did not die when the network came back.

Let me explain a bit further.

1. Opened a sqlplus session from a linux client.
2. Disconnected network.
3. Issued a sql...it hanged(network still disconnected).
4. After 15-20mins, reconnected network with the client.
5. The select statement gave its output.
But it case of windows client platform it starightway gave 'end of file communication channel' when a sql was fired from a disconnected session.

I hope this a due to different TCP protocol stack behaviour for different OS(linux and windows).How do I can make this behaviour in sync, as my client environment can be heterogeneous(linux/windows).

Can you please throw some light on this.

Thanks
Subhasis

Tom,

What do you have to do to enable dead client detection? Do you just edit the sqlnet.ora file and that's it? Do we have to bounce anything?

"""
tcp_disconnect.py
Echo network data test program in python. This program easily translates to C & Java.

By TCP rules, the only way for a server program to know if a client has disconnected,
is to try to read from the socket. Specifically, if select() says there is data, but
recv() returns 0 bytes of data, then this implies the client has disconnected.

But a server program might want to confirm that a tcp client is still connected without
reading data. For example, before it performs some task or sends data to the client.
This program will demonstrate how to detect a TCP client disconnect without reading data.

The method to do this:
1) select on socket as poll (no wait)
2) if no recv data waiting, then client still connected
3) if recv data waiting, the read one char using PEEK flag
4) if PEEK data len=0, then client has disconnected, otherwise its connected.
Note, the peek flag will read data without removing it from tcp queue.

To see it in action: 0) run this program on one computer 1) from another computer,
connect via telnet port 12345, 2) type a line of data 3) wait to see it echo,
4) type another line, 5) disconnect quickly, 6) watch the program will detect the
disconnect and exit.

I hope this is helpful to someone. John Masinter, 17-Dec-2008.
"""

import socket
import time
import select

HOST = '' # all local interfaces
PORT = 12345 # port to listen

# listen for new TCP connections
s = socket.socket(socket.AF_INET, socket.SOCK_STREAM)
s.setsockopt(socket.SOL_SOCKET, socket.SO_REUSEADDR, 1)
s.bind((HOST, PORT))
s.listen(1)
# accept new conneciton
conn, addr = s.accept()
print 'Connected by', addr
# loop reading/echoing, until client disconnects
try:
conn.send("Send me data, and I will echo it back after a short delay.\n")
while 1:
data = conn.recv(1024) # recv all data queued
if not data: break # client disconnected
time.sleep(3) # simulate time consuming work
# below will detect if client disconnects during sleep
r, w, e = select.select([conn], [], [], 0) # more data waiting?
print "select: r=%s w=%s e=%s" % (r,w,e) # debug output to command line
if r: # yes, data avail to read.
t = conn.recv(1024, socket.MSG_PEEK) # read without remove from queue
print "peek: len=%d, data=%s" % (len(t),t) # debug output
if len(t)==0: # length of data peeked 0?
print "Client disconnected." # client disconnected
break # quit program
conn.send("-->"+data) # echo only if still connected
finally:
conn.close()

Hello Tom:

Is it possible to set the SQLNET.EXPIRE_TIME for multiple database hosts from a singler central location or do you have to make the entry in the SQLNET.ORA file on each host?

I use a group policy in active directory to set the TNS_ADMIN parameter to point to single TNS_NAMES file on the LAN. Would this work for the SQLNET.ORA? Is there a better way?

"By default, sqlnet.ora is located in the $ORACLE_HOME/network/admin directory on UNIX operating systems and the ORACLE_HOME\network\admin directory on Windows operating systems. sqlnet.ora can also be stored in the directory specified by the TNS_ADMIN environment variable."

Oracle® Database Net Services Reference Guide

Hello Tom:

Will DCD work if a client connects to a database then queries an HSODBC data source across a data base link?

For example I connect with toad to the Oracle DB then issue a query to the HSODBC database (SQLServer) then shutdown Toad without logging out of the session.

very good efforts

Hi Tom,

DCD has a limitation:

"Dead connection detection is not allowed on bequeathed connections."

I try testing this by connecting using sqlplus. 'ps' shows that my connection is bequeathed'

oracle 11504 0.1 0.6 1206848 26672 ? Ss 08:42 0:00 oraclemynewdb (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))

I then killed my sqlplus using the 'kill' command. When I checked if my session is still there, it is gone.

So how did my session get killed?

I am using version 11.2.0.1.0.

Than you.

Hi Tom,

If the client does not gracefully ends it session(say it is killed) how long before its corresponding session is removed? Or will the session stays there forever?

Thank you.