Monitoreo de espacio en tablespace

Una de las tareas de un DBA es monitorear el espacio de la base de datos, debido a que esto consume mucho tiempo cuando se tienen varias DB’s es bueno automatizar tareas repetitivas y tediosas. Una manera de realizar la automatización del monitoreo de DB’s en UNIX es por medio del crontab, el siguiente es un ejemplo de como usar el crontab para monitorear los tablespaces.

  • Los siguientes scripts permiten obtener el espacio utilizado y libre de los tablespaces, uno lo obtiene en base el porcentaje libre de espacio y el otro obtiene el espacio en base a los MB libres. Estos scripts reciben dos parametros: &1 .- es el directorio y archivo donde se va a crear el reporte(spool) y &2 que es el limite ya sea porcentaje (99) o Mb(99999).

tablespace_size_pct.sql

SET line 132
SET pages 50
SET pause OFF
SET feedback OFF
SET echo OFF
SET verify OFF

COLUMN c1 heading "Tablespace|Name"
COLUMN c2 heading "File|Count"
COLUMN c3 heading "Allocated|in MB"  
COLUMN c4 heading "Used|in MB"       
COLUMN c5 heading "%|free" format 99.99
COLUMN c6 heading "Free|in MB"       
COLUMN c7 heading "%|used" format 99.99

spool &1;

SELECT c1,ROUND(c3,2) c3,ROUND(c4,2) c4,ROUND(c6,2) c6,ROUND(c7,2) c7,ROUND(c5,2) c5,c2
FROM(
SELECT   NVL (b.tablespace_name, NVL (a.tablespace_name, 'UNKOWN')) c1,
         mbytes_alloc c3, mbytes_alloc - NVL (mbytes_free, 0) c4,
         NVL (mbytes_free, 0) c6,
         ((mbytes_alloc - NVL (mbytes_free, 0)) / mbytes_alloc) * 100 c7,
           100
         - (((mbytes_alloc - NVL (mbytes_free, 0)) / mbytes_alloc) * 100) c5,
         b.files c2
    FROM (SELECT   SUM (BYTES) / 1024 / 1024 mbytes_free, tablespace_name
              FROM SYS.dba_free_space
          GROUP BY tablespace_name) a,
         (SELECT   SUM (BYTES) / 1024 / 1024 mbytes_alloc, tablespace_name,
                   COUNT (file_name) files
              FROM SYS.dba_data_files
          GROUP BY tablespace_name) b
   WHERE a.tablespace_name(+) = b.tablespace_name
UNION ALL
SELECT   f.tablespace_name,
         SUM (ROUND ((f.bytes_free + f.bytes_used) / 1024 / 1024, 2)
             ) "total MB",
         SUM (ROUND (NVL (p.bytes_used, 0) / 1024 / 1024, 2)) "Used MB",
         SUM (ROUND (  ((f.bytes_free + f.bytes_used) - NVL (p.bytes_used, 0)
                       )
                     / 1024
                     / 1024,
                     2
                    )
             ) "Free MB",
           (SUM (ROUND (NVL (p.bytes_used, 0) / 1024 / 1024, 2)) * 100)
         / (SUM (ROUND ((f.bytes_free + f.bytes_used) / 1024 / 1024, 2))),
           100
         -   (SUM (ROUND (NVL (p.bytes_used, 0) / 1024 / 1024, 2)) * 100)
           / (SUM (ROUND ((f.bytes_free + f.bytes_used) / 1024 / 1024, 2))),
         COUNT (d.file_name)
    FROM SYS.v_$temp_space_header f,
         dba_temp_files d,
         SYS.v_$temp_extent_pool p
   WHERE f.tablespace_name(+) = d.tablespace_name AND f.file_id(+) = d.file_id
         AND p.file_id(+) = d.file_id
GROUP BY f.tablespace_name)
WHERE C1 NOT IN('USERS') 
  AND C7 >= &2
ORDER BY c6 ASC;

spool off;
exit;

tablespace_size_spc.sql

SET line 132
SET pages 50
SET pause OFF
SET feedback OFF
SET echo OFF
SET verify OFF

COLUMN c1 heading "Tablespace|Name"
COLUMN c2 heading "File|Count"
COLUMN c3 heading "Allocated|in MB"  
COLUMN c4 heading "Used|in MB"       
COLUMN c5 heading "%|free" format 99.99
COLUMN c6 heading "Free|in MB"       
COLUMN c7 heading "%|used" format 99.99

spool &1;

