Re: Table Locking Problem

Mark D Powell wrote:
>
> In article <1SrL4.4736$E4.9913_at_newsfeeds.bigpond.com>,
> "Graeme Farmer" <g_farmer_at_halas.com.au> wrote:
> > But surely if ORACLE was busy performing these operations I would see
 at
> > least some CPU activity.
> > A lack of disk activity I can understand to a certain extent, but
 there was
> > no CPU activity for 15 mins which is what lead me to rather drastic
> > measures.
> > I have heard that shutdown immediate has been known to take a number
 of
> > hours and concur that it was probably not wise to do this (it was
 probably
> > not wise to kill the user session in the first place!).
> >
> > Mark D Powell wrote in message <8dkflq$j8s$1_at_nnrp1.deja.com>...
> > >I think it is the process monitor, pmon, which does the clean-up. As
> > >for as to why Oracle took so long to shut down when it appeared to be
> > >doing nothing after the shutdown immediate was issued I belive this
 is
> > >what happens:
> > >1 - you issue shutdown immediate
> > >2 - Oralce startes terminating and rolling back all in-process
> > >transactions plus it refuses new connection requstes
> > >3 - Completed transactions that are transmitting data continue to
> > >transmit
> > >
> > >I do not know the exact details of what qualifies under item 3 but
> > >there are certain types of activies that Oracle waits to complete on
 a
> > >shutdown immediate. I am not sure where you can find this in the
> > >manuals as the DBA Admin does not list this fact, but it is in the
> > >student notes in one of the Oracle DBA classes.
> > >
> > >In article <38FD6B74.A6174725_at_hotmail.com>,
> > > s_c_99_at_hotmail.com wrote:
> > >> Graeme, Mark
> > >>
> > >> As per my understanding:- when you kill a process
> > >> its status appear to be as killed but the process is
> > >> actually cleaned by SMON. SMON cleans up processes at
> > >> regular intervals (which is around 3 minutes).
> > >> Cant remember the parameter that sets smon timings (if any).
> > >>
> > >> you can perhaps try to write some shell script in unix
> > >> that will trigger smon process thereby clearing up the process.
> > >>
> > >> s_c_99_at_hotmail.com
> > >>
> > >> Graeme Farmer wrote:
> > >> >
> > >> > Mark,
> > >> >
> > >> > It definately took a lot longer for Oracle to release the lock on
 the table
> > >> > than it did to lock it. As for the user process I did check it
 and
 it was
> > >> > still there as KILLED but I wouldn't know if the serial# was
 changing.
> > >> >
> > >> > Can you, or anyone else, answer the question as to what Oracle
 was
 doing
> > >> > when I issued the shutdown immediate command and throughout 15
 minutes there
> > >> > was zero CPU/DISK activity? Surely if the db wasn't shutting down
 due to the
> > >> > locks being removed there would be some CPU activity as the locks
 were being
> > >> > removed.
> > >> > Ta,
> > >> > Graeme.
> > >--
> > >Mark D. Powell -- The only advice that counts is the advice that
> > > you follow so follow your own advice --
> > >
> It is possible that the shutdown immediate hung for some reason.
> Generally speaking shutdown immediate is the best way to stop your
> database. A normal shutdown waits for all users to disconnect which
> never happens, shutdown transactional [sp] waits for more things to
> finish than immediate, and abort is the last option. If you perform a
> shutdown immediate and it has not completed in five to ten minutes then
> you can open another svrmgrl session and issue the abort command.
>
> We always use immediate and normally our busy databases stop in 30
> seconds to five minutes.
> --
> Mark D. Powell -- The only advice that counts is the advice that
> you follow so follow your own advice --
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.

A common strategy for shutdown I've seen is to set off a background command which:

sleeps 10 mins
looks for /tmp/"sid"
if there, do a shutdown abort, if not, do nothing

and then you shut down the database with:

touch /tmp/"sid"
shutdown immediate
rm /tmp/"sid"

In this way, you get an immediate 'most' of the time and an abort when immediate gets itself in a knot..

HTH

-- 
===========================================
Connor McDonald
http://www.oracledba.co.uk

We are born naked, wet and hungry...then things get worse