Oracle initscript and usability improvements

Oracle can be installed on regular Linux systems nowadays, but there are a few things that we felt could be improved in terms of integration and usability. We've made the initscript's behaviour more in-line with normal expectations, and provide a few shell shortcuts to make life easier.

Initscript diff

This diff works for us at the time of writing. I wouldn't expect it to suddenly change substantially, but it'd be easy to update if Oracle makes some modifications themselves.

   1 --- oracle-xe.orig      2009-07-31 13:45:43.000000000 +1000
   2 +++ oracle-xe   2009-07-31 13:47:35.000000000 +1000
   3 @@ -20,6 +20,7 @@
   4  #                         changing the password for anonymous user logic is also
   5  #                         removed                          
   6  #      svaggu 12/12/05 -  Password validation is modified
   7 +#      anchor 2009-07-16 -  Various improvements, gives a sure-kill on stop()
   8  #
   9  
  10  # Source fuction library
  11 @@ -532,26 +533,43 @@
  12  '/etc/init.d/oracle-xe configure' as the root user to configure the database."
  13                 exit 0
  14         fi
  15 +
  16 +       # Start the listener
  17         status=`ps -ef | grep tns | grep oracle`
  18         if [ "$status" == "" ]
  19         then
  20                 if [ -f $ORACLE_HOME/bin/tnslsnr ]  
  21                 then
  22 -                       echo "Starting Oracle Net Listener."
  23 +                       echo -n "Starting Oracle Net Listener... "
  24                         $SU -s /bin/bash $ORACLE_OWNER -c "$LSNR  start" > /dev/null 2>&1
  25 -               fi
  26 -       fi
  27 -       echo "Starting Oracle Database 10g Express Edition Instance."
  28 -       $SU -s /bin/bash  $ORACLE_OWNER -c "$SQLPLUS -s /nolog @$ORACLE_HOME/config/scripts/startdb.sql" > /dev/null 2>&1
  29      RETVAL=$?
  30      if [ $RETVAL -eq 0 ]
  31      then
  32 -        echo
  33 +                               echo "Okay."
  34 +                       else
  35 +                               echo "Failed but continuing."
  36 +                       fi
  37 +               fi
  38 +       fi
  39 +
  40 +       # Start the DB component
  41 +       echo -n "Starting Oracle Database 10g Express Edition Instance... "
  42 +       LASTLINE=`$SU -s /bin/bash $ORACLE_OWNER -c "$SQLPLUS -s /nolog @$ORACLE_HOME/config/scripts/startdb.sql" | tail -1`
  43 +       if [ x"$LASTLINE" == x"Database opened." ]
  44 +       then
  45 +               RETVAL=0
  46 +               echo "Okay."
  47 +       elif [ x"$LASTLINE" == x"ORA-01081: cannot start already-running ORACLE - shut it down first" ]
  48 +       then
  49 +               RETVAL=1
  50 +               echo "$LASTLINE"
  51      else
  52 -        echo Failed to start Oracle Net Listener using $ORACLE_HOME/bin/tnslsnr\
  53 -             and Oracle Express Database using $ORACLE_HOME/bin/sqlplus.
  54          RETVAL=1
  55 +               echo "Unknown error - $LASTLINE"
  56 +               echo "Failed to start Oracle Express Database using $ORACLE_HOME/bin/sqlplus."
  57      fi
  58 +
  59 +       # We're done
  60      return $RETVAL
  61  }
  62  
  63 @@ -563,21 +581,48 @@
  64          exit 0
  65      fi
  66      
  67 -    # Stop Oracle 10g Express Edition Database and Listener
  68 -    echo Shutting down Oracle Database 10g Express Edition Instance.
  69 -       $SU -s /bin/bash $ORACLE_OWNER -c "$SQLPLUS -s /nolog @$ORACLE_HOME/config/scripts/stopdb.sql" > /dev/null 2>&1
  70 -       echo Stopping Oracle Net Listener.
  71 +       # Stop the DB component
  72 +       echo -n "Shutting down Oracle Database 10g Express Edition Instance... "
  73 +       LASTLINE=`$SU -s /bin/bash $ORACLE_OWNER -c "$SQLPLUS -s /nolog @$ORACLE_HOME/config/scripts/stopdb.sql" | tail -1`
  74 +       if [ x"$LASTLINE" == x"ORACLE instance shut down." ]
  75 +       then
  76 +               RETVAL=0
  77 +               echo "Okay."
  78 +       elif [ x"$LASTLINE" == x"Linux Error: 2: No such file or directory" ]
  79 +       then
  80 +               RETVAL=1
  81 +               echo "Failed, Oracle is not running"
  82 +       else
  83 +               RETVAL=1
  84 +               echo "Unknown error, $LASTLINE"
  85 +               echo "Failed to stop Oracle Express Database using $ORACLE_HOME/bin/sqlplus."
  86 +       fi
  87 +
  88 +       # Stop the listener
  89 +       echo -n "Stopping Oracle Net Listener... "
  90      $SU -s /bin/bash  $ORACLE_OWNER -c "$LSNR stop" > /dev/null 2>&1
  91 -    RETVAL=$?
  92 -    echo 
  93 -    if [ $RETVAL -eq 0 ] && rm -f /var/lock/subsys/$LSNR
  94 +       LISTENRETVAL=$?
  95 +       if [ $LISTENRETVAL -eq 0 ]
  96      then
  97 -        return $RETVAL
  98 +               echo "Okay."
  99 +               rm -f /var/lock/subsys/$LSNR
 100 +       else
 101 +               echo "Failed."
 102 +               RETVAL=1
 103 +               rm -f /var/lock/subsys/$LSNR
 104      fi
 105 +
 106 +       # Nuke any remaining processes from orbit; it's the only way to be sure.
 107 +       /bin/sleep 2
 108 +       /usr/bin/skill -9 -u oracle
 109 +       /bin/sleep 1
 110 +
 111 +       # We're done
 112 +       return $RETVAL
 113  }
 114  
 115  dostatus() {
 116 -       if test ! -f "$CONFIGURATION"
 117 +       if [ ! -f "$CONFIGURATION" ]
 118      then       
 119         echo "Oracle Database 10g Express Edition is not configured.  You must run
 120  '/etc/init.d/oracle-xe configure' as the root user to configure the database."
 121 @@ -589,16 +634,43 @@
 122          exit 0
 123      fi
 124          
 125 -       $SU -s /bin/bash $ORACLE_OWNER -c "$LSNR status"
 126 -    RETVAL=$?
 127 +       # Check the TNS Listener
 128 +       $SU -s /bin/bash $ORACLE_OWNER -c "$LSNR status" >/dev/null
 129 +       LISTENRETVAL=$?
 130 +
 131 +       # Check the DB itself. Assume that we can't check the DB if the listener isn't running
 132 +       if [ $LISTENRETVAL -eq 0 ]
 133 +       then
 134 +               # Show the user
 135 +               $SU -s /bin/bash $ORACLE_OWNER -c "$LSNR status" | egrep '^  Instance "[A-Za-z_]+", status (UNKNOWN|READY), has 1 handler\(s\) for this service...'
 136 +               # "PLSExtProc" belongs to the TNS Listener, so we don't want to count it
 137 +               NUMPROCS=`$SU -s /bin/bash $ORACLE_OWNER -c "$LSNR status" | grep -v '^  Instance "PLSExtProc"' | egrep '^  Instance "[A-Za-z_]+", status (UNKNOWN|READY), has 1 handler\(s\) for this service...' | wc --lines`
 138 +       fi
 139 +
 140 +       # Sort out the error codes
 141 +       RETVAL=0
 142 +       if [ $LISTENRETVAL -ne 0 ]
 143 +       then
 144 +               echo "TNS Listener is not running, and cannot check the DB"
 145 +               RETVAL=1
 146 +       else
 147 +               if [ $NUMPROCS -lt 1 ]
 148 +               then
 149 +                       echo "Expecting at least one 'XE' process running, database does not appear to be running"
 150 +                       RETVAL=1
 151 +               fi
 152 +       fi
 153 +
 154 +       # We're done
 155 +       return $RETVAL
 156  }
 157  
 158  # See how we were called
 159  case "$1" in
 160    start)
 161 -       if test -f "$CONFIGURATION"
 162 +       if [ -f "$CONFIGURATION" ]
 163         then
 164 -               if test "$ORACLE_DBENABLED" != "true" 
 165 +               if [ "$ORACLE_DBENABLED" != "true" ]
 166                 then
 167                         exit 0
 168                 fi
 169 @@ -613,9 +685,9 @@
 170          configure
 171          ;;
 172    stop)
 173 -       if test -f "$CONFIGURATION"
 174 +       if [ -f "$CONFIGURATION" ]
 175         then
 176 -               if test "$ORACLE_DBENABLED" != "true" 
 177 +               if [ "$ORACLE_DBENABLED" != "true" ]
 178                 then
 179                         exit 0
 180                 fi

Improving usability

We've applied these mods for Oracle XE (the free edition). They should be similarly applicable to Real Oracle, but we haven't tested it. We assume you use a bash-alike shell.

  1. Improve the root account's .bashrc/.bash_profile (we routinely login as root when doing maintenance)

    # Append this to the file, it makes getting to oracle much faster
    alias sql='echo Changing to oracle account ; su -c "sqlplus / as sysdba" - oracle'
  2. Improve the oracle user's .bashrc/.bash_profile - the oracle user needs its environment setup to get proper access. Oracle comes with a source-able file to do this for you, upon which this is based.

    # This is from oracle's supplied environment setup file
    export ORACLE_HOME=/usr/lib/oracle/xe/app/oracle/product/10.2.0/server
    export ORACLE_SID=XE
    export NLS_LANG=`$ORACLE_HOME/bin/nls_lang.sh`
    export PATH=$ORACLE_HOME/bin:$PATH
    if [ $?LD_LIBRARY_PATH ]
    then
            export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH
    else
            export LD_LIBRARY_PATH=$ORACLE_HOME/lib
    fi
    
    
    # For your convenience
    export PS1='oracle $ '
    alias sql='sqlplus / as sysdba' # 'sqlplus' is a lot to type; the best sysadmins are lazy