SQL script for generating multiple AWR Reports

 
multiple_awr.sql - Use below script 
 
REM###########www.ora-am.blogspot.com#################
 
REM multiple_awr.sql: Script for creating multiple consecutive Oracle AWR Reports
REM
REM Creates an output SQL script which, when run, will generate all AWR Reports
REM between the specificed start and end snapshot IDs, for all instances
REM
REM For educational purposes only - no warranty is provided
REM Test thoroughly - use at your own risk
REM
 
set feedback off
set echo off
set verify off
set timing off
 
-- Set AWR_FORMAT to "text" or "html"
define AWR_FORMAT = 'html'
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 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;
-- 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 script
prompt
prompt Specify output script name
prompt ~~~~~~~~~~~~~~~~~~~~~~~~~~
prompt This script produces output in the form of another SQL script
prompt The output script contains the commands to generate the AWR Reports
prompt
prompt The default output file name is &DEFAULT_OUTPUT_FILENAME
prompt To accept this name, press <return> to continue, otherwise enter an alternative
prompt
 
set heading off
column 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 800
set serverout on
set termout off
 
-- spool to outputfile
spool &outfile_name
 
-- write script header comments
prompt REM Temporary script created by multiple_awr.sql
prompt REM Used to create multiple AWR reports between two snapshots
select '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 reports
DECLARE
 
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
 
prompt
prompt Script written to &outfile_name - check and run in order to generate AWR reports...
prompt
 
--clear columns sql
undefine outfile_name
undefine AWR_FORMAT
undefine DEFAULT_OUTPUT_FILENAME
undefine NO_ADDM
undefine OUTFILE_NAME
 
set feedback 6 verify on lines 100 pages 45
 

================Demo=========================

This SQL script does not create any objects and simply produces a second script which can be inspected and then run. The result is a complete set of AWR reports for all snapshots within the specified range. In the case of RAC systems this also means all instances.

Steps to execute the multiple_awr.sql file

 

$ ls -lrt
total 128
-rw-r--r--    1 oracle   dba            5283 Jul 22 17:52 awr_generator.sql
 
$ . oraenv
ORACLE_SID = [oracle] ? ORCL
The Oracle base remains unchanged with value /applications/oracle
 
$ sqlplus "/ as sysdba"
 
SQL*Plus: Release 12.1.0.2.0 Production on Thu Jul 23 09:15:18 2020
Copyright (c) 1982, 2014, Oracle.  All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
 
SQL> @ multiple_awr.sql
 
Current Instance
~~~~~~~~~~~~~~~~
 
   DB Id    DB Name      Inst Num Instance
----------- ------------ -------- ------------
 4060650314 ORCL              1 ORCL
 
 
Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
 
   DB Id     Inst Num DB Name      Instance     Host
------------ -------- ------------ ------------ ------------
* 4060650314        1 ORCL       ORCL       ffm04aixhate                                           
 
 
Using 4060650314 for database Id
Using          1 for instance number
 
 
Specify the number of days of snapshots to choose from
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Entering the number of days (n) will result in the most recent
(n) days of snapshots being listed.  Pressing <return> without
specifying a number lists all completed snapshots.
 
 
Enter value for num_days: 1
 
Listing the last day's Completed Snapshots
 
 
 
 
 
 
                                                        Snap
Instance     DB Name        Snap Id    Snap Started    Level
------------ ------------ --------- ------------------ -----
ORCL           ORCL             219 23 Jul 2020 00:00      1
                                220 23 Jul 2020 01:00      1
                                221 23 Jul 2020 02:00      1
                                222 23 Jul 2020 03:01      1
                                223 23 Jul 2020 04:00      1
                                224 23 Jul 2020 05:00      1
                                225 23 Jul 2020 06:00      1
                                226 23 Jul 2020 07:00      1
                                227 23 Jul 2020 08:00      1
                                228 23 Jul 2020 09:00      1
 
 
 
Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 219 ------------ pass the snap ID from where you want to generate
Begin Snapshot Id specified: 219
 
Enter value for end_snap: 226 ------------ pass the snap ID from where you want to end
End   Snapshot Id specified: 226
 
 
Specify output script name
~~~~~~~~~~~~~~~~~~~~~~~~~~
This script produces output in the form of another SQL script
The output script contains the commands to generate the AWR Reports
 
The default output file name is awr-generate.sql
To accept this name, press <return> to continue, otherwise enter an alternative
 
Enter value for outfile_name: /home/oracle/awr_generator.sql
 
Using the output file name awr-generate.sql  à just run the file
 
Script written to awr-generate.sql - check and run in order to generate AWR reports...
 
SQL> @awr-generate.sql;
Beginning AWR Generation...
Creating AWR Report awrrpt_1_219_220.html for instance number 1 snapshots 219 to 220
 
Creating AWR Report awrrpt_1_220_221.html for instance number 1 snapshots 220 to 221
 
Creating AWR Report awrrpt_1_221_222.html for instance number 1 snapshots 221 to 222
 
Creating AWR Report awrrpt_1_222_223.html for instance number 1 snapshots 222 to 223
 
Creating AWR Report awrrpt_1_223_224.html for instance number 1 snapshots 223 to 224
 
Creating AWR Report awrrpt_1_224_225.html for instance number 1 snapshots 224 to 225
 
Creating AWR Report awrrpt_1_225_226.html for instance number 1 snapshots 225 to 226
 
AWR Generation Complete
 
SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
 
$
$ ls -lrt
total 8344
-rw-r--r--    1 oracle   dba            5283 Jul 22 17:52 awr_generator.sql
-rw-r--r--    1 oracle   dba            2187 Jul 23 09:15 multiple_awr.sql
-rw-r--r--    1 oracle   dba          607255 Jul 23 09:16 awrrpt_1_219_220.html
-rw-r--r--    1 oracle   dba          618055 Jul 23 09:16 awrrpt_1_220_221.html
-rw-r--r--    1 oracle   dba          626223 Jul 23 09:16 awrrpt_1_221_222.html
-rw-r--r--    1 oracle   dba          592553 Jul 23 09:16 awrrpt_1_222_223.html
-rw-r--r--    1 oracle   dba          572039 Jul 23 09:16 awrrpt_1_223_224.html
-rw-r--r--    1 oracle   dba          580327 Jul 23 09:16 awrrpt_1_224_225.html
-rw-r--r--    1 oracle   dba          590378 Jul 23 09:16 awrrpt_1_225_226.html

You can see the AWR generated files.