Script to Generate AWR Reports for All snap_ids Between 2 Given Dates

Script to Generate AWR Reports for All snap_ids Between 2 Given Dates

gen_batch.sql

set echo off heading off feedback off verify off
select 'Please enter dates in DD-MON-YYYY HH24 format:' from dual;
select 'You have entered:', '&&BEGIN_DATE', '&&END_DATE' from dual;
set pages 0 termout off 
spool batch.sql
SELECT DISTINCT '@pcreport '
                                ||b.snap_id
                                ||' '
                                ||e.snap_id
                                ||' '
                                || TO_CHAR(b.end_interval_time,'YYMMDD_HH24MI_')
                                ||TO_CHAR(e.end_interval_time,'HH24MI')
                                ||'.txt' Commands,
                '-- '||TO_CHAR(b.end_interval_time,'YYMMDD_HH24MI') lineorder
FROM            dba_hist_snapshot b,
                dba_hist_snapshot e
WHERE           b.end_interval_time>=to_date('&BEGIN_DATE','DD-MON-YYYY HH24')
AND             b.end_interval_time<=to_date('&END_DATE','DD-MON-YYYY HH24')
AND             e.snap_id           =b.snap_id+1
ORDER BY        lineorder
/
spool off
set termout on
select 'Generating Report Script batch.sql.....' from dual;
select 'Report file created for snap_ids between:', '&&BEGIN_DATE', '&&END_DATE', 'Check file batch.sql' from dual;
set echo on termout on verify on heading on feedback on

 

pcreport.sql

define num_days = 0;
define report_type = 'text'
column inst_num new_value inst_num
column dbname new_value dbname
column dbid new_value dbid
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;
column begin_snap new_value begin_snap
column end_snap new_value end_snap
column report_name new_value report_name
SELECT &1 begin_snap
FROM   dual;
SELECT &2 end_snap
FROM   dual;
SELECT name
              ||'_'
              ||'&3' report_name
FROM   v$database;
@@?/rdbms/admin/awrrpti

run as sysdba
SQL> @gen_batch
SQL> set echo off heading off feedback off verify off
Please enter dates in DD-MON-YYYY HH24 format:
once complete than run 
SQL> @batch
It will create AWR in text format for 1 hour interval from begin till end time.
Advertisements

About Sher khan

Senior Oracle DBA, Oracle 10g , Oracle 11g OCE ,Oracle 12C and Oracle 11g OPN certified Specialist, OCP Certified in Oracle 9i,10g , 11g and 12C. I am working in Oracle technology since 2004. Currently working in U.A.E Email: sher487@hotmail.com
This entry was posted in Performance and Tuning. Bookmark the permalink.

4 Responses to Script to Generate AWR Reports for All snap_ids Between 2 Given Dates

  1. sadiq says:

    Hi Sher Khan,
    The script to “Generate AWR Reports for All snap_ids Between 2 Given Dates” works fine. However, I am getting multiple output file. I want it as a single consolidated file between two dates.

    Regards,

    • Sher khan says:

      Hi Sadiq,

      It is very simple to generate single AWR. suppose you are connected as sys user
      SQL> @?/rdbms/admin/awrrpt.sql

      supose you have 10 snap-shot. Now you want to generate only one AWR of 10 snap-ids.
      put 1 for initial snap-id
      and then put 10 for last snap-id
      after that give the report path and name. So it will generate a single report.

      Thanks,

  2. sadiq says:

    Hi Sher Khan,
    Sorry i did not put across my question clearly. I wanted to generated a awr report on the basis of two given dates. i tried using your script mentioned above. The output generated is not as a single consolidated report. It gives me multiple reports when i execute the above mentioned script.
    I wanted a single consolidated report with the query mentioned above not using awrrpt.

    Regards,

  3. I love what you guys tend to be up too. Such clever work
    and reporting! Keep up the excellent works guys I’ve added
    you guys to my blogroll.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s