Rebuilding Redo Logs

This topic describes how to rebuild the redo logs.

Once you start tuning, you may have to modify the size of and append the redo logs. In the $ORACLE_BASE/admin/SID/bdump/alert_SID.log file, the rate of switching of redo logs should ideally be every 20-30 minutes. If at dataload time, you notice a switch rate of 30 secs/1minute, Oracle is poorly tuned. The solution is to increase the size (and possibly number) of your redo logs; however, you cannot resize a redo log, you must drop and recreate the log. Remember that one redo log may have two or more members spread across multiple disks.

A user with database administrator privileges can perform these steps.

To drop and add a redo log:

  1. Log into SQL*Plus as system or sys. Select your redo logs to determine how many, which are current, etc. by entering: 

    "select * from v$log;"  
"select * from v$logfile;"

    You cannot alter the current redo log.

    Note whether you have one or more logmembers per redo log.

    Note: We recommend that you have at least 2 valid redo logs at all times. If you have only 2 redo logs, you should construct a new one before deleting one.

  2. To drop a redo log, use a command similar to the following: 

    alter database drop logfile member
'c:\orant\database\log3orcl.ora';
alter database drop logfile
'c:\orant\database\log4orcl.ora';
alter database drop logfile member
'c:\orant\database\log2orcl.ora';

    Note that the logfile is listed by path and filename, and the exact specification will likely vary.

  3. To add a redo log and/or member of a redo log, use something like: 

    alter database oracle
add logfile group 1 'g:\orant\database\redolog01.log'
size 10M reuse;
alter database oracle
add logfile group 3 'g:\orant\database\redolog03.log'
size 10M reuse; 
alter database oracle
add logfile group 4 'g:\orant\database\redolog04.log'
size 10M reuse;

    Note: You must name the database (oracle), the group number, the log location and size.

  4. Now cycle through all the redo logs to make sure all are functional as follows: 

    alter system switch logfile;
select * from v$log;
select * from v$logfile;
alter system switch logfile; .....

    Note: If multiple log members exist in a group, they must be dropped and added as well.