Re: Why is it so difficult to shutdown Oracle?

From: Steve Butler <sbut-is_at_seatimes.com>
Date: 1996/01/26
Message-ID: <Pine.SUN.3.91.960126074129.1030A-100000_at_seatimes>#1/1

On 23 Jan 1996, Arthur Chung wrote:
> 'shutdown normal or immediate' only kills the processes
> locally connected.

It appears to work that way here also.

> And since these 'non-local' connection won't die out when we use
> 'shutdown normal or immediate', dba created a sql script that
> generates the session-id of all the connection
> by querying v$session table.
>
> And then, we are using those session-id to disconnect each session
> that are connected from PC. (ie, alter system kill session '11,555')
> Sounds complicated?... I think so.
>
> Is this the right way to do shutdown in client-server environment?

That's whay I have the operations staff here do. There is a script called SHOWNKILL that allows the operator to pick (by menu #) which session to kill. It could quickly be fixed to kill everybody logged on (except the session doing the killing).

After operations kills all the attached sessions (some are sessions where the user left their PC on but went home for the night -- others the user just turned the BRS (big red switch) without exiting the application) they then do the shutdown and backup.

SHOWNKILL script follows:
<<< snip here >>>>
#!/usr/bin/ksh

typeset -u USERNAME
typeset -i SID SERIALNO
typeset -R4 mycnt

operinfo=oper/some_password

while [ 1 -eq 1 ]
do
  sqlplus -s $operinfo << EOF >shownkill.$PPID     set feed off
    select lpad(ltrim(rownum),4) LINE, sid, serial#, username, status

       from v\$session
       where type = 'USER' and username <> 'OPER';

EOF
  cat shownkill.$PPID

  max=$(grep '^ ' shownkill.$PPID | wc -l | sed 's/ //g')

  if [ $max -eq 0 ]; then
    rm shownkill.$PPID
    echo "------> No users to KILL in database <---------"     exit 0
  else
    echo "Enter LINE # of user to KILL (0 to bypass): \c"     read resp
    mycnt=$resp

    case $mycnt in

      "   0"|"    ")
        rm shownkill.$PPID
        exit
        ;;
      *) 
        myline=$(grep "^$mycnt" shownkill.$PPID)
        rm shownkill.$PPID
        if [ "$myline" = "" ]; then
          echo "------->  Invalid LINE specified <-------"
        else
          SID=$(echo $myline | cut -f 2 -d ' ')
          SERIALNO=$(echo $myline | cut -f 3 -d ' ')
          USERNAME=$(echo $myline | cut -f 4 -d ' ')

          sqlplus -s $operinfo <<EOF > /dev/null
            alter system kill session '$SID,$SERIALNO';
EOF
          echo "-----> The user $USERNAME was killed"
        fi
        ;;

    esac
  fi
done
<< end of script >>

Have fun.

--Steve

PS You will need to use a SHUTDOWN IMMEDIATE since a killed session will hang around until ORACLE can notify the application that it has been killed. A SHUTDOWN NORMAL will stay up until the user exits. An IMMEDIATE will proceed to force them off.

I suppose you could have a smart script that waited a minute or two and then looked to see if any killed sessions where still attached. Then it could decide whether to use a NORMAL or IMMEDIATE. If you can at all avoid it, DO NOT use a shutdown abort!

+----------------------------------------------------+
| Steve Butler          Voice:  206-464-2998         |
| The Seattle Times       Fax:  206-382-8898         |
| PO Box 70          Internet:  sbut-is_at_seatimes.com |
| Seattle, WA 98111    Packet:  KG7JE_at_N6EQZ.WA       |
+----------------------------------------------------+

All standard and non-standard disclaimers apply. All other sources are annonymous. Received on Fri Jan 26 1996 - 00:00:00 CET