批量生成AWR

--  Author: Clive Bostock (Oracle ACS)

--    Date: July 2009

--    Version 1.0

--

--  This program is provided for educational purposes and is not supported by Oracle Corporation.

--

--  Description

--  batch_awrrpt allows you to specify:

--      o A snapshot date

--        This date acts as an upper bound for the snapshots to be reported

--      o Snapshot days

--        The number for days prior to the specified snapshot date that should

--        also have their snapshots included. The default is 1 day.

--      o The snapshot format

--        Here you should specify text or html, if neither of these are entered

--        then text is assumed.

--

--   You are prompted interactivley to enter the criteria. Once these have been entered batch_awrrpt.sql

--   will generate an AWR report for every snapshot interal for the days specified.

--

--   For RAC systems you will need to execute the script. on each individual instance

--   of interest.

--

--  Version History:

--

--  Date        Author                 Mod Description

--  ==========  =====================  =====================================================

--  09.07.2009  C. Bostock             Initial cut.

--

set serverout on size 1000000 feedback off

accept snap_date  date format 'DD.MM.YYYY' prompt 'Please enter the snapshot date (DD.MM.YYYY): '

accept num_days number prompt 'Please enter the snapshot days [1]: '

accept snap_format prompt 'Please enter the snapshot format text/html [text]: '

 

--

-- Get the current database/instance information - this will be used

-- later in the report along with bid, eid to lookup snapshots

set verify off

set echo off heading on underline on;

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

 

prompt

prompt Current Instance

prompt ~~~~~~~~~~~~~~~~

 

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;

 

spool batch_awr.tmp

DECLARE

 

CURSOR C_snaps (p_dbid NUMBER

               ,p_inst_num NUMBER

               ,p_num_days NUMBER

               ,p_snap_date date

               ,p_format VARCHAR2)

 IS

  select /* BATCH_AWRRPT */

         to_char(s.startup_time,'dd Mon "at" HH24:mi:ss')  instart_fmt

       , s.startup_time

       , di.instance_name                                  inst_name

       , di.db_name                                        db_name

       , s.snap_id                                         snap_id

       , s.end_interval_time                               end_interval_time

       , to_char(s.end_interval_time,'DD.MM.YYYY HH24:mi') snapdat

       , s.snap_level                                      lvl

    from dba_hist_snapshot s

       , dba_hist_database_instance di

   where s.dbid              = p_dbid

     and di.dbid             = p_dbid

     and s.instance_number   = p_inst_num

     and di.instance_number  = p_inst_num

     and trunc(s.end_interval_time) <= p_snap_date + 1

     and di.dbid             = s.dbid

     and di.instance_number  = s.instance_number

     and di.startup_time     = s.startup_time

     and s.end_interval_time >= (p_snap_date - p_num_days + 1)

   order by db_name, instance_name, snap_id;

 

  n_num_days       NUMBER;

  d_snap_date      DATE;

  first_pass       BOOLEAN := TRUE;

  n_begin_snap     INTEGER := 0;

  v_snap_format    VARCHAR2(4);

  v_file_ext       VARCHAR2(5);

  t_last_startup   dba_hist_snapshot.startup_time%TYPE := NULL;

 

BEGIN

 

    n_num_days := NVL('&&num_days',1);

    IF n_num_days = 0

    THEN

      n_num_days := 1;

    END IF;

 

    IF '&&snap_format' = 'html'

    THEN

      v_snap_format := 'html';

      v_file_ext    := '.html';

    ELSIF '&&snap_format' = 'text'

    THEN

      v_snap_format := 'text';

      v_file_ext    := '.text';

    ELSE

      v_snap_format := 'text';

      v_file_ext    := '.txt';

    END IF;

 

 

    d_snap_date := NVL(to_date('&&snap_date','DD.MM.YYYY'), trunc(sysdate));

 

    dbms_output.put_line('-- Snap Date:   ' || d_snap_date);

    dbms_output.put_line('-- Num Days     : ' || n_num_days);

    dbms_output.put_line('-- DB Id        : ' || &&dbid);

    dbms_output.put_line('-- Inst No      : ' || &&inst_num);

    dbms_output.put_line('-- Snap format  : ' || '&&snap_format');

    dbms_output.put_line('-- Snap format  : ' || v_snap_format);

 

    FOR R_snaps IN C_snaps (&&dbid, &&inst_num, n_num_days, d_snap_date, v_snap_format)

    LOOP

      IF first_pass

      THEN

          n_begin_snap := R_snaps.snap_id;

          first_pass := FALSE;

          GOTO end_loop;

      END IF;

 

      IF NOT first_pass AND R_snaps.startup_time != t_last_startup

      THEN

          dbms_output.put_line('REM INFO: Report for #napshots skipped: ' || n_begin_snap

                                                        || ' - '

                                                        || R_snaps.snap_id);

          dbms_output.put('REM INFO: Database was shutdown ');

          dbms_output.put_line(' between these snapshots ('

                                 || R_snaps.instart_fmt ||')');

          GOTO end_loop;

      END IF;

         

      dbms_output.put_line('REM Snap end date:      '|| R_snaps.snapdat);

      dbms_output.put_line('define  inst_num     = ' || &&inst_num || ';');

      dbms_output.put_line('define  num_days     = ' || n_num_days || ';');

      dbms_output.put_line('define  inst_name    = ' || '&&inst_name' || ';');

      dbms_output.put_line('define  db_name      = ' || '&&db_name'   || ';');

      dbms_output.put_line('define  dbid         = ' || &&dbid || ';');

      dbms_output.put_line('define  begin_snap   = ' || n_begin_snap);

      dbms_output.put_line('define  end_snap     = ' || R_snaps.snap_id);

      dbms_output.put_line('define  report_type  = ' || v_snap_format || ';');

      dbms_output.put_line('define  report_name  = ' || 'awrrep-'

--                                                      || n_begin_snap || '_'

--                                                      || R_snaps.snap_id || '_'

                                                        ||to_char(R_snaps.end_interval_time,

                                                                   'YYYY.MM.DD-HH24.MI')

                                                        || v_file_ext || ';' );

      dbms_output.put_line('@@?/rdbms/admin/awrrpti;');

 

    <>

 

      n_begin_snap := R_snaps.snap_id;

      t_last_startup := R_snaps.startup_time;

     

    END LOOP;

