oracle大量匯出AWR報告，oracle匯出awr

oracle大量匯出AWR報告，oracle匯出awr

工作需求：項目中需要把生產庫中所有的AWR報告dump出來，然後匯入到方便測試的資料庫中。在測試庫中的AWR報告需要根據dbid和執行個體名逐個匯出，如果遇到很多再加上RAC系統，會很麻煩。在網上找了一些指令碼，發現沒有適合自己的，所以就自己學習了一個預存程序來實現這樣的功能。<p></p><p>說明：在$ORACLE_HOME/rdbms/admin/awrrpti.sql中可以看到，產生AWR報告主要使用<span style="background-color:rgb(240,240,240)">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/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;

國外哥們兒寫的SQL參考：

REM http://flashdba.com/database/useful-scripts/awr-generator/REM AWR-Generator.sql: Script for creating multiple consecutive Oracle AWR ReportsREMREM Creates an output SQL script which, when run, will generate all AWR ReportsREM between the specificed start and end snapshot IDs, for all instancesREMREM For educational purposes only - no warranty is providedREM Test thoroughly - use at your own riskREM set feedback offset echo offset verify offset timing off -- Set AWR_FORMAT to "text" or "html"define AWR_FORMAT = 'text'define DEFAULT_OUTPUT_FILENAME = 'awr-generate.sql'define NO_ADDM = 0 -- Get values for dbid and inst_num before calling awrinput.sql set echo off heading oncolumn inst_num heading "Inst Num" new_value inst_num format 99999;column inst_name heading "Instance" new_value inst_name format a12;column db_name heading "DB Name" new_value db_name format a12;column dbid heading "DB Id" new_value dbid format 9999999999 just c; promptprompt Current Instanceprompt ~~~~~~~~~~~~~~~~ select d.dbid dbid , d.name db_name , i.instance_number inst_num , i.instance_name inst_name from v$database d, v$instance i;-- Call the Oracle common input script to setup start and end snap ids@@?/rdbms/admin/awrinput.sql -- Ask the user for the name of the output scriptpromptprompt Specify output script nameprompt ~~~~~~~~~~~~~~~~~~~~~~~~~~prompt This script produces output in the form of another SQL scriptprompt The output script contains the commands to generate the AWR Reportspromptprompt The default output file name is &DEFAULT_OUTPUT_FILENAMEprompt To accept this name, press <return> to continue, otherwise enter an alternativeprompt set heading offcolumn outfile_name new_value outfile_name noprint;select 'Using the output file name ' || nvl('&&outfile_name','&DEFAULT_OUTPUT_FILENAME') , nvl('&&outfile_name','&DEFAULT_OUTPUT_FILENAME') outfile_name from sys.dual; set linesize 800set serverout onset termout off -- spool to outputfilespool &outfile_name -- write script header commentsprompt REM Temporary script created by awr-generator.sqlprompt REM Used to create multiple AWR reports between two snapshotsselect 'REM Created by user '||user||' on '||sys_context('userenv', 'host')||' at '||to_char(sysdate, 'DD-MON-YYYY HH24:MI') from dual; set heading on -- Begin iterating through snapshots and generating reportsDECLARE c_dbid CONSTANT NUMBER := :dbid; c_inst_num CONSTANT NUMBER := :inst_num; c_start_snap_id CONSTANT NUMBER := :bid; c_end_snap_id CONSTANT NUMBER := :eid; c_awr_options CONSTANT NUMBER := &&NO_ADDM; c_report_type CONSTANT CHAR(4):= '&&AWR_FORMAT'; v_awr_reportname VARCHAR2(100); v_report_suffix CHAR(5); CURSOR c_snapshots IS select inst_num, start_snap_id, end_snap_id from ( select s.instance_number as inst_num, s.snap_id as start_snap_id, lead(s.snap_id,1,null) over (partition by s.instance_number order by s.snap_id) as end_snap_id from dba_hist_snapshot s where s.dbid = c_dbid and s.snap_id >= c_start_snap_id and s.snap_id <= c_end_snap_id ) where end_snap_id is not null order by inst_num, start_snap_id; BEGIN dbms_output.put_line(''); dbms_output.put_line('prompt Beginning AWR Generation...'); dbms_output.put_line('set heading off feedback off lines 800 pages 5000 trimspool on trimout on'); -- Determine report type (html or text) IF c_report_type = 'html' THEN v_report_suffix := '.html'; ELSE v_report_suffix := '.txt'; END IF; -- Iterate through snapshots FOR cr_snapshot in c_snapshots LOOP -- Construct filename for AWR report v_awr_reportname := 'awrrpt_'||cr_snapshot.inst_num||'_'||cr_snapshot.start_snap_id||'_'||cr_snapshot.end_snap_id||v_report_suffix; dbms_output.put_line('prompt Creating AWR Report '||v_awr_reportname ||' for instance number '||cr_snapshot.inst_num||' snapshots '||cr_snapshot.start_snap_id||' to '||cr_snapshot.end_snap_id); dbms_output.put_line('prompt'); -- Disable terminal output to stop AWR text appearing on screen dbms_output.put_line('set termout off'); -- Set spool to create AWR report file dbms_output.put_line('spool '||v_awr_reportname); -- call the table function to generate the report IF c_report_type = 'html' THEN dbms_output.put_line('select output from table(dbms_workload_repository.awr_report_html(' ||c_dbid||','||cr_snapshot.inst_num||','||cr_snapshot.start_snap_id||','||cr_snapshot.end_snap_id||','||c_awr_options||'));'); ELSE dbms_output.put_line('select output from table(dbms_workload_repository.awr_report_text(' ||c_dbid||','||cr_snapshot.inst_num||','||cr_snapshot.start_snap_id||','||cr_snapshot.end_snap_id||','||c_awr_options||'));'); END IF; dbms_output.put_line('spool off'); -- Enable terminal output having finished generating AWR report dbms_output.put_line('set termout on'); END LOOP; dbms_output.put_line('set heading on feedback 6 lines 100 pages 45'); dbms_output.put_line('prompt AWR Generation Complete'); -- EXCEPTION HANDLER? END;/ spool off set termout on promptprompt Script written to &outfile_name - check and run in order to generate AWR reports...prompt --clear columns sqlundefine outfile_nameundefine AWR_FORMATundefine DEFAULT_OUTPUT_FILENAMEundefine NO_ADDMundefine OUTFILE_NAME set feedback 6 verify on lines 100 pages 45

