Path: dp-news.maxwell.syr.edu!spool.maxwell.syr.edu!drn.maxwell.syr.edu!news.maxwell.syr.edu!postnews.google.com!f14g2000cwb.googlegroups.com!not-for-mail
From: premmehrotra@hotmail.com
Newsgroups: comp.databases.oracle.server
Subject: Re: shutdown mmediate gets hung
Date: 2 Feb 2005 17:52:59 -0800
Organization: http://groups.google.com
Lines: 180
Message-ID: <1107395579.897558.32120@f14g2000cwb.googlegroups.com>
References: <1107190455.737530.190960@z14g2000cwz.googlegroups.com>
   <1107203098.561874.176260@z14g2000cwz.googlegroups.com>
   <1107211287.341434.150200@f14g2000cwb.googlegroups.com>
   <1107368583.196948.178830@o13g2000cwo.googlegroups.com>
NNTP-Posting-Host: 148.177.1.212
Mime-Version: 1.0
Content-Type: text/plain; charset="iso-8859-1"
X-Trace: posting.google.com 1107395583 20491 127.0.0.1 (3 Feb 2005 01:53:03 GMT)
X-Complaints-To: groups-abuse@google.com
NNTP-Posting-Date: Thu, 3 Feb 2005 01:53:03 +0000 (UTC)
In-Reply-To: <1107368583.196948.178830@o13g2000cwo.googlegroups.com>
User-Agent: G2/0.2
Complaints-To: groups-abuse@google.com
Injection-Info: f14g2000cwb.googlegroups.com; posting-host=148.177.1.212;
   posting-account=EhDFoAwAAACX_h_LFIzqy-IEYmI3KjNc
Xref: dp-news.maxwell.syr.edu comp.databases.oracle.server:234865


fitzjarrell@cox.net wrote:
> premmehrotra@hotmail.com wrote:
> > hpuxrac wrote:
> > > Prem, my first advice is to think about taking an oracle course
> like
> > > database fundamentals 1.
> > >
> > > Why do you shutdown your database every sunday?  Is it for a cold
> > > backup?
> > >
> > > Shutdown abort normally doesn't cause problems that oracle
instance
> > > recovery cannot deal with but most oracle dba's reserve using it
> only
> > > when absolutely necessary.
> > >
> > > When is it necessary?  Usually only when a shutdown immediate is
> > > hanging for some reason.
> > >
> > > No you should not change a startup to a startup restrict blindly
> > either.
> >
> > As you have pointed  out reason for shutting down database is for
> > "cold" backup.
> >
> > Reason for using shutdown abort is not to wait indefintely for
> shutdown
> > to happen late in the night if some process is connected to
database.
> > If  I am not  mistaken, Oracle recommends this procedure: shudtown
> > abort, startup, shutdown immediate when one is trying to put
shudtown
> > script for automatc shutdown during system reboots etc.
> > Script has always woked fine, problem hapened this Sunday.
>
> You should seriously consider re-writing this task, possibly using a
> script to kill user sessions prior to the shutdown:
>
> declare
> cursor get_reg_users is
> select sid, serial#
> from v$session
> where trunc(logon_time) > (select trunc(startup_time) from
> v$instance);
>
> sqltxt  varchar2(200);
> curr_sess exception;
> pragma exception_init(curr_sess, -27);
> begin
> for urec in get_reg_users loop
> sqltxt:='alter system kill session
> '''||urec.sid||','||urec.serial#||'''';
> execute immediate sqltxt;
> end loop;
> exception
> when curr_sess then
> null;
> end;
> /
>
> This may leave O/S processes still running, however.  Checking with
ps
> should reveal these processes:
>
> ps -ef | grep LOCAL
>
> Using this to create a list of processes to kill at the O/S level:
>
> set - `ps -ef | grep LOCAL | awk '{print $2}'`
> while [ $# -gt 0 ]
> do
> kill -9 $1
> shift
> done
>
> This, of course, presumes the current process is not connected to the
> database.  So, to put this together into a working script:
>
> #!/bin/ksh
>
> #
> # Set the environment
> #
>
> . $HOME/.profile
>
> #
> # Kill Oracle sessions
> #
>
> sqlplus /nolog << EOF
> connect / as sysdba
>
> declare
> cursor get_reg_users is
> select sid, serial#
> from v$session
> where trunc(logon_time) > (select trunc(startup_time) from
> v$instance);
>
> sqltxt  varchar2(200);
> curr_sess exception;
> pragma exception_init(curr_sess, -27);
> begin
> for urec in get_reg_users loop
> sqltxt:='alter system kill session
> '''||urec.sid||','||urec.serial#||'''';
> execute immediate sqltxt;
> end loop;
> exception
> when curr_sess then
> null;
> end;
> /
>
> exit
> EOF
>
> #
> # Kill O/S processes
> #
>
> set - `ps -ef | grep LOCAL | awk '{print $2}'`
> while [ $# -gt 0 ]
> do
> kill -9 $1
> shift
> done
>
> #
> # Shutdown the instance
> #
>
> sqlplus /nolog << EOF
> connect / as sysdba
> shutdown immediate
> exit
> EOF
>
> #
> # Backup the database
> #
>
> <your code here>
>
> #
> # Start the instance
> #
>
> sqlplus /nolog <<EOF
> connect / as sysdba
> startup
> exit
> EOF
>
> You could also use the dbstart/dbshut pair of scripts to
> shutdown/startup your instance, but that choice is up to you.
>
> I believe the code posted would prevent you from issuing a shutdown
> abort, possibly avoiding the shutdown issues you currently
experience.
> Of course you should test this on a non-production system, if
posible.
> David Fitzjarrell
David:

Thanks for the script. The problem which I had was there were no
sessions
connected to database, we already shutdown everything before
shtting down oracle yet shutdown immedate hung. I still want to know,
if I did shutdown abort, startup and then shutdown immedaite,
is it any better than doing shutdown immediate (in terms of getting
hung).

I have seen people doing a shutdown abort when shutdown immediate
appears
hung, and doing startup and shutdown immedaite, most of the time
it solves the hung problem.