♣
题目 部分【DB笔试面试821】在Oracle中，如何定时生成AWR报告?
♣答案部分
有的系统需要定时生成html格式的AWR报告，这个需求可以使用SHELL脚本来完成。
下面给出相应的SHELL脚本：
1[oracle@rhel6lhr awr]$ ll
2total 68
3-rwxr-xr-x. 1 oracle oinstall 3112 Oct 23 2014 autogetawr.sh
4drwxr-xr-x. 2 oracle oinstall 49152 May 23 11:05 log
5-rw-r--r--. 1 oracle oinstall 276 Oct 22 2014 oracle_env.conf
6-rw-r--r--. 1 oracle oinstall 235 Oct 22 2014 readme.txt
7-rwxr-xr-x. 1 oracle oinstall 303 Oct 23 2014 rungetawr.sh
8
9[oracle@rhel6lhr ~]$ crontab -l
105 * * * * /home/oracle/lhr/awr/rungetawr.sh
11[oracle@rhel6lhr ~]$ more /home/oracle/lhr/awr/rungetawr.sh
12#!/bin/bash
13# define end snapshot time
14NH=`date +%Y%m%d%H`
15# define begin snapshot time
16LH=`date -d "last-hour" +%Y%m%d%H`
17# define the report format HTML or TEXT
18FH=HTML
19#get the awr
20#echo autogetawr.sh -f $LH -t $NH p $FH
21. $HOME/lhr/awr/oracle_env.conf
22$SCRIPT_DIR/autogetawr.sh -f $LH -t $NH -p $FH
23[oracle@rhel6lhr ~]$ more $HOME/lhr/awr/oracle_env.conf
24export ORACLE_BASE=/u01/app/oracle
25export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/dbhome_1
26export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/usr/lib
27export PATH=$ORACLE_HOME/bin:$PATH
28export ORACLE_USER=lhr/lhr@orclasm
29
30export SCRIPT_DIR=$HOME/lhr/awr
31export LOG_DIR=$SCRIPT_DIR/log
32[oracle@rhel6lhr ~]$ more /home/oracle/lhr/awr/autogetawr.sh
33# !/bin/bash
34# get the env about oracle
35# must define SID and ORACLE_HOME in profile
36#. ~oracle/.bash_profile
37. $HOME/lhr/awr/oracle_env.conf
38
39# ********************************
40# * dba_oracle_awr.sh
41# ********************************
42# Usage: dba_oracle_awr.sh
43# -f [from time]
44# -t [to time]
45# -p [report type, html or text]
46#
47# time format: 'yyyymmddhh24'.
48# E.g 2011030417 means 05pm, Mar 04, 2011
49#**********************
50# get parameters
51#**********************
52while getopts ":f:t:p" opt
53 do
54 case $opt in
55 f) from=$OPTARG
56 ;;
57 t) to=$OPTARG
58 ;;
59 p) type=$OPTARG
60 type=$(echo $type|tr "[:upper:]" "[:lower:]")
61 ;;
62 '?') echo "$0: invalid option ?$OPTARG">&2
63 exit 1
64 ;;
65 esac
66done
67
68if [ "$from" = "" ]
69then
70 echo "from time (?f} needed"
71 echo "program exiting..."
72 exit 1
73fi
74if [ "$to" = "" ]
75then
76 echo "to time (?t) needed"
77 echo "program exiting..."
78 exit 1
79fi
80if [ "$type" = "" ]
81then
82 type="html"
83fi
84
85
86# ********************
87# trim function
88# ********************
89function trim()
90{
91 local result
92 result=`echo $1|sed 's/^ *//g' | sed 's/ *$//g'`
93 echo $result
94}
95
96#*******************************
97# get begin and end snapshot ID
98# *******************************
99define_dur()
100{
101begin_id=`sqlplus -s $ORACLE_USER <<EOF
102 set pages 0
103 set head off
104 set feed off
105 select max(SNAP_ID) from DBA_HIST_SNAPSHOT where
106 END_INTERVAL_TIME<=to_date($from,'yyyymmddhh24');
107EOF`
108ret_code=$?
109if [ "$ret_code" != "0" ]
110then
111 echo "sqlplus failed with code $ret_code"
112 echo "program exiting..."
113 exit 10
114fi
115end_id=`sqlplus -s $ORACLE_USER <<EOF
116 set pages 0
117 set head off
118 set feed off
119 select min(SNAP_ID) from DBA_HIST_SNAPSHOT where
120 END_INTERVAL_TIME>=to_date($to,'yyyymmddhh24');
121 spool off
122EOF`
123ret_code=$?
124if [ "$ret_code" != "0" ]
125then
126 echo "sqlplus failed with code $ret_code"
127 echo "program exiting..."
128 exit 10
129fi
130begin_id=$(trim ${begin_id})
131end_id=$(trim ${end_id})
132#echo "begin_id: $begin_id end_id: $end_id"
133}
134
135#*******************************
136# generate AWR report
137# *******************************
138generate_awr()
139{
140 tmp1_id=${begin_id}
141 while [[ ${tmp1_id} -lt ${end_id} ]]
142 do
143 let tmp2_id=${tmp1_id}+1
144 if [ $type = "text" ]
145 then
146 report_name=$LOG_DIR/"awrrpt_$from}_${to}.txt"
147 else
148 report_name=$LOG_DIR/"awrrpt_${from}_${to}.html"
149 fi
150#echo $report_name
151sqlplus -s $ORACLE_USER >/dev/null<<EOF
152 set term off
153 define report_type=$type
154 define num_days=1
155 define begin_snap=${tmp1_id}
156 define end_snap=${tmp2_id}
157 define report_name=${report_name}
158 @?/rdbms/admin/awrrpt.sql
159 exit;
160EOF
161 tmp1_id=${tmp2_id}
162 done
163}
164
165#*******************************
166# main routing
167# *******************************
168define_dur
169generate_awr
170[oracle@rhel6lhr ~]$
