Oracle 11g ASH报告的生成方法

我就是我 2022-05-28 11:09 210阅读 0赞

数据库报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  
  
脚本执行成功后，会在 **@$ORACLE\_HOME/rdbms/admin/目录下生成指定报告。**