Forum OpenACS Q&A: Oracle /etc/init.d scripts should use shutdown immediate

The $ORACLE_HOME/bin/dbshut script shipped with Oracle 8.1.7, which is called by all the /etc/init.d/oracle startup/shutdown scripts I've ever seen, basically starts svrmgrl and does a plain "shutdown", which is equivalent to shutdown normal.

This looks clearly wrong to me. If the machine needs to go down, (e.g., the UPS just said "Oh, no, only 1 minute of power left, shutdown now!"), you should be giving Oracle a "shutdown immediate" so that it rolls back all transactions, disconnects all users, and shuts down ASAP, rather than sitting there waiting, possibly forever, until all connected Oracle users disconnect!

Others have reported Oracle hanging on shutdown, sitting at the svrmgrl prompt until AOLserver is shut down. I've seen this problem myself now on Linux (Debian 3.0). (And since the shutdown scripts run sequentially, Oracle getting stuck also prevented my PostgreSQL and various other stuff from shutting down as well.)

Interestingly, I've never seen or heard of the problem on Solaris, including at least one equivalently configured Solaris box that I use at work. So I suspect the shutdown order of /etc/inittab stuff and /etc/init.d shutdown scripts differs between Solaris and Linux.

C. R. Oldhan solved the problem by putting killall -TERM nsd into his /etc/init.d/oracle script. That sounds wise to me. Seems to me though, that we should both make sure we kill all AOLservers that talk to Oracle before trying to shutdown Oracle, and we should use "shutdown immediate".

Does anyone know of any good reason not to always use "shutdown immediate" in your Oracle shutdown scripts? How have others solved this problem?

How does PostgreSQL behave in this respect?

On Linux (Debian 3.0, kernel 2.4.18) I did the following tests with Oracle 8.1.7.4:

Manually start up AOLserver which connects to Oracle, then run "sudo /etc/init.d/oracle8i stop". Oracle does not shut down, script hangs, but leave that script there, let it keep trying throughout the rest of the next steps. Kill AOLserver. Oracle is still not shutting down, ps -ef shows two nsd processes or threads parented to 1. Plain kill of those two processes has no effect. kill -9 killes the two nsd processes. Immediately after kill -9 of remaining nsd processes, Oracle successfully shuts down.

Edit $ORACLE_HOME/bin/dbshut to use "shutdown immediate" instead of plain "shutdown". The relevent part of the script should now look like this:

      case $VERSION in
                  6)  sqldba command=shutdown ;;
                  *)  $SQLDBA >>EOF
connect internal
shutdown immediate
EOF
                    ;;
              esac

After changing the dbshut script, start up Oracle, manually start up AOLserver, run "sudo /etc/init.d/oracle8i stop", and bam, Oracle shuts down right away. (The running AOLserver, of course, will now error out when trying to talk to Oracle.)

Seems very repeatable and clear. Therefore, I have switched to using "shutdown immediate" from my shutdown scripts like the above, on both Linux and Solaris.

The implications of the different flavors of shutdown are explained pretty clearly in the Oracle docs. ("shutdown transactional" would also be a good alternative for the shutdown script, but "shutdown immediate" is still probably more appropriate.)

I recomend both insuring that all normally running programs which connect to Oracle shut down cleanly before Oracle tries to shut down, and using shutdown immediate for the Oracle shutdown script.

I don't happen to use daemontools so I'm not clear on the details, but Jade's example above looks like a good method for insuring that the normally running AOLservers are killed cleanly. This is good, but not sufficient, as (definitely) if someone starts up an AOLserver manually and forgets to shut it down, or (presumably, I didn't test this) leaves himself logged into sqlplus, Oracle will not shut down, and in fact, will prevent your unix box from shutting down as well.

There's another bug in Oracle's dbshut script - each instance is shut down only if it is configured in oratab to automatically start up on boot. Here's a patch gainst the stock version, with both fixes (I ran ns_quotehtml over it, so it should display properly here on the web):

$ pwd
/ora8/m01/app/oracle/product/8.1.7/bin

$ cvs diff -u -r 1.1 -r 1.2 dbshut 
Index: dbshut
===================================================================
RCS file: /home/cvsroot/ora8-koudelka/product/8.1.7/bin/dbshut,v
retrieving revision 1.1
retrieving revision 1.2
diff -u -r1.1 -r1.2
--- dbshut	28 Nov 2002 07:03:25 -0000	1.1
+++ dbshut	15 May 2003 00:01:42 -0000	1.2
@@ -1,6 +1,7 @@
 :
 #
-# $Header: /home/cvsroot/ora8-koudelka/product/8.1.7/bin/dbshut,v 1.1 2002/11/28 07:03:25 atp Exp $ dbshut.sh.pp Copyr (c) 1991 Oracle
+# Header: dbshut.sh.pp 30-may-2000.14:31:09 jboyce Exp $ dbshut.sh.pp Copyr (c) 1991 Oracle
+# $Header: /home/cvsroot/ora8-koudelka/product/8.1.7/bin/dbshut,v 1.2 2003/05/15 00:01:42 atp Exp $
 #
 
 ###################################
@@ -38,7 +39,23 @@
         \#*) ;;        #comment-line in oratab
         *)
 #       Proceed only if third field is 'Y'.
-        if [ "`echo $LINE | awk -F: '{print $3}' -`" = "Y" ] ; then
+
+        # That's a VERY bad idea!  When the unix box goes down, want
+        # ALL Oracle instances to be shut down cleanly, whether
+        # they're configured to start up on system boot or not!
+        # --atp@piskorski.com, 2003/05/14 19:29 EDT
+
+        # TOOD: This is also going to shut each instance down
+        # sequentially, which is kind of slow.  Might be much better
+        # to do them all in parallel.  --atp@piskorski.com, 2003/05/14
+        # 19:52 EDT
+
+        dbstart_p=`echo $LINE | awk -F: '{print $3}' -`
+        # Shutdown only if oratab start field is Y:
+        #if [ "$dbstart_p" = "Y" ]
+        # Shutdown as long as oratab field is not empty string:
+        if [ "$dbstart_p" ]
+        then
             ORACLE_SID=`echo $LINE | awk -F: '{print $1}' -`
             if [ "$ORACLE_SID" = '*' ] ; then
                 ORACLE_SID=""
@@ -71,7 +88,7 @@
                   6)  sqldba command=shutdown ;;
                   *)  $SQLDBA <<EOF
 connect internal
-shutdown
+shutdown immediate
 EOF
                     ;;
               esac