Way to success...

--"Running Away From Any PROBLEM Only Increases The DISTANCE From The SOLUTION"--.....--"Your Thoughts Create Your FUTURE"--.....--"EXCELLENCE is not ACT but a HABIT"--.....--"EXPECT nothing and APPRECIATE everything"--.....

Monday, June 6, 2016

Script to Monitor Temporary (TEMP) Tablespace Usage


Pre-requisites to Run the script:

Make sure mailx and sendmail is installed on your OS, also check below environements/file is created prior to execute the scripts.

This script is tested on Linux Server.

This script needs to be deployed in database node/server.

#Base location for the DBA scripts
DBA_SCRIPTS_HOME=$HOME/DBA_MON

#OS User profile where database environment file is set
$HOME/.bash_profile

Create Custom .sysenv file for scripts

Download(.sysenv)
Download .sysenv file and save it under $HOME/DBA_MON
$ chmod 777 .sysenv
$ cat $HOME/DBA_MON/.sysenv

export DBA_SCRIPTS_HOME=$HOME/DBA_MON
export PATH=${PATH}:$DBA_SCRIPTS_HOME
export DBA_EMAIL_LIST=kiran.jadhav@domain.com,jadhav.kiran@domain.com
#Below parameter is used in script, whenever there is planned downtime you can set it to Y so there will be no false alert.
export DOWNTIME_MODE=N

Script to check TEMP tablespace Usage and send alert if usage is more than given threshold(%). 



Download(TempTsMon.sh)
Download TempTsMon.sh and save it under $HOME/DBA_MON/bin/
$ chmod 755 TempTsMon.sh

#!/bin/bash

################################################################################################
# Script Name : TempTsMon.sh                                                                   #
#                                                                                              #
# Description:                                                                                 #
# Script to check TEMP tablespace Usage and send alert if usage is more than given threshold(%)#
#                                                                                              #
# Usage : sh <script_name> <ORACLE_SID> <%USED>                                                #
# For example : sh TempTsMon.sh ORCL 85                                                        #
#                                                                                              #
# Created by : Kiran Jadhav - (https://h2hdba.blogspot.com)                                    #
################################################################################################

# Initialize variables

INSTANCE=$1
THRESHOLD=$2
HOST_NAME=`hostname | cut -d'.' -f1`
PROGRAM=`basename $0 | cut -d'.' -f1`
export DBA_SCRIPTS_HOME=$HOME/DBA_MON
APPS_ID=`echo $INSTANCE | tr '[:lower:]' '[:upper:]'`
LOG_DIR=$DBA_SCRIPTS_HOME/logs/$HOST_NAME
OUT_FILE=$LOG_DIR/`echo $PROGRAM`_$APPS_ID.html.out
LOG_FILE=$LOG_DIR/`echo $PROGRAM`_$APPS_ID.log
ERR_FILE=$LOG_DIR/`echo $PROGRAM`_$APPS_ID.err
LOG_DATE=`date`


# Source the env
. $HOME/.bash_profile
. $DBA_SCRIPTS_HOME/.sysenv

if [ $? -ne 0 ]; then
   echo "$LOG_DATE" > $LOG_FILE  
   echo "Please pass correct environment : exiting the script  \n" >> $LOG_FILE
   cat $LOG_FILE
   exit
fi

if [ -s $OUT_FILE ]; then
 echo "$LOG_DATE" > $LOG_FILE
 echo "Deleting existing output file $OUT_FILE" >> $LOG_FILE
 rm -f $OUT_FILE
 cat $LOG_FILE
fi

# If there is a plan downtime then create $ORACLE_SID.down file in $DBA_SCRIPTS_HOME to silent the alerts during maintenance window.

if [ -f $DBA_SCRIPTS_HOME/`echo $ORACLE_SID`.down ]; then
 echo "$LOG_DATE" >> $LOG_FILE
        echo "Host: $HOST_NAME | Instance: $ORACLE_SID is under maintenance: exiting the script" >> $LOG_FILE
        cat $LOG_FILE
 exit
fi

if [ $DOWNTIME_MODE = "Y" ]; then
 echo "$LOG_DATE" >> $LOG_FILE
 echo "Host: $HOST_NAME | Instance: $ORACLE_SID is under maintenance: exiting the script" >> $LOG_FILE
 cat $LOG_FILE
 exit
fi

usage()
{
  echo "$LOG_DATE" > $LOG_FILE
        echo "Script to check TEMP Tablespace usage"  >> $LOG_FILE
        echo "Usage   : sh <script_name> <ORACLE_SID> <%USED> " >> $LOG_FILE
  echo "For example : sh $PROGRAM.sh $ORACLE_SID 85" >> $LOG_FILE
        echo
}

