Re: X$DBGALERTEXT
- From: Niall Litchfield <niall.litchfield@xxxxxxxxx>
- To: lawrence.wolfson@xxxxxxxxxx
- Date: Thu, 5 Jan 2012 12:07:35 +0000
Larry
An excellent question. The table appears to essentially be some sort of
external table built on the log.xml file in the
DIAG_DEST\<db_name>\<instance_name>\alert directory (i.e not on the trace
file version). As such the contents *ought* to be controlled by the purge
policies for the ADR_HOME and or any manual purging you have in place.
However in my quick tests in 11.2.0.3 on windows this doesn't seem to be
the case.
I'd be really grateful as well if there was a clear definitive reference
for what is subject to the short purge policy and what to the long purge
policy - incidents are defined in the docs but the other diag_dest contents
don't seem to be there.
Niall
On Thu, Jan 5, 2012 at 6:20 AM, Wolfson Larry - lwolfs <
lawrence.wolfson@xxxxxxxxxx> wrote:
> Happy New Year!
> I've got a lot of 11.2.0.2 databases and was wondering what controls how
> many days are retained in the internal alert log
> X$DBGALERTEXT
>
> Servers may be
> AIX, DELL, HP, SOLARIS
>
> When I run the code below
>
> COL COUNT(*) FOR 99,999,999 HEA 'R O W S'
> COL DDAY FOR A10 HEA 'D A Y S'
> SELECT DISTINCT TO_CHAR (ORIGINATING_TIMESTAMP,'YYYY-MM-DD') DDAY
> ,COUNT(*)
> FROM X$DBGALERTEXT
> GROUP BY TO_CHAR (ORIGINATING_TIMESTAMP,'YYYY-MM-DD')
> ORDER BY TO_CHAR (ORIGINATING_TIMESTAMP,'YYYY-MM-DD')
> ;
>
> Some DBs have millions of rows back to when they were created
> Some have a few days back.
> Others only return the current day
>
> Now one isn't showing anything for 2012 although I see the data in the
> trace directory alert log
> So that may be a bug.
> I have a another DB on the same server, Dell Red Hat Enterprise Linux
> Server release 5.5 (Tikanga),
> which does show both 2011 and 2012.
>
> And another DB on another DELL server with same OS
> D A Y S R O W S
> ---------- -----------
> 2011-09-22 488
> 2011-09-23 18
> 2011-09-24 9
> 2011-09-25 12
> 2011-09-26 548
> 2011-09-27 12
> 2011-09-28 9
> 2011-09-29 9
> 2011-09-30 2,590
> 2011-10-01 16
> 2011-10-02 9
> 2011-10-03 5,060
> 2011-10-04 18,743,441
> 2011-10-05 1,032
> 2011-10-06 2,538
> 2011-10-07 2,588
> Skipped a lot
> 2011-12-22 1,395
> 2011-12-23 13
> 2011-12-26 968
> 2011-12-27 186
> 2011-12-28 32
> 2011-12-29 9
> 2011-12-30 9
> 2012-01-02 2,936 Works fine
> 2012-01-03 5,465
> 2012-01-04 9,130
> 2012-01-05 3
> 102 rows selected.
>
> While we're usually only interested in the last couple of days, it would
> be nice to go back as far as we'd want to.
>
> TIA
> Larry
>
> ***************************************************************************
> The information contained in this communication is confidential, is
> intended only for the use of the recipient named above, and may be legally
> privileged.
>
> If the reader of this message is not the intended recipient, you are
> hereby notified that any dissemination, distribution or copying of this
> communication is strictly prohibited.
>
> If you have received this communication in error, please resend this
> communication to the sender and delete the original message or any copy
> of it from your computer system.
>
> Thank You.
>
> ****************************************************************************
>
> --
> //www.freelists.org/webpage/oracle-l
>
>
>
--
Niall Litchfield
Oracle DBA
http://www.orawin.info
--
//www.freelists.org/webpage/oracle-l
Other related posts:
