【AWR】自动生成AWR报告脚本以及用法

1，使用ftp工具上传文件autoawrrpt.sh到服务器/home/oracle目录

 

 

2，以Oracle用户登录，修改shell脚本执行权限

[root@PrimaryDB oracle]# su - oracle

[oracle@PrimaryDB ~]$ chmod  755 autoawrrpt.sh

 

 

3，shell脚本使用方法

./autoawrrpt.sh -s orcl -f 2013092917 -t 2013092918 -p HTML -h /home/ora11g/product/11.1.0

 

 

4，autoawrrpt.sh是附件shell脚本

shell脚本参数说明：

-f 2013092917   开始时间 格式年月日小时

 

-t 2013092918   结束时间 格式年月日小时

 

-p 格式 html或者是text

 

-h 环境变量ORACLE_HOME的值

 

-s 环境变量ORACLE_SID的值

 

 

5，使用浏览器打开当前目录下awr报告文件，定位到SQL ordered by CPU Time部分分析

 

 

 

附autoawrrpt.shshell脚本内容：

#!/bin/bash
# ********************************
# * autoawrrpt.sh
# ********************************
# Usage: autoawrrpt.sh -s [instance_name]
#          -f [from time]
#          -t [to time]
#          -p [report type, html or text]
#          -h [oracle home]
#          -n [tns admin]
#
#         time format: 'yyyymmddhh24'.
#         E.g 2011030417 means 05pm, 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<  conn /as sysdba
  set pages 0
  set head off
  set feed off
  select max(SNAP_ID) from DBA_HIST_SNAPSHOT where
    BEGIN_INTERVAL_TIME<=to_date($from,'yyyymmddhh24');
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<  conn /as sysdba
  set pages 0
  set head off
  set feed off
  select min(SNAP_ID) from DBA_HIST_SNAPSHOT where
    END_INTERVAL_TIME>=to_date($to,'yyyymmddhh24');
  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 "/as sysdba">/dev/null<      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

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/14359/viewspace-774020/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/14359/viewspace-774020/