create or replace directory AWR_REPORTS_DIR as ‘/u01/awr/‘;DECLARE  l_snap_start       NUMBER := 14632;  l_snap_end         NUMBER := 14643;  l_dir              VARCHAR2(50) := ‘AWR_REPORTS_DIR‘;    l_last_snap        NUMBER := NULL;  l_dbid             NUMBER := 813977229;  l_file             UTL_FILE.file_type;  l_file_name        VARCHAR(50);  cursor cur_inum is  SELECT instance_number FROM dba_hist_snapshot WHERE dbid = l_dbid GROUP BY instance_number ORDER BY instance_number;BEGIN  for l_instance_number in cur_inum loop     l_last_snap := NULL;    FOR cur_snap IN (SELECT snap_id                   FROM   dba_hist_snapshot                   WHERE  instance_number = l_instance_number.instance_number                   AND    snap_id BETWEEN l_snap_start AND l_snap_end                   ORDER BY snap_id)  LOOP    IF l_last_snap IS NOT NULL THEN      l_file := UTL_FILE.fopen(l_dir, ‘awr_‘ ||l_instance_number.instance_number||‘_‘|| l_last_snap || ‘_‘ || cur_snap.snap_id || ‘.html‘, ‘w‘, 32767);            FOR cur_rep IN (SELECT output                      FROM   TABLE(DBMS_WORKLOAD_REPOSITORY.awr_report_html(l_dbid, l_instance_number.instance_number, l_last_snap, cur_snap.snap_id)))      LOOP        UTL_FILE.put_line(l_file, cur_rep.output);      END LOOP;      UTL_FILE.fclose(l_file);    END IF;    l_last_snap := cur_snap.snap_id;  END LOOP;  end loop;  EXCEPTION  WHEN OTHERS THEN    IF UTL_FILE.is_open(l_file) THEN      UTL_FILE.fclose(l_file);    END IF;    RAISE; END;/