SELECT c1,ROUND(c3,2) c3,ROUND(c4,2) c4,ROUND(c6,2) c6,ROUND(c7,2) c7,ROUND(c5,2) c5,c2
FROM(
SELECT   NVL (b.tablespace_name, NVL (a.tablespace_name, 'UNKOWN')) c1,
         mbytes_alloc c3, mbytes_alloc - NVL (mbytes_free, 0) c4,
         NVL (mbytes_free, 0) c6,
         ((mbytes_alloc - NVL (mbytes_free, 0)) / mbytes_alloc) * 100 c7,
           100
         - (((mbytes_alloc - NVL (mbytes_free, 0)) / mbytes_alloc) * 100) c5,
         b.files c2
    FROM (SELECT   SUM (BYTES) / 1024 / 1024 mbytes_free, tablespace_name
              FROM SYS.dba_free_space
          GROUP BY tablespace_name) a,
         (SELECT   SUM (BYTES) / 1024 / 1024 mbytes_alloc, tablespace_name,
                   COUNT (file_name) files
              FROM SYS.dba_data_files
          GROUP BY tablespace_name) b
   WHERE a.tablespace_name(+) = b.tablespace_name
UNION ALL
SELECT   f.tablespace_name,
         SUM (ROUND ((f.bytes_free + f.bytes_used) / 1024 / 1024, 2)
             ) "total MB",
         SUM (ROUND (NVL (p.bytes_used, 0) / 1024 / 1024, 2)) "Used MB",
         SUM (ROUND (  ((f.bytes_free + f.bytes_used) - NVL (p.bytes_used, 0)
                       )
                     / 1024
                     / 1024,
                     2
                    )
             ) "Free MB",
           (SUM (ROUND (NVL (p.bytes_used, 0) / 1024 / 1024, 2)) * 100)
         / (SUM (ROUND ((f.bytes_free + f.bytes_used) / 1024 / 1024, 2))),
           100
         -   (SUM (ROUND (NVL (p.bytes_used, 0) / 1024 / 1024, 2)) * 100)
           / (SUM (ROUND ((f.bytes_free + f.bytes_used) / 1024 / 1024, 2))),
         COUNT (d.file_name)
    FROM SYS.v_$temp_space_header f,
         dba_temp_files d,
         SYS.v_$temp_extent_pool p
   WHERE f.tablespace_name(+) = d.tablespace_name AND f.file_id(+) = d.file_id
         AND p.file_id(+) = d.file_id
GROUP BY f.tablespace_name)
WHERE C1 NOT IN('USERS') 
  AND C6 <= &2
ORDER BY c6 ASC;

SPOOL OFF;
exit;
  • Con el siguiente script podemos ejecutar los SQL scripts anteriores dependiendo los parametros que le enviemos. La forma de ejecutar el script es la siguiente:
    • Para reportar en base al espacio, despues del parametro -d debe seguir el nombre de la instancia (SID), despues del parametro -s sigue la cantidad minima que puede tener libre un tablespace:
      • tbs_monitor.ksh -d DEVELOPMENT -s 500
        Connected.
        Instance: DEVELOPMENT
        Tablespaces with usage < 500 MB.
        
        Tablespace                      Allocated       Used       Free      %      %       File
        Name                                in MB      in MB      in MB   used   free      Count
        ------------------------------ ---------- ---------- ---------- ------ ------ ----------
        SYSTEM                                600     403.13     196.88  67.19  32.81          1
        IDX1                                11741      11363        378  96.78   3.22          3
    • Para reportar en base al porcentaje, en lugar de utilizar el parametro -s se utiliza el parametro -p seguido del porcentaje maximo que puede tener un tablespace:
      • tbs_monitor.ksh -d DEVELOPMENT -p 80
    • Connected.
      Instance: DEVELOPMENT
      Tablespaces with usage >= 80%.
      
      Tablespace                      Allocated       Used       Free      %      %       File
      Name                                in MB      in MB      in MB   used   free      Count
      ------------------------------ ---------- ---------- ---------- ------ ------ ----------
      IDX1                                11741      11363        378  96.78   3.22          3
      IDX2                                 3201       2696        505  84.22  15.78          2
      IDX5                                18385      17394        991  94.61   5.39          3
      DATA02                              13312   11520.13    1791.88  86.54  13.46          2
      DATA03                              33797   31709.13    2087.88  93.82   6.18          4
      DATA01                              56629   46606.38   10022.63  82.30  17.70          7

tbs_monitor.ksh

