Question: I have an Oracle shutdown script which
usually works fine, but the shutdown hung with these messages in the
alert log:
SHUTDOWN: waiting for active
calls to complete.
*** 2015-12-22 10:59:09.298
SHUTDOWN: waiting for active calls to complete.
SQL> connect /as sysdba
ERROR: ORA-01089: immediate shutdown in progress - no operations are
permitted
How do I make Oracle shutdown immediately?
Answer: Oracle has a misnomer with "shutdown
immediate", as this shutdown mode will wait for in-flight transactions
to complete! See these notes on the
different types of shutdowns and here if you want pre-written
shutdown
procedure scripts.
For more complete details on diagnosing a hung database
with Oracle Utilities, see the book "Advanced Oracle Utilities: The
Definitive Reference".
In my production databases, everything is on a schedule, and the
shutdown must happen at the appointed time. The shutdown abort
command is pretty much a guaranteed way to get your database to
shutdown. It's a "hard crash" of the database, and this can result in a
longer time to start the database back up. Still, you can't really hurt
the database using the shutdown abort command.
Here is a sample shell script to shutdown Oracle. For more, get
the book "Oracle shell Scripting":
# **************************
# Shutdown crontab entry
# **************************
00 7 * * * /u01/app/oracle/bin/shutdown.ksh prodb1 > /home/tmp/shut.lst
shutdown.ksh
# **************************
# Shutdown script
# **************************
#!/bin/ksh
# Source the db.env to set the Oracle environment
. /home/oracle/db.env sqlplus ' / as sysdba ' @/export/home/mysid/shutdown.sql
&1
# Or, manually set the environment . . . .
ORACLE_SID=mon1
export ORACLE_SID
ORACLE_HOME=`cat /etc/oratab|grep ^$ORACLE_SID:|cut -f2 -d':'`
export ORACLE_HOME
PATH=$ORACLE_HOME/bin:$PATH
export PATH
-- **************************
-- Shutdown script
-- **************************
shutdown abort;
startup restrict;
shutdown immediate;
While this uses the "shutdown abort" (meaning to abort in-flight
transactions, it's necessary to re-start and allow Oracle to rollback
the in-flight work. Right after this normal restricted startup,
Oracle will safely shutdown with shutdown immediate.
If you want a full set of Oracle shutdown and startup scripts, get the
Oracle script
collection.
Debugging a
hung database
Back in Oracle 10g a hung database was a real
problem, especially if the DBA could not connect via SQL*Plus to release
the source of the hanging.
In these cases, the DBA had few options other than to force-down the
instance and warmstart it.
There is a new feature in Oracle 11g SQL*Plus
called the "prelim" option. This option is very useful for running
oradebug and other utilities that do not require a real connection to
the database.
root> sqlplus –prelim
SQL>
or
SQL>
set _prelim on
SQL> connect / as sysdba
At this point you are free to run the oradebug
commands to diagnose a hung database issue using the new
hanganalyze utility:
-
SQL> oradebug hanganalyze 3
-
Wait at least 2 minutes to give time to
identify process state changes.
-
SQL> oradebug hanganalyze 3
-
Open a separate SQL session and immediately
generate a system state dump.
-
SQL> alter session set events 'immediate trace
name SYSTEMSTATE level 10';