oracle批量导出AWR报告

工作需求：项目中需要把生产库中所有的AWR报告dump出来，然后导入到方便测试的数据库中。在测试库中的AWR报告需要根据dbid和实例名逐个导出，如果遇到很多再加上RAC系统，会很麻烦。在网上找了一些脚本，发现没有适合自己的，所以就自己学习了一个存储过程来实现这样的功能。

说明：在$ORACLE_HOME/rdbms/admin/awrrpti.sql中可以看到，生成AWR报告主要使用DBMS_WORKLOAD_REPOSITORY.awr_report_html包。至于喜欢玩哪种方式，要看个人偏好。

存储过程实现代码：

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;
/

　++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

shell 脚本实现参考：

#!/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/db10gr2
ORACLE_SID=portaldb1
ORACLE_HOME="$ORACLE_HOME"
PATH=$ORACLE_HOME/bin:$PATH
CONNECTSTR=" / 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_SID
export ORACLE_HOME
export PATH



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"
    ;;
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`'||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;

国外哥们儿写的SQL参考：

  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 BEGIN
100  
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' THEN
108  v_report_suffix := '.html';
109  ELSE
110  v_report_suffix := '.txt';
111  END IF;
112  
113 -- Iterate through snapshots
114  FOR cr_snapshot in c_snapshots
115  LOOP
116  -- Construct filename for AWR report
117  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_reportname
120  ||' 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 screen
124  dbms_output.put_line('set termout off');
125  
126 -- Set spool to create AWR report file
127  dbms_output.put_line('spool '||v_awr_reportname);
128  
129 -- call the table function to generate the report
130  IF c_report_type = 'html' THEN
131  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  ELSE
134  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 report
141  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 off
155  
156 set termout on
157  
158 prompt
159 prompt Script written to &outfile_name - check and run in order to generate AWR reports...
160 prompt
161  
162 --clear columns sql
163 undefine outfile_name
164 undefine AWR_FORMAT
165 undefine DEFAULT_OUTPUT_FILENAME
166 undefine NO_ADDM
167 undefine OUTFILE_NAME
168  
169 set feedback 6 verify on lines 100 pages 45