Database Not Generating ASH Reports

Asset ID:	1-72-1529808.1
Update Date:	2017-03-02
Keywords:

Solution Type Problem Resolution Sure

Solution 1529808.1 : Database Not Generating ASH Reports

Applies to:

Exadata Database Machine X2-2 Full Rack - Version All Versions to All Versions [Release All Releases]
Oracle Database - Enterprise Edition - Version 11.2.0.1 to 11.2.0.3 [Release 11.2]
Information in this document applies to any platform.

Symptoms

Running ashrpt fails with following error:

declare
*
ERROR at line 1:
ORA-20200: No ASH samples exist for Database/Instance 221894972/1
ORA-06512: at line 39

* statistics_level parameter is set to TYPICAL

* AWR reports (both instance level and RAC level) are getting generated properly and we can see the accurate data in the reports.

Looking at the underlying tables:

SQL> select count(*) from SYS.WRH$_ACTIVE_SESSION_HISTORY;

COUNT(*)
----------
0

SQL> select * from x$KEWASH;

no rows selected

SQL> select * from x$ash;

no rows selected

SQL> select a.ksppinm parameter,b.ksppstvl value from x$ksppi a,x$ksppcv b where a.indx=b.indx and ksppinm='_ash_enable';

PARAMETER
--------------------------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------
_ash_enable
TRUE

Changes

Possible changes to the AWR settings/Parameters

Cause

Trying to collect ashdump as follows:

SQL> oradebug setmypid
SQL> oradebug dump ashdump 10;
SQL> oradebug tracefile_name

Trace file shows the following:

*** 2013-02-05 05:03:18.236
Processing Oradebug command 'dump ashdump 10'
Active Session History infrastructure is disabled!
Use _ash_enable=true in init.ora to enable ASH!

Solution

Reset the parameter "_ash_enable" as follows:

alter system set "_ash_enable"=false;
alter system set "_ash_enable"=true;

After this ASH report will not have the problem.

Attachments

This solution has no attachment