AWR自动生成，定时推送

AWR自动生成，定时推送 1. 首先编辑AWR报告生成脚本：autoawrrpt.sh #!/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: 'yyyymmdd'. # E.g 20110304 means  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 /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,'yyyymmdd'); 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 /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,'yyyymmdd'); 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" #echo "end_id is: $end_id" while [ ${tmp1_id} -lt ${end_id} ] do let tmp2_id=${end_id} if [ $type = "text" ] then report_name=/home/oracle/report/"awrrpt_${instance}_${from}.txt" else report_name=/home/oracle/report/"awrrpt_${instance}_${from}.html"----report名称按需修改 fi #echo $report_name $sqlplus -s "/as sysdba">/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 2. 以下为：awr.sh内容（按需设置） #!/bin/sh source /home/oracle/.bash_profile b=`date -d last-day +%Y%m%d` a=$(date +"%Y%m%d") echo $b echo $a /home/oracle/report/autoawrrpt.sh -s orcl（实例名） -f $b -t $a -p HTML -h /home/app/oracle/product/11.2.0 echo "AWRRPT $b"（报告名） |mail -a /home/oracle/report/"awrrpt_orcl_$b.html"（autoawrrpt.sh里面设置的报告名称） -s "orcl-AWRRPT-$b" test@qq.com 3. liunx 系统设置定时任务 30 00 * * * /home/oracle/report/awr.sh