Generating AWR/ADDM reports at database level and instance level in 11gR2 RAC

Purpose:

Generate AWR/ADDM reports both at instance level and database level for an  Oracle 11g R2 RAC database.

We can query DBA_HIST_SNAPSHOT view to get the snapshot IDs and their begin and end timings.

For eg:

SQL> select SNAP_ID,BEGIN_INTERVAL_TIME, END_INTERVAL_TIME from DBA_HIST_SNAPSHOT where INSTANCE_NUMBER=7 order by 1;


1. Generate AWR Report

a) Instance level

    Use the script awrrpt.sql available in rdbms/admin to generate AWR report for the particular node
    Specify the begin/end snapshots for the report and give a name

$ cd $ORACLE_HOME/rdbms/admin
$ sqlplus sys as sysdba
SQL> @awrrpt.sql

b) Database level

    Use the script awrgrpt.sql available in rdbms/admin to generate AWR report for the entire database where g stands for global
    Specify the begin/end snapshots for the report and give a name

$ cd $ORACLE_HOME/rdbms/admin/
$ sqlplus sys as sysdba
SQL> @awrgrpt.sql

2. Generate ADDM Report

ADDM report can be executed in three different modes in 11gR2. The same package  DBMS_ADDM is being called in all three cases albeit with different procedures.

a. Database Level

Use this mode to analyze all the instances in the RAC

VAR rname VARCHAR2(30);
BEGIN
  :rname := 'ADDM for DB Mar21-23';
  DBMS_ADDM.ANALYZE_DB(:rname,  4877, 4913);
END;
/

where 4877 and 4913 are the begin and end snapshot IDs
Get the report:

set long 1000000

spool ADDM_Full_DB.txt

SELECT DBMS_ADDM.GET_REPORT( :rname) FROM DUAL;

spool off;

b. Instance Level:

It is run just like the DB mode except for that we pass the instance number also as part of parameters.

VAR rname VARCHAR2(30);
BEGIN
  :rname := 'ADDM OHADI 7 for Mar21-23';
  DBMS_ADDM.ANALYZE_INST(:rname,4877,4913, 7);
END;
/

Get the report:

set long 1000000

spool ADDM_Instance.txt

SELECT DBMS_ADDM.GET_REPORT( :rname) FROM DUAL;

spool off;

We can run addmrpt.sql script under $ORACE_HOME/rdbms/admin and get the same instance level output.

c. Partial Mode:

VAR rname VARCHAR2(30);
BEGIN
  :rname := 'ADDM OHADI 7 and 8 for Mar21-23';
  DBMS_ADDM.ANALYZE_PARTIAL(:rname, '7,8', 4877,4913);
END;
/

where 7 and 8 are instance name

set long 1000000

spool ADDM_Partial.txt

SELECT DBMS_ADDM.GET_REPORT( :rname) FROM DUAL;

spool off;

Note:

We can still make use of awrrpti.sql and addmrpti.sql to generate AWR and ADDM reports at instance level. These scripts would prompt for DBID and Instance number