#!/bin/ksh
#######################################################################
#                                                                      
# SCRIPT       : tbs_monitor.ksh                            
#                                                                
# DESCRIPTION  : This script will monitor usage in tablespaces of a database.
#                If the usage is >= 90% it will send an email to the DBA group.
#                                                                
# INPUT FILES  : [oracle_sid] only one database is processed
#                                                                
# OUTPUT FILES : NONE
#                                                                
# CALLED OBJECT: $SCRIPTS/switchdb.ksh
# ASSUMPTIONS  : NONE
#                                                                      
# SETUP INSTRUCTIONS : NONE            
#                                                                      
# COMMAND LINE EXECUTION: tbs_monitor.ksh -d [oracle_sid] [-s sizeinMB] [-p pct]
#                                                                      
# EXAMPLE: tbs_monitor.ksh -d DEV -s 300
# EXAMPLE: tbs_monitor.ksh -d DEV -p 85
#                                                                      
# RESTART INSTRUCTIONS: rerun script                                   
# SPECIAL CONSIDERATIONS: NONE                                         
#######################################################################
# Modification Log:                                                    
# Version Date        Name              Description                    
#######################################################################
# 1.0     04-Jul-2006 Delfino Nunez     Initial Creation               
#######################################################################

########################################################################
# FUNCTION   : showHelp
# DESCRIPTION: Print the help for using the script
########################################################################
function showHelp {
			echo " Argument      Description"
      echo " ------------  ----------------------------------------------"
      echo "  -d [sid]     Oracle sid to check."
      echo "  -p xx        Report on percentage usage(pct > xx)."
      echo "  -s xxxxxx    Report on MB size usage(sizeMB < xxxxxx)."
      echo "  -h           This help."
      echo " \n\n EXAMPLE: "
      echo "          Report tablespaces that have more than 95% space used"
      echo "          tbs_monitor.ksh -s EDS2TSDE -p 95"
      echo "          Report tablespaces that have less than 300Mb space"
      echo "          tbs_monitor.ksh -s EDS2TSDE -s 300"
}

########################################################################
# Main Program start here
########################################################################
# Verify input paramtter and set environment variables                
#parm_list=$@
. $DBA_BASE/init
#set -- $parm_list

#check parameters
if [ "$1" != "" ]
then
while [ "$#" != "0" ]
do
  case $1 in
    -s)
      shift
      if [ "$1" != "" ]; then
        export RPTTYPE="SIZE"
        export PSIZE=$1
        shift
      fi
    ;;
    -d)
      shift
      if [ "$1" != "" ]; then
        export ORACLE_SID=$1
        shift
      fi
    ;;
    -p)
      shift
      if [ "$1" != "" ]; then
        export RPTTYPE="PCT"
        export PSIZE=$1	
        shift
      fi
    ;;
    -h)
      showHelp

      exit 0;
    ;;
    *)
      echo "Bad argument: $1"
      showHelp

      exit 1;
    ;;
  esac
done
else
	showHelp
	exit 1
fi

. $SCRIPTS/switchdb.ksh $ORACLE_SID

BOXNAME=`uname -n`
MAILIDS=`cat $DBA_BASE/.forward`

# point to where the rpt file will go 
export RPTFILE=$TMP/${ORACLE_SID}_tablespace_rpt.txt
export EMAILMSG=$TMP/tbs_email.txt
export dbpass=`cat $PWDFILE`

# should not need to be changed
export DATE=$(date +%y-%m-%d.%H:%M:%S)

#remove any previous file
if [ -a $RPTFILE ]
then
	rm $RPTFILE
fi
if [ -a $EMAILMSG ]
then
	rm $EMAILMSG
fi

#Run the status report.

case "$RPTTYPE" in
	"PCT")
	   MESSAGE="Tablespaces with usage >= $PSIZE%."
	   sqlplus -s /nolog <
connect $dbpass
prompt Instance: $ORACLE_SID
prompt Tablespaces with usage >= $PSIZE%.
@$SQL/tablespace_size_pct.sql $RPTFILE $PSIZE
EOF

	   ;;
	"SIZE")
     MESSAGE="Tablespaces with usage <= $PSIZE Mb."	
	   sqlplus -s /nolog <
connect $dbpass
prompt Instance: $ORACLE_SID
prompt Tablespaces with usage < $PSIZE MB.
@$SQL/tablespace_size_spc.sql $RPTFILE $PSIZE
EOF
	   ;;
esac

#tablespace with problems
if [[ -s $RPTFILE ]]; then
	echo "The following database have problems with the size of their tablespaces:\n" >> $EMAILMSG
	echo "Instance: $ORACLE_SID" >> $EMAILMSG
	echo $MESSAGE >> $EMAILMSG
	cat $RPTFILE >> $EMAILMSG
  mailx -s "ALERT!!! Tablespace Usage Problems - $BOXNAME:$ORACLE_SID $DATE" $MAILIDS < $EMAILMSG
