工作中DBA经常会查看alert log来检查数据库后台都记录了些什么日志，如果只想看某一天或者某段时间范围的日志，能够把这些日志从大的alert log中截取下来放到一个单独的文件中，对于查看和下载都是很有意义的事，接下来附上脚本
script.sh
代码语言：javascript复制#!/bin/bash
################################################################################
# this script is to get the specified date log of oracle database alert log. #
# author: yangbao #
# usage: ./script.sh -- --> get the log on -- #
# ./script.sh -- -- --> get the log between -- and -- #
################################################################################
# 判断日期格式是否有效
check_date() {
date -d "$1" &> /dev/null
flag=$?
if [ $flag -eq ]; then
echo "date is incorrect, please input the correct date formate like 2019-5-1."
exit
fi
}
# 得到开始和结束的日期的指定格式
get_begin_time() {
year1=`date -d "$1" %Y`
month1=`date -d "$1" %b`
day1=`date -d "$1" %d`
week1=`date -d "$1" %a`
}
get_end_time() {
year2=`date -d "$1" %Y`
month2=`date -d "$1" %b`
day2=`date -d "$1" %d`
week2=`date -d "$1" %a`
}
# 判断输入的参数个数是否正确
cnt=$#
if [ $cnt -eq ]; then
echo "you must input at least one date, formate like 2019-5-1."
exit
elif [ $cnt -eq ]; then
var1=$
check_date $var1
get_begin_time $var1
get_end_time $var1
elif [ $cnt -eq ]; then
var1=$
var2=$
check_date $var1
check_date $var2
t1=`date -d "$var1" %s`
t2=`date -d "$var2" %s`
if [ $t1 -gt $t2 ]; then
temp=$var1
var1=$var2
var2=$temp
fi
get_begin_time $var1
get_end_time $var2
elif [ $cnt -gt ]; then
echo "you input too much arguments, at most two arguments allowed."
exit
fi
# 查找alert日志所在的路径
sqlplus -s /nolog &> /dev/null << eof
set feedback off heading off verify off trimspool on timing off
set pagesize linesize
conn / as sysdba;
spool /tmp/tmpdir.txt
select value from v$parameter where name='background_dump_dest';
spool off
exit;
eof
errs=`grep 'ERROR' /tmp/tmpdir.txt | wc -l`
if [ $errs -gt ]; then
echo "query alert log direction run error, please check the /tmp/tmpdir.txt for details."
exit
else
dir=`cat /tmp/tmpdir.txt`
fi
# 得到日志开始和结束的行数
row1=`find $dir/alert_$ORACLE_SID.log | xargs grep -n "$week1 $month1 $day1" | grep "$year1" | head -n | cut -d ":" -f `
if [ "$row1" == "" ]; then
echo "$1 is not found in alert log"
exit
fi
row2=`find $dir/alert_$ORACLE_SID.log | xargs grep -n "$week2 $month2 $day2" | grep "$year2" | tail -n | cut -d ":" -f `
if [ "$row2" == "" ]; then
echo "$2 is not found in alert log"
exit
fi
row2=$(($row2 ))
# 截取日志
if [ $cnt == ]; then
sed -n "${row1},${row2}p" $dir/alert_$ORACLE_SID.log > ./alert_$ORACLE_SID.$var1.log
echo "alert_$ORACLE_SID.$var1.log has created!"
elif [ $cnt == ]; then
sed -n "${row1},${row2}p" $dir/alert_$ORACLE_SID.log > ./alert_$ORACLE_SID."$var1"_to_"$var2".log
echo "alert_$ORACLE_SID."$var1"_to_"$var2".log has created!"
fi
exit 使用说明：
1.使用oracle用户执行，脚本会自动寻找alert日志的位置
2.如果只查一天的日志，如2019-5-1这天的日志，则使用./script.sh 2019-5-1
3.如果查一段时间范围的日志，如2019-5-1到2019-5-10这10天的日志，则使用./script.sh 2019-5-1 2019-5-10
4.执行完成之后会在当前目录生成一个新文件
5.输入的日期必须在alert日志中存在才会有新文件生成，否则会报错
