Re: Oracle refuses to shutdown

In article <374E2AFE.8AA2E83D_at_spin.ch>,   sjo_at_spin.ch wrote:
> Hi
>
> I mentioned a similar problem some times ago. I'm using a script to
make
> cold backups, which involves shutdown and startup of the database. The
> problem
> is, that Oracle refuses to perform the shutdown, if any transactions
are
> still
> open, regardless of using normal, immediate or transactional
parameter.
> It doesn't
> matter whether an SQLPLUS session is still open or whether a Java
> application has
> still JDBC connections open to the database. The result is a hang-up
of
> the
> backup script, and the final result is, that the backup isn't done. As
> soon as
> I log out of SQLPLUS resp. shut down my applications which have
> connections open,
> the shutdown is performed soon.
>
> This problem is a severe one for me, since my applications hold DB
> connections open
> and count on exceptions beeing thrown, if the database is shut down
and
> the
> connections become invalid.
>
> I'm using Oracle 8.0.5. for Linux. I heard that the shutdown command
> seems to
> have problems, but now I need a solution. At the moment all which
comes
> me to
> mind is to switch to hot-backups, but I really don't like to do it.
> Additionally
> I would like to shutdown the db whenever I like to, not beeing
dependant
> on
> any applications running on the db and not releasing database
> connections.
> And I don't think that Oracle behaves correctly in this case, at
least I
> interpret
> this behaviour as non-consistent to the documentation.
>
> bye
> --
> Sam Jordan
>

I do not know the cause of you problem, but here is a sql*plus script that will generate the sql to terminate all connected sessions using an 'alter system kill session': We run this on our older VMS system to aid shutdown in a timely fashion. I recommend that you run this script in sql*plus then alter the system into restricted session before going into svrmgrl to do the shutdown.

set echo off
rem
rem 1997 04 24 m d powell New script to help shutdown Oracle inst. rem
set feedback off
set verify off
set pagesize 0
set linesize 80
prompt
prompt This script generates a script to terminate all connected sessions
prompt in the user's home directory, executes it, and removes it when done.
prompt
spool $HOME/kill_list.sql
select 'alter system kill session '''||s.sid||','||s.serial#||'''; ' from v$session s
where username is not null
/
spool off
undefine tbl_nm
set feedback on
set echo on
@$HOME/kill_list
!rm $HOME/kill_list.sql
set pagesize 14

Note - Oracle will not allow you to terminate your own session so you do not need to exempt the sid of the session executing this script in the select.

--
Mark D. Powell -- The only advice that counts is the advice that  you follow so follow your own advice --

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't. Received on Fri May 28 1999 - 08:22:45 CDT