fi
  • Y el ultimo script nos permite obtener el reporte para todas las bases que se encuentran en el servidor. Este script lee el archivo ORATAB y saca todas las bases de datos registradas en el servidor. Este script se puede poner en el crontab de la siguiente manera:
  • 00 00-01,06-23 * * * /home/oracle/dba/scripts/all.tbs_monitor -p >/dev/null 2>&1
  • Aqui le estamos diciendo al crontab que ejecute el script cada hora excepto de 2am-5am, esto por cuestiones de respaldos.
  • Si no se quiere procesar todas las bases de datos y solo se quiere verificar algunas entonces es necesario de calendarizar el script tbs_monitor.ksh por cada una de las bases de datos.

all.tbs_monitor

#!/bin/ksh
#######################################################################
#                                                                      
# SCRIPT       : all.tbs_monitor
#                                                                
# DESCRIPTION  : This script will call tbs_monitor script for all databases
#                                                                
# INPUT FILES  : $DBA_BASE/init - configuration file
#                -a all databases are processed       
#                -p production (*PD) databases are processed
#                                                                
# OUTPUT FILES : NONE       
#                                                                
# CALLED OBJECT: tbs_monitor
# ASSUMPTIONS  : NONE
#                                                                      
# SETUP INSTRUCTIONS : Need to switchdb first to a default.            
#                                                                      
# COMMAND LINE EXECUTION:                   
#                                                                      
# EXAMPLE:                                        
#                                                                      
# RESTART INSTRUCTIONS: rerun script                                   
# SPECIAL CONSIDERATIONS: NONE                                         
#######################################################################
# Modification Log:                                                    
# Version Date        Name              Description                    
#######################################################################
# 1.0     04-Jul-2006 Delfino Nunez     Initial Creation               
#######################################################################

############################################################################
# MAIN PROGRAM STARTS HERE                                                  
############################################################################

. $HOME/dba/init
priority=1

########################################################################
# FUNCTION   : showHelp
# DESCRIPTION: Print the help for using the script
########################################################################
function showHelp {
			echo " Argument      Description"
      echo " ------------  ----------------------------------------------"
      echo " -p            Only production databases."
      echo " -a            All databases."
      echo " -h            This help."
      echo " \n\n EXAMPLE: all.tbs_monitor -a"
      echo "          all.tbs_monitor -p"
}

########################################################################
# Main Program start here
########################################################################
if [ -f /var/opt/oracle/oratab ] 
then
   export ORATAB='/var/opt/oracle/oratab'
elif [ -f /etc/oratab ]
then
   export ORATAB='/etc/oratab'
else
# following code from Steve Larson
   FOUND_ORATAB=$( find / -name oratab 2>/dev/null | head -1 )
   if [ "$FOUND_ORATAB" ] && [ -f "$FOUND_ORATAB" ]
   then
       ORATAB="$FOUND_ORATAB"
   else
       exit 1
   fi
fi

#check parameter
if [ "$1" != "" ]
then
while [ "$#" != "0" ]
do
  case $1 in
  	-p)
      export INSTANCES=`awk -F: '$1 !~ /\#/ && $1 !~ /\*/ {print $1}' $ORATAB|grep "PD"`
      shift
    ;;
    -a)
      export INSTANCES=`awk -F: '$1 !~ /\#/ && $1 !~ /\*/ {print $1}' $ORATAB`
      shift
    ;;
    -h)
      showHelp  
      exit 0;
    ;;
    *)
      echo "Bad argument: $1"
      echo ""
    	showHelp  
      exit 1;
    ;;
  esac
done
else
	showHelp
	exit 1
fi

if [ "$#" != "0" ]
then
    ORACLE_SID=$1
    shift
    export ORACLE_SID
else
    for ORACLE_SID in $INSTANCES
    do
        swdb $ORACLE_SID
        tbs_monitor.ksh -d $ORACLE_SID -s 300
    done
fi

Los scripts utilizan otros scripts o archivos que no estan incluidos aqui y que voy a ir publicando, pero estos scripts pueden usarse como base para crear unos personalizados.

  1. sql wildcard,sql wildcards,sql rollback,rollback sql,sql copy table,sql sum,sql mirroring,sum sql,sql cluster,sql server performance,truncate in sql,backup sql,backup sql database,backup sql server,sql performance,date functions in sql,sql over,truncate s

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: