Re: 8.1.6 goes mad until HD full

"Hannes Erven" <h.e_at_gmx.at> wrote in message news:3bd89790$0$18120$6e365a64_at_newsreader02.highway.telekom.at...
> Folks,
>
>
> I am running 8.1.6 on Linux RedHat 6.2 with all current patches applied.
The
> server is backing a web site with moderate traffic.
>
> At normal times, it writes about one archived redo log per quarter hour
> (500k per piece).
>
> Sometimes it starts writing more and more archived redo, up until 50-100
> archived logs per minute (!!). This fills up the partition rather quick
(<12
> hours) and results in Oracle panicking and exiting.
> Cleaning up archived redo in the meantime delays the crash, but eventually
> oracle is writing archived redo more quickly that you could delete it.
>
>
> I tried executing queries like
>
> select username,first_load_time, to_char(logon_time, 'HH24:MI:SS
> DD.MM.YYYY'), sql_text from v$sqlarea, v$session where address =
sql_address
>
> while the database was "going mad" to identify queries or processes that
> could cause all that - without being successful.
>
>
> On average, every 1-2 weeks oracle crashes due to this problem. Solution
is
> to clean up the archived log directory, startup, shutdowm immediate (or it
> will continue to write archived redo at high speed) and startup again.
> Sometimes it is also necessary to restart the web server software whichs
> runs on a different machine, but is the only source of oracle users.
>
> I can more or less exclude external people connecting to the server as it
is
> behind a firewall that restricts traffic to ssh and http.
>
>
> I hope there is someone out there who has experienced [and solved ;-)] a
> similar problem....
>
> best regards and good night,
> many thanks in advance
> -hannes
>
>

If you 'clean up' the archived redo log (which I assume means delete those fills) you ruin whatever backup and recovery possibilities you have. Did you ever read the chapters on backup and recovery in the Oracle Server Administrators manual?
Your online redolog files are way too small. Make them 5M at least. Then you need to *backup* those archived redolog files, or you could better run that database in noarchivelog, go home, and wait until disaster strikes (other than a crashing database)

Regards,

Sybrand Bakker
Senior Oracle DBA Received on Fri Oct 26 2001 - 00:35:26 CDT