oracle database size monitor(daily)

Check daily database size and sent mail to dba

#crontab -l

0 7 * * * su - oracle -c "/housekeep/scripts/dbsize cuoln01" > /dev/null 2>&1

##/housekeep/scripts/dbsize

#edit by wonderful 2012-12-12 . `dirname $0`/script.cfg function f_dbsize { SERVER=$1 LOGFILE=/housekeep/logs/$SERVER/dbsize.log ORACLE_SID=$1 export ORACLE_SID # Create the Log file directory, if necessary mkdir -p `dirname $LOGFILE` CUR_DATE_TIME=`date +"%m/%d/%y %H:%M"` # # For Oracle Instance # echo "$CUR_DATE_TIME xxx    (Rept: ORA02)                 Oracle Instance space utilitation for $SERVER " > $LOGFILE sqlplus -S <<EOF | tail +2 | awk '{ printf "%-25s %12s %12s %12s %12s %7s\n", $1,$2,$3,$4,$5,$6 }' >> $LOGFILE / as sysdba set feed off pages 999 echo off line 250 column tablespace_name format a20 column "ALLOCATED/MB" format 9999999.99 column "MAXSPACE/MB" format 9999999.99 column "FREE/MB" format 9999999.99 column "UNUSED/MB" format 9999999.99 column "UNUSED%" format 999.99   SELECT a.tablespace_name,          SUM (a.bytes) / 1024 / 1024 "ALLOCATED/MB",            SUM (DECODE (a.autoextensible, 'YES', a.maxbytes, a.bytes))          / 1024          / 1024             "MAXSPACE/MB",          SUM (b.free) / 1024 / 1024 "FREE/MB",          SUM(b.free              + DECODE (a.autoextensible, 'YES', (a.maxbytes - a.bytes), 0))          / 1024          / 1024             "UNUSED/MB",          SUM(b.free              + DECODE (a.autoextensible, 'YES', (a.maxbytes - a.bytes), 0))          / SUM (DECODE (a.autoextensible, 'YES', a.maxbytes, a.bytes))          * 100             "UNUSED%"     FROM (SELECT file_id,                  tablespace_name,                  autoextensible,                  maxbytes,                  bytes             FROM dba_data_files) a,          (  SELECT file_id, tablespace_name, SUM (bytes) free               FROM dba_free_space           GROUP BY file_id, tablespace_name) b    WHERE a.file_id = b.file_id (+) GROUP BY a.tablespace_name; @/housekeep/scripts/bkpjobchk.sql EOF # Mail the sizing report to local DBA mailx -s "Oracle DB size report for $SERVER" $RECEIVER_DBA $RECEIVER_DWHDBA < $LOGFILE # Store the sizing if it is the First of the Month if [ `date +"%d"` -eq 1 ]; then   DIR=`dirname $LOGFILE`/dbsize   mkdir -p $DIR   cp $LOGFILE $DIR/dbsize.`date +'%b%y'`.log   # Remove all log files older than one year (i.e. keep newest 12 versions)   find $DIR -mtime +366 -exec rm -f {} \; fi } f_dbsize $1

##/housekeep/scripts/bkpjobchk.sql

column job_name format a25 column owner format a8 column scheduled format a12 column "TIME_TAKEN(M)" format 999999999.99 column status format a20 column target format a10 select job_name, job_owner OWNER, scheduled_time SCHEDULED, round((end_time-start_time)*24*60,2) "TIME_TAKEN(M)", status, target_name TARGET from SYSMAN.MGMT$JOB_EXECUTION_HISTORY where job_type in ('Backup', 'BkpMgmt') and start_time > (sysdate-7) and scheduled_time < sysdate order by scheduled_time desc;