Oracle批量导出AWR报告(二)

echo "==========++++++++++++++===========";

CMDPM=`echo $1 | awk '{print tolower($1)}'`
case $CMDPM in
bi | -bi | byid)
SNAP_ID=`Snap_id_between_id`
;;
bt | -bt | bytime)
SNAP_ID=`Snap_id_between_time`
;;
lt | -lt | liketime)
SNAP_ID=`Snap_id_like_time`
;;
*)
#SNAP_ID=`Snap_id_like_time`
echo "please usage like : $0 -bt"
;;
esac

echo "$SNAP_ID";
echo "==========++++++++++++++===========";

#Lftp to sftpServer .lftp Just fo Linux.
function Lftp_awr_report {
cd .
for VAR in *.htm*
do
lftp -u ${FTPUSER},${FTPPASS} sftp://${FTPSERVERIP}< cd /u01/docdata/olm/xh/121.160
put ${VAR}
bye
EOF
done;
}

#FTP to ftpServer
function Ftp_awr_report {
cd /tmp
HOSTNAME=`hostname`;
LOCALDIR="olm/xh/`grep ${HOSTNAME} /etc/hosts|awk '{print $1;}'|head -1`";
ftp -n ${FTPSERVERIP} < passive
prompt
user ${FTPUSER} ${FTPPASS}
cd ${LOCALDIR}
binary
mput *.html
ascii
mput out222*.log
bye
EOF
}

function Create_awr_report {
for snap_id_line in $SNAP_ID ; do
bid="$eid"
eid="$snap_id_line"
if [ "$bid" != "" -a "$eid" != "" ] ; then
sqlplus -S $CONNECTSTR < set echo off;
set veri off;
set feedback off;
set termout on;
set heading off;
set trimspool on;
set linesize 1500;
set termout off;
column report_name new_value report_name noprint;
select name1 || name2 as report_name
from (select a.snap_id as begin_snap_id,a.end_interval_time as begin_time,
to_char(a.end_interval_time, 'yyyymmdd_') ||
'`Instname`'||to_char(a.end_interval_time, '_hh24')||
to_char(a.end_interval_time, 'mi') || '-' as name1
from dba_hist_snapshot a
where a.snap_id = $bid
and a.instance_number = `Instnum`) t1,
(select b.snap_id as end_snap_id,b.end_interval_time as end_time,
to_char(b.end_interval_time, 'hh24') ||
to_char(b.end_interval_time, 'mi') || '.' || 'html ' name2
from dba_hist_snapshot b
where b.snap_id = $eid
and b.instance_number = `Instnum`) t2
where rownum < 2
and end_snap_id - begin_snap_id < 3
and end_time-begin_time set termout off;
spool &report_name;
select output from TABLE(DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_HTML(`Dbid`,`Instnum`,$bid, $eid,0 ));
spool off;
set termout on;
clear columns sql;
ttitle off;
btitle off;
repfooter off;
exit
EOF
fi
done
}

Create_awr_report;
#Ftp_awr_report;

国外哥们儿写的SQL参考：

REM http://flashdba.com/database/useful-scripts/awr-generator/
REM AWR-Generator.sql: Script for creating multiple consecutive Oracle AWR Reports
REM
REM Creates an output SQL script which, when run, will generate all AWR Reports
REM between the specificed start and end snapshot IDs, for all instances
REM
REM For educational purposes only - no warranty is provided
REM Test thoroughly - use at your own risk
REM

set feedback off
set echo off
set verify off
set timing off

-- Set AWR_FORMAT to "text" or "html"
define AWR_FORMAT = 'text'
define DEFAULT_OUTPUT_FILENAME = 'awr-generate.sql'
define NO_ADDM = 0

-- Get values for dbid and inst_num before calling awrinput.sql

set echo off heading on
column inst_num heading "Inst Num" new_value inst_num format 99999;
column inst_name heading "Instance" new_value inst_name format a12;