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 Monitor Current Database Size and its Growth Since Last Run.
Download(MonDBSizeGrowth.sh)
Download MonDBSizeGrowth.sh and save it under $HOME/DBA_MON/bin/
$ chmod 755 MonDBSizeGrowth.sh
#!/bin/bash
###################################################################################
# Script Name : MonDBSizeGrowth.sh #
# #
# Description: #
# Script to Monitor Current Database Size and its Growth Since Last Run #
# #
# Usage : sh <script_name> <ORACLE_SID> #
# For example : sh MonDBSizeGrowth.sh ORCL #
# #
# Note : Initially Run this script for 2 Times #
# #
# Created by : Kiran Jadhav - (https://h2hdba.blogspot.com) #
###################################################################################
# Initialize variables
INSTANCE=$1
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
PREV_PHY_SIZE=$LOG_DIR/`echo $PROGRAM`_$APPS_ID.prevphy
PREV_LOGI_SIZE=$LOG_DIR/`echo $PROGRAM`_$APPS_ID.prevlogi
CURR_PHY_SIZE=$LOG_DIR/`echo $PROGRAM`_$APPS_ID.currphy
CURR_LOGI_SIZE=$LOG_DIR/`echo $PROGRAM`_$APPS_ID.currlogi
LOG_FILE=$LOG_DIR/`echo $PROGRAM`_$APPS_ID.log
ERR_FILE=$LOG_DIR/`echo $PROGRAM`_$APPS_ID.err
LOG_DATE=`date`
LAST_CAPTURED_DATE_TIME=`grep "Current Database Size" $OUT_FILE |cut -d'-' -f2`
# 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 [ -s $CURR_PHY_SIZE ]; then
echo "$LOG_DATE" > $LOG_FILE
echo "Moving $CURR_PHY_SIZE to Previous $PREV_PHY_SIZE" >> $LOG_FILE
mv $CURR_PHY_SIZE $PREV_PHY_SIZE
#cat $LOG_FILE
fi
if [ -s $CURR_LOGI_SIZE ]; then
echo "$LOG_DATE" > $LOG_FILE
echo "Moving $CURR_LOGI_SIZE to Previous $PREV_LOGI_SIZE" >> $LOG_FILE
mv $CURR_LOGI_SIZE $PREV_LOGI_SIZE
#cat $LOG_FILE
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
# 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
usage()
{
echo "$LOG_DATE" > $LOG_FILE
echo "Script To Monitor Current Database Size and its Growth Since Last Run" >> $LOG_FILE
echo "Usage : ksh <script_name> <ORACLE_SID> " >> $LOG_FILE
echo "For example : ksh $PROGRAM $ORACLE_SID" >> $LOG_FILE
echo
}
if [ $# -lt 1 ] || [ "$INSTANCE" != "$ORACLE_SID" ]; then
usage
echo "Error : Insufficient arguments." >> $LOG_FILE
#cat $LOG_FILE
exit
fi
sqlplus -s '/as sysdba' <<EOF
SET ECHO OFF
SET pagesize 1000
set feedback off
set lines 180
set heading off;
spool $CURR_PHY_SIZE
SELECT round(SUM (BYTES / (1014 * 1024 ))) "PHYSICAL_SIZE(MB)" FROM dba_data_files;
SPOOL OFF;
spool $CURR_LOGI_SIZE
SELECT "PHYSICAL_SIZE(MB)" - "FREE_SPACE(MB)" "LOGICAL_SIZE(MB)"
FROM (SELECT (SELECT round(SUM (BYTES / (1014 * 1024 )))
FROM dba_data_files) "PHYSICAL_SIZE(MB)", (SELECT round(SUM (BYTES / (1024 * 1024 )))
FROM dba_free_space) "FREE_SPACE(MB)"
FROM DUAL);
spool off;
exit;
EOF
CURR_PHY_SIZE_V=`cat $CURR_PHY_SIZE`
CURR_LOGI_SIZE_V=`cat $CURR_LOGI_SIZE`
PREV_PHY_SIZE_V=`cat $PREV_PHY_SIZE`
PREV_LOGI_SIZE_V=`cat $PREV_LOGI_SIZE`
#echo $CURR_PHY_SIZE_V
#echo $CURR_LOGI_SIZE_V
#echo $PREV_PHY_SIZE_V
#echo $PREV_LOGI_SIZE_V
PHY_GROWTH=`expr $CURR_PHY_SIZE_V - $PREV_PHY_SIZE_V`
#echo $PHY_GROWTH
LOGI_GROWTH=`expr $CURR_LOGI_SIZE_V - $PREV_LOGI_SIZE_V`
#echo $LOGI_GROWTH
sqlplus -s '/as sysdba' <<EOF
SET ECHO OFF
SET pagesize 1000
set feedback off
set lines 180
set heading on;
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 Current Database Size - `date`:
PROMPT
SELECT Name "DB_NAME","PHYSICAL_SIZE(GB)", "PHYSICAL_SIZE(GB)" - "FREE_SPACE(GB)" "LOGICAL_SIZE(GB)", "FREE_SPACE(GB)"
FROM (SELECT (SELECT round(SUM (BYTES / (1014 * 1024 * 1024)))
FROM dba_data_files) "PHYSICAL_SIZE(GB)", (SELECT round(SUM (BYTES / (1024 * 1024 * 1024)))
FROM dba_free_space) "FREE_SPACE(GB)"
FROM DUAL),v\$database;
PROMPT <br>
PROMPT <br>
PROMPT <br>
PROMPT Database Growth (in MB) Since $LAST_CAPTURED_DATE_TIME
select Name "DB_NAME",$PHY_GROWTH "PHYSICAL_GROWTH(MB)", $LOGI_GROWTH "LOGICAL_GROWTH(MB)" from dual,v\$database;
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: Report : $APPS_ID - Current Database Size and its Growth 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
#Taking Backup of Output File To keep History On Server
cp $OUT_FILE $OUT_FILE.`date +"%m-%b-%Y:%T"`
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>
$ cd $HOME/DBA_MON/bin
$ sh MonDBSizeGrowth.sh DEV11G
This script will send the notification with current database size and its growth since last run.
Sample Output:
