Automatically Generate Hourly AWR report Script - Oracle – AWR, ASH and ADDM reports for RAC ~ Blog for oracle RAC Goldengate Dataguard ASM database

Hello All,

Any one can use the below script to generate Multiple Awr reports for between specific snap id of oracle Awr

Save the below code as awrgenerator.sql (it works for single instance , will work for RAC too but only for 1 instance at a time , Need to run script on both instance )

-- File Name : awrgenerator.sql

-- Author :

-- Sultan Khan

-- Description

-- This script will used to Generate the AWR reports automatically for given snapshots.

-- Last Modified

-- 21 Dec 2018

SET serveroutput ON

SET pagesize 10000

SET linesize 120

SET verify OFF

-- Accept all the required inputs.

ACCEPT begin_snap prompt 'Enter Begin Snap ID : '

ACCEPT end_snap prompt 'Enter End Snap ID : '

ACCEPT snap_int DEFAULT 60 prompt 'Enter the interval in MINS for AWR snapshots (Default 60) : '

ACCEPT rpt_int DEFAULT 60 prompt 'What should be the interval in MINS for each AWR report (Default 60) : '

ACCEPT rpt_type DEFAULT 'HTML' prompt 'Enter the Report Type - HTML or TEXT (Default HTML) : '

ACCEPT out_dir prompt 'Enter output directory path : '

DECLARE

-- Declare all the required values

v_begin_snap number := '&begin_snap';

v_end_snap number := '&end_snap';

v_snap_int number := '&snap_int';

v_rpt_int number := '&rpt_int';

v_rpt_type varchar2(20) := upper('&rpt_type');

v_out_dir varchar2(200) := '&out_dir';

v_snap_delta number;

v_curr_val number;

v_Instance_number v$instance.instance_number%TYPE;

v_Instance_name v$instance.instance_name%TYPE;

v_dbid V$database.dbid%TYPE;

v_file utl_file.file_type;

v_file_name varchar2(200);

BEGIN

SELECT instance_number, instance_name

into v_Instance_number,v_Instance_name

FROM v$instance;

SELECT dbid

INTO v_dbid

FROM v$database;

-- Create a Oracle directory

EXECUTE IMMEDIATE('CREATE OR REPLACE DIRECTORY TEMP_DIR AS '''||v_out_dir||'''');

v_curr_val := v_begin_snap;

v_snap_delta := ceil(v_rpt_int/v_snap_int);

dbms_output.put_line('+----------------------------------------------------+');

dbms_output.put_line('| * * * * * * * AWR Reports Generated * * * * * * * |');

dbms_output.put_line('+----------------------------------------------------+');

dbms_output.put_line('Following AWR Reports generated under directory : '||v_out_dir);

WHILE v_curr_val <= v_end_snap-1

LOOP

BEGIN

v_file_name := 'awr_' || v_Instance_name ||'_'|| v_Instance_number || '_' || v_curr_val || '_' || (v_curr_val+v_snap_delta) ;

IF v_rpt_type = 'TEXT' THEN

v_file_name := v_file_name || '.txt';

v_file := utl_file.fopen('TEMP_DIR', v_file_name, 'w', 32767);

FOR c_awrreport IN (SELECT output FROM TABLE (dbms_workload_repository.awr_report_text( v_dbid, v_Instance_number, v_curr_val, v_curr_val+v_snap_delta)))

LOOP

utl_file.put_line(v_file, c_awrreport.output);

END LOOP;

ELSE

v_file_name := v_file_name || '.html';

v_file := utl_file.fopen('TEMP_DIR', v_file_name, 'w', 32767);

FOR c_awrreport IN (SELECT output FROM TABLE (dbms_workload_repository.awr_report_html( v_dbid, v_Instance_number, v_curr_val, v_curr_val+v_snap_delta)))

LOOP

utl_file.put_line(v_file, c_awrreport.output);

END LOOP;

END IF;

utl_file.fclose(v_file);

dbms_output.put_line(v_file_name);

v_curr_val := v_curr_val + v_snap_delta;

END;

END LOOP;

EXECUTE IMMEDIATE('DROP DIRECTORY TEMP_DIR');

END;

output for the script is as below :

SQL> @awrgenerator.sql
Enter Begin Snap ID : 22
Enter End Snap ID : 24
Enter the interval in MINS for AWR snapshots (Default 60) :
What should be the interval in MINS for each AWR report (Default 60) :
Enter the Report Type - HTML or TEXT (Default HTML) :
Enter output directory path : /oracle/app/orawork
+----------------------------------------------------+
| * * * * * * * AWR Reports Generated * * * * * * * |
+----------------------------------------------------+
Following AWR Reports generated under directory : /oracle/app/orawork
awr_Findb1_1_22_23.html
awr_Findb1_1_23_24.html

PL/SQL procedure successfully completed.

Scripts to generate reports .

SQL script for getting AWR Report on RAC database:

SQL>@$ORACLE_HOME/rdbms/admin/awrgrpt.sql

SQL script for getting AWR Report for single instance:

SQL>@$ORACLE_HOME/rdbms/admin/awrrpt.sql

SQL script for getting ASH Report on RAC database:

SQL>@$ORACLE_HOME/rdbms/admin/ashrpti.sql

SQL script for getting ASH Report for single Instance:

SQL>@$ORACLE_HOME/rdbms/admin/ashrpt.sql

SQL script for getting ADDM Report on RAC database:

SQL>@$ORACLE_HOME/rdbms/admin/addmrpti.sql

SQL script for getting ADDM Report for single instance:

SQL>@$ORACLE_HOME/rdbms/admin/addmrpt.sql

Automatically Generate Hourly AWR report Script - Oracle – AWR, ASH and ADDM reports for RAC

Related Post