Normal 0 7.8 pt 0 2 false false false MicrosoftInternetExplorer4

#!/bin/bash

# This script is used for monitoring database servers including the following items daily

# 1 Disk space usage;

# 2 Database space usage;

# 2.1 ASM_disgroup USAGE;

# 2.2 Data size status and tablespace usage and Top 5 wait event within 7 days;

# 2.3 Database Error log info;

#

#Author:Ben

#Create Date: 2011-02-18

#Create monitor log directory:

SCRIPT_BASE=/home/oracle/monitor

if [ ! -d $SCRIPT_BASE ]; then

mkdir $SCRIPT_BASE

fi

LOGDIR=$SCRIPT_BASE/log

if [ ! -d $LOGDIR ]; then

mkdir $LOGDIR

fi

DATE=`date +%Y-%m-%d`

Time=`date +%X`

LOG=$LOGDIR/$DATE.log

Servername=`hostname`

echo "">$LOG

echo "========$Servername monitor log is as below=======">>$LOG

echo "">>$LOG

echo "========Monitor time is $DATE-$Time.======= ">>$LOG

####### 1 Check the filesystem usage and log if usage is over 90%###########

echo ''>>$LOG

echo '1 Filesystem usage:'>>$LOG

Filesystem=`df -h|awk '{print $6}'|grep -v Filesystem|grep -v none|grep -v ":/"`

for i in `df -h|awk '{print $5}'|grep -v Use|grep -v "/"|cut -d% -f1`

do

if [ $i -gt 90 ]; then

echo " Warning:The usage of $Filesystem has been $i%">>$LOG

fi

done

echo " `df -h`">>$LOG

echo ''>>$LOG

####### 2 Check database space usage###########

###2.1 ASM###

echo ''>>$LOG

echo "2 Database space usage:">>$LOG

oraclesidnum=`ps -ef|grep pmon|grep "_"|awk '{print $8}'|cut -d_ -f3|grep -v "+"|wc -l` ## count the instance number

oracle_home=`grep -v ^[#] /etc/oratab|grep -v '^$' |grep -v '+'|grep -v '*'|awk -F : '{print $2}'|head -n 1` ## get ORACLE_HOME

####Check if use ASM

asm=`ps -ef|grep pmon|grep "_"|awk '{print $8}'|cut -d_ -f3|grep "+"`

if [ "$asm" = "" ];then

echo " oracle storage is not using ASM!">>$LOG

else

oracle_sid_asm=`ps -ef|grep pmon|grep "_"|awk '{print $8}'|cut -d_ -f3|grep -v "+"|head -n 1` ## get ORACLE_SID

export ORACLE_SID=$oracle_sid_asm

export ORACLE_HOME=$oracle_home

export PATH=$oracle_home/bin:$PATH

echo ''>>$LOG

echo " 2.1 ASM_DISGROUP usage:">>$LOG

sqlplus -s "/ as sysdba" < ##Need to change accordingly

spool '$LOG' app

set linesize 180

set feedback off

select GROUP_NUMBER,NAME,TOTAL_MB/1024 G,FREE_MB/1024 AG from V$ASM_DISKGROUP;

spool off

exit

EOF

fi

####Data size status and tablespace usage and Top 5 event ####

echo ''>>$LOG

echo " 2.2 Data size status and tablespace usage and top 5 event">>$LOG

#Ora_version=`grep "Oracle Database" $LOG|awk '{print $3}|cut -dg -f1'`

for i in `ps -ef|grep pmon|grep "_"|awk '{print $8}'|cut -d_ -f3|grep -v "+"`

do

export ORACLE_SID=$i

export ORACLE_HOME=$oracle_home

export PATH=$oracle_home/bin:$PATH

Sdate=`date +%b%d%R`

spoolfile=$LOGDIR/$Sdate.$i.txt

echo " Instance $i:">>$LOG

sqlplus -s "/ as sysdba" <>$spoolfile ##Need to change accordingly

spool '$LOG' app

set linesize 180

set feedback off

col VALUE for a60

col file_name for a60

col event_name for a30

show parameter db_name;

SELECT VALUE FROM v$parameter WHERE name like '%background_dump_dest%';

select * from v$flash_recovery_area_usage;

select thread#,max(sequence#) from v$archived_log group by thread#;

select dbf.AllocateSize,

de.UsedSize,

(de.UsedSize/dbf.AllocateSize) * 100 Per_Used,

(dbf.AllocateSize-de.UsedSize) FreeSize

from

(SELECT

SUM(bytes)/1024/1024/1024 AllocateSize

FROM dba_data_files files) dbf,

(SELECT SUM(bytes)/1024/1024/1024 UsedSize

FROM dba_extents) de;

SELECT

D.TABLESPACE_NAME,SPACE "SUM_SPACE(M)",

SPACE-NVL(FREE_SPACE,0) "USED_SPACE(M)",

FREE_SPACE "FREE_SPACE(M)",

ROUND((1-NVL(FREE_SPACE,0)/SPACE)*100,2) "USED_RATE(%)"

FROM

