批量生成awr报告

-----------------------------------------方法一：
set linesize 300 ;
set pagesize 0;
set long 99999;
set heading off;
set termout off;
set echo off;
set feedback off;
set verify   off;
set timing off;
 
select
'spool ebsrac_awrrpt_'||snap_id||'_'||(snap_id+1)||'_'||dbid||'_'||instance_number||'.html'||chr(10)||
'select output '||chr(10)||
'  from table(dbms_workload_repository.awr_report_html('||dbid||','||instance_number||','||snap_id||','||(snap_id+1)||'));'||chr(10)||
'spool off;'||chr(10)
from dba_hist_snapshot
where snap_id between 59105 and 59153-1 and dbid=188752074 and instance_number=1;

spool ebsrac1_awrrpt_batch.sql
/
spool off
@ebsrac1_awrrpt_batch.sql
exit;

set linesize 300 ;
set pagesize 0;
set long 99999;
set heading off;
set termout off;
set echo off;
set feedback off;
set verify   off;
set timing off;
 
select
'spool ebsrac_awrrpt_'||snap_id||'_'||(snap_id+1)||'_'||dbid||'_'||instance_number||'.html'||chr(10)||
'select output '||chr(10)||
'  from table(dbms_workload_repository.awr_report_html('||dbid||','||instance_number||','||snap_id||','||(snap_id+1)||'));'||chr(10)||
'spool off;'||chr(10)
from dba_hist_snapshot
where snap_id between 59105 and 59153-1 and dbid=188752074 and instance_number=2;

spool ebsrac2_awrrpt_batch.sql
/
spool off
@ebsrac2_awrrpt_batch.sql
exit;

-----------------------------------------方法二：
export nls_date_format='yyyy-mm-dd hh24:mi:ss'
export nls_timestamp_format='yyyy-mm-dd hh24:mi:ss'
selectuser="connect / as sysdba"

#awr snapshot.

awrsnap(){

   Time=""
   read -p "Input within the last few days (default 1):" Time

   Inst_id=""
   read -p "Inst_id (default 1):" Inst_id
   
   if [ -z $Time ]; then

       Time=1

   fi
   echo $Time   >> $Logfile

   if [ -z $Inst_id ]; then

       Inst_id=1

   fi
   echo $Inst_id   >> $Logfile

   sqlplus -s /NOLOG <<EOF | egrep -v "Session altered|Connected|rows selected"
   $selectuser
   select  instance_number,snap_id-1 as begin_snapid
           ,to_char(begin_interval_time,'yyyy-mm-dd hh24:mi:ss') as begin_interval_time
           ,snap_id as end_snapid
           ,to_char(end_interval_time,'yyyy-mm-dd hh24:mi:ss') as end_interval_time
           ,to_char(startup_time,'yyyy-mm-dd hh24:mi:ss') as startup_time
           ,round((cast(end_interval_time as date)-cast(begin_interval_time as date))*24*60) as interval_min
   from dba_hist_snapshot 
   where end_interval_time>sysdate-$Time and instance_number=$Inst_id
   order by instance_number,snap_id;
    
   Prompt AWR setting:
   select
      extract( day from snap_interval) *24*60+  extract( hour from snap_interval) *60+  extract( minute from snap_interval ) as  "Interval_min",
      extract( day from retention) + extract( hour from retention) /24 +   extract( minute from retention )/24/60 as  "Retention_days"
   from dba_hist_wr_control; 
   exit; 
EOF
   unset Time
   unset Inst_id
}



#awr report.
awr(){

   awrsnap;

   bid=""
   while [ -z "$bid" ]
   do
      read -p "Input Begin snap_id:" bid
   done
	echo $bid   >> $Logfile

   eid=""
   while [ -z "$eid" ]
   do
      read -p "Input End   snap_id:" eid
   done
   echo $eid   >> $Logfile

   if [ $bid -ge $eid ]; then
      echo begin_snap_id:$bid end_snap_id:$eid  input error!
   else
      sqlplus -s /NOLOG <<EOF | egrep -v "Session altered|Connected|rows selected"
      $selectuser
      set autop off ver off serverout on size unlimited;
      set termout off echo off feedback off
      set trims on
      spool _awr.sql
   
      declare 
         node_cnt number:=0; 
         v_dbid number;
      begin
         dbms_output.put_line('set termout off pagesize 0 heading off linesize 8000 trimspool on trimout on tab off timing off feedback off');
         dbms_output.put_line('exec dbms_workload_repository.awr_set_report_thresholds(top_n_sql=>100);');
         for i in (select dbid,inst_id from gv\$database order by 1)
         loop
             dbms_output.put_line('set termout on');
             dbms_output.put_line('prompt generate awr for node'||i.inst_id||' ,  please wait for a few minutes......');
             dbms_output.put_line('set termout off');
             dbms_output.put_line('spool op_awrrpt_'||i.inst_id||'_'||$bid||'_'||$eid||'.html');
             dbms_output.put_line('select * from table(dbms_workload_repository.awr_report_html('||i.dbid||','||i.inst_id||','||$bid||','||$eid||'));');
             dbms_output.put_line('spool off');
             node_cnt:=node_cnt+1;
             v_dbid  :=i.dbid;
         end loop;
         if node_cnt>1 then 
         dbms_output.put_line('set termout on');
         dbms_output.put_line('prompt generate awr for rac ,  please wait for a few minutes');
         dbms_output.put_line('set termout off');
         dbms_output.put_line('spool op_awrrpt_rac_'||$bid||'_'||$eid||'.html');
         dbms_output.put_line('select * from table(dbms_workload_repository.awr_global_report_html('||v_dbid||', cast(null as varchar2(10)),'||$bid||' ,'||$eid||'));');
         dbms_output.put_line('spool off');
         end if;
      END;
      /
   
      spool off;
      set serverout off
      @_awr    
      exit;
EOF
      rm _awr.sql
      echo "generate awr report for snapid $bid - $eid "
   fi
   unset bid
   unset eid
}
awr;