SHELL版的自动批量生成AWR报告的脚本

最新推荐文章于 2021-04-06 18:59:34 发布

zhyuh_perl

最新推荐文章于 2021-04-06 18:59:34 发布

阅读量1.5k

点赞数

文章标签： shell oracle report date 脚本 html

本文链接：https://blog.csdn.net/zhyuh_perl/article/details/6250231

版权

本文介绍了一个使用Shell脚本批量生成Oracle AWR报告的方法。脚本通过接收参数实例名、开始时间、结束时间和报告类型，利用SQL*Plus连接数据库获取快照ID并生成HTML或TEXT类型的AWR报告。

摘要由CSDN通过智能技术生成

上文里面的功能，对比着用shell来实现，比较好玩

用法：

bash ./dba_oracle_awr.sh -s ORCL -f 20110312070000 -t 20110312090000 -p TEXT -h /usr/local/oracle/10.2.0-64

脚本：

#!/usr/bin/bash
# ********************************
# * dba_oracle_awr.sh
# ********************************
# Usage: dba_oracle_awr.sh -s [instance_name]
#          -f [from time]
#          -t [to time]
#          -p [report type, html or text]
#          -h [oracle home]
#          -n [tns admin]
#
#         time format: 'yyyymmddhh24miss'.
#         E.g 20110304170000 means 05:00:00pm, Mar 04, 2011
#
#

# **********************
# get parameters
# **********************
while getopts ":s:f:t:p:h:n" opt
do
    case $opt in
    s) instance=$OPTARG
       ;;
    f) from=$OPTARG
       ;;
    t) to=$OPTARG
       ;;
    p) type=$OPTARG
       type=$(echo $type|tr "[:upper:]" "[:lower:]")
       ;;
    h) oracle_home=$OPTARG
       ;;
    n) tns_admin=$OPTARG
       ;;
    '?') echo "$0: invalid option -$OPTARG">&2
       exit 1
       ;;
    esac
done

if [ "$instance" = "" ]
then
echo "instance name(-s) needed"
echo "program exiting..."
exit 1
fi

if [ "$from" = "" ]
then
echo "from time (-f} needed"
echo "program exiting..."
exit 1
fi

if [ "$to" = "" ]
then
echo "to time (-t) needed"
echo "program exiting..."
exit 1
fi

if [ "$oracle_home" ="" ]
then
echo "oracle home (-h) needed"
echo "program exiting..."
exit 1
fi
sqlplus="${oracle_home}/bin/sqlplus"
echo $sqlplus

if [ "$type" = "" ]
then
type="html"
fi

# ********************
# trim function
# ********************
function trim()
{
local result
result=`echo $1|sed 's/^ *//g' | sed 's/ *$//g'`
echo $result
}

# *******************************
# read interchange ID & passwd
# *******************************
read_act()
{
echo "interchange ID: "
read user
echo "password: "
stty -echo
read pswd
stty echo
}

# *******************************
# get begin and end snapshot ID
# *******************************
define_dur()
{
begin_id=`$sqlplus -s /nolog<<EOF
conn $user/$pswd@$instance
set pages 0
set head off
set feed off
select max(SNAP_ID) from DBA_HIST_SNAPSHOT where
BEGIN_INTERVAL_TIME<=to_date($from,'yyyymmddhh24miss');
EOF`

ret_code=$?
if [ "$ret_code" != "0" ]
then
echo "sqlplus failed with code $ret_code"
echo "program exiting..."
exit 10
fi

end_id=`$sqlplus -s /nolog<<EOF
conn $user/$pswd@$instance
set pages 0
set head off
set feed off
select min(SNAP_ID) from DBA_HIST_SNAPSHOT where
END_INTERVAL_TIME>=to_date($to,'yyyymmddhh24miss');
spool off
EOF`

ret_code=$?
if [ "$ret_code" != "0" ]
then
echo "sqlplus failed with code $ret_code"
echo "program exiting..."
exit 10
fi

begin_id=$(trim ${begin_id})
end_id=$(trim ${end_id})
# echo "begin_id: $begin_id end_id: $end_id"
}

# *******************************
# generate AWR report
# *******************************
generate_awr()
{
awrsql="${oracle_home}/rdbms/admin/awrrpt.sql"
if [ ! -e $awrsql ]
then
echo "awrrpt.sql does not exist, exiting..."
exit 20
fi

tmp1_id=${begin_id}
#echo "begin_id is: $begin_id"
#echo "tmp1_id is: $tmp1_id"
while [ ${tmp1_id} -lt ${end_id} ]
do
    let tmp2_id=${tmp1_id}+1
    if [ $type = "text" ]
    then
      report_name="awrrpt_${instance}_${tmp1_id}_${tmp2_id}.txt"
    else
      report_name="awrrpt_${instance}_${tmp1_id}_${tmp2_id}.html"
    fi
    #echo $report_name

$sqlplus -s $user/$pswd@$instance>/dev/null<<EOF
      set term off
      define report_type=$type
      define num_days=1
      define begin_snap=${tmp1_id}
      define end_snap=${tmp2_id}
      define report_name=${report_name}
      @${oracle_home}/rdbms/admin/awrrpt.sql
      exit;
EOF

tmp1_id=${tmp2_id}
done
}

# *******************************
# main routing
# *******************************
read_act
define_dur
generate_awr