Re: Dealing with "orphaned" sessions

i'm not sure this will be of any help... but...

is auditing enabled for this database?

i recall having exactly the same problem with "shutdown immediate" on an 8.0.6 database (on hp-ux). if i remember correctly, auditing had been enabled, but nothing was being audited...

before we got around to disabling the auditing, we found ourselves resorting to "shutdown abort", "startup restrict", followed by a "shutdown immediate"...

i know, i know, not an elegant solution, but hey... i get tired waiting after four hours for a shutdown immediate to complete. after waiting for that long, i was tempted to reboot the entire box. oracle was definitely waiting for something...

and this was not a one time deal, it happened consistently.

"Ed Stevens" <Ed_Stevens_at_nospam.noway.nohow> wrote in message news:3b16a58d.24469274_at_ausnews.austin.ibm.com...
>
> On Wed, 30 May 2001 23:49:40 +0200, "Sybrand Bakker"
> <postbus_at_sybrandb.demon.nl> wrote:
>
> >Comments and answers embedded
> >
> >Sybrand Bakker, Oracle DBA
> >
> >"Ed Stevens" <Ed_Stevens_at_nospam.noway.nohow> wrote in message
> >news:3b1517ce.14775856_at_ausnews.austin.ibm.com...
> >> Subject: Dealing with "orphaned" sessions
> >>
> >> Platform: Oracle 8.0.5, Std. Ed.; Windows NT 4.0, sp6
> >>
> >> Every Saturday evening we have a short maintenance window, during
> >> which we have the servers hosting our Oracle db's rebooted. Most of
> >> the servers host multiple Oracle databases. I have created a command
> >> file that the operators execute before cycling the server. One of the
> >> things this file does is execute a SQL file to log on to each database
> >> and issue a SHUTDOWN IMMEDIATE.
> >>
> >> For several weeks, this process seems to hang at one particular
> >> database. The logs indicate that the connection is successful and the
> >> SHUTDOWN is issued, but up to an hour later we are still waiting for
> >> the shutdown to complete. Due to time constraints we eventually have
> >> to pull the plug and depend on crash recovery when we restart.
> >>
> >> Looking at V$SESSIONS and V$SESSION_WAIT, I see an inordinate number
> >> of sessions on this database. The application only runs on 4 walk-up
> >> touch-screen kiosks. Those and a couple of admin machines are all the
> >> connections the db should be getting, yet there will be well over 100
> >> sessions. Further investigation of V$SESSION_WAIT shows that the
> >> vast majority of these sessions are waiting for "SQL*Net message from
> >> client" with SECONDS_IN_WAIT ranging up to several days! The nature
> >> of the application allows users to walk away, but the application
> >> developer (with whom I have a very good working relationship, and
> >> trust to be shooting straight) insists the app checks this and issues
> >> a disconnect after a few minutes of inactivity.
> >>
> >> Question #1: Am I mis-interpreting V$SESSION_WAIT when I assume that a
> >> session showing "SQL*Net message from client" and SECONDS_IN_WAIT of
> >> 179909 (49.97 hours) probably represents a session that didn't get
> >> disconnected and someone initiated a new session from the same
> >> machine?
> >>
> >You'r 100% correct. You could also check the command column in v$session.
 It
> >will be 0 is the program is doing nothing.
> >Similar info is in last_call_et, which seems to be the number of
> >milliseconds since the last call.
> >
> >
> >> Question #2: Would waiting for SHUTDOWN IMMEDIATE to roll these back
> >> be causing my shutdown problems? If not, what else should I be
> >> looking for? If so . . .
> >
> >
> >Likely
> >>
> >> Question #3: If I were to write a script to detect these and
> >> terminate them, would that not generate the same rollback delays as
> >> simply issuing a SHUTDOWN IMMEDIATE.
> >
> >
> >
> >My experience tells me as long as you get the spid of the associated
> >v$process record (use session.paddr to join to process.addres)
> >and issue a kill -9 (Unixes) or orakill (NT on that process), you don't
 have
> >that problem.
> >
> >>
> >>
> >>
> >> --
> >> Ed Stevens
> >> (Opinions expressed do not necessarily represent those of my employer.)
> >
> >
> After going over this some more with the application programmer, I'm
> beginning to wonder if I'm chasing a red herring.
>
> While all of the "orphaned" sessions look suspicious, from what he
> tells me, there really wouldn't be that much activity to roll back on
> a SHUTDOWN IMMEDIATE. The app is an ordering system running on a kiosk
> -- a cabinet containing a completely enclosed PC, with the only user
> access being the face of a touch-screen monitor. When they begin a
> session, a row is updated to "flag" that the application is in use. As
> they step their way through several pages of info, the only database
> activity would be a series of SELECTs. Only when they confirm a
> completed order does the app issue an INSERT command, after which it
> immediately does a disconnect and recycles back to its "Welcome"
> screen.
>
> Is there anything else I should/could be looking at that could cause
> inordinately long shutdowns?
>
>
> --
> Ed Stevens
> (Opinions expressed do not necessarily represent those of my employer.)
>
Received on Thu May 31 2001 - 23:07:17 CDT