| Lesson 7 | Size the online redo log files |
| Objective | Determine sizes for online redo logs. |
Redo Logs Online Size Determination
Because of their critical function, the online redo log files are specified at database creation time. To see this, examine the following create database statement:
CREATE DATABASE TOM
CONTROLFILE REUSE
LOGFILE
GROUP 1
('/lu1.3-5/tom_log_01.dbf') SIZE 524288000,
GROUP 2
('/lu1.3-5/tom_log_02.dbf') SIZE 524288000,
GROUP 3
('/lu1.3-5/tom_log_03.dbf') SIZE 524288000
DATAFILE '/disk1/tom_system_01.dbf' SIZE 104857600
REUSE NOARCHIVELOG;
Fortunately, Oracle provides a simple method for re-sizing the online redo log files if you determine that they need to be increased in size. This is performed with the "alter database" commands, where you can drop and re-create redo log files to any size that you desire.
Here is a handy script that will show you the current sizes of your redo log files:
SELECT
a.group#,
substr(b.member,1,30) name,
a.members,
a.bytes,
a.status
FROM
v$log a,
v$logfile b
WHERE
a.group# = b.group#;
GROUP# NAME MEMBERS BYTES STATUS
---------- ------------- ------ --------- ---------
1 /lu1.3-5/samp_log_01.dbf 1 524288000 INACTIVE
2 /lu1.3-5/samp_log_02.dbf 1 524288000 INACTIVE
3 /lu1.3-5/samp_log_03.dbf 1 524288000 CURRENT
Log switch frequency
As a general rule, you should size your redo log files so that they do not switch more frequently than twice per hour.
A redo log switch forces a checkpoint, and this can degrade the performance of your database. You can view the redo_buffer_allocation_retries in V$SYSSTAT to see if your redo log files are too small.
To see the frequency of log switches try:
- V$LOGHIST view
- Oracle alert.log
V$LOGHIST view
The V$LOGHIST view is very useful for viewing the historical data on redo log activity. This view captures the log sequence number, size, and time that each online redo log was switched.
This can be very useful for determining the amount of update activity in your database.
The alert log
The alert log is a valuable tool for monitoring the rate that log switches occur, and subsequently, when checkpoints occur. Oracle recommends sizing the online redo logs such that switches occur no more than three times each hour.
The following is an example of redo logs that are sized to switch about twice per hour:
Tue Aug 10 11:58:40 1999
Thread 1 advanced to log sequence 35889
Current log# 1 seq# 35889 mem# 0: /disk1/tom_log_01.dbf
Tue Aug 10 12:28:55 2000
Thread 1 advanced to log sequence 35890
Current log# 3 seq# 35890 mem# 0: /disk1/tom_log_03.dbf
Tue Aug 10 12:50:20 2001
Thread 1 advanced to log sequence 35891
Current log# 2 seq# 35891 mem# 0: /disk1/tom_log_02.dbf
Tue Aug 10 13:13:35 2002
Thread 1 advanced to log sequence 35892
Current log# 1 seq# 35892 mem# 0: /disk1/tom_log_01.dbf
Now let us explore why sizing the archived redo log file system is important for Oracle performance.
Sizing Redo Log Files - Exercise
