create ADDM(Automatic Database Diagnostic Monitor) report manually

ADDM is an adviser,which detects problems and gives the recommendations. It uses statistical data from AWR,which is stored in sysaux tablespace.These statistical data is stored in the disk by snapshots in every hour(but you can change this parameter which will be shown later) and the retention period by default is 7 days.Be careful because the parameter statistics_level should not have the value "basic"(default is "typical").AWR report which we have discussed earlier gives more detailed information than ADDM report.

To create snapshot manually:

SQL>exec dbms_workload_repository.create_snapshot;

To change retention period(default 7 days) to 3 days and interval(default 1 hr) to 20 minutes:

SQL> exec dbms_workload_repository.modify_snapshot_settings(retention=>4320,interval=>20);

To create report:

C:/> cd %ORACLE_HOME%
C:/> cd rdbms\admin

SQL>@addmrpt.sql
.
.
Enter value for begin_snap:1
.
.
Enter value for end_snap:7
.
.
Enter value for report_name: --default is addmrpt_1_1_7.txt