shell脚本oracle循环,使用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/，如需转载，请注明出处，否则将追究法律责任。