#!/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."
# 得到开始和结束的日期的指定格式
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."
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
get_begin_time $var1
get_end_time $var2
elif [ $cnt -gt ]; then
echo "you input too much arguments, at most two arguments allowed."
# 查找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;
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."
dir=`cat /tmp/tmpdir.txt`
# 得到日志开始和结束的行数
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"
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"
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!"
exit
