题目部分
【DB笔试面试821】在Oracle中,如何定时生成AWR报告?
♣
答案部分
有的系统需要定时生成html格式的AWR报告,这个需求可以使用SHELL脚本来完成。
下面给出相应的SHELL脚本:
[oracle@rhel6lhr awr]$ ll
total 68
-rwxr-xr-x. 1 oracle oinstall 3112 Oct 23 2014 autogetawr.sh
drwxr-xr-x. 2 oracle oinstall 49152 May 23 11:05 log
-rw-r--r--. 1 oracle oinstall 276 Oct 22 2014 oracle_env.conf
-rw-r--r--. 1 oracle oinstall 235 Oct 22 2014 readme.txt
-rwxr-xr-x. 1 oracle oinstall 303 Oct 23 2014 rungetawr.sh
[oracle@rhel6lhr ~]$ crontab -l
5 * * * * /home/oracle/lhr/awr/rungetawr.sh
[oracle@rhel6lhr ~]$ more /home/oracle/lhr/awr/rungetawr.sh
#!/bin/bash
# define end snapshot time
NH=`date +%Y%m%d%H`
# define begin snapshot time
LH=`date -d "last-hour" +%Y%m%d%H`
# define the report format HTML or TEXT
FH=HTML
#get the awr
#echo autogetawr.sh -f $LH -t $NH p $FH
. $HOME/lhr/awr/oracle_env.conf
$SCRIPT_DIR/autogetawr.sh -f $LH -t $NH -p $FH
[oracle@rhel6lhr ~]$ more $HOME/lhr/awr/oracle_env.conf
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/dbhome_1
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/usr/lib
export PATH=$ORACLE_HOME/bin:$PATH
export ORACLE_USER=lhr/lhr@orclasm
export SCRIPT_DIR=$HOME/lhr/awr
export LOG_DIR=$SCRIPT_DIR/log
[oracle@rhel6lhr ~]$ more /home/oracle/lhr/awr/autogetawr.sh
# !/bin/bash
# get the env about oracle
# must define SID and ORACLE_HOME in profile
#. ~oracle/.bash_profile
. $HOME/lhr/awr/oracle_env.conf
# ********************************
# * dba_oracle_awr.sh
# ********************************
# Usage: dba_oracle_awr.sh
# -f [from time]
# -t [to time]
# -p [report type, html or text]
#
# time format: 'yyyymmddhh24'.
# E.g 2011030417 means 05pm, Mar 04, 2011
#**********************
# get parameters
#**********************
while getopts ":f:t:p" opt
do
case $opt in
f) from=$OPTARG
;;
t) to=$OPTARG
;;
p) type=$OPTARG
type=$(echo $type|tr "[:upper:]" "[:lower:]")
;;
'?') echo "$0: invalid option ?$OPTARG">&2
exit 1
;;
esac
done
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 [ "$type" = "" ]
then
type="html"
fi
# ********************
# trim function
# ********************
function trim()
{
local result
result=`echo $1|sed 's/^ *//g' | sed 's/ *$//g'`
echo $result
}
#*******************************
# get begin and end snapshot ID
# *******************************
define_dur()
{
begin_id=`sqlplus -s $ORACLE_USER <<EOF
set pages 0
set head off
set feed off
select max(SNAP_ID) from DBA_HIST_SNAPSHOT where
END_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 $ORACLE_USER <<EOF
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()
{
tmp1_id=${begin_id}
while [[ ${tmp1_id} -lt ${end_id} ]]
do
let tmp2_id=${tmp1_id}+1
if [ $type = "text" ]
then
report_name=$LOG_DIR/"awrrpt_$from}_${to}.txt"
else
report_name=$LOG_DIR/"awrrpt_${from}_${to}.html"
fi
#echo $report_name
sqlplus -s $ORACLE_USER >/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}
@?/rdbms/admin/awrrpt.sql
exit;
EOF
tmp1_id=${tmp2_id}
done
}
#*******************************
# main routing
# *******************************
define_dur
generate_awr
[oracle@rhel6lhr ~]$
本文选自《Oracle程序员面试笔试宝典》,作者:小麦苗