使用SHELL脚本批量生成AWR报告

一、查询需要生成的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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/21582653/viewspace-2121992/