RelationalDBDesign RelationalDBDesign


Tuning Instance   «Prev  Next»
Lesson 8 Size the archived redo log file system
Objective Set appropriate sizes for the archived redo log file system.

Size the Archived Redo Log File System

Support large roll-forward of Database

When sizing the archived redo log file system, it is important to have enough space to support a large roll-forward of the database. For example, if you copy your archived redo logs to tape once per day, and you need enough redo logs to roll-forward through 24 hours of updates, then you want to size your redo logs to hold an average of two days activities. Remember, if Oracle cannot archive a redo log, your entire database will stop and wait, so it is very important that this file system is appropriately sized and periodically archived to tape. The V$LOGHIST view keeps statistics on redo activity. Examine the script below to see an archive of the redo log file system for two days of average redo activity:

set pages 9999;
set feedback on;
set echo on;
drop table temp_loghist;
create table temp_loghist
as
select substr(first_time,1,8) day,
       count(*) switches
from v\$loghist@${db}
group by substr(first_time,1,8);
select max(switches) max,
        round(avg(switches)) avg,
        bytes/1048576 MB,
        (max(switches)*bytes)/1048576 day,
        ((max(switches)*bytes)/1048576)*2 two_day
from temp_loghist, v\$log@${db}
group by bytes
;
Table created.
Filesystem Components

Roll your cursor over the following output table if you are browsing this page on a desktop.
  1. Shows the maximum number of log switches per day
  2. Shows the average number of log switches per day
  3. The redo log size
  4. maximum number of megabytes required per day
  5. Since some DBAs want to size the archived redo log file system to hold two days' worth of updates, this column shows the maximum amount of redo log space over a two-day period

Redo Log Tuning Concepts - Quiz

The next lesson explores common redo log operations.
Before moving on to the next lesson, click the Quiz link below to test your understanding of the archived redo log file system.
Redo Log Tuning Concepts - Quiz