Generate an AWR Report without using OEM

Posted: May 20, 2014 in UNIX Command Line and SQL PLUS

Good afternoon Oracle fanatics!

Today’s lunchtime special details how to create an AWR Report without using OEM.

The first obvious question is why would you ever want to do this?

And the only logical answer is… your company either doesn’t use OEM or it uses an outdated version of OEM. Since my new job will not be utilizing OEM, I am starting to learn what life will be like without it. So here goes… have some Old School Candy!!

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

Current Instance

   DB Id    DB Name      Inst Num Instance
----------- ------------ -------- ------------
 1194455108 DBS_PRD             1 dbs_prd

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: html

Type Specified:  html

Instances in this Workload Repository schema

   DB Id     Inst Num DB Name      Instance     Host
------------ -------- ------------ ------------ ------------
* 1194455108        1 DBS_PRD      dbs_prd      dbsprd01
  1030901871        1 DBS_PRD      dbs_prd      dbsprd01

Using 1194455108 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> without
specifying a number lists all completed snapshots.
Enter value for num_days: 1
Listing the last days Completed Snapshots
Instance     DB Name       Snap Id   Snap Started   Level
------------ ------------ --------- ------------------ -----
dbs_prd     DBS_PRD         43257 20 May 2014 00:00     1
                             43258 20 May 2014 01:00     1
                             43259 20 May 2014 02:00     1
                             43260 20 May 2014 03:00     1
                             43261 20 May 2014 04:00     1
                             43262 20 May 2014 05:00     1
                            43263 20 May 2014 06:00     1
                             43264 20 May 2014 07:00     1
                             43265 20 May 2014 08:00     1
                             43266 20 May 2014 09:00     1
                            43267 20 May 2014 10:00     1
                             43268 20 May 2014 11:00     1
                             43269 20 May 2014 12:00     1

Specify the Begin and End Snapshot Ids
Enter value for begin_snap: 43266
Begin Snapshot Id specified: 43266
Enter value for end_snap: 43269
End   Snapshot Id specified: 43269
Specify the Report Name
The default report file name is awrrpt_1_43266_43269.html. To use this name,
press <return> to continue, otherwise enter an alternative.
Enter value for report_name: jason_awr
Using the report name jason_awr
--This will spool a huge html version of the report on your UNIX/PUTTY screen.
dbprd01:/home/oracle(dbs_prd)>cp jason_awr.lst jason_awr.html
This creates an html version of the file in case the job doesn’t create it for you.
Use PSFTP or another secure copy function to copy the html file to your Windows file system.
login as: oracle
Using keyboard-interactive authentication.
Remote working directory is /home/oracle
psftp> get jason_awr.html
remote:/home/oracle/jason_awr.html => local:jason_awr.html

Now open the file!

You should see the standard AWR Report page!

Have fun!




Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s