Re: shutdown mmediate gets hung

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. Received on Wed Feb 02 2005 - 19:52:59 CST