Re: Archive Log

A copy of this was sent to "Essam Elsherif" <eelsherif_at_wtgi.com> (if that email address didn't require changing) On Wed, 14 Oct 1998 14:21:26 -0400, you wrote:

>I'm running an ORACLE 8.04 database on NT Server n Archive Log mode. I
>frequenly get the message
>
>"Instance xxxx - Can not allocate log, archival required". It's a warning
>message, but I do not understand why ORACLE can not allocate the archive
>log? There is enough space in the archive log destination disk. How to get
>ride of this message?
>

it could be one of 2 things. If the problem goes away all by itself (eventually, without you doing anything, the log gets allocated) then its because the ARCH process cannot copy the log files to the log destination fast enough. You should in this case:

1. evaluate the number and size of the online redo logs.
2. check for system or I/O contention
3. verify that Oracle's data files, redo logs and archive logs are on their own dedicated and separate disk drives (not co-mingled)
4. check queue lengths c) check CPU waits and usage
5. check for contention in disk / channel / controller levels
6. tune the archiver process - ARCH
7. try increasing archive_log_buffer_size
8. try increasing archive_log_buffer
9. consider spawning multiple archiver processes issue "alter system archive log all"Solution Explanation====================

Most often, increasing the size and the number of online redo log groups will give archiver more time to catch up to log writer. Adding more online logs does not help a situation where the archiver cannot keep up with log writer. It can help if there are bursts of redo generation since it gives archiver more time to average its processing rate over time.

Increasing the number and size of online redo log groups as well as spawning extra archive processes seems to be the most efficient solution.

On the other hand, if the problem does't go away until you manually archive the files then.... The cause of the problem is automatic archiving is disabled. When the
databse is in archive log mode and the redo logs fill up, no activity will be allowed until the log files are archived. If the database is not archiving automatically, activity will cease until the redo logs are manually archived. Redo logs can be manually archived by issuing the command

     ALTER SYSTEM ARCHIVE LOG ALL or you can specify individual log files. See Oracle Server SQL Reference for syntax of the ALTER SYSTEM command and the ARCHIVE LOG clause. A better option is to modify the init.ora and set the parameter 'LOG_ARCHIVE_START=TRUE'. This parameter enables automatic archiving and prevents you from needing to manually archive redo logs every time they fill up.  

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA

--
http://govt.us.oracle.com/    -- downloadable utilities
 
----------------------------------------------------------------------------
Opinions are mine and do not necessarily reflect those of Oracle Corporation
 
Anti-Anti Spam Msg: if you want an answer emailed to you, 
you have to make it easy to get email to you.  Any bounced
email will be treated the same way i treat SPAM-- I delete it.