#!/bin/sh# version 1.0 created by sprilich 20121101# version 1.2 edited  by sprilich 20121214# set the environment#ORACLE_SID=eupdb#ORACLE_HOME=/u01/oracle/product/db10gr2ORACLE_SID=portaldb1ORACLE_HOME="$ORACLE_HOME"PATH=$ORACLE_HOME/bin:$PATHCONNECTSTR=" / as sysdba"#BEGIN_ID="223"#END_ID="226"BEGIN_TIME="20140504_00:00:00"END_TIME="20140506_12:00:00"#FTPSERVERIP="10.193.16.86"#FTPUSER="ftpuser"#FTPPASS="1qaz2wsx"export ORACLE_SIDexport ORACLE_HOMEexport PATHfunction Dbid {  sqlplus -S $CONNECTSTR <<EOF  set pages 0 termout off verify off  feedback off  select DBID from v\$database;  exitEOF}Instnum() {  sqlplus -S $CONNECTSTR <<EOF  set pages 0 termout off verify off  feedback off  select instance_number from v\$instance;  exitEOF}Instname() {  sqlplus -S $CONNECTSTR <<EOF  set pages 0 termout off verify off  feedback off  select instance_name from v\$instance;  exitEOF}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;  exitEOF}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;  exitEOF}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;  exitEOF}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"    ;;esacecho "$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}     byeEOFdone;}#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  byeEOF}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`‘||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;      exitEOF    fi  done}Create_awr_report;#Ftp_awr_report;

  1 REM http://flashdba.com/database/useful-scripts/awr-generator/  2 REM AWR-Generator.sql: Script for creating multiple consecutive Oracle AWR Reports  3 REM  4 REM Creates an output SQL script which, when run, will generate all AWR Reports  5 REM between the specificed start and end snapshot IDs, for all instances  6 REM  7 REM For educational purposes only - no warranty is provided  8 REM Test thoroughly - use at your own risk  9 REM 10   11 set feedback off 12 set echo off 13 set verify off 14 set timing off 15   16 -- Set AWR_FORMAT to "text" or "html" 17 define AWR_FORMAT = ‘text‘ 18 define DEFAULT_OUTPUT_FILENAME = ‘awr-generate.sql‘ 19 define NO_ADDM = 0 20   21 -- Get values for dbid and inst_num before calling awrinput.sql 22   23 set echo off heading on 24 column inst_num heading "Inst Num" new_value inst_num format 99999; 25 column inst_name heading "Instance" new_value inst_name format a12; 26 column db_name heading "DB Name" new_value db_name format a12; 27 column dbid heading "DB Id" new_value dbid format 9999999999 just c; 28   29 prompt 30 prompt Current Instance 31 prompt ~~~~~~~~~~~~~~~~ 32   33 select d.dbid dbid 34  , d.name db_name 35  , i.instance_number inst_num 36  , i.instance_name inst_name 37  from v$database d, 38  v$instance i; 39 -- Call the Oracle common input script to setup start and end snap ids 40 @@?/rdbms/admin/awrinput.sql 41   42 -- Ask the user for the name of the output script 43 prompt 44 prompt Specify output script name 45 prompt ~~~~~~~~~~~~~~~~~~~~~~~~~~ 46 prompt This script produces output in the form of another SQL script 47 prompt The output script contains the commands to generate the AWR Reports 48 prompt 49 prompt The default output file name is &DEFAULT_OUTPUT_FILENAME 50 prompt To accept this name, press <return> to continue, otherwise enter an alternative 51 prompt 52   53 set heading off 54 column outfile_name new_value outfile_name noprint; 55 select ‘Using the output file name ‘ || nvl(‘&&outfile_name‘,‘&DEFAULT_OUTPUT_FILENAME‘) 56  , nvl(‘&&outfile_name‘,‘&DEFAULT_OUTPUT_FILENAME‘) outfile_name 57  from sys.dual; 58   59 set linesize 800 60 set serverout on 61 set termout off 62   63 -- spool to outputfile 64 spool &outfile_name 65   66 -- write script header comments 67 prompt REM Temporary script created by awr-generator.sql 68 prompt REM Used to create multiple AWR reports between two snapshots 69 select ‘REM Created by user ‘||user||‘ on ‘||sys_context(‘userenv‘, ‘host‘)||‘ at ‘||to_char(sysdate, ‘DD-MON-YYYY HH24:MI‘) from dual; 70   71 set heading on 72   73 -- Begin iterating through snapshots and generating reports 74 DECLARE 75   76 c_dbid CONSTANT NUMBER := :dbid; 77  c_inst_num CONSTANT NUMBER := :inst_num; 78  c_start_snap_id CONSTANT NUMBER := :bid; 79  c_end_snap_id CONSTANT NUMBER := :eid; 80  c_awr_options CONSTANT NUMBER := &&NO_ADDM; 81  c_report_type CONSTANT CHAR(4):= ‘&&AWR_FORMAT‘; 82  v_awr_reportname VARCHAR2(100); 83  v_report_suffix CHAR(5); 84   85 CURSOR c_snapshots IS 86  select inst_num, start_snap_id, end_snap_id 87  from ( 88  select s.instance_number as inst_num, 89  s.snap_id as start_snap_id, 90  lead(s.snap_id,1,null) over (partition by s.instance_number order by s.snap_id) as end_snap_id 91  from dba_hist_snapshot s 92  where s.dbid = c_dbid 93  and s.snap_id >= c_start_snap_id 94  and s.snap_id <= c_end_snap_id 95  ) 96  where end_snap_id is not null 97  order by inst_num, start_snap_id; 98   99 BEGIN100  101 dbms_output.put_line(‘‘);102  dbms_output.put_line(‘prompt Beginning AWR Generation...‘);103  104 dbms_output.put_line(‘set heading off feedback off lines 800 pages 5000 trimspool on trimout on‘);105  106 -- Determine report type (html or text)107  IF c_report_type = ‘html‘ THEN108  v_report_suffix := ‘.html‘;109  ELSE110  v_report_suffix := ‘.txt‘;111  END IF;112  113 -- Iterate through snapshots114  FOR cr_snapshot in c_snapshots115  LOOP116  -- Construct filename for AWR report117  v_awr_reportname := ‘awrrpt_‘||cr_snapshot.inst_num||‘_‘||cr_snapshot.start_snap_id||‘_‘||cr_snapshot.end_snap_id||v_report_suffix;118  119 dbms_output.put_line(‘prompt Creating AWR Report ‘||v_awr_reportname120  ||‘ for instance number ‘||cr_snapshot.inst_num||‘ snapshots ‘||cr_snapshot.start_snap_id||‘ to ‘||cr_snapshot.end_snap_id);121  dbms_output.put_line(‘prompt‘);122  123 -- Disable terminal output to stop AWR text appearing on screen124  dbms_output.put_line(‘set termout off‘);125  126 -- Set spool to create AWR report file127  dbms_output.put_line(‘spool ‘||v_awr_reportname);128  129 -- call the table function to generate the report130  IF c_report_type = ‘html‘ THEN131  dbms_output.put_line(‘select output from table(dbms_workload_repository.awr_report_html(‘132  ||c_dbid||‘,‘||cr_snapshot.inst_num||‘,‘||cr_snapshot.start_snap_id||‘,‘||cr_snapshot.end_snap_id||‘,‘||c_awr_options||‘));‘);133  ELSE134  dbms_output.put_line(‘select output from table(dbms_workload_repository.awr_report_text(‘135  ||c_dbid||‘,‘||cr_snapshot.inst_num||‘,‘||cr_snapshot.start_snap_id||‘,‘||cr_snapshot.end_snap_id||‘,‘||c_awr_options||‘));‘);136  END IF;137  138 dbms_output.put_line(‘spool off‘);139  140 -- Enable terminal output having finished generating AWR report141  dbms_output.put_line(‘set termout on‘);142  143 END LOOP;144  145 dbms_output.put_line(‘set heading on feedback 6 lines 100 pages 45‘);146  147 dbms_output.put_line(‘prompt AWR Generation Complete‘);148  149 -- EXCEPTION HANDLER?150  151 END;152 /153  154 spool off155  156 set termout on157  158 prompt159 prompt Script written to &outfile_name - check and run in order to generate AWR reports...160 prompt161  162 --clear columns sql163 undefine outfile_name164 undefine AWR_FORMAT165 undefine DEFAULT_OUTPUT_FILENAME166 undefine NO_ADDM167 undefine OUTFILE_NAME168  169 set feedback 6 verify on lines 100 pages 45