先点赞后观看,养成好习惯
oracle_cron.sh
source /home/oracle/.bash_profile
sqlplus -s / as sysdba > /tmp/tablespace.log<<EOF
CLEAR COLUMNS BREAKS COMPUTES
set lines 134 pages 200
COLUMN STATUS HEADING 'Status' ENTMAP off
COLUMN TABLESPACE_NAME HEADING 'Name' ENTMAP off
COLUMN CONTENTS FORMAT a12 HEADING 'TS Type' ENTMAP off
COLUMN ALL_MB FORMAT 9,999,999 HEADING 'All Size(MB)' ENTMAP off
COLUMN MAX_MB FORMAT 9,999,999 HEADING 'Max Size(MB)' ENTMAP off
COLUMN FREE_MB FORMAT 9,999,999 HEADING 'Free Size(MB)' ENTMAP off
COLUMN FREE_EXT FORMAT 9,999,999 HEADING 'Max Free' ENTMAP off
COLUMN PCT_FREE FORMAT 999 HEADING 'Pct. Free' ENTMAP off
COLUMN PCT_FREE_EXT FORMAT 999 HEADING 'Max Free%' ENTMAP off
SELECT T.TABLESPACE_NAME TABLESPACE_NAME,
T.CONTENTS CONTENTS,
ROUND(SUM(A.BYTES) / 1048576) ALL_MB,
ROUND(SUM(DECODE(MAXBYTES, 0, A.BYTES, MAXBYTES)) /1048576) MAX_MB,
ROUND(SUM(NVL(F.BYTES,0)) / 1048576) FREE_MB,
ROUND((SUM(DECODE(MAXBYTES, 0, 0, MAXBYTES-A.BYTES)) + SUM(NVL(F.BYTES, 0))) / 1048576) FREE_EXT,
ROUND(100 * SUM(NVL(F.BYTES, 0)) / SUM(A.BYTES)) PCT_FREE,
ROUND(100 * (SUM(DECODE(MAXBYTES, 0, 0, MAXBYTES-A.BYTES)) + SUM(NVL(F.BYTES, 0))) / SUM(DECODE(A.MAXBYTES, 0, A.BYTES, A.MAXBYTES))) PCT_FREE_EXT
FROM DBA_DATA_FILES A,
DBA_TABLESPACES T,
(SELECT FILE_ID,SUM(BYTES) BYTES FROM DBA_FREE_SPACE GROUP BY FILE_ID) F
WHERE A.FILE_ID = F.FILE_ID(+)
AND A.TABLESPACE_NAME = T.TABLESPACE_NAME
AND T.CONTENTS != 'TEMPORARY'
GROUP BY T.TABLESPACE_NAME, T.CONTENTS, T.STATUS
ORDER BY ROUND(100 * SUM(NVL(F.BYTES, 0)) / SUM(A.BYTES));
EOF
sed -i '$d' /tmp/tablespace.log
sed -i '$d' /tmp/tablespace.log
sed -i 's/,//g' /tmp/tablespace.log
#取第8列数据;去掉不是数字的;去掉大于20的;有返回数据插入NO,没有返回数据插入YES
if cat /tmp/tablespace.log | awk '$8 <= 20 {print $8}' | grep -qE '[0-9]+'; then
echo "NO" > /tmp/tablespace.log
else
echo "YES" > /tmp/tablespace.log
fi
监控项
vfs.file.regmatch[/tmp/tablespace.log,NO]
触发器
last(/Check the Oracle tablespace/vfs.file.regmatch[/tmp/tablespace.log,NO])=1
1.查找文件中的字符串,如果有则返回1,没有则返回0。
2.是否存在与字符串"NO"匹配的内容,如果匹配结果为1,则触发报警
su - oracle
crontab -e
# Check the Oracle tablespace
0 */3 * * * /home/oracle/scripts/oracle_cron.sh > /tmp/oracle_cron.sh.log 2>&1