#!/bin/ksh
#
# dbmon.sh
# ========
#
# This script is provided by Database Specialists, Inc. 
# (http://www.dbspecialists.com) for individual use and not for sale. 
# Database Specialists, Inc. does not warrant the script in any way 
# and will not be responsible for any loss arising out of its use.
#
# Your feedback is welcome! Please send your comments about this script
# to scriptfeedback@dbspecialists.com
#
# This script performs a few routine checks of the specified database
# and sends an email message to the specified user if anything goes wrong.
#
# This script is suitable for running via cron. As such, it generates no
# standard output. All output is written to a log. You will need to edit
# this script before using in order to invoke your password lookup facility
# or otherwise determine the correct Oracle password.
#
# This script has been used on Solaris 2.5.1 and 2.6 against Oracle 7.3.4 
# and 8.0.5 databases.
#
# Usage: dbmon.sh -d sid -n notify_list
#        sid is the ORACLE_SID of the database to monitor. 
#        notify_list is a list of comma-separated email addresses where email
#            should be sent if any problems are discovered.
#
# Version 03-26-1999
#

#
# The quit_dbmon function sends a failure email message, cleans up, and exits.
#

function quit_dbmon
{
  echo $1 >> $LOGFILE
  mailx -s"Database $ORACLE_SID requires attention" $NOTIFY < $LOGFILE
  rm -f $TMPLOG $LOGFILE $TMPFILE $ALRTFILE
  exit 1
}

#
# Parse the command line arguments.
#

USAGE="Usage: `basename $0` -d sid -n notify_list"
SID=""
NOTIFY=""

while getopts :d:n: opt
do
  case "$opt" in
    "d") SID="$OPTARG" ;;
    "n") NOTIFY="$OPTARG" ;;
    ":"|"?") echo "$USAGE" 1>&2 ; exit 1 ;;
  esac
done
 
let i=$#+1
if [ "$i" != "$OPTIND" -o -z "$SID" -o -z "$NOTIFY" ]
then
  echo "$USAGE" 1>&2
  exit
fi

#
# Set up the Oracle environment.
#

export ORACLE_SID="$SID"
export ORAENV_ASK="NO"
export PATH="$PATH:/usr/local/bin"
. /usr/local/bin/oraenv
# Note: If oraenv does not set $ORACLE_BASE, you must set it here.

#
# Set up the general environment.
#

STATFILE="$ORACLE_BASE/local/tools/.dbmon${ORACLE_SID}.stat"
# Note: You may need to edit the above assignment; choose an appropriate location
# for the stat file.
LOGFILE="/tmp/dbmon$$.log"
TMPLOG="/tmp/dbmon$$.logtmp"
TMPFILE="/tmp/dbmon$$.tmp"
ALRTFILE="/tmp/dbmon$$.alr"

> $TMPFILE
> $ALRTFILE

cat <<EOF > $LOGFILE
Date:      `date`
Server:    `uname -n`
Database:  $ORACLE_SID

EOF

#
# Get the necessary Oracle password.
#

ORAPWD="`<enter your password lookup service here> $ORACLE_SID system 2>&1"
# If you want to hardcode the Oracle password, change the above line to read:
# ORAPWD="<enter your password here>"

if [ "$?" != "0" ]
then
  echo $ORAPWD >> $LOGFILE
  quit_dbmon "Unable to get Oracle password for system schema"
fi
  
#
# Prepare a SQL*Plus script to check various things on the database.
#

echo "system/$ORAPWD" > $TMPFILE
chmod 700 $TMPFILE

cat <<\EOF >> $TMPFILE
WHENEVER SQLERROR EXIT FAILURE 1
WHENEVER OSERROR  EXIT FAILURE 2

DEFINE extent_threshold = 10
DEFINE space_threshold = 52428800

SET TERMOUT      OFF
SET SERVEROUTPUT ON SIZE 100000
SET HEADING      OFF
SET TRIMSPOOL    ON
SET FEEDBACK     OFF
SET VERIFY       OFF
SET PAGESIZE     0

SPOOL &1