if [ $# -lt 2 ] || [ "$INSTANCE" != "$ORACLE_SID" ]; then
    usage
    echo "Error : Insufficient arguments." >> $LOG_FILE
 cat $LOG_FILE
    exit
fi

get_count()
{
 sqlplus -s '/as sysdba' <<!

 set feedback off
 set head off
 set feed off;
 set veri off;
 select count(1) from 
 (
  select TABLESPACE ,round(MB_TOTAL) MB_TOTAL,round(MB_USED) MB_USED,round(MB_FREE) MB_FREE,
      round(((MB_TOTAL-MB_FREE)*100)/MB_TOTAL)||'%' Per_Used ,
      round(((MB_TOTAL-MB_USED)*100)/MB_TOTAL)||'%' Per_Free
  from
  (SELECT   A.tablespace_name tablespace, D.mb_total,
     SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used,
     D.mb_total - SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_free
  FROM     v\$sort_segment A,
     (
     SELECT   B.name, C.block_size, SUM (C.bytes) / 1024 / 1024 mb_total
     FROM     v\$tablespace B, v\$tempfile C
     WHERE    B.ts#= C.ts#
     GROUP BY B.name, C.block_size
     ) D
  WHERE    A.tablespace_name = D.name
  GROUP by A.tablespace_name, D.mb_total)
  where round(((MB_TOTAL-MB_FREE)*100)/MB_TOTAL) > $THRESHOLD
 );

    exit;
!
}

count=`get_count`
#echo $count

echo "$LOG_DATE" > $ERR_FILE
get_count >> $ERR_FILE
ERR_COUNT=`grep "ORA-" $ERR_FILE |wc -l`

if [ $ERR_COUNT -gt 0 ]; then
 cat $ERR_FILE | mailx -s "<ERROR> Critical: $APPS_ID - High TEMP Tablespace Usage on $HOST_NAME" $DBA_EMAIL_LIST
 exit
fi

if [ $count -gt 0 ];
then

  sqlplus -s '/as sysdba' <<EOF

  set echo off
  set feedback off
  set lines 1000
  col bytes for 999999999999999.99
  col value for 999999999999999.99
  set long 2000000
  set pagesize 300
  set linesize 300
  col SQL_FULLTEXT for a9999
  SET MARKUP HTML ON SPOOL ON -
  HEAD '<title></title> -
  <style type="text/css"> -
     table { background: #eee; } -
     th { font:bold 10pt Arial,Helvetica,sans-serif; color:#b7ceec; background:#151b54; padding: 5px; align:center; } -
     td { font:10pt Arial,Helvetica,sans-serif; color:Black; background:#f7f7e7; padding: 5px; align:center; } -
  </style>' TABLE "border='1' align='left'" ENTMAP OFF


  spool $OUT_FILE


  PROMPT Hi Team,

  PROMPT
  PROMPT TEMP Tablespace Usage is more than $THRESHOLD%, Please check ASAP:
  PROMPT


  select TABLESPACE ,round(MB_TOTAL) MB_TOTAL,round(MB_USED) MB_USED,round(MB_FREE) MB_FREE,
      round(((MB_TOTAL-MB_FREE)*100)/MB_TOTAL)||'%' Per_Used ,
      round(((MB_TOTAL-MB_USED)*100)/MB_TOTAL)||'%' Per_Free
  from
  (SELECT   A.tablespace_name tablespace, D.mb_total,
     SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used,
     D.mb_total - SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_free
  FROM     v\$sort_segment A,
     (
     SELECT   B.name, C.block_size, SUM (C.bytes) / 1024 / 1024 mb_total
     FROM     v\$tablespace B, v\$tempfile C
     WHERE    B.ts#= C.ts#
     GROUP BY B.name, C.block_size
     ) D
  WHERE    A.tablespace_name = D.name
  GROUP by A.tablespace_name, D.mb_total)
  where round(((MB_TOTAL-MB_FREE)*100)/MB_TOTAL) > $THRESHOLD;

  SPOOL OFF
  SET MARKUP HTML OFF
  exit;

EOF

(
echo "To: $DBA_EMAIL_LIST"
echo "MIME-Version: 1.0"
echo "Content-Type: multipart/alternative; "
echo ' boundary="PAA08673.1018277622/server.xyz.com"'
echo "Subject: Critical: $APPS_ID - High TEMP Tablespace Usage on $HOST_NAME"
echo ""
echo "This is a MIME-encapsulated message"
echo ""
echo "--PAA08673.1018277622/server.xyz.com"
echo "Content-Type: text/html"
echo ""
cat $OUT_FILE
echo "--PAA08673.1018277622/server.xyz.com"
) | /usr/sbin/sendmail -t

echo "$LOG_DATE" > $LOG_FILE
echo "Details sent through an email" >> $LOG_FILE
cat $LOG_FILE

else 
    echo "$LOG_DATE" > $OUT_FILE
 echo "TEMP Tablespace Usage is Normal" >> $OUT_FILE
fi




Logs and Out files will be generated under $DBA_SCRIPTS_HOME/logs/$HOST_NAME
So make sure to create logs/$HOST_NAME directory under $DBA_SCRIPTS_HOME before executing the script.

Once the script is ready, then as per the requirement please schedule it in crontab/OEM.

Execute the Script as below:

Syntax :  sh <script_name> <ORACLE_SID> <%USED>

$ cd $HOME/DBA_MON/bin
$ sh TempTsMon.sh ORCL 85

This script will send the notification with TEMP Tablsepcae usage details if its utilization is 85% and above.



No comments:

Post a Comment