Re: When LOG_ARCHIVE_DEST fills up?

Chris Little <chris.little_at_cho.ge.com> wrote:

>I read a long time ago that when the LOG_ARCHIVE_DEST fills up (there's
>no space left on the disk), that the database comes to a halt. However, I
>forget the details and can't seem to find a detailed description of the
>event in the documentation. I'm wondering what happens to current
>transactions, when does the halt take place (when a the archiver tries to
>write the redo log file or when the log writer tries to write to that
>redo log file again, but discovers that it hasn't been written by the
>archiver yet.)
 

>If anyone can provide the details or point me to the appropriate
>documentation, I would be most grateful. Thanks in advance for your help.
 

>Chris Little
>SAP Basis Admin & Oracle DBA
>GE Fanuc Automation

From the DBA Handbook, p74
The LGWR writes to the redo files in a cyclical fashion; after filling up the first, it goes on to the second and so on. Once it is at the end of the list, it begins writing (or overwriting it) to the first redo file again.

If archiving is performed, it is done by the ARCH background process, it makes a copy of the redo log -- before the LGWR writes over if. If ARCH can not write to where the LOG_ARCHIVE_DEST specified, then the instance locks. Any DB connects will receive a message that archive logs are filled and only SQLDBA Connect intenal is allowed.

If you fill your log files, you can do the following (assuming UNIX)

cp the archive logs to a volume that has space,

compress * the files you copied, you will be amazed how much space is recovered.

rm the files, in the archive directory that you copied.

cp the compressed files back to the archive directory.

Note: File names now end in .Z and are compressed. You will have to uncompress them if you need to use them to roll forward during a recovery.

Another option is to create a CRON that runs every few hours that either 1) does a compress of the archive log directory or 2) back up the archive log directory to tape and remove files in the log directory that are backed up. Received on Fri Aug 11 1995 - 00:00:00 CEST