AWR/ADDM/ASH 報告

Case:  Collect the AWR/ADDM/ASH reports:

Between 12 Jul 2010 14:00 and 12 Jul 2010 15:00

01、 ORACLE_awr

# su oracle

# cd $ORACLE_HOME/rdbms/admin

# sqlplus / as sysdba

SQL>@awrrpt

Current Instance

~~~~~~~~~~~~~~~~

   DB Id    DB Name      Inst Num Instance

----------- ------------ -------- ------------

 4246758972 OSSDB               1 ossdb

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

Instances in this Workload Repository schema

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

   DB Id     Inst Num DB Name      Instance     Host

------------ -------- ------------ ------------ ------------

* 4246758972        1 OSSDB        ossdb        osssvr

Using 4246758972 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:3

                                                       Snap

Instance     DB Name        Snap Id    Snap Started    Level

------------ ------------ --------- ------------------ -----

ossdb        OSSDB             525 12 Jul 2010 10:36      1

                                526 12 Jul 2010 12:00      1

                                527 12 Jul 2010 13:00      1

                                528 12 Jul 2010 14:00      1

                                529 12 Jul 2010 15:00      1

Specify the Begin and End Snapshot Ids

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Enter value for begin_snap: 528

Enter value for end_snap: 529  [The best interval: end_snap – begin_snap <= 1 hour ]

Specify the Report Name

~~~~~~~~~~~~~~~~~~~~~~~

The default report file name is awrrpt_1_528_529.html.  To use this name,

press <return> to continue, otherwise enter an alternative.

Enter value for report_name:<return>

02、 ORACLE_ADDM

# su – oracle

% sqlplus / as sysdba

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

Listing the last 3 days of Completed Snapshots

                                                       Snap

Instance     DB Name        Snap Id    Snap Started    Level

------------ ------------ --------- ------------------ -----

ossdb        OSSDB             525 12 Jul 2010 10:36      1

                                526 12 Jul 2010 12:00      1

                                527 12 Jul 2010 13:00      1

                                528 12 Jul 2010 14:00      1

                                529 12 Jul 2010 15:00      1

Specify the Begin and End Snapshot Ids

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Enter value for begin_snap: < Like: 528>

Enter value for end_snap: <Like: 529> [The best interval: end_snap – begin_snap <= 1 hour ]

Specify the Report Name

~~~~~~~~~~~~~~~~~~~~~~~

The default report file name is addmrpt_1_528_529.txt.  To use this name,

press <return> to continue, otherwise enter an alternative.

Enter value for report_name: <return>

End of Report

Report written to addmrpt_1_528_529.txt

03、 ORACLE_ASH

#su - oracle

$sqlplus / as sysdba

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

Current Instance

~~~~~~~~~~~~~~~~

   DB Id    DB Name      Inst Num Instance

----------- ------------ -------- ------------

 1811595752 ORAHW               1 orahw

Specify the Report Type

~~~~~~~~~~~~~~~~~~~~~~~

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

------------ -------- ------------ ------------ ------------

* 1811595752        1 ORAHW        orahw        sun880-1

Defaults to current database

Using database id:

1811595752

Defaults to current instance

Using instance number:

1

ASH Samples in this Workload Repository schema

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Oldest ASH sample available:

30-Aug-11 22:00:43   [    12738

mins in the past]

Latest ASH sample available:

08-Sep-11 18:18:34   [        0

mins in the past]

Specify the timeframe to generate the ASH report

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Enter begin time for report:

--    Valid input formats:

--      To specify absolute begin time:

--        [MM/DD[/YY]] HH24:MI[:SS]

--        Examples: 02/23/03 14:30:15

--                  02/23 14:30:15

--                  14:30:15

--                  14:30

--      To specify relative begin time: (start with '-' sign)

--        -[HH24:]MI

--        Examples: -1:15  (SYSDATE - 1 Hr 15 Mins)

--                  -25    (SYSDATE - 25 Mins)

Defaults to -15 mins

Enter value for begin_time: 07/12/10 14:00:00

Report begin time specified: 07/12/10 14:00:00

Enter duration in minutes starting from begin time:

Defaults to SYSDATE - begin_time

Press Enter to analyze till current time

Enter value for duration: 60 

Report duration specified:   60

…

Enter value for report_name: <return>

SQL>exit