Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: shutdown mmediate gets hung
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