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