一、检查实例
echo "`date` "
echo "Oracle Database(s) Status`hostname` "
namelst=`ps -ef|grep ora_pmon|grep -vgrep|awk '{print $8}'|cut -c10-15`
for name in $namelst
do
if [ -z $name ];then
echo "Oracle Instance - $name:Down"
else
echo "Oracle Instance - $name:Up"
fi
done
下面解释下这个脚本的含义:
首先打印出当前时间与主机名,然后定义了一个变量namelst,
ps –ef | grep ora_pman | grep –v grep
结果为
oracle 3088 1 0 16:50 ? 00:00:00 ora_pmon_prod
现在要过滤出prod这个实例名,使用awk工具,
Awk ‘{print $8}’的意思是把上面的结果打印出第8列,也就是ora_pmon_prod,完了之后,再使用cut –c10-15,截取这个字符串的第10个字符到第15个字符之间的内容,也就是prod。然后用一个循环,其中判断条件-z $name意思是变量name的字符串长度是否为零,若为零,则打印该实例已经Down,反之为Up状态。
以上是一个普通的shell脚本,但实际操作时,我们一般将检测结果使用邮件的方式发送出来,上一篇博客我配置了一个sendmail,现在把这个脚本变通一下,
echo "`date` "
echo "Oracle Database(s) Status`hostname` "
namelst=`ps -ef|grep ora_pmon|grep -vgrep|awk '{print $8}'|cut -c10-15`
mailuser="oracle,root"
for name in $namelst
do
if [ -z $name ];then
echo "Oracle Instance - $name:Down" > mail.txt
mailx -s "Instance is down"$mailuser < mail.txt
else
echo "Oracle Instance - $name:Up" > mail.txt
mailx -s "Instance is up"$mailuser < mail.txt
fi
done
这样就把检测结果发给了oracle和root用户,是不是很方便?
但现在我觉得这还不够,每次我都要呆在机器旁边周期性执行脚本太麻烦了,那怎么让机器周期性地自动运行脚本,然后把结果发到我们的邮箱呢?crotab可以做到!
首先[oracle@bj ~]$ vi .bash_profile
把环境变量放到这个脚本中,下面是最新的脚本:
#!/bin/bash
#chkinst2.sh
export EDITOR=vi
export ORACLE_SID=prod
export ORACLE_BASE=/u01/app/oracle
exportORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
exportPATH=$ORACLE_HOME/bin:/bin:/usr/bin:/usr/sbin:/usr/local/bin:/usr/X11R6/bin
umask 022
echo "`date` "
echo "Oracle Database(s) Status`hostname` "
namelst=`ps -ef|grep ora_pmon|grep -vgrep|awk '{print $8}'|cut -c10-15`
mailuser="oracle,root"
for name in $namelst
do
if [ -z $name ];then
echo "Oracle Instance - $name:Down" > mail.txt
mailx -s "Instance is down"$mailuser < mail.txt
else
echo "Oracle Instance - $name:Up" > mail.txt
mailx -s "Instance is up"$mailuser < mail.txt
fi
done
现在创建contab
[oracle@bj shell]$ crontab –e
添加
0-59/10 * * * * sh/home/oracle/shell/chkinst2.sh >/dev/null 2>&1
保存。
这个意思是每隔10分钟执行一次这个脚本,至于四个*代表什么,就是每天、每周、每月、每年的意思,不管是标准输出还是错误输出都丢入/dev/null,也就是不输出。
二、检测listener的运行状态
#!/bin/bash
export EDITOR=vi
export ORACLE_SID=prod
export ORACLE_BASE=/u01/app/oracle
exportORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
exportPATH=$ORACLE_HOME/bin:/bin:/usr/bin:/usr/sbin:/usr/local/bin:/usr/X11R6/bin
umask 022
mailuser='oracle,root'
lsn=`netstat -an |grep :1521 |head -1 |awk'{print $5}'|cut -c15-19`
if [ "$lsn" = '1521' ] ;then
echo "Current Listener Port is : $lsn" > mail.txt
mailx -s "Listener is up" $mailuser< mail.txt
else
echo"This `hostname` listener is down !" > mail.txt
mailx -s "Listenr is down and startit,please check." $mailuser < mail.txt
lsnrctl start
fi
然后添加计划任务
[oracle@bj shell]$ crontab –e
加入
0-59/5 * * * * sh/home/oracle/shell/lsnr.sh >/dev/null 2>&1
三、检测非默认listener(转)
案例1(RedHat 6)
[oracle@bj shell]$ cat chklsn.sh
#!/bin/bash
export TMP=/tmp
export TMPDIR=$TMP
export ORACLE_BASE=/u01/app/oracle
exportORACLE_HOME=$ORACLE_BASE/product/10.2.0/db_1;
export ORACLE_SID=prod
export ORACLE_TERM=xterm
export PATH=/usr/sbin:$PATH
export PATH=$ORACLE_HOME/bin:$PATH; exportPATH
exportLD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib
exportCLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib;
export muser="oracle,root"
lsnr1=`netstat -an |grep -i listen|grep:1521 |head -1 |awk '{print $4}'|cut -d":" -f2`
lsnr2=`netstat -an |grep -i listen|grep:1522 |head -1 |awk '{print $4}'|cut -d":" -f2`
if [ "$lsnr1" = '1521' ]&& [ "$lsnr2" = '1522' ] ;then
echo "Current Listener Port is : $lsnr1 and $lsnr2"
echo 'listener is up !'
else
echo"This `hostname` listener is down !"
echo 'hello ,this host listener is down ,please note!'>mail.txt
mailx -s "listener is down" $muser<mail.txt
echo"Starting listener ...."
lsnrctl start
lsnrctlstart lsn2
fi
[oracle@bj shell]$
案例2 :(RedHat 55)
[oracle@bj shell]$ cat chklsn.sh
#!/bin/bash
export TMP=/tmp
export TMPDIR=$TMP
export ORACLE_BASE=/u01/app/oracle
exportORACLE_HOME=$ORACLE_BASE/product/10.2.0/db_1;
export ORACLE_SID=prod
export ORACLE_TERM=xterm
export PATH=/usr/sbin:$PATH
export PATH=$ORACLE_HOME/bin:$PATH; exportPATH
exportLD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib
exportCLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib;
export muser="oracle,root"
lsnr1=`netstat -an |grep -i listen|grep:1521 |head -1 |awk '{print $4}'|cut -c4-10`
lsnr2=`netstat -an |grep -i listen|grep:1522 |head -1 |awk '{print $4}'|cut -c4-10`
if [ "$lsnr1" = '1521' ]&& [ "$lsnr2" = '1522' ] ;then
echo "Current Listener Port is : $lsnr1 and $lsnr2"
echo 'listener is up !'
else
echo"This `hostname` listener is down !"
echo 'hello ,this host listener is down ,please note!'>mail.txt
mailx -s "listener is down" $muser<mail.txt
echo"Starting listener ...."
lsnrctl start
lsnrctl start lsn2
fi
案例 3:
[oracle@bj shell]$ cat chklsnr.sh
#!/bin/bash
export TMP=/tmp
export TMPDIR=$TMP
export ORACLE_BASE=/u01/app/oracle
exportORACLE_HOME=$ORACLE_BASE/product/10.2.0/db_1;
export ORACLE_SID=prod
export ORACLE_TERM=xterm
export PATH=/usr/sbin:$PATH
export PATH=$ORACLE_HOME/bin:$PATH; exportPATH
exportLD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib
exportCLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib;
lsn=`ps -ef |grep -i tnslsnr |grep -v grep`
if [ -z "$lsn" ]
then
echo"This `hostname` listener is down !"
echo"Starting listener ...."
name=`cat$ORACLE_HOME/network/admin/listener.ora |awk '{print $1}'|grep -v '^('|grep -v '^)'|grep -v '^#' |grep -v -i'sid'|grep -v '^$'|grep -v -i 'adr_base'`
forlsnname in $name
do
lsnrctl start $lsnname
done
else
echo 'listener is up !'
fi
案例3不推荐使用。
四、监控告警日志并备份
#!/bin/bash
export EDITOR=vi
export ORACLE_SID=prod
export ORACLE_BASE=/u01/app/oracle
exportORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
exportPATH=$ORACLE_HOME/bin:/bin:/usr/bin:/usr/sbin:/usr/local/bin:/usr/X11R6/bin
umask 022
#######################################################################
## clean_arch.ksh ##
#######################################################################
#!/bin/bash
# $ df -k | grep dsk1
#Filesystem kbytes used avail capacityMounted on
#/dev/mapper/datavg-lv_dsk1 4128448 303004 3615732 8% /dsk1
df -k | grep -i dsk1|tail -1 >dfk.result
archive_filesystem=`awk -F" " '{print $5 }' dfk.result`
archive_capacity=`awk -F" " '{print $4 }' dfk.result`
if [ $archive_capacity > 50% ]
then
echo "Filesystem ${archive_filesystem}is ${archive_capacity} filled"
# try one of the following option depend onyour need
arch_dir=/dsk3/arch_bj
for name in `find $arch_dir -type f -mtime +2`
do
mv $name /home/oracle/backup
done
fi
在crontab中添加
0 20 * * * sh/home/oracle/shell/chkarch.sh >/dev/null 2>&1
五、监控表空间的使用
#!/bin/ksh
export EDITOR=vi
export ORACLE_SID=prod
export ORACLE_BASE=/u01/app/oracle
exportORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
exportPATH=$ORACLE_HOME/bin:/bin:/usr/bin:/usr/sbin:/usr/local/bin:/usr/X11R6/bin
umask 022
LD_LIBRARY_PATH=$ORACLE_HOME/lib; exportLD_LIBRARY_PATH
TNS_ADMIN=/var/opt/oracle;export TNS_ADMIN
NLS_LANG=american; export NLS_LANG
NLS_DATE_FORMAT='Mon DD YYYY HH24:MI:SS';export NLS_DATE_FORMAT
ORATAB=/etc/oratab;export ORATAB
DBALIST="oracle,root";exportDBALIST
sqlplus '/as sysdba' <<EOF
set feed off
set linesize 100
set pagesize 200
column "USED (MB)" format a10
column "FREE (MB)" format a10
column "TOTAL (MB)" format a10
column PER_FREE format a10
spool /home/oracle/tablespace.alert
SELECT F.TABLESPACE_NAME,
TO_CHAR ((T.TOTAL_SPACE - F.FREE_SPACE),'999,999') "USED(MB)",
TO_CHAR (F.FREE_SPACE, '999,999') "FREE (MB)",
TO_CHAR (T.TOTAL_SPACE, '999,999') "TOTAL (MB)",
TO_CHAR ((ROUND ((F.FREE_SPACE/T.TOTAL_SPACE)*100)),'999')||' %'PER_FREE
FROM (
SELECT TABLESPACE_NAME,
ROUND (SUM (BLOCKS*(SELECT VALUE/1024
FROM V\$PARAMETER
WHERE NAME = 'db_block_size')/1024)
)FREE_SPACE
FROM DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME
) F,
(
SELECT TABLESPACE_NAME,
ROUND (SUM (BYTES/1048576)) TOTAL_SPACE
FROM DBA_DATA_FILES
GROUP BY TABLESPACE_NAME
) T
WHERE F.TABLESPACE_NAME = T.TABLESPACE_NAME
AND(ROUND ((F.FREE_SPACE/T.TOTAL_SPACE)*100)) < 80;
spool off
exit
EOF
if [ `cat /home/oracle/tablespace.alert|wc-l` -gt 0 ]
then
cat /home/oracle/tablespace.alert >tablespace.tmp
mailx -s "TABLESPACE ALERT forprod" $DBALIST < tablespace.tmp
fi