#!/bin/bash
echo "======================================"
echo "=    監控監聽器  by ardu 2010/06/07  ="
echo "======================================"
. /home/oracle/.bash_profile
tempfile=/u01/tempfile.lis
lsnrctl status > $tempfile
if [ $? != 0 ]
then
   echo " " >> $tempfile
   echo "=============================================" >> $tempfile
   echo " " >> $tempfile
   date >> $tempfile
   echo "Listener is down, restarting it agin." >> $tempfile
   lsnrctl start >> $tempfile

   if [ $? = 0 ]
   then
      mail ardu@163.com -s "Listener is restarted." < $tempfile
   else
      mail ardu@163.com -s "Listener is down, restarting failed." < $tempfile
   fi
 fi
rm -f $tempfile

#/bin/bash
echo "======================================"
echo "=   磁盤空間  by ardu 2009/08/23  ="
echo "======================================"
limit=85%
RESULT=$(df | grep "^/dev/" | awk '{print $5}' | sort -nr | head -1)
if [[ $RESULT > $limit ]]; then
     df -h | mail -s "Server Warning: Disk usage nearly full " ardu@163.com
fi

#! /bin/bash
echo "======================================"
echo "=   磁盤空間  by ardu 2009/08/23  ="
echo "======================================"
limit=85
tempfile=chk_disk_space.tmp
alert=n
for percent in `df | grep -v Filesystem | awk '{ print $5 }' | cut -d'%' -f1`
do
   if [ $percent -ge $limit ]
   then
      alert=y
      break
    fi
 done

 if [ ! alert = 'n' ]
 then
    df -k > $tempfile
    mail -s "Disk usage above $limit% on `hostname`" ardu@163.com < $tempfile
    rm -f $tempfile
 fi

#! /bin/bash
echo "======================================"
echo "=    警告日誌  by ardu 2010/08/23  ="
echo "======================================"
. /home/oracle/.bash_profile
cd $ORACLE_BASE/admin/$ORACLE_SID/bdump
if [ -f alert_$ORACLE_SID.log ]
then
   mv alert_$ORACLE_SID.log alert_temp.log
   touch alert_$ORACLE_SID.log
   cat alert_temp.log >> alert_$ORACLE_SID.hist
   grep ORA- alert_temp.log > alert.err
 fi
 if [ `cat alert.err|wc -l` -gt 0 ]
 then
    mail -s "ORACLE ALERT ERRORS" ardu@163.com < alert.err
 fi
 rm -f alert.err
 rm -f alert_temp.log

#! /bin/bash
echo "======================================"
echo "=    大於80%的tablespace  by ardu 2010/08/23  ="
echo "======================================"
. /home/oracle/.bash_profile
sqlplus "system/oracle11g"> /dev/null << EOF
set feed off
set heading off
set pagesize 0
set linesize 300
spool tbsfree.alert
 SELECT UPPER(F.TABLESPACE_NAME) TABLESPACE_NAME,
       ??TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) /
                         D.TOT_GROOTTE_MB * 100,
                         ??2),
                   ?? '990.99') free_pre ?
  FROM (SELECT TABLESPACE_NAME,
               ROUND(SUM(BYTES) / (1024 * 1024), 2) TOTAL_BYTES,
               ROUND(MAX(BYTES) / (1024 * 1024), 2) MAX_BYTES
          FROM SYS.DBA_FREE_SPACE
         GROUP BY TABLESPACE_NAME) F,
       ?? (SELECT DD.TABLESPACE_NAME,
                    ROUND(SUM(DD.BYTES) / (1024 * 1024), 2) TOT_GROOTTE_MB
               FROM SYS.DBA_DATA_FILES DD
              GROUP BY DD.TABLESPACE_NAME) D
 WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME
   AND TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100,
                     ??2),
               '990.99') > 80
---AND D.TABLESPACE_NAME NOT IN ('UNDOTBS1','SYSTEM','SYSAUX')

 ORDER BY 2 DESC;
spool off
exit
EOF

if [ `cat tbsfree.alert | wc -l` -gt 0 ]
then
   cat tbsfree.alert | mail ardu@163.com -s "No enough free space in Orace db! "
fi

sqlplus scott/tiger << EOF
set pagesize 5000 
set heading off 
SPOOL ANALYTAB.SQL 
SELECT 'ANALYZE TABLE SCOTT.'||TABLE_NAME||' ESTIMATE STATISTICS SAMPLE 20 PERCENT ;' FROM USER_TABLES;
SPOOL OFF 
SPOOL ANALYIND.SQL 
SELECT 'ANALYZE TABLE SCOTT.'||TABLE_NAME||' ESTIMATE STATISTICS SAMPLE 20 PERCENT FOR ALL INDEXES;' FROM USER_TABLES;
SPOOL OFF  SPOOL ANALYZE.LOG
@ANALYTAB.SQL
@ANALYIND.SQL
 SPOOL OFF  EXIT