#!/bin/bash
#Purpose: This script. is used for DB daily monitoring check including following items:
#
1 Basic OS Configuration (主机基本配置信息)
#
2 Check if DB background process run normally (数据库后台进程是否正常)
# 3 Check if any ORA- errors in alert log file (警告日志是否有ORA-错误)
# 4 Check CRS status (RAC CRS 资源状态)
# 5 Check DB backup status (数据库备份情况)
#
6 Check OS performance of CPU/IO/MEM
(主机 CPU/IO/内存 使用情况)
#
7 Check OS filesystem usage (主机文件系统使用率)
#
8 Check ASM storage usage (数据库ASM使用情况)
# 9 Check 1)DB approximate size and tablespace usage;
# 2)growth trend history of last 30 day ㄊ?菘獯蟾糯笮?表空间使用率和增长率)
#
10 Check redo log info and activities (日志文件信息和切换活动)
#
11 Check if any INVALID objects (数据库的无效对象)
#
12 Check TOP 5 EVENTS for last 2 days (最近两天数据库TOP 5事件)
#
13 Check blocking session and blocked objects (会话阻塞和锁情况)
# 14 Check max sessions and active sessions (最大会话数和活动换峄笆?)
# 15 Collect buffer ratio include 1)library cache;2)dictionary cache;3)data buffer cache;4)sort disk;5)Rollback ;6)Redo allocate retries
# 16 Housekeep and archive the log files (日志文件的大小检查清理和监控日志归档)
#Author: Shawn (xiaobin)
#Created Date: 15-Jul-2012
#Usage: sh/bash DailyCheck.sh
#############################################
#Create script. directory and initial variable
#############################################
if [ -f $HOME/.bash_profile ];
then
. $HOME/.bash_profile
fi
export NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS'
#if [ -f /etc/oratab ];
#then
# DBNAME=`grep -v ^# /etc/oratab |grep -v '^$'|grep -v '+'|grep -v '*'|awk -F : '{print $1}'|head -n 1`
#else
#exit 99
#fi
ORACLE_SID=`echo $ORACLE_SID`
BASE=$ORACLE_BASE
SCRIPTDIR=$BASE/scripts
SRCDIR=$SCRIPTDIR/src
SQLDIR=$SCRIPTDIR/sql
LOGDIR=$SCRIPTDIR/logs
DATE=`date +"%Y%m%d"`
DAY=`date +"%Y%m"`
TIME=`date +"%Y%m%d%H"`
MON_LOGFILE=${LOGDIR}/${TIME}_${ORACLE_SID}_DailyCheck.log
################################################
# 1 Present DB OS info
################################################
echo "">${MON_LOGFILE}
echo "##############################################################################################################">>${MON_LOGFILE}
echo " 1 Basic OS Configuration">>${MON_LOGFILE}
echo "##############################################################################################################">>${MON_LOGFILE}
echo "
Starting checking time:" `date` >>${MON_LOGFILE}
echo "
OS verison:" `uname -srp`>>${MON_LOGFILE}
echo "
CPU Info:" `cat /proc/cpuinfo |grep "model name"|sort -u|awk -F : '{print $2}'` \
" "`cat /proc/cpuinfo |grep "physical id"|sort -u|wc -l`"CPU*"`cat /proc/cpuinfo |grep "core id"|sort -u|wc -l`"Core" >>${MON_LOGFILE}
echo "
Memmory Info:" `free -g|head -2|tail -1|awk '{print $2}'`"G" >>${MON_LOGFILE}
echo "">>${MON_LOGFILE}
##################################################
# 2 Check if DB background process run normally
##################################################
echo "">>${MON_LOGFILE}
echo "##############################################">>${MON_LOGFILE}
echo "@2 Check if DB backgroup process run normally:">>${MON_LOGFILE}
echo "##############################################">>${MON_LOGFILE}
echo "">>${MON_LOGFILE}
ps -ef|grep -E "pmon|smon|dbw|lgwr|ckpt|arch"
if [ $? -eq 0 ]; then
echo "Oracle background processes are running!" >>${MON_LOGFILE}
else
echo "Oracle backgroud processes are NOT running,please health check!" >>${MON_LOGFILE}
fi
##################################################
# 3 Check if any ORA- errors in DB error log file
##################################################
#Confirm alert log file
echo "">>${MON_LOGFILE}
#Get DB name and trim blank
ALERTLOG=alert_${ORACLE_SID}.log
DBNAMEFILE=$LOGDIR/dbname.log
sqlplus -s "/ as sysdba" <
spool "$DBNAMEFILE"
set linesize 180
set feedback off
set heading off
set timing off
select value from v\$parameter where name='db_name';
spool off
exit
EOF
DBNAME=`grep -v '^$' $DBNAMEFILE|grep -o "[^ ]\+\(\+[^ ]\+\)*"` #trim content blank
ERRORLOGFILE=${BASE}/admin/$DBNAME/bdump/${ALERTLOG}
TODAY=`date +"%b %d"`
#TODAY="$1"
YEAR=`date +"%Y"`
echo "">>${MON_LOGFILE}
echo "########################">>${MON_LOGFILE}
echo "@3 ORA errors checking:">>${MON_LOGFILE}
echo "########################">>${MON_LOGFILE}
echo "">>${MON_LOGFILE}
ERRMSG=`cat ${ERRORLOGFILE}|grep -A 3 -B 3 "${YEAR}"|grep -A 2 -B 2 "ORA-"|tail -8|grep "${TODAY}"|tail -1`
ERRMSGTEXT=`cat ${ERRORLOGFILE}|grep -A 3 -B 3 "${YEAR}"|grep -A 2 -B 2 "ORA-"|tail -8`
if [ -z $ERRMSG ]; then
echo "There is NO ORA- errors in the error log file $ERRORLOGFILE on ${TODAY}!" >>${MON_LOGFILE}
else
echo "----ORA Content:----" >>${MON_LOGFILE}
echo "">>${MON_LOGFILE}
echo "CAUTION: Found ORA error in $ERRORLOGFILE as below,please check the cause:" >>${MON_LOGFILE}
echo "">>${MON_LOGFILE}
echo "${ERRMSGTEXT}" >>${MON_LOGFILE}
echo "----ORA Content:----" >>${MON_LOGFILE}
echo "">>${MON_LOGFILE}
fi
##################################################
# 4 Check CRS status
##################################################
echo "">>${MON_LOGFILE}
echo "########################">>${MON_LOGFILE}
echo "@4 Check CRS status:">>${MON_LOGFILE}
echo "########################">>${MON_LOGFILE}
echo "">>${MON_LOGFILE}
if [ -d $ORACLE_CRS_HOME ]; then
echo "CRS status as below:">>${MON_LOGFILE}
crs_stat -t|grep -v OFFLINE >>${MON_LOGFILE}
else
echo "Please check if DB is RAC env, if YES, please check CRS status manually again!" >>${MON_LOGFILE}
fi
##################################################
# 5 Check DB backup status
##################################################
echo "">>${MON_LOGFILE}
echo "#########################">>${MON_LOGFILE}
echo "@5 Check DB backup status:">>${MON_LOGFILE}
echo "#########################">>${MON_LOGFILE}
echo "">>${MON_LOGFILE}
rman target / log=${LOGDIR}/${DATE}_checkbackup.log <
list backup summary;
eof
tail -15 ${LOGDIR}/${DATE}_checkbackup.log >>${MON_LOGFILE}
##################################################
# 6 Check OS performance of CPU/IO/MEM
##################################################
echo "">>${MON_LOGFILE}
echo "########################################">>${MON_LOGFILE}
echo "@6 Check OS performance of CPU/IO/MEM :">>${MON_LOGFILE}
echo "########################################">>${MON_LOGFILE}
echo "">>${MON_LOGFILE}
echo "Current CPU/IO/Memeory usage status:" >>${MON_LOGFILE}
echo "">>${MON_LOGFILE}
mpstat 1 6 >>${MON_LOGFILE}
echo "">>${MON_LOGFILE}
free -m >>${MON_LOGFILE}
echo "">>${MON_LOGFILE}
vmstat 1 6 >>${MON_LOGFILE}
echo "Active Memory:"
cat /proc/meminfo |grep -E "MemTotal|MemFree|Active" >>${MON_LOGFILE}
##################################################
# 7 Check OS filesystem usage
##################################################
echo "">>${MON_LOGFILE}
echo "#############################">>${MON_LOGFILE}
echo "@7 Check OS filesystem usage:">>${MON_LOGFILE}
echo "############################">>${MON_LOGFILE}
echo "">>${MON_LOGFILE}
FILESYSTEM=`df -h|awk '{print $6}'|grep -E -v ":/|^$|none|Mounted|Filesystem"`
for i in `df -h|awk '{print $5}'|grep -E -v "Use|/"|cut -d% -f1`
do
if [ $i -gt 85 ];then
echo " WARNING:The usage of $FILESYSTEM has been $i%" >>${MON_LOGFILE}
fi
done
echo "">>${MON_LOGFILE}
##################################################
# 8 Check ASM diskgroup usage and rebalence status
##################################################
echo "">>${MON_LOGFILE}
echo "#############################">>${MON_LOGFILE}
echo "@8 Check ASM diskgroup usage:">>${MON_LOGFILE}
echo "#############################">>${MON_LOGFILE}
echo "">>${MON_LOGFILE}
ASM=`ps -ef|grep pmon|grep "_"|awk '{print $8}'|cut -d_ -f3|grep "+"`
if [ ${ASM} = "" ];then
echo " Oracle Storage is not using ASM!">>${MON_LOGFILE}
else
sqlplus -s "/ as sysdba" <
spool "${MON_LOGFILE}" app
set linesize 180
set feedback off
prompt ASM Usage:
select group_number,name,total_mb,free_mb FREE_G,(total_mb-free_mb)/total_mb*100 "Used(%)" from v\$ASM_DISKGROUP where total_mb!=0;
spool off
exit
EOF
fi
#Check if asm disk any rebalance issue
ASMSTATFILE=${LOGDIR}/${DATE}_asm_stat_result.log
echo "">>${MON_LOGFILE}
export ORACLE_SID=+ASM1
sqlplus -s "/ as sysdba" <
spool "$ASMSTATFILE"
Set linesize 180
set heading off
set feedback off
select NAME,STATE,TOTAL_MB,FREE_MB,MOUNT_STATUS from v\$asm_disk_stat where FREE_MB=0;
spool off
exit
EOF
COUNT=`grep -v "rows selected" $ASMSTATFILE|wc -l`
if [ $COUNT -gt 1 ];then
echo "There might be rebalence issue after adding disk to ASM Diskgroup, please further check!" >>${MON_LOGFILE}
else
echo "No ASM rebelance issue in ASM DISKGROUP!" >>${MON_LOGFILE}
fi
# Reset Oracle ENV to use RDBMS instances
. $HOME/.bash_profile
###########################################################################################
# 9 Check 1)DB approximate size and tablespace usage;2)growth trend history of last 30 days
###########################################################################################
echo "">>${MON_LOGFILE}
echo "#######################################################################################">>${MON_LOGFILE}
echo "@9 Check DB approximate Size and tablespace usage、growth trend history of last 30 days:">>${MON_LOGFILE}
echo "#######################################################################################">>${MON_LOGFILE}
echo "">>${MON_LOGFILE}
#1)DB approximate size and tablespace usage;
echo "1)DB approximate size and tablespace usage:" >>${MON_LOGFILE}
echo "">>${MON_LOGFILE}
sqlplus -s "/ as sysdba" <
spool "${MON_LOGFILE}" app
@${SQLDIR}/dbsize.sql
@${SQLDIR}/tbsusage.sql
spool off
exit
EOF
## 2) Log tbs growth trend history
echo "2) Tablespace growth trend:" >>${MON_LOGFILE}
echo "">>${MON_LOGFILE}
TBSGROWTHTRENDLOG=${LOGDIR}/${TIME}_${ORACLE_SID}_tbsgrowthtrend.log
sqlplus -s "/ as sysdba" <
spool "$TBSGROWTHTRENDLOG"
@${SQLDIR}/tbsgrowthhistory.sql
spool off
exit
EOF
echo "Please check log file $TBSGROWTHTRENDLOG for tablespace growth trend detail!" >>${MON_LOGFILE}
##################################################################################################
# 10 Check redo log info and activities
# 10.1 If checkpoint not complete info (Cause: IO issue/redo log size small/less redo log group)
# 10.2 Redo activities include: average log size,
# Frequency of Log Switches,
# Summary of log volume processed by day for last 1 days,
# Summary of log volume processed per hour for last 1 days
##################################################################################################
echo "">>${MON_LOGFILE}
echo "########################################">>${MON_LOGFILE}
echo "@10 Check redo log info and activities :">>${MON_LOGFILE}
echo "########################################">>${MON_LOGFILE}
echo "">>${MON_LOGFILE}
echo " @@10.1 Check if 'checkpoint not complete event' exists!">>${MON_LOGFILE}
LOGERRMSG=`cat ${ERRORLOGFILE} |grep -A 3 -B 3 "${TODAY}"|grep -A 2 -B 2 "${YEARWithTZ}"|grep "Checkpoint not complete"|tail -1`
LOGERRMSGTEXT=`cat ${ERRORLOGFILE} |grep -A 3 -B 3 "${TODAY}"|grep -A 2 -B 2 "${YEARWithTZ}"|grep -A 1 -B 1 "Checkpoint not complete" |tail -2`
if [ -z $LOGERRMSG ]; then
echo " There is NO 'Checkpoint not complete' event in the error log file ${ERRORLOGFILE} on ${TODAY}!" >>${MON_LOGFILE}
else
echo "">>${MON_LOGFILE}
echo " CAUTION: Found 'Checkpoint not complete' event in ${ERRORLOGFILE} as below,please fix it:" >>${MON_LOGFILE}
echo "">>${MON_LOGFILE}
echo "${LOGERRMSGTEXT}" >>${MON_LOGFILE}
echo "">>${MON_LOGFILE}
fi
echo "">>${MON_LOGFILE}
echo " @@10.2 Check redo activities include: Average log size!">>${MON_LOGFILE}
echo " Frequency of Log Switches for last 3 days.">>${MON_LOGFILE}
echo " Summary of log volume processed by day for last 1 days.">>${MON_LOGFILE}
echo "">>${MON_LOGFILE}
sqlplus -s "/as sysdba" <
spool "${MON_LOGFILE}" app
set linesize 180
@${SQLDIR}/RedoCollect.sql
spool off
exit
EOF
#################################################
# 11 Check if any INVALID objects
#################################################
echo "">>${MON_LOGFILE}
echo "################################">>${MON_LOGFILE}
echo "@11 Check if any INVALID objects:">>${MON_LOGFILE}
echo "################################">>${MON_LOGFILE}
echo "">>${MON_LOGFILE}
OBJECTSTATFILE=${LOGDIR}/${DATE}_object_stat.log
sqlplus -s "/as sysdba" <
spool "$OBJECTSTATFILE"
@${SQLDIR}/checkinvalidobject.sql
spool off
exit
EOF
OBJECTCOUNT=`grep -v "^$" $OBJECTSTATFILE|grep -o "[^ ]\+\(\+[^ ]\+\)*"`
if [ $OBJECTCOUNT -gt 0 ]; then
echo "There are ${OBJECTCOUNT} INVALID objects currently in DB, please further check!" >>${MON_LOGFILE}
else
echo "There are NO INVALID objects in DB." >>${MON_LOGFILE}
fi
#################################################
# 12 Check TOP 5 EVENTS for last 2 days
#################################################
echo "">>${MON_LOGFILE}
echo "#######################################">>${MON_LOGFILE}
echo "@12 Check TOP 5 EVENTS for last 2 days:">>${MON_LOGFILE}
echo "###############################i#######">>${MON_LOGFILE}
echo "">>${MON_LOGFILE}
sqlplus -s "/as sysdba" <
spool "${MON_LOGFILE}" app
@${SQLDIR}/top5event.sql
spool off
exit
EOF
#################################################
# 13 Check blocking session and blocked objects
#################################################
echo "">>${MON_LOGFILE}
echo "###############################################">>${MON_LOGFILE}
echo "@13 Check blocking session and blocked objects:">>${MON_LOGFILE}
echo "###############################################">>${MON_LOGFILE}
echo "">>${MON_LOGFILE}
sqlplus -s "/as sysdba" <
<%}%>
spool "${MON_LOGFILE}" app
@${SQLDIR}/block.sql
spool off
exit
EOF
#################################################
# 14 Check max sessions and active sessions
#################################################
echo "">>${MON_LOGFILE}
echo "###############################################">>${MON_LOGFILE}
echo "@14 Check max sessions and active sessions:">>${MON_LOGFILE}
echo "###############################################">>${MON_LOGFILE}
echo "">>${MON_LOGFILE}
sqlplus -s "/as sysdba" <
spool "${MON_LOGFILE}" app
@${SQLDIR}/sessions.sql
spool off
exit
EOF
#################################################
# 15 Get buffer ratio
#################################################
echo "">>${MON_LOGFILE}
echo "#####################">>${MON_LOGFILE}
echo "@15 Get buffer ratio:">>${MON_LOGFILE}
echo "#####################">>${MON_LOGFILE}
echo "">>${MON_LOGFILE}
sqlplus -s "/as sysdba" <
spool "${MON_LOGFILE}" app
@${SQLDIR}/bufferratio.sql
spool off
exit
EOF
#################################################
# 16 Housekeep log file size and retention
#################################################
echo "">>${MON_LOGFILE}
echo "########################################################">>${MON_LOGFILE}
echo "@16 Housekeep log file size and archive monitoring logs:">>${MON_LOGFILE}
echo "########################################################">>${MON_LOGFILE}
echo "">>${MON_LOGFILE}
# Housekeep: if log file size over 100M, then empty the log file
LISTENERLOG=`lsnrctl status|grep "Listener Log File"|awk '{print $4}'`
LOG_SIZE=`du $LISTENERLOG|awk '{print $1}'`
if [ 100000 -lt $LOG_SIZE ]; then
echo "$LISTENERLOG has been over 100M, it will be backup and empty.....">>${MON_LOGFILE}
rm -fr $LISTENERLOG.${DATE}.bak
cp $LISTENERLOG $LISTENERLOG.${DATE}.bak
echo ""> $LISTENERLOG
echo "$LISTENERLOG has been backuped and empty.">>${MON_LOGFILE}
echo "">>${MON_LOGFILE}
fi
#Archive monitoring logs
LOGDATEDIR=${LOGDIR}/${DAY}
echo "Archive daily monitoring logs to $LOGDATEDIR.....">>${MON_LOGFILE}
if [ ! -d $LOGDATEDIR ]; then
mkdir $LOGDATEDIR
fi
mv ${MON_LOGFILE} $LOGDATEDIR
#mv $TBSGROWTHTRENDLOG $LOGDATEDIR
echo "Housekeeping and archive completed!">>${MON_LOGFILE}
##################################
# Daily Monitor Check completiton
##################################
echo "">>${MON_LOGFILE}
echo "##############################################################################################################################">>${MON_LOGFILE}
echo "!Daily monitoring check has been completed. Please check log file ${MON_LOGFILE} for detail info!!">>${MON_LOGFILE}
echo "##############################################################################################################################">>${MON_LOGFILE}
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/27064837/viewspace-745228/,如需转载,请注明出处,否则将追究法律责任。
请登录后发表评论
登录
全部评论
<%=items[i].createtime%>
<%=items[i].content%>
<%if(items[i].items.items.length) { %>
<%for(var j=0;j
<%}%> <%if(items[i].items.total > 5) { %>
<%}%>
<%=items[i].items.items[j].createtime%>
<%=items[i].items.items[j].username%> 回复 <%=items[i].items.items[j].tousername%>: <%=items[i].items.items[j].content%>
还有<%=items[i].items.total-5%>条评论
) data-count=1 data-flag=true>点击查看
<%}%>
最新文章
- Copied-RAC Get Full Name of crs_stat resource
- Monitor-Get ORA errors by given days
- Copied-Diskname changes on Linux with ISCSI
- TS - Oracle Recyclebin Cause tbs query slow issue
- Oracle 10G Pre-install configure on Linux x86_64
- Linux Get Lun & HBA & Multipath info
- 420525.1:Automatic Tuning of Undo_retention Causes Space Problems
- HugePages on Oracle Linux 64-bit [ID 361468.1]
- Copied--About WWN
- Copied--About Lun
转载于:http://blog.itpub.net/27064837/viewspace-745228/