Re: lingering session causing shutdown immediate to hang

hastenthunder wrote:
> I have a strange problem on Oracle 10g/Red Hat Enterpirse 3
>
> One of the user's session was accessing some procedure in her own schema,
> which was preventing her from recompiling it.
> I tried to do "Alter system kill session 'sid, serial#', but the session
> does not die, it just got marked as "killed".
> I waited 40 minutes for that session to die, and finally gave up and used
> UNIX command "kill -9" to terminate her OS process. Strangely, the session
> still shows up in v$session.

Standard behaviour. When you kill a session using 'alter system', it is merely marked as killed, and is not actually killed until the user next attempts to do something new.

If the user never actually attempts to do something new (like trying to commit an update, or to issue a new select statement) then the session never actually gets killed off and will indeed remain listed in v$session.

It is at that point that you can indeed resort to kill -9 (or orakill on Windows).

So what you've done is entirely the right thing and done in the right order (you should not kill -9 first, just in case you were tempted).

> I then tried to restart the instance by issuing "shutdown immediate", and
> the database hanged (it was hanging for the past hour and half).

Difficult to know whether it really was hanging, or just taking its time. Who knows whether SMON was attempting to coalesce something, for example.

> This is
> really strange, since there were no active transaction before I tried to
> shut the database down, I'd expect the database would be down fairly
> quickly. And no, no one was trying to logon while I was performing the
> shut-down.
>
> It seems the only alternative is to perform "shutdown abort" and then
> instance recovery. But what should I do if startup hangs after the database
> got shutdown abnormally?

Shutdown abort followed by startup restrict followed by shutdown immediate is actually quite common.

Why do you think the startup might hang? It can certainly take a long time if there were lots of pending transactions at the time you did the shutdown abort -they all have to be rolled forward using the current redo log contents. But assuming you haven't lost your current online redo log (in which case, you'd have more problems than just a hang on startup!), the shutdown abort is quite safe, and the subsequent startup will get there in the end.

You do multiplex your online redo logs, don't you? As well as hardware mirror them??

Regards
HJR
>
> Thanks for your help
>
>
Received on Tue Nov 30 2004 - 16:49:44 CST