借助一个文件,存放ps –ef | grep pmon的输出
[test]:/orasw/dba>more /orasw/dba/scripts/ db_running.log
oracle 8922 1 0 Dec 17 ? 10:48 ora_pmon_spw2005p
oracle 13535 1 0 Dec 17 ? 10:00 ora_pmon_mstrmd1p
oracle 28599 1 0 Dec 19 ? 10:39 ora_pmon_tsr2008p
oracle 7561 1 0 Dec 17 ? 11:57 ora_pmon_asa2p
oracle 6756 1 0 Dec 17 ? 11:53 ora_pmon_tsr2009p
oracle 12041 1 0 Dec 17 ? 11:01 ora_pmon_spw06p
oracle 6737 1 0 Dec 17 ? 10:57 ora_pmon_spw2003p
oracle 16046 1 0 Dec 17 ? 12:32 ora_pmon_crs2007t
oracle 5002 1 0 Dec 19 ? 10:04 ora_pmon_smart3p
shell脚本
1 遍历该文件,将sid取出
2 遍历sid 列表,分别执行检查表空间sql脚本,并将结果发送给相关人员
#!/bin/ksh
db_name=`cat /orasw/dba/scripts/ db_running.log|awk 'BEGIN { FS = "_" } ; { print $3 }'|sed '/^$/d'`
echo $db_name
for k in $db_name
do
h=`ps -ef | grep pmon | grep -c $k`
echo $k
ORACLE_SID=$k; export ORACLE_SID
ORAENV_ASK=NO
. oraenv
#. oraenv
export ORACLE_HOME
# Set threshhold limit for tablespace to send alert
#
export threshold=70
export script_dir=/orasw/dba/scripts/
rm -rf $script_dir/${k}_ts.rpt
rm -rf $script_dir/${k}_chk_ts.out
cut_of_pt=$threshold
tmp_file=$script_dir/${k}_chk_ts.out
ts_stat_rpt=$script_dir/${k}_ts.rpt
sqlplus -s '/ as sysdba' @$script_dir/chk_ts.sql >> $ts_stat_rpt <
exit
EOF
echo "TABLESPACE_NAME PCT_USED(%)" > $tmp_file
echo "-----------------------------" >> $tmp_file
cat $ts_stat_rpt | awk '{
if (int($2) > int("'$cut_of_pt'"))
print $0
}' >> $tmp_file
echo "----------------------------------" >> $ts_stat_rpt
if test -s $tmp_file
then
subject="Below TableSpaces crossed threshold limits of $cut_of_pt in $k. Please take immediate action."
echo $subject
mailx -s "$subject" ***@***.com < $tmp_file
fi
done
相应sql脚本
bash-3.2$ more chk_ts.sql
set lines 120
set pages 100
set echo off
set feedback off
set head off
col PCT_USED format a10
col tablespace_name format a20
(select t.tablespace_name tablespace_name,TO_CHAR((1 - DECODE(tsf.bytes, NULL, tsa.bytes, tsf.bytes) / tsa.bytes) * 100, '990') PCT_USED
from sys.dba_tablespaces t, sys.sm$ts_avail tsa, sys.sm$ts_free tsf
where t.tablespace_name = tsa.tablespace_name and t.tablespace_name = tsf.tablespace_name (+)
)
union
(SELECT D.TABLESPACE_NAME TABLESPACE_NAME, TO_CHAR(DECODE(A.SUM_ALLOCATE, NULL, 0, NVL(T.SUM_USED, 0) / A.SUM_ALLOCATE * 100), '990') PCT_USED
FROM DBA_TABLESPACES D,
(SELECT TABLESPACE_NAME, SUM(BYTES) SUM_ALLOCATE FROM DBA_TEMP_FILES GROUP BY TABLESPACE_NAME) A,
(SELECT TABLESPACE_NAME, SUM(BYTES_CACHED) SUM_USED FROM V$TEMP_EXTENT_POOL GROUP BY TABLESPACE_NAME) T
WHERE D.TABLESPACE_NAME = A.TABLESPACE_NAME(+)
AND D.TABLESPACE_NAME = T.TABLESPACE_NAME(+)
AND D.EXTENT_MANAGEMENT = 'LOCAL' AND D.CONTENTS = 'TEMPORARY'
)
/
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/15480802/viewspace-774718/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/15480802/viewspace-774718/