Return-Path: X-Original-To: oracle-l@orafaq.com Delivered-To: oracle-l@orafaq.com Received: from puck1183.startdedicated.com (localhost [127.0.0.1]) by puck1183.startdedicated.com (Postfix) with ESMTP id CDE2C1961232 for ; Tue, 9 Sep 2014 14:28:57 +0200 (CEST) Received: from turing.freelists.org (freelists-180.iquest.net [206.53.239.180]) by puck1183.startdedicated.com (Postfix) with ESMTP for ; Tue, 9 Sep 2014 14:28:57 +0200 (CEST) Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id D9F202F35D; Tue, 9 Sep 2014 08:28:55 -0400 (EDT) X-Virus-Scanned: Debian amavisd-new at turing.freelists.org Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing.freelists.org [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id EPgEPcn8dgAS; Tue, 9 Sep 2014 08:28:55 -0400 (EDT) Received: from turing.freelists.org (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 002222F29E; Tue, 9 Sep 2014 08:28:14 -0400 (EDT) Received: with ECARTIS (v1.0.0; list oracle-l); Tue, 09 Sep 2014 08:27:34 -0400 (EDT) Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 7977B2F174 for ; Tue, 9 Sep 2014 08:27:33 -0400 (EDT) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing.freelists.org [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id iajDhUmhvxuE for ; Tue, 9 Sep 2014 08:27:33 -0400 (EDT) Received: from mail1.bemta3.messagelabs.com (mail1.bemta3.messagelabs.com [195.245.230.165]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id DD9672EFEF for ; Tue, 9 Sep 2014 08:27:10 -0400 (EDT) Received: from [85.158.137.3:21064] by server-5.bemta-3.messagelabs.com id 45/68-30889-D12FE045; Tue, 09 Sep 2014 12:27:09 +0000 X-Env-Sender: John.Hallas@morrisonsplc.co.uk X-Msg-Ref: server-2.tower-38.messagelabs.com!1410265447!25653270!17 X-Originating-IP: [91.208.239.183] X-StarScan-Received: X-StarScan-Version: 6.11.3; banners=morrisonsplc.co.uk,-,- X-VirusChecked: Checked Received: (qmail 3537 invoked from network); 9 Sep 2014 12:25:39 -0000 Received: from unknown (HELO tlsmail1.morrisonsplc.co.uk) (91.208.239.183) by server-2.tower-38.messagelabs.com with RC4-SHA encrypted SMTP; 9 Sep 2014 12:25:39 -0000 Received: from EXCH1.morrisonsplc.co.uk ([10.3.221.7]) by wmm9999hts001.morrisonsplc.co.uk ([10.3.221.25]) with mapi; Tue, 9 Sep 2014 13:25:07 +0100 From: John Hallas To: "jeremy.schneider@ardentperf.com" , Oracle-L Date: Tue, 9 Sep 2014 13:25:06 +0100 Subject: RE: sysaux purge problems Thread-Topic: sysaux purge problems Message-ID: References: In-Reply-To: Accept-Language: en-US, en-GB Content-Language: en-US X-MS-Has-Attach: X-MS-TNEF-Correlator: acceptlanguage: en-US, en-GB Content-Type: multipart/alternative; boundary="_000_EC65ECF8123FEE4D8FC5B212637C304001667B5F1A27EXCH1morris_" MIME-Version: 1.0 X-archive-position: 56272 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-to: oracle-l-bounce@freelists.org X-original-sender: John.Hallas@morrisonsplc.co.uk Precedence: normal Reply-To: John.Hallas@morrisonsplc.co.uk List-help: List-unsubscribe: List-software: Ecartis version 1.0.0 List-Id: oracle-l X-List-ID: oracle-l List-subscribe: List-owner: List-post: List-archive: X-list: oracle-l --_000_EC65ECF8123FEE4D8FC5B212637C304001667B5F1A27EXCH1morris_ Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable Have a look at the comments on a recent blog entry of mine - This was rel= ated to a 11.2.0.4 database but it did seem to be a bug Sorry I have not got more time to investigate myself - just tidying up my = Oracle-L folder and I noticed this http://jhdba.wordpress.com/2014/07/09/tidying-up-sysaux-removing-old-snaps= hots-which-you-didnt-know-existed/ John From: oracle-l-bounce@freelists.org [mailto:oracle-l-bounce@freelists.org]= On Behalf Of Jeremy Schneider Sent: 05 September 2014 18:36 To: Oracle-L Subject: sysaux purge problems I've reviewed the various MOS docs and bugs related to sysaux purge proble= ms already. I'm just wondering if anyone else out there has specifically = seen inconsistencies where it seems that the AWR is purging fine except fo= r only the ASH table? Or where DBMS_STATS.GET_STATS_HISTORY_AVAILABILITY = reports that data has been purged, but a select on dba_tab_stats_history s= hows otherwise? maybe one of the existing bugs causes this behavior, just seemed to me fro= m the descriptions that get_stats_history_availability is generally accura= te. i'll work the SR and everything, just wondered if this problem was al= ready solved and I just missed the doc. the initial support guys aren't b= ad, but sometimes oracle-l buddies correct my search misses faster. :) btw this is on 11.2.0.3 psu11. =3D=3D=3D=3D=3D=3D=3D=3D AWR/ASH: SQL> select min(begin_interval_time) from dba_hist_snapshot; MIN(BEGIN_INTERVAL_TIME) --------------------------------------------------------------------------= - 2014-05-23 01:30:11 SQL> select min(sample_time) from DBA_HIST_ACTIVE_SESS_HISTORY; MIN(SAMPLE_TIME) --------------------------------------------------------------------------= - 2013-10-09 02:00:01 =3D=3D=3D=3D=3D=3D=3D=3D stats history: SQL> SELECT DBMS_STATS.GET_STATS_HISTORY_AVAILABILITY FROM DUAL; GET_STATS_HISTORY_AVAILABILITY --------------------------------------------------------------------------= - 2014-08-05 00:01:08 SQL> select min(STATS_UPDATE_TIME) from dba_tab_stats_history; MIN(STATS_UPDATE_TIME) --------------------------------------------------------------------------= - 2013-09-15 01:01:06 SQL> select min(savtime) from WRI$_OPTSTAT_HISTHEAD_HISTORY; MIN(SAVTIME) --------------------------------------------------------------------------= - 2013-08-04 21:19:35 -- http://about.me/jeremy_schneider ______________________________________________________________________ Wm Morrison Supermarkets Plc is registered in England with number 358949. = The registered office of the company is situated at Gain Lane, Bradford, W= est Yorkshire BD3 7DL. This email and any attachments are intended for the= addressee(s) only and may be confidential.=20 If you are not the intended recipient, please inform the sender by replyin= g to the email that you have received in error and then destroy the email.= =20 If you are not the intended recipient, you must not use, disclose, copy or= rely on the email or its attachments in any way.=20 This email does not constitute a contract in writing for the purposes of t= he Law of Property (Miscellaneous Provisions) Act 1989. Our Standard Terms and Conditions of Purchase, as may be amended from time= to time, apply to any contract that we enter into. The current version of= our Standard Terms and Conditions of Purchase is available at: http://www= .morrisons.co.uk/gscop Although we have taken steps to ensure the email and its attachments are v= irus-free, we cannot guarantee this or accept any responsibility,=20 and it is the responsibility of recipients to carry out their own virus ch= ecks.=20 ______________________________________________________________________ --_000_EC65ECF8123FEE4D8FC5B212637C304001667B5F1A27EXCH1morris_ Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

Have a look at the comments on a recent blog entry of= mine  - This was related to a 11.2.0.4 database but it did seem to b= e a bug

Sorry I have not got more time to investigate myself – ju= st tidying up my Oracle-L folder and I noticed this

<= p class=3DMsoNormal>http://jhdba.wordpress.com/2014/07/09/tidying-= up-sysaux-removing-old-snapshots-which-you-didnt-know-existed/

 

John

 

F= rom: oracle-l-bounce@freelists.org [mailto:oracle-l-boun= ce@freelists.org] On Behalf Of Jeremy Schneider
Sent: 05 = September 2014 18:36
To: Oracle-L
Subject: sysaux purg= e problems

 

=

I've reviewed the various MOS docs and bugs= related to sysaux purge problems already.  I'm just wondering if any= one else out there has specifically seen inconsistencies where it seems th= at the AWR is purging fine except for only the ASH table?  Or where&n= bsp;DBMS_STATS.GET_STATS_HISTORY_AVAILABILITY reports that data has been p= urged, but a select on dba_tab_stats_history shows otherwise?

 

maybe one of the existing bugs causes this behavior, jus= t seemed to me from the descriptions that get_stats_history_availability i= s generally accurate.  i'll work the SR and everything, just wondered= if this problem was already solved and I just missed the doc.  the i= nitial support guys aren't bad, but sometimes oracle-l buddies correct my = search misses faster.  :)

 

btw this is on 11.= 2.0.3 psu11.

 

=3D=3D=3D=3D=3D=3D=3D=3D AWR/ASH:

SQL> select min(begin_inter= val_time) from dba_hist_snapshot;

MIN(BEGIN_INTERVAL_TIME)

-------------------------------------------------------------------= --------

2014-05-23 01:30:11=

 

=

SQL> select min(sample_time) from DBA_HIST_AC= TIVE_SESS_HISTORY;

MIN(SAMPL= E_TIME)

--------------------= -------------------------------------------------------

2013-10-09 02:00:01

<= p class=3DMsoNormal> 

=3D=3D=3D=3D=3D=3D=3D=3D stats history:

SQL> SELECT DBMS_STATS.GET_STATS_HISTORY_AVAILABI= LITY FROM DUAL;

GET_STATS_HI= STORY_AVAILABILITY

---------= ------------------------------------------------------------------

2014-08-05 00:01:08

<= /div>

 

=

SQL> select min(STATS_UPDATE_TIME) from dba_tab_st= ats_history;

MIN(STATS_UPDAT= E_TIME)

--------------------= -------------------------------------------------------

2013-09-15 01:01:06

=

 

SQL> select min(savtime) from WRI$_OPTSTAT_HISTHEAD_HISTORY;<= o:p>

MIN(SAVTIME)

<= /div>

-------------------------------------------= --------------------------------

2013-08-04 21:19:35

 


______________________________________________________________________
= Wm Morrison Supermarkets Plc is registered in England with number 358949. = The registered office of the company is situated at Gain Lane, Bradford, W= est Yorkshire BD3 7DL. This email and any attachments are intended for the= addressee(s) only and may be confidential.

If you are not the intended recipient, please inform the sender by replyin= g to the email that you have received in error and then destroy the email.=
If you are not the intended recipient, you must not use, disclose, copy or= rely on the email or its attachments in any way.

This email does not constitute a contract in writing for the purposes of t= he Law of Property (Miscellaneous Provisions) Act 1989.

Our Standard Terms and Conditions of Purchase, as may be amended from time= to time, apply to any contract that we enter into. The current version of= our Standard Terms and Conditions of Purchase is available at: http://www= .morrisons.co.uk/gscop

Although we have taken steps to ensure the email and its attachments are v= irus-free, we cannot guarantee this or accept any responsibility,
and it is the responsibility of recipients to carry out their own virus ch= ecks.
______________________________________________________________________
= --_000_EC65ECF8123FEE4D8FC5B212637C304001667B5F1A27EXCH1morris_-- -- http://www.freelists.org/webpage/oracle-l