8个DBA常用的监控Oracle数据库的shell脚本

1. 检查Oracle实例的可用性

####################################################################
##                        ckinstance.sh                           ##
##                       created by Lance                         ##
##                         2012-05-21                             ##
####################################################################

#!/bin/bash

EDITOR=vi;export EDITOR
ORACLE_BASE=/u01/app/oracle;export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/product/10.2.0/db_1;export ORACLE_HOME
ORACLE_SID=vrh1;export ORACLE_SID
ORATAB=/etc/oratab;export ORATAB
PATH=$PATH:$ORACLE_HOME:$ORACLE_HOME/bin:/sbin:/usr/local/bin:/opt/bin:bin;export PATH
DBALIST="953656107@qq.com"; export DBALIST

ORATAB=/etc/oratab
echo "`date` "
echo "Oracle Database(s) Status `hostname` :/n"
db=`egrep -i ":Y|:N" $ORATAB | cut -d":" -f1 `
pslist="`ps -ef | grep pmon`"
for i in $db ;
do
echo "$pslist" | grep "ora_pmon_$i" > /dev/null 2>$1 &
if (( $? )); then
echo "Oracle Instance - $i: Down"
else
echo "Oracle Instance - $i: Up"
fi
done

#?:(上一个执行命令的回传码)
#[root@linux ~]#echo $SHELL
#/bin/bash
#[root@linux ~]#echo $?
#0
#因为上一个命令执行过程中，并没有错误，为成功执行完毕，所以回传0.
#[root@linux ~]12name=VBird
#-bash:12name-VBird:Command not found
#[root@linux ~]#echo $?
#127
#发生错误。所以echo $?时，就会出现错误代码
#我们可以利用这个代码来搜索错误的原因。
#[root@linux ~]#echo $?
#0
#怎么又变成正确了？这是因为"?"只与"上一个执行命令"有关
#所以，我们上一个命令是执行"echo $?",当然没有错误，所以是0没错

3. 检查Alert日志（ORA-XXXXX）

##########################################################
##                     ckalertlog.sh                    ##
##                   created by Lance                   ##
##                      2012-05-21                      ##
##########################################################

 

#!/bin/bash

EDITOR=vi;export EDITOR
ORACLE_BASE=/u01/app/oracle;export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/product/10.2.0/db_1;export ORACLE_HOME
ORACLE_SID=vrh1;export ORACLE_SID
ORATAB=/etc/oratab;export ORATAB
PATH=$PATH:$ORACLE_HOME:$ORACLE_HOME/bin:/sbin:/usr/local/bin:/opt/bin:bin;export PATH
DBALIST="953656107@qq.com"; export DBALIST

cd $ORACLE_BASE/admin/vrh1/bdump
if [ -f alert_vrh1.log ]
then
mv alert_vrh1.log alert_work.log
touch alert_vrh1.log
cat alert_work.log >> alert_vrh1.hist
grep ORA- alert_vrh1.hist > alert.err
fi
if [ `cat alert.err | wc -l` -gt 0 ]
then
mailx -s "VRH1 ORACLE ALERT ERRORS" $DBALIST < alert.err
fi

6. 检查表空间的使用

##########################################################
##                      cktbsp.sh                       ##
##                   created by Lance                   ##
##                      2012-05-21                      ##
##########################################################

#!/bin/bash

EDITOR=vi;export EDITOR
ORACLE_BASE=/u01/app/oracle;export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/product/10.2.0/db_1;export ORACLE_HOME
ORACLE_SID=vrh1;export ORACLE_SID
ORATAB=/etc/oratab;export ORATAB
PATH=$PATH:$ORACLE_HOME:$ORACLE_HOME/bin:/sbin:/usr/local/bin:/opt/bin:bin;export PATH
DBALIST="953656107@qq.com"; export DBALIST

sqlplus -s '/ as sysdba' <<EOF
set feed off
set linesize 400
set pagesize 400
spool tablespace.alert
select a.tablespace_name,
round(a.total) "total(MB)",
round(a.total) - round(b.free) "used(MB)",
round(b.free) "free(MB)",
round(b.free/total*100)||'%' free_rate
from
(select tablespace_name,sum(bytes)/1024/1024 total
from dba_data_files
group by tablespace_name)a,
(select tablespace_name,sum(bytes)/1024/1024 free
from dba_free_space
group by tablespae_name)b
where a.tablespace_name=b.tablespace_name;
spool off
exit
EOF
if [`cat tablespace.alert | wc -l` -gt 0];
then
cat tablespace.alert > tablespace.tmp
mailx -s "TABLESPACE ALERT FOR VRH1" $DBALIST < tablespace.tmp
fi

Email的例子如下:

TABLESPACE_NAME                 total(MB)   used(MB)   free(MB) FREE_RATE
------------------------------ ---------- ---------- ---------- -----------------------------------------
UNDOTBS1                              585         25        560 96%
SYSAUX                                490        261        229 47%
USERS                                  25          3         22 88%
SYSTEM                                980        501        479 49%
EXAMPLE                               160         68         92 57%
VRH1                                  500         24        476 95%