SELECT A.value || '/alert_' || B.value || '.log'
FROM   v$parameter A, v$parameter B
WHERE  A.name = 'background_dump_dest'
AND    B.name = 'db_name';

SPOOL OFF

SET TERMOUT  ON
SET PAGESIZE 999

SELECT 'SEGMENTS WITHIN &extent_threshold EXTENTS OF MAXEXTENTS'
FROM   SYS.dual
WHERE  EXISTS
       (
       SELECT 'x'
       FROM   SYS.dba_segments
       WHERE  owner NOT IN ('SYS', 'TECHWEB')
       AND    extents > max_extents - &extent_threshold
       );

SET HEADING  ON

COL owner        FORMAT a8
COL segment_type FORMAT a6  TRUNCATE
COL segment_name FORMAT a28 TRUNCATE
COL extents      FORMAT 990 
COL max_extents  FORMAT 990
COL bytes        FORMAT 9,999,999,990

SELECT   owner, segment_type, segment_name, extents, max_extents, bytes
FROM     SYS.dba_segments
WHERE    owner NOT IN ('SYS', 'TECHWEB')
AND      extents > max_extents - &extent_threshold
ORDER BY owner, segment_type, segment_name;

DECLARE
  CURSOR c_ts IS
    SELECT   tablespace_name, SUM (bytes) siz
    FROM     SYS.dba_data_files
    GROUP BY tablespace_name
    ORDER BY tablespace_name;
  CURSOR c_segments IS
    SELECT   owner, segment_type, segment_name, tablespace_name, next_extent
    FROM     SYS.dba_segments
    ORDER BY owner, segment_type, segment_name;
  TYPE varchararray IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
  TYPE numberarray  IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
  v_ts_names      varchararray;
  v_ts_sizes      numberarray;
  v_free_spaces   numberarray;
  v_biggest_frees numberarray;
  v_ts_count      NUMBER := 0;
  v_first_display BOOLEAN := TRUE;
  i               NUMBER;
  j               NUMBER;
BEGIN
  FOR r IN c_ts LOOP
    v_ts_count := v_ts_count + 1;
    v_ts_names(v_ts_count) := r.tablespace_name;
    v_ts_sizes(v_ts_count) := r.siz;
    SELECT NVL (SUM(bytes), 0), NVL (MAX(bytes), 0)
    INTO   v_free_spaces(v_ts_count), v_biggest_frees(v_ts_count)
    FROM   SYS.dba_free_space
    WHERE  tablespace_name = r.tablespace_name;
    --
    -- If a tablespace other than TEMP has sufficient free space
    -- but it is not contiguous, then coalesce the free space for
    -- the tablespace and check how much contiguous space is free
    -- after the coalesce.
    --
    IF r.tablespace_name != 'TEMP' AND 
       v_biggest_frees(v_ts_count) < &space_threshold AND
       v_free_spaces(v_ts_count) > &space_threshold THEN
      i := dbms_sql.open_cursor;
      dbms_sql.parse (i, 'ALTER TABLESPACE ' || r.tablespace_name || 
                         ' COALESCE', dbms_sql.native);
      j := dbms_sql.execute (i);
      dbms_sql.close_cursor (i);
      SELECT NVL (SUM(bytes), 0), NVL (MAX(bytes), 0)
      INTO   v_free_spaces(v_ts_count), v_biggest_frees(v_ts_count)
      FROM   SYS.dba_free_space
      WHERE  tablespace_name = r.tablespace_name;
    END IF;
    --
    -- On the TEMP tablespace we just look for ample free space. On all other
    -- tablespaces, we look for ample contiguous free space.
    --
    IF (r.tablespace_name != 'TEMP' AND
        v_biggest_frees(v_ts_count) < &space_threshold) OR
       (r.tablespace_name = 'TEMP' AND
        v_free_spaces(v_ts_count) < &space_threshold) THEN
      IF v_first_display THEN
        dbms_output.put_line (CHR(9));
        dbms_output.put_line ('TABLESPACES WITH FEWER THAN ' ||
          '&space_threshold CONTIGUOUS BYTES OF FREE SPACE');
        dbms_output.put_line (CHR(9));
        dbms_output.put_line ('TABLESPACE_NAME          TOTAL_SIZE ' ||
          '    FREE_SPACE   BIGGEST_FREE');
        dbms_output.put_line ('-------------------- -------------- ' ||
          '-------------- --------------');
        v_first_display := FALSE;
      END IF;
      dbms_output.put_line (RPAD (SUBSTR (v_ts_names(v_ts_count), 1, 20), 20) 
        || ' ' || TO_CHAR (v_ts_sizes(v_ts_count), '9,999,999,990') 
        || ' ' || TO_CHAR (v_free_spaces(v_ts_count), '9,999,999,990') 
        || ' ' || TO_CHAR (v_biggest_frees(v_ts_count), '9,999,999,990'));
    END IF;
  END LOOP;
  v_first_display := TRUE;
  FOR r IN c_segments LOOP
    i := v_ts_count;
    LOOP
      EXIT WHEN i = 0 OR v_ts_names(i) = r.tablespace_name;
      i := i - 1;
    END LOOP;
    IF i > 0 THEN
      IF v_biggest_frees(i) < r.next_extent THEN
        IF v_first_display THEN
          dbms_output.put_line (CHR(9));
          dbms_output.put_line ('SEGMENTS WHERE NOT ENOUGH FREE SPACE ' ||
            'EXISTS TO ALLOCATE ANOTHER EXTENT');
          dbms_output.put_line (CHR(9));
          dbms_output.put_line ('OWNER      TYPE     SEGMENT_NAME   ' ||
            '                TABLESPACE_NAME DESIRED_NEXT');
          dbms_output.put_line ('---------- -------- ---------------' ||
            '--------------- --------------- ------------');
          v_first_display := FALSE;
        END IF;
        dbms_output.put_line (RPAD (SUBSTR (r.owner, 1, 10), 10) || ' ' ||
          RPAD (SUBSTR (r.segment_type, 1, 8), 8) || ' ' || 
          RPAD (r.segment_name, 30) || ' ' || 
          RPAD (SUBSTR (r.tablespace_name, 1, 15), 15) || ' ' ||
          TO_CHAR (r.next_extent, '999,999,990'));
      END IF;
    END IF;
  END LOOP;