資料庫:Oracle 11g AWR 系列五：怎產生 AWR 報告？

1.產生單一實例 AWR 報告：@$ORACLE_HOME/rdbms/admin/awrrpt.sql2.產生 Oracle RAC AWR 報告：@$ORACLE_HOME/rdbms/admin/awrgrpt.sql3.產生 RAC 環境中特定資料庫執行個體的 AWR 報告:@$ORACLE_HOME/rdbms/admin/awrrpti.sql4.產生 Oracle RAC 環境中多個資料庫執行個體的 AWR 報告的方法：@$ORACLE_HOME/rdbms/admin/awrgrpti.sql5.產生 SQL 陳述式的 AWR 報告：@$ORACLE_HOME/rdbms/admin/awrsqrpt.sql6.產生特定資料庫執行個體上某個 SQL 陳述式的 AWR 報告：@$ORACLE_HOME/rdbms/admin/awrsqrpi.sql--產生 AWR 時段對比報告7.產生單一實例 AWR 時段對比報告@$ORACLE_HOME/rdbms/admin/awrddrpt.sql9.產生 Oracle RAC AWR 時段對比報告@$ORACLE_HOME/rdbms/admin/awrgdrpt.sql10.產生特定資料庫執行個體的 AWR 時段對比報告11.產生 Oracle RAC 環境下特定（多個）資料庫執行個體的 AWR 時段對比報告
 
產生oracle資料庫awr報告所需要的許可權？

AWR報告(的)產生技巧如下::
1,,登陸Oracle資料庫::

sqlplus / as sysdba

2, 在sqlplus中,,以sys使用者運行下面(的)命令,,產生第一份效能資料快照::

exec DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT (flush_level=>'ALL');

3,,運行您找到(的)所有或很多效能較差(的)應用或SQL,,讓系統壓力始終處於比較高(的)狀態（如CPU或IO(的)使用）,,並保持此狀態達到半小時到1小時

4,,在sqlplus中,,以sys使用者再次運行下面(的)命令,,產生第二份效能資料快照::

exec DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT (flush_level=>'ALL');

5,,在sqlplus中,,以sys使用者運行下面(的)命令,,以產生效能報告::

@?/rdbms/admin/awrrpt.sql

根據提示,,選擇上面產生(的)2個效能資料快照,,最後會產生一個最終(的)AWR效能報告..