How To Generate AWR Report In Oracle

How To Generate AWR Report In Oracle

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

For NON-SYSDBA USERS, BELOW GRANTS ARE REQUIRED TO GENERATE AWR REPORT:

SQL> grant connect,SELECT_CATALOG_ROLE to MQM;

SQL>  grant execute on dbms_workload_repository to MQM;

Note:

AWR report can be generating in RAC database using 2 scripts awrrpt.sql or awrrpti.sql

awrrpt.sql – > This will generate the one report for the database across all the nodes(i.e for all instances) for a partiular snapshot range.

awrrpti.sql – > This will genereate report for a particular instance, i.e for a 2 node RAC database , there will be two reports( one for each instance).

Following are the scripts that can be executed as sysdba in order to get the AWR, ASH and ADDM reports on Oracle RAC:

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