Re: Dealing with "orphaned" sessions

I checked, and AUDIT_TRAIL=NONE

On Thu, 31 May 2001 23:07:17 -0500, "Spencer" <spencerp_at_swbell.net> wrote:

>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.)
>>
>
>

--
Ed Stevens
(Opinions expressed do not necessarily represent those of my employer.)