END;

/

spool off

@batch_awr.tmp

set feedback on verify on

undefine num_days;

undefine report_type;

undefine report_name;

undefine begin_snap;

undefine end_snap;

-- NEXT TWO LINES SHOULD BE COMMENTED OUT IF USING WINDOWS

REM host grep "^REM INFO:" batch_awr.tmp

REM host rm batch_awr.tmp

-- NEXT TWO LINES SHOULD BE COMMENTED OUT IF USING UNIX / LINUX

host type batch_awr.tmp | find /i "REM INFO"

host del batch_awr.tmp

--

-- End of file

/*

usage:

1、noninteractive:

SQL>@awrrpt_batch.sql start_snap end_snap

2、interactive:

SQL>@awrrpt_batch.sql

*/

set serveroutput on;

set feedback off;

set linesize 120;

prompt ***************************************************************;

prompt usage:;

prompt 1、noninteractive:SQL>@awrrpt_batch.sql start_snap end_snap;

prompt 2、interactive   :SQL>@awrrpt_batch.sql；

prompt ***************************************************************;

pause press enter to continue or ctrl-c to exit.;

col snap_id for 999999999;

col startup_time for a30;

col begin_interval_time for a30;

col end_interval_time for a30;

select snap_id, startup_time, begin_interval_time, end_interval_time

  from dba_hist_snapshot

 where instance_number in (select instance_number from v$instance)

 order by snap_id;

exec dbms_output.put_line(chr(13)||chr(10)||'please enter start and end snap_id:');

declare

    v_dbid number;

    v_instance number;

    v_b_id number;

    v_e_id number;

    v_code number;

    v_errm varchar2(300);

    v_sql varchar2(300);

    v_html varchar2(20000);

    cur_awrrpt_html SYS_REFCURSOR;

  cur_snapshot SYS_REFCURSOR;

    fileID utl_file.file_type;

    v_filename varchar2(30);

  v_snap_id number;

  v_startup_time timestamp(3);                                         

  v_begin_snap_time timestamp(3);                                   

  v_end_snap_time   timestamp(3);

  v_dpath varchar2(60);

begin

    v_b_id:=&1;

    v_e_id:=&2;

  dbms_output.put_line(chr(13)||chr(10)||'awrrpt report files:');

    for k in v_b_id..v_e_id-1 loop                                                         

        v_filename:='awrrpt_'||k||'_'||(k+1)||'.html';

        fileID:=utl_file.fopen('DATA_PUMP_DIR',v_filename,'a',32767);

        select dbid into v_dbid from v$database;

        select instance_number into v_instance from v$instance;

        v_sql:='select output from table(dbms_workload_repository.awr_report_html('||v_dbid||','||v_instance||','||k||','||(k+1)||',8))';

        open cur_awrrpt_html for v_sql;

        loop

            exit when cur_awrrpt_html%notfound;

            fetch cur_awrrpt_html into v_html;

            utl_file.put_line(fileID,v_html);

        end loop;

        utl_file.fclose(fileID);   

    execute immediate 'select directory_path from dba_directories where directory_name=:dname' into v_dpath using 'DATA_PUMP_DIR';

    dbms_output.put_line(v_dpath||'/'||v_filename);

    end loop;

    exception

            when others then

                v_code:=SQLCODE;

                v_errm:=SQLERRM;

                dbms_output.put_line('ERROR CODE'||v_code||':'||v_errm);

end;

/