Re: Redo logs

Andrew,

Do you want to flush archived redologs or online redologs? If you want to flush the archive directory you will to setup a operating script to do it, and then schedule your script to run after each database backup.

Space for online redologs is pre-allocated, just like Oracle datafiles, so "flushing" the files won't free up any space. In order to free space on your filesystem used by the oneline redologs, you would have to reduce the size and/or number of redolog files.

Oracle requires a minimum of 2 redolog file groups of at least 50Kb each.

You can find out what settings you have by using the following query:

SQL> select group#, sequence#, bytes/1024 Kb, members from v$log;

    GROUP# SEQUENCE# KB MEMBERS ---------- ---------- ---------- ----------

         1     132522      16384          1
         2     132523      16384          1
         3     132521      16384          1
         4     132524      16384          1

Here we have 4 redolog groups of 16Mb each. Each group has one member (file),
meaning we have a total of 64Mb of redolog space. In order to reduce this we can drop all but two logfile groups using:

SQL>alter database drop logfile group n;

If the logfile group you want to drop is currently being used by Oracle it won't let you drop it. You can force Oracle to use a different group with

SQL>alter system switch logfile;

Which will then free the logfile group in question.

Alternatively, or in addition to, you can replace large groups with smaller ones by adding new small groups and then removing the old ones. Add a new group by:

SQL>alter database add logfile thread 1 group 5

    'DUA2:[ORACLE7.DB_DAISY]REDO_5A.RDO' size 8M reuse;

Provided you keep at least two groups available at all times, you should be able to add/remove groups as you system resources allow.

Note however, that by reducing the total redolog space available, you might well solve your disk space problem at the expense of Oracle performance, especially if you are running in archivelog mode. Production databases should
normally be run in archivelog mode and have at least three redolog groups so that one group can be written to, one archived, and one available at all times.

Hope some of that was relevant.

Graham

Andrew Thomas wrote in message <0VqR2.559$GQ2.56251_at_news20.ispnews.com>...
>Is there a way to 'flush' the redo logs? I have a bunch of logs on a
>filesystem which is now 97% full (a script sends me an email warning me of
>this about every 15 minutes). Is there a way to flush these redo logs so
>that it frees space on my filesystem? How can I prevent this from happening
>in the future?
>
>Thanks,
> -Andrew Thomas-
>athomas_at_iminformation.com
>
>
>
Received on Thu Apr 15 1999 - 15:23:49 CDT