oracle的脚本日志,学习笔记:Oracle alert日志文件巡检脚本

天萃荷净

分享一篇Oracle alert日志文件巡检脚本

每天都检查oracle日志，所以写了一个比较完善的shell，让其自动处理，在运行程序之前，需要在该脚本目录下新建tmp目录

#!/usr/bin/ksh

export LANG=en

#临时目录

tmp_dest=$HOME/check/tmp

#用户名

username=username

#密码

password=password

cd $tmp_dest

sqlplus -s $username/$password

set echo on

spool sqlplus.txt

col name format a20

col value format a55

select name,value from v\$parameter where name='background_dump_dest';

spool off

exit

XFF

alert_path_num=$(grep -n "background_dump_dest" $tmp_dest/sqlplus.txt |awk -F":" '{print $1}')

alert_path=$(cat $tmp_dest/sqlplus.txt |sed -n "${alert_path_num}p" | awk -F" " '{print $2}')

first_day=`cat $tmp_dest/first_day.tmp`

d_day=$(date +%e)

if [ $d_day -lt 10 ]

then

###########注意:以下两种方式选择其一###################

#部分系统出现alert日志例如:Tue Aug 7 07:44:59 2012

last_day=$(date +%a)' '$(date +%b)'.*'$d_day'.*'$(date +%Y)

#部分系统出现alert日志例如:Thu Jun 07 13:56:18 2012

n_day=`echo $d_day | awk 'gsub(/^ *| *$/,"")'`

last_day=$(date +%a)' '$(date +%b)'.*0'$n_day'.*'$(date +%Y)

else

last_day=$(date|cut -c 1-10).*$(date +%Y)

fi

echo $last_day > $tmp_dest/first_day.tmp

first_num=$(grep -n "$first_day" $alert_path/alert_$ORACLE_SID.log |head -1|awk -F":" '{print $1}')

if [ -z "$first_num" ]

then

first_num=1

fi

#修改$last_day为'.*',表示alert日志结尾

last_num=$(grep -n ".*" $alert_path/alert_$ORACLE_SID.log |tail -1|awk -F":" '{print $1}')

point=1

export=$point

echo "########################## checking alert_log start $first_day ########################## "

sed -n "${first_num},${last_num}p" $alert_path/alert_$ORACLE_SID.log > $tmp_dest/trunc_alert

cat $tmp_dest/trunc_alert|grep ORA-|while read line

do

line=$(echo "$line"|sed -e 's/\[/\\[/g;s/\]/\\]/g')

time=$(grep -n "$line" $tmp_dest/trunc_alert | awk -F':' '{print $1}'|wc -l)

if [ "$time" -ge 1 ]

then

num=$(grep -n "$line" $tmp_dest/trunc_alert|awk -F':' '{print $1}'|tail -1)

#echo $num

front_num=$((num-1))

back_num=$((num+9))

echo "++++++++++++++++++$point++++++++++++++++++++"

sed -n "${front_num},${back_num}p" $tmp_dest/trunc_alert

echo "++++++++++++++++++$point-End++++++++++++++++"

point=$((point+1))

flag=1

else

if [ -z "$time" ]

then

flag=0

fi

fi

done

#rm $tmp_dest/trunc_alert

#rm $tmp_dest/sqlplus.txt

if [ " $flag " -eq 0 ]

then

echo "No errors in $first_day !"

fi

echo "########################## checking alert_log end $last_day ########################## "

检查结果

########################## checking alert_log start Fri Jan 27.*2012 ##########################

++++++++++++++++++1++++++++++++++++++++

Current log# 3 seq# 918 mem# 0: /opt/oracle/oradata/orcl/mcrm/redo03.log

Tue Jan 31 22:00:22 2012

Errors in file /opt/oracle/admin/mcrm/bdump/mcrm_j001_23329.trc:

ORA-01114: IO error writing block to file 201 (block # 550944)

ORA-27072: File I/O error

Linux-x86_64 Error: 28: No space left on device

Additional information: 4

Additional information: 550944

Additional information: -1

ORA-01114: IO error writing block to file 201 (block # 550922)

ORA-27072: File I/O error

Additional information: 4

Additional information: 550922

++++++++++++++++++1-End++++++++++++++++

++++++++++++++++++2++++++++++++++++++++

Additional information: 550944

Additional information: -1

ORA-01114: IO error writing block to file 201 (block # 550922)

ORA-27072: File I/O error

Additional information: 4

Additional information: 550922

Additional information: 114688

Wed Feb 1 07:24:02 2012

Thread 1 advanced to log sequence 919 (LGWR switch)

Current log# 1 seq# 919 mem# 0: /opt/oracle/oradata/orcl/mcrm/redo01.log

Wed Feb 1 07:24:02 2012

Errors in file /opt/oracle/admin/mcrm/bdump/mcrm_arc1_3624.trc:

ORA-19504: failed to create file "/opt/oracle/oradata/archivelog/1_918_741262746.dbf"

++++++++++++++++++2-End++++++++++++++++

########################## checking alert_log end Sun Feb 12.*2012##########################

--------------------------------------ORACLE-DBA----------------------------------------

最权威、专业的Oracle案例资源汇总之学习笔记:Oracle alert日志文件巡检脚本