The Automatic Workload Repository (AWR) collects and maintains statistics of the database.
We can generate awr report for a particular time frame in the past using the script awrrpt.sql ( located under $ORACLE_HOME/rdbms/admin)
script – @$ORACLE_HOME/rdbms/admin/awrrpt.sql
conn / as sysdba SQL> @$ORACLE_HOME/rdbms/admin/awrrpt.sql Specify the Report Type ~~~~~~~~~~~~~~~~~~~~~~~ AWR reports can be generated in the following formats. Please enter the name of the format at the prompt. Default value is 'html'. 'html' HTML format (default) 'text' Text format 'active-html' Includes Performance Hub active report Enter value for report_type: old 1: select 'Type Specified: ',lower(nvl('&&report_type','html')) report_type from dual new 1: select 'Type Specified: ',lower(nvl('','html')) report_type from dual Type Specified: html old 1: select '&&report_type' report_type_def from dual new 1: select 'html' report_type_def from dual old 1: select '&&view_loc' view_loc_def from dual new 1: select 'AWR_PDB' view_loc_def from dual Current Instance ~~~~~~~~~~~~~~~~ DB Id DB Name Inst Num Instance Container Name -------------- -------------- -------------- -------------- -------------- 2719871177 TESTDB 1 TESTDB TESTDB Instances in this Workload Repository schema ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ DB Id Inst Num DB Name Instance Host ------------ ---------- --------- ---------- ------ * 2719871177 1 TESTDB TESTDB sec60-1 Using 2719871177 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 without specifying a number lists all completed snapshots. Enter value for num_days: 1 Listing the last day's Completed Snapshots Instance DB Name Snap Id Snap Started Snap Level ------------ ------------ ---------- ------------------ ---------- TESTDB TESTDB 4761 08 Sep 2017 00:00 1 4762 08 Sep 2017 01:00 1 4763 08 Sep 2017 02:00 1 4764 08 Sep 2017 03:00 1 4765 08 Sep 2017 04:00 1 4766 08 Sep 2017 05:00 1 4767 08 Sep 2017 06:00 1 4768 08 Sep 2017 07:00 1 4769 08 Sep 2017 08:00 1 4770 08 Sep 2017 09:00 1 4771 08 Sep 2017 10:00 1 4772 08 Sep 2017 11:00 1 4773 08 Sep 2017 12:00 1 4774 08 Sep 2017 13:00 1 4775 08 Sep 2017 14:00 1 4776 08 Sep 2017 15:00 1 4777 08 Sep 2017 16:00 1 4778 08 Sep 2017 17:00 1 Specify the Begin and End Snapshot Ids ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Enter value for begin_snap: 4776 Begin Snapshot Id specified: 4776 Enter value for end_snap: 4777 End Snapshot Id specified: 4777 Specify the Report Name ~~~~~~~~~~~~~~~~~~~~~~~ The default report file name is awrrpt_1_4776_4777.html. To use this name, press to continue, otherwise enter an alternative. Enter value for report_name: 1hrreport.html
For rac databases, the script awrrpt.sql script need to be run again each instance.
For NON-SYSDBA USERS, BELOW GRANTS ARE REQUIRED TO GENERATE AWR REPORT:
SQL> grant connect,SELECT_CATALOG_ROLE to support_id; SQL> grant execute on dbms_workload_repository to support_id;
Much helpful. Perfect steps.
Of a great help cheers
Thanks. What about for a PDB? I’m having trouble getting it to work and I’m not sure if it’s me or a bug.