Re: shutdown mmediate gets hung

From: <fitzjarrell_at_cox.net>
Date: 2 Feb 2005 22:06:43 -0800
Message-ID: <1107410803.611547.203700@z14g2000cwz.googlegroups.com>

premmehrotra_at_hotmail.com wrote:
> fitzjarrell_at_cox.net wrote:
> > premmehrotra_at_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.

I am addressing your original problem by providing another method to accomplish your goal. This has nothing to do with the current script you now run, it's designed as a *replacement* script to prevent the 'shutdown abort' from being used as your first line of attack. I have never used 'shutdown abort' as a front-line shutdown option, and I do not recommend its regular use. The script I provided does what one could want to ensure a safe, clean shutdown of an Oracle database without resorting to a 'shutdown abort', namely killing all user sessions from the Oracle side, killing the O/S processes driving those sessions, and finally shutting down the instance. Something is causing your 'hang', and my money is on a recovery operation necessitated by the 'shutdown abort'. You need to seriously reconsider what you're doing, and how you're doing it, and assess others ideas and suggestions on how to accomplish the same result. The script I posted is a much safer way to shutdown your instance. I would seriously suggest you consider using it, or something very similar to it, in place of the brute force operation you now use.

David Fitzjarrell Received on Thu Feb 03 2005 - 00:06:43 CST