我們在運維Oracle資料庫時，有時候會對比Oracle 的awr報告，那麼如何批量生成報告呢，下面幾個指令碼以供參考。

#### sql指令碼,注意時間格式
``` 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指令碼，注意指令碼開頭時間
``` shell
#!/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="" termout="" off="" verify="" feedback="" select="" dbid="" from="" v="" exit="" eof="" instnum="" sqlplus="" instance_number="" instname="" instance_name="" function="" snap_id_like_time="" snap_id="" dba_hist_snapshot="" where="" to_char="" in="" and="" trunc=""> 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="" termout="" off="" verify="" feedback="" select="" snap_id="" from="" dba_hist_snapshot="" where="" trunc="">= 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="" termout="" off="" verify="" feedback="" select="" snap_id="" from="" dba_hist_snapshot="" where="">= 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="" put="" bye="" eof="" done="" to="" ftpserver="" function="" ftp_awr_report="" hostname="`hostname`;" localdir="olm/xh/`grep ${HOSTNAME} /etc/hosts|awk '{print $1;}'|head -1`" ftp="" passive="" prompt="" user="" binary="" mput="" ascii="" out222="" create_awr_report="" for="" snap_id_line="" in="" do="" bid="$eid" eid="$snap_id_line" if="" then="" sqlplus="" set="" echo="" off="" veri="" feedback="" termout="" on="" heading="" trimspool="" linesize="" column="" report_name="" new_value="" noprint="" select="" name1="" name2="" as="" from="" a.snap_id="" begin_snap_id="" begin_time="" to_char="" dba_hist_snapshot="" a="" where="" and="" a.instance_number="`Instnum`)" t1="" b.snap_id="" end_snap_id="" end_time="" b="" b.instance_number="`Instnum`)" t2="" rownum="" end_time-begin_time="" hour="" spool="" output="" table="" clear="" columns="" sql="" ttitle="" btitle="" repfooter="" exit="" fi=""> 內容整理自網路，如有侵權，請聯絡</eof></eof></eof></eof>

【AWR】Oracle批量生成awr報告指令碼

相關文章