Collect AWR report

How to Collect Standard Diagnostic Information Using AWR Reports for Performance Issues ( Doc ID 1903158.1 )

Gathering an AWR report for a Performance Issue

To gather an AWR report using the AWR report generation script: $ORACLE_HOME/rdbms/admin/awrrpt.sql. As with ADDM, you can run an AWR report as any user as long as they have the following roles and privileges:
•ADVISOR , SELECT_CATALOG_ROLE  and EXECUTE ON dbms_workload_repository .

For example, to allow the SCOTT user to both generate AWR snapshots and submit ADDM recommendation jobs, the user will need to be granted the following (you may skip this if you have already granted the privileges for the ADDM section above):

# sqlplus / as sysdba
 GRANT ADVISOR TO scott;
 GRANT SELECT_CATALOG_ROLE TO scott;
 GRANT EXECUTE ON dbms_workload_repository TO scott;

Once this is done you can generate the report by changing to $ORACLE_HOME/rdbms/admin/ , logging in and executing the awrrpt.sql script:

# cd $ORACLE_HOME/rdbms/admin/
# sqlplus SCOTT/TIGER
SQL> START awrrpt.sql

The report will prompt whether you want HTML or TXT (choose HTML), Begin and End Snapshot Ids and a report name (if you do not wish to select the default).
By default the report is written to the current directory and will be called : awrrpt_1_BEGIN_SNAP_END_SNAP.txt for example awrrpt_1_1531_1532.html
The report will output the location once it has completed.

Note: If you do not have permissions on the current folder, the report will not be produced and you will receive a message similar to the following:
SP2-0606: Cannot create SPOOL file "awrrpt_1_1531_1532.html"