Re: Alert Log Mining for Downtime

What's the question? It sounds like you've got it all figured out already. Just write a little Perl/sed and you're all set.

Finn

On 11/8/07, JApplewhite_at_austinisd.org <JApplewhite_at_austinisd.org> wrote:
>
>
> Some auditor or other is wanting total downtime for our production
> financials database for the past two fiscal years( Sep 1 thru Aug 31 for
> us). I know of no data dictionary or hidden table that tracks the date/time
> of, for example, every database startup - the shutdowns couldn't accurately
> be recorded because of server crashes, etc. If you know of such an internal
> source, please let me know.
>
> The option that occurs to me is mining all the old alert logs, which we
> have, for database shutdown and database open times. I'd use a text editor
> to filter only alert log lines with the following strings:
> Shutting down instance
> Completed: ALTER DATABASE OPEN
> ... or the lines following the format of...
> Mon Oct 8 05:15:44 2007
>
> I'd use SQL*Loader to load those lines, in order, with a
> sequence-generated ID, into a table. Then I'd use some kind of analytic SQL
> to sum up the time differences between the times just before the Shutting
> down and just after DATABASE OPEN lines. I'd just have to find the last
> recorded time line before a DATABASE OPEN to account for database crashes
> when there's no Shutting down line since the previous DATABASE OPEN line.
>
> I need to automate this because we've bounced that database three times
> per week for years - first as a workaround for an old 8i space leak bug on
> HPUX, then as a workaround for some quirks our Financials software exhibits
> now that it's a 9i DB. Anyway, that makes too many shutdowns and startups
> for manual examination of the alert logs, so I want ot use SQL to do the
> mining.
>
> Any ideas?
>
> Thanks for any guidance on this.
>
> Jack C. Applewhite - Database Administrator
> Austin I.S.D.
> 414.9715 (phone) / 935.5929 (pager)
>

--
http://www.freelists.org/webpage/oracle-l