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"--.....

Wednesday, November 16, 2016

Script to Monitor Current Database Size and its Growth Since Last Run 


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:


























Posted by



at


























Labels:


















2 comments:








  1. AnonymousAugust 25, 2017 at 3:51 PM
    Hi Kiran,

    Good script, however I have a query. If I want to generate a report of the DB growth monthly what changes do we need to make in the query? If possible please post the entire query.

    Thanks
    ReplyDelete
    Replies
    1. Kiran Jadhav - Happy To Help DBA'sSeptember 8, 2017 at 2:01 AM
      Hi,

      Nothing to change in query.
      This Script to Monitor Current Database Size and its Growth Since Last Run
      You just have to schedule it accordingly to get monthly growth.

      Ex. If you schedule this script to run on 1st of every month then this will calculate the DB growth from last run.

      DB Growth = Current Size - Previous captured size
      Delete




























        

      









Subscribe to:
Post Comments (Atom)