Generating AWR for TNPMW Oracle DB

Answer

You will need to connect to Oracle using sqlplus as 'sys' or 'system' user to do this.

The steps to run the script looks something like this:

1) cd to a working directory, such as the /tmp directory. This will serve as the directory where the final report is saved into.

Log in to the database using sqlplus:

sqlplus system/<password>@<SID>

For this example, the SID is 'vtdb'.

2)  run the script:

SQL>   @$ORACLE_HOME/rdbms/admin/awrrpt.sql

You will see something like below:

Current Instance
~~~~~~~~~~~~~~~~

DB Id    DB Name      Inst Num Instance
----------- ------------ -------- ------------
986219312 VTDB                1 vtdb

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 text
Defaults to 'html'
Enter value for report_type:

Type Specified:  html

Hit enter to select the default ( HTML is the default type)

3) At this stage, you will see this message:

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> without
specifying a number lists all completed snapshots.

Enter value for num_days:

Enter the number of days of snapshot you want to see. If you want to see two days of snapshots, enter '2'  to get the snapshot for 2 days.

4)  You should see something like this:

Listing the last day's Completed Snapshots
                                                   Snap
Instance     DB Name        Snap Id    Snap Started    Level
------------ ------------ --------- ------------------ -----
vtdb         VTDB            1590 31 Dec 2007 00:00      2
                          1591 31 Dec 2007 01:00      2
                          1592 31 Dec 2007 02:00      2
                          1593 31 Dec 2007 03:00      2
                          1594 31 Dec 2007 04:00      2
                          1595 31 Dec 2007 05:00      2
                          1596 31 Dec 2007 06:01      2
                          1597 31 Dec 2007 07:00      2
                          1598 31 Dec 2007 08:00      2
                         1599 31 Dec 2007 09:00      2
                          1600 31 Dec 2007 10:00      2
                          1601 31 Dec 2007 11:01      2
                          1602 31 Dec 2007 12:00      2
                          1603 31 Dec 2007 13:00      2
                          1604 31 Dec 2007 14:00      2
                          1605 31 Dec 2007 15:00      2
                          1606 31 Dec 2007 16:01      2

Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap:

Here, you need to enter the start of the snapshot ( begin_snap).  For
example, the output above, for 31 Dec 2007 11:01. the snapshot number is
1601.  

5) After you enter the begin_snap, you will be prompted for the end_snap:

Enter value for end_snap:

Just enter the snapshot number required.

6)   Now, you will see a message asking for a report name. Just hit enter
to accept the default.  It will take a minute or two to complete. The report
will be saved in the directory where you run the sqlplus in, eg. /tmp, and
it looks something like 'awrrpt_xxxxxxxxx.html'.

7) Send the 'awrrpt_xxxxxx.html' to support for further analysis on the DB performance issue.