单个脚本如何监控主机上所有实例的表空间利用率
这篇文章给大家分享的是有关单个脚本如何监控主机上所有实例的表空间利用率的内容。小编觉得挺实用的，因此分享给大家做个参考，一起跟随小编过来看看吧。
借助一个文件，存放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 <<-EOF
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'
)
/
感谢各位的阅读！关于"单个脚本如何监控主机上所有实例的表空间利用率"这篇文章就分享到这里了，希望以上内容可以对大家有一定的帮助，让大家可以学到更多知识，如果觉得文章不错，可以把它分享出去让更多的人看到吧！
