listener procs: diehard

Oracle People:

I recieved a good tip from dbasupport to try the PROFILES with "resource_limit" and "IDLE_TIME". Thats great but 2 problems have occured:

1.) Once the user is killed by "IDLE_TIME" timeout his listener process remains untill he attempts another action:

-------
oracle 22701 22700 0 10:09:12 ? 0:00 oraclefreddy (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
-------
So I get stuck with ghost listener procs even though I got rid of the user.

2.) I try to use my brain and think- OK try to find the way to timeout listener procs. So I try the following which doesn't seem to kill off the ghost listener procs:

edit listerner.ora:
---------------------------
(SID_DESC =
(GLOBAL_DBNAME = freddy)
(ORACLE_HOME = /u00/app/oracle/product/8.1.7)
(SID_NAME = freddy)
(PRESPAWN_MAX = 99)
(PRESPAWN_LIST =
(PRESPAWN_DESC =
(PROTOCOL = TCP)
(POOL_SIZE = 10)
(TIMEOUT = 5)
)
)
----------------------------------

I guess I guessed wrong and this timeout is only for prespawned procs. If so ...

How do I automatically get rid of listener procs that hang around after the user is killed. This is bad because I seem to get these ghost procs from web servers.

Thanks,

Roger

Ghost listener processes from redundant connections?

I think you're referring to the wrong thing here. Once the listener has passed your connection through to the database, it goes back to listening for other potential connections.

There will be no continuing interaction between your created user session and the listener.

Originally posted by rogerF:

Quote:

---

Once the user is killed by "IDLE_TIME" timeout his listener process remains untill he attempts another action:

---

There is no 'dedicated' listener for each user. Listener only runs on the host and there is generally only one listener process running in the server for all user connections connecting to a particular db from a particular port.

I think you are confusing issues here. Do you mean dedicated server process?

Apologies !

I used the wrong terminolgy. I realize there is 1 listener. I meant that the process that shows up in the Unix proc table shown with "ps" remains after the user is "killed" from the SQL prompt:

---------------------------------------------------------
oracle 6763 6762 0 16:28:17 ? 0:00 oraclefreddy (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
---------------------------------------------------------

When the remote client attempts another action( which he often does NOT do(think ghost web procs) ) the process shown in the Unix ps command die.

Rational ClearQuest web is "known" to leave these wonderful little procs and I must deal with it :-) Using the IDLE_TIME helps but doesn't kill the Unix proc untill the user attempts another action- Ghost web procs don't attempt later actions.

So I need to get rid of(auto) the procs that remain despite that they have been killed in SQL with alter kill session.

Thanks,

Roger

Probably PMON has not cleaned up the user processes which are no longer active.
You can kill them at the os level or by alter system kill session command.

just to follow up VINIT's post:

PMON is yet to clean that process that just been killed in sql.
So if you are going to wait a little longer let's say 3-5mins, then PMON will do the cleanup operation.

Vinit:

I thought the same ... but there is a problem. My scripts that show the users show the Unix proc id#. The web connections don't show as Unix proc# - just the port #:

-------------------------------------------------------
CQ05 / cqweb dllhost.exe-NTS31(ERDSE087\NTS 13/53909 INACTIVE 1632:296 22-NOV-2001:15:53:28 19:35:59
31 ) /
22-NOV-2001:15:54:07
-----------------------------------------------------

This previous is the result from a script that uses:
V$SESSION

As you see the "Unix process" is shown as "1632:296" which are the port numbers NOT the Unix procs. The script does however work to show "Unix Procs" when run on a normal(non web) sql connections as shown here:

--------------------------------------------------------
ROGER / oracle [email protected] 12/9327 INACTIVE 17024 23-NOV-2001:11:33:34 00:00:28
e (TNS /
V1-V3)-pts/3(ora04.era-a.erics 23-NOV-2001:11:33:33
son.se)
-------------------------------------------------

So, the problem is that I can't identify the correct Unix procs to kill off when dealing with web connections that leave Unix procs after being killed with kil session.

Catch 22 - Rational says I should kill them manually but I can't readily identify them. If they were normal SQL connects I could easily see the Unix Proc and create a script to cleanup.

This is a bit strange but a real problem.

-Roger

1) Web or No Web, each Oracle connection has to have a Server process created at OS level and hence
for both cases there has to be a corresponding OS PID , unless u r using Application Server
connection pooling than one connection serves many sessions.

So the only problem which i can think of is if u r not using connection pooling and using single common
Oracle account for all Web connections, then how to identify which Oracle session points to which
Web connection.This is an application development issue , use dbms_session.Use this to identify.

And Port number u found on OS should be common for all connections , since it would be for listener
if u rusing Listener.

Also Why would PMON care to clear up OS hung process, it just cleans up Oracle sessions after crash/killed.