Oracle批量生成awr报告脚本

我们在运维Oracle数据库时，有时候会对比Oracle 的awr报告，那么如何批量生成报告呢，下面几个脚本以供参考。

sql脚本,注意时间格式

select
'spool awr_'||dbid||'_'||instance_number||'_'||b_snap_id||'_'||e_snap_id||'.html'
||chr(10)||
'select output from table(dbms_workload_repository.awr_report_html('||dbid||', '||instance_number||','||b_snap_id||','||e_snap_id||'));'
||chr(10)||
'spool off'  
from 
(select dbid,instance_number,snap_id b_snap_id,lead(snap_id,1,0) over(partition by instance_number order by snap_id) e_snap_id
from dba_hist_snapshot
where begin_interval_time>=trunc(sysdate-1)
and begin_interval_time<=trunc(sysdate))
where e_snap_id!=0;

sh脚本，注意脚本开头时间

#!/bin/sh
# set the environment
source /home/oracle/.bash_profile
CONNECTSTR=" / as sysdba"
BEGIN_TIME="20211012_09:00:00"
END_TIME="20211012_12:00:00"
function Dbid {
  sqlplus -S $CONNECTSTR <<EOF
  set pages 0 termout off verify off  feedback off
  select DBID from v\$database;
  exit
EOF
}
Instnum() {
  sqlplus -S $CONNECTSTR <<EOF
  set pages 0 termout off verify off  feedback off
  select instance_number from v\$instance;
  exit
EOF
}
Instname() {
  sqlplus -S $CONNECTSTR <<EOF
  set pages 0 termout off verify off  feedback off
  select instance_name from v\$instance;
  exit
EOF
}
function Snap_id_like_time {
  sqlplus -S $CONNECTSTR <<EOF
  set pages 0 termout off verify off  feedback off;
  select SNAP_ID from dba_hist_snapshot
   where to_char(end_interval_time,'hh24') in (9,10,11,12,14,15,17)
     and trunc(end_interval_time,'mi')> trunc(sysdate-1)
     and instance_number in (select instance_number from v\$instance)
   order by snap_id;
  exit
EOF
}
function Snap_id_between_time {
  sqlplus -S $CONNECTSTR <<EOF
  set pages 0 termout off verify off  feedback off;
  select SNAP_ID from dba_hist_snapshot
   where trunc(end_interval_time,'hh')>= trunc(to_date('$BEGIN_TIME','YYYYMMDD_HH24:MI:SS'),'hh')
     and trunc(end_interval_time,'hh')<= trunc(to_date('$END_TIME','YYYYMMDD_HH24:MI:SS'),'hh')
     and instance_number in (select instance_number from v\$instance)
   order by snap_id;
  exit
EOF
}
function Snap_id_between_id {
  sqlplus -S $CONNECTSTR <<EOF
  set pages 0 termout off verify off  feedback off;
  select SNAP_ID from dba_hist_snapshot
   where snap_id>= to_number($BEGIN_ID)
     and snap_id<= to_number($END_ID)
     and instance_number in (select instance_number from v\$instance)
   order by snap_id;
  exit
EOF
}
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(bytime)"
    ;;
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}<<EOF
     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} <<EOF
  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 <<EOF
        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`_awr'||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<INTERVAL '2' HOUR;
        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;

内容整理自网络，如有侵权，请联系

来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/29487349/viewspace-2795517/，如需转载，请注明出处，否则将追究法律责任。