测试oracle脚本编写,oracle 检测脚本on linux

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"

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#;

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

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

WHERED.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 (

SELECTto_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@]