(SELECT

TABLESPACE_NAME,

ROUND(SUM(BYTES)/(1024*1024),2) SPACE,

SUM(BLOCKS) BLOCKS

FROM DBA_DATA_FILES

GROUP BY TABLESPACE_NAME) D,

(SELECT

TABLESPACE_NAME,

ROUND(SUM(BYTES)/(1024*1024),2) FREE_SPACE

FROM DBA_FREE_SPACE

GROUP BY TABLESPACE_NAME) F

WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)

order by TABLESPACE_NAME;

SELECT

D.TABLESPACE_NAME,SPACE1 "Total",SPACE "SUM_SPACE(M)",

SPACE-NVL(FREE_SPACE,1) "USED_SPACE(M)",

SPACE1-(SPACE-NVL(FREE_SPACE,0)) "FREE_SPACE(M)",

ROUND((SPACE-NVL(FREE_SPACE,1))/SPACE1*100,2) "USED_RATE(%)"

FROM

(SELECT

TABLESPACE_NAME,

ROUND(SUM(decode(MAXBYTES,0,BYTES,MAXBYTES))/(1024*1024),2) SPACE1,

ROUND(SUM(BYTES)/(1024*1024),2) SPACE

FROM DBA_DATA_FILES

GROUP BY TABLESPACE_NAME ) D,

(SELECT

TABLESPACE_NAME,

ROUND(SUM(BYTES)/(1024*1024),2) FREE_SPACE

FROM DBA_FREE_SPACE

GROUP BY TABLESPACE_NAME) F

WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)

order by TABLESPACE_NAME;

select Day, Event_name, Total_wait from (

select day, event_name, sum(event_time_waited) total_wait,

row_number() over (partition by day order by sum(event_time_waited) desc) rn from (

SELECT to_date(to_char(begin_interval_time,'dd/mm/yyyy'),'dd/mm/yyyy') day,s.begin_interval_time, m.*

FROM (SELECT ee.instance_number, ee.snap_id, ee.event_name,

ROUND (ee.event_time_waited/1000000) event_time_waited,

ee.total_waits,

ROUND ((ee.event_time_waited * 100)/et.total_time_waited,

1

) pct,

ROUND ((ee.event_time_waited/ee.total_waits)/1000

) avg_wait

FROM (SELECT ee1.instance_number, ee1.snap_id, ee1.event_name,

(ee1.time_waited_micro-ee2.time_waited_micro) event_time_waited,

(ee1.total_waits-ee2.total_waits) total_waits

FROM dba_hist_system_event ee1 JOIN dba_hist_system_event ee2

ON ee1.snap_id = ee2.snap_id + 1

AND ee1.instance_number = ee2.instance_number

AND ee1.event_id = ee2.event_id

AND ee1.wait_class_id <> 2723168908

AND (ee1.time_waited_micro-ee2.time_waited_micro) > 0

UNION

SELECT st1.instance_number, st1.snap_id,

st1.stat_name event_name,

(st1.VALUE-st2.VALUE) event_time_waited,

1 total_waits

FROM dba_hist_sys_time_model st1 JOIN dba_hist_sys_time_model st2

ON st1.instance_number = st2.instance_number

AND st1.snap_id = st2.snap_id + 1

AND st1.stat_id = st2.stat_id

AND st1.stat_name = 'DB CPU'

AND (st1.VALUE-st2.VALUE) > 0

) ee

JOIN

(SELECT et1.instance_number, et1.snap_id,

(et1.VALUE-et2.VALUE) total_time_waited

FROM dba_hist_sys_time_model et1 JOIN dba_hist_sys_time_model et2

ON et1.snap_id = et2.snap_id + 1

AND et1.instance_number = et2.instance_number

AND et1.stat_id = et2.stat_id

AND et1.stat_name = 'DB time'

AND (et1.VALUE-et2.VALUE) > 0

) et

ON ee.instance_number = et.instance_number

AND ee.snap_id = et.snap_id

) m

JOIN

dba_hist_snapshot s ON m.snap_id = s.snap_id

) group by day ,event_name

order by day desc, total_wait desc

)where rn < 6;

spool off

exit

dbsize

echo ''>>$LOG

##############Check alert log######

echo ''>>$LOG

echo " 2.3 Error from alert.log file:">>$LOG

echo " Error from Instance $i: ">>$LOG

dbname=`grep 'db_name' $spoolfile|awk '{print $3}'`

Bdumppath=`grep "/$dbname/" $spoolfile`

cd $Bdumppath

Alertfile=alert_$i.log

Todate=`date +'%b'` ##get abbreviated month name

Errmsg=`grep "$Todate" $Alertfile|grep "ORA-" `

if [ !-n $Errmsg ];then

echo "There is no error in instance $i">>$LOG

else

echo "Error message is:">>$LOG

echo "$Errmsg">>$LOG

echo ''>>$LOG

fi

rm -rf $spoolfile

done

cd $LOGDIR

find . -name '*.log' -mtime +7 -exec rm -rf {} ;

#find . -name '*.txt' -mtime +3 -exec rm -rf {} ;

#######Mail to Ben

cat $LOG |mail -s "Seatadvisor: $Servername Daily Monitor Result" jerry.mao@bestit.com

[@more@]