Oracle 11g ASH報告的生成方法

資料庫報ORA-600錯誤

Wed Mar 23 15:28:50 2016
Errors in file /u01/app/oracle/diag/rdbms/gtfdb/gtfdb2/trace/gtfdb2_ora_8946.trc  (incident=135114):
ORA-00600: ?????í?ó?ú??, ????: [kolrghte: hash table not exist], [], [], [], [], [], [], [], [], [], [], []
Incident details in: /u01/app/oracle/diag/rdbms/gtfdb/gtfdb2/incident/incdir_135114/gtfdb2_ora_8946_i135114.trc
想檢視報錯時間前後15分鐘的ASH報告

生成方法：

[oracle@GTFDB2 admin]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Thu Mar 24 14:01:06 2016

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, Oracle Label Security,
OLAP, Data Mining, Oracle Database Vault and Real Application Testing options

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

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

   DB Id    DB Name Inst Num Instance
----------- ------------ -------- ------------
 4241661131 GTFDB 2 gtfdb2

Specify the Report Type
~~~~~~~~~~~~~~~~~~~~~~~
Enter 'html' for an HTML report, or 'text' for plain text
--選擇生成格式，預設為html
Defaults to 'html'
Enter value for report_type: 

Type Specified:  html

Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

   DB Id     Inst Num DB Name   Instance Host
------------ -------- ------------ ------------ ------------
* 4241661131    2 GTFDB   gtfdb2 GTFDB2
  4241661131    1 GTFDB   gtfdb1 GTFDB1

Defaults to current database


Using database id: 4241661131

Enter instance numbers. Enter 'ALL' for all instances in a
RAC cluster or explicitly specify list of instances (e.g., 1,2,3).
Defaults to current instance.

Using instance number(s): 2

ASH Samples in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Oldest ASH sample available:  03-Mar-16 23:59:55   [  29641 mins in the past]
Latest ASH sample available:  24-Mar-16 14:01:18   [  0 mins in the past]


Specify the timeframe to generate the ASH report
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter begin time for report:
--選擇開始時間，這裡輸入的是03/23/16 13:13:00，時間格式是[MM/DD[/YY]] HH24:MI[:SS]，預設值為15分鐘之前
--    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: 03/23/16 13:13:00
Report begin time specified: 03/23/16 13:13:00

Enter duration in minutes starting from begin time:
--輸入開始時間後的時間區間，這裡輸入的是30分，預設為到當前時間
Defaults to SYSDATE - begin_time
Press Enter to analyze till current time
Enter value for duration: 30
Report duration specified:   30

Using 23-Mar-16 13:13:00 as report begin time
Using 23-Mar-16 13:43:00 as report end time

Specify Slot Width (using ashrpti.sql) for 'Activity Over Time' section
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

-- Explanation:
--   In the 'Activity Over Time' section of the ASH report,
--   the analysis period is divided into smaller slots
--   and top wait events are reported in each of those slots.

-- Default:
--   The analysis period will be automatically split upto 10 slots
--   complying to a minimum slot width of
--     1 minute,  if the source is V$ACTIVE_SESSION_HISTORY or
--     5 minutes, if the source is DBA_HIST_ACTIVE_SESS_HISTORY.

Specify Slot Width in seconds to use in the 'Activity Over Time' section:
Defaults to a value as explained above:
Slot Width specified:

Specify Report Targets (using ashrpti.sql) to generate the ASH report
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

-- Explanation:
--   ASH Report can accept "Report Targets",
--   like a particular SQL statement, or a particular SESSION,
--   to generate the report on. If one or more report targets are
--   specified, then the data used to generate the report will only be
--   the ASH samples that pertain to ALL the specified report targets.

-- Default:
--   If none of the report targets are specified,
--   then the target defaults to all activity in the database instance.

Specify SESSION_ID (eg: from V$SESSION.SID) report target:
Defaults to NULL:
SESSION report target specified:

Specify SQL_ID (eg: from V$SQL.SQL_ID) report target:
Defaults to NULL: (% and _ wildcards allowed)
SQL report target specified:

Specify WAIT_CLASS name (eg: from V$EVENT_NAME.WAIT_CLASS) report target:
[Enter 'CPU' to investigate CPU usage]
Defaults to NULL: (% and _ wildcards allowed)
WAIT_CLASS report target specified:

Specify SERVICE_HASH (eg: from V$ACTIVE_SERVICES.NAME_HASH) report target:
Defaults to NULL:
SERVICE report target specified:

Specify MODULE name (eg: from V$SESSION.MODULE) report target:
Defaults to NULL: (% and _ wildcards allowed)
MODULE report target specified:

Specify ACTION name (eg: from V$SESSION.ACTION) report target:
Defaults to NULL: (% and _ wildcards allowed)
ACTION report target specified:

Specify CLIENT_ID (eg: from V$SESSION.CLIENT_IDENTIFIER) report target:
Defaults to NULL: (% and _ wildcards allowed)
CLIENT_ID report target specified:

Specify PLSQL_ENTRY name (eg: "SYS.DBMS_LOB.*") report target:
Defaults to NULL: (% and _ wildcards allowed)
PLSQL_ENTRY report target specified:

Specify the Report Name
--指定報表名稱，直接回車系統會自動生成名稱
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is ashrpt_2_0323_1343.html.  To use this name,
press to continue, otherwise enter an alternative.
Enter value for report_name: 

Using the report name ashrpt_2_0323_1343.html

Summary of All User Input
-------------------------
Format       : HTML
DB Id       : 4241661131
Inst num       : 2
Begin time     : 23-Mar-16 13:13:00
End time       : 23-Mar-16 13:43:00
Slot width     : Default
Report targets : 0
Report name    : ashrpt_2_0323_1343.html