AWR Report – How to get it through EM

October 31st, 2013 por Jorge Acevedo Flores
gravatar
Compartir este post:
  • Google Reader
  • Google Plus
  • Identi.ca
  • Meneame
  • PDF
  • Print
  • Twitter
  • Facebook
  • Email
  • LinkedIn

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.

[oaPROD usa.com01 PROD] $ sqlplus ‘/as sysdba’
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.