Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: shutdown mmediate gets hung

Re: shutdown mmediate gets hung

From: <fitzjarrell_at_cox.net>
Date: 2 Feb 2005 10:23:03 -0800
Message-ID: <1107368583.196948.178830@o13g2000cwo.googlegroups.com>

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 Received on Wed Feb 02 2005 - 12:23:03 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US