Fix hanging SHUTDOWN: waiting for active calls to complete

 

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.
*** 2008-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:

1. SQL> oradebug hanganalyze 3
2. Wait at least 2 minutes to give time to identify process state changes.
3. SQL> oradebug hanganalyze 3
4. Open a separate SQL session and immediately generate a system state dump.
5. SQL> alter session set events 'immediate trace name SYSTEMSTATE level 10';

---