We need a database user/pwd
….
But we can generate it using SQLPLUS as well :
AWR Snapshot Reports
Oracle provides reports that you can run to analyze the data in the AWR. These reports are much like the statspack reports prior to Oracle Database 10g. There are two reports: awrrpt.sql and awrrpti.sql, which are available in the directory $ORACLE_HOME/ rdbms/ admin.
The output of these reports is essentially the same, except that awrrpti.sql script allows you to define a specific instance to report on. The reports are much like the statspack reports of old, in that you define a beginning and ending snapshot ID, and the output filename of the report. Additionally, you can opt to produce the report in either text format or HTML format.
SQL> @awrrpt.sql
Current Instance DB Id DB Name Inst Num Instance
———– ———— ——– ————
Specify the Report Type
~~~~~~~~~~~~~~~~~~~~~~~
Would you like an HTML report, or a plain text report?Enter ‘html’ for an HTML report, or ‘text’ for plain textDefaults to ‘html’
Enter value for report_type: text
Type Specified: text Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DB Id Inst Num DB Name Instance Host
———— ——– ———— ———— ————
01 1141957891 1 PROD PROD usa.com
02 1141957891 1 GLD1 LGLD1 ind.com
Using 124707144 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 <return> withoutspecifying a number lists all completed snapshots. Enter value for num_days: 1
Listing the last day’s Completed Snapshots
SnapInstance DB Name Snap Id Snap Started Level
———— ———— ——— —————— —–
PROD PROD
6400 14 Oct 2008 00:01 1
6401 14 Oct 2008 01:00 1
6402 14 Oct 2008 02:00 1
6403 14 Oct 2008 03:00 1
6404 14 Oct 2008 04:00 1
6405 14 Oct 2008 05:01 1
Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 6412
Begin Snapshot Id specified: 6412
Enter value for end_snap: 6413
End Snapshot Id specified: 6413
Specify the Report Name
The default report file name is awrrpt_1_6412_6413.txt. To use this name,press <return> to continue, otherwise enter an alternative.
Enter value for report_name:End of ReportReport written to awrrpt_1_6412_6413.txt
————-
You can see what snapshots are currently in the AWR by using the DBA_HIST_SNAPSHOT view as seen in this example:
SQL> SELECT snap_id, begin_interval_time, end_interval_time FROM dba_hist_snapshot ORDER BY 1;
J.