一、查询需要生成的AWR报告的开始快照ID和结束快照ID,比如我们要查询7月12日9-18点的AWR快照ID
SELECT distinct trim(SNAP_ID)
FROM DBA_HIST_SNAPSHOT
where to_char(BEGIN_INTERVAL_TIME,'yyyy-mm-dd hh24')='2016-07-12 09'
;
TRIM(SNAP_ID)
--------------------------------------------------------------------------------
43097
SELECT distinct trim(SNAP_ID)
FROM DBA_HIST_SNAPSHOT
where to_char(BEGIN_INTERVAL_TIME,'yyyy-mm-dd hh24')='2016-07-12 18'
;
TRIM(SNAP_ID)
--------------------------------------------------------------------------------
43106
二、根据查询到的AWR快照ID开发SHELL脚本
# vi awr3.sh
#set -x
V_DIR=/home/oracle/olm/20160713/awr
V_SNAP_ID=43097
###########Print AWR#############
while [ $V_SNAP_ID -lt 43106 ]
do
AWR_FORMAT=html
NUM_DAYS=1
MAX_SNAP_ID=`sqlplus -S / as sysdba << EOF
set heading off trimspool on feedback off
SELECT trim(max(SNAP_ID)) FROM DBA_HIST_SNAPSHOT where SNAP_ID=$V_SNAP_ID;
EOF`
MIN_SNAP_ID=`expr $MAX_SNAP_ID - 1`
AWR_LOG=$V_DIR/AWR_$ORACLE_SID_`date '+%Y%m%d_%H%M'`_$V_SNAP_ID.html
echo -e "$AWR_FORMAT\n$NUM_DAYS\n$MIN_SNAP_ID\n$MAX_SNAP_ID\n$AWR_LOG\n"|(sqlplus -S / as sysdba @?/rdbms/admin/awrrpt.sql) > /dev/null
V_SNAP_ID=`expr $V_SNAP_ID + 1`
done
#set +x
赋予脚本执行权限
chmod 755 awr3.sh
执行脚本就会在 /home/oracle/olm/20160713/awr 目录下按每小时一份生成9到18点的AWR报告。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/21582653/viewspace-2121992/,如需转载,请注明出处,否则将追究法律责任。