END;
/

EXIT 7
EOF

#
# Run the SQL*Plus script.
#

sqlplus -s @$TMPFILE $ALRTFILE > $TMPLOG 2>&1
RETCODE="$?"
cat $TMPLOG >> $LOGFILE

case "$RETCODE" in
  "0") quit_dbmon "Unable to connect to database" ;;
  "7") ;;
  *)   quit_dbmon "SQL*Plus exited with error code $RETCODE" ;;
esac

#
# Look for the most recent ORA-600 or ORA-12012 error in the alert log.
#

if [ -s "$ALRTFILE" ]
then
  ALERT_LOG="`cat $ALRTFILE`"
  grep -n "ORA-00600" $ALERT_LOG | tail -1 | cut -f 1 -d : | read NEW_LINE_NO1
  grep -n "ORA-12012" $ALERT_LOG | tail -1 | cut -f 1 -d : | read NEW_LINE_NO2
  if [ "$NEW_LINE_NO1" -gt "$NEW_LINE_NO2" ]
  then
    NEW_LINE_NO="$NEW_LINE_NO1"
  else
    NEW_LINE_NO="$NEW_LINE_NO2"
  fi
  if [ -s "$STATFILE" ]
  then
    OLD_LINE_NO="`cat $STATFILE`"
  else
    OLD_LINE_NO=""
  fi
  if [ "$NEW_LINE_NO" != "$OLD_LINE_NO" ]
  then
    echo "\nCheck alert log $ALERT_LOG\nfor ORA-00600 or ORA-12012 error at line $NEW_LINE_NO"\
    >> $LOGFILE
    echo $NEW_LINE_NO > $STATFILE
    echo "x" > $TMPLOG
  fi
fi
  
#
# If any problems were found, then send email.
#

[ -s "$TMPLOG" ] && quit_dbmon ""

#
# The monitor was completed successfully. Now clean up and exit.
#

rm -f $TMPLOG $LOGFILE $TMPFILE $ALRTFILE
exit 0