How to resize REDO LOGS
The redo logs record all changes that occurred to the data. Every DB has at least two redo log files because
Oracle writes these files in a circular fashion: when a redo log file is full then Oracle writes in the next, when
the last redo log file is full then Oracle restarts from the first but making sure to store information in the
data file before you overwrite them.
If any anomaly does not allow writing of the changes that occurred to the database in their data file, then
we can safely get those changes from the redo log files. Data changes, therefore, are never lost.
The redo log files represent the history of changes to the data and are vital to the integrity of the DB.
Oracle allows you to have more than one copy for each redo log file: This important feature is called
multiplexing redo log files.
To make the resize of redo logs files procedures to be followed are:
1. Check how many redo logs have been created and the path where they are located:
select group #, bytes / 1024/1024 as MB, status from v $ log;
MB GROUP # STATUS
— — — — — — — — — — — — — — — — — —
1 50 CURRENT
2 50 INACTIVE
3 50 INACTIVE
September lin 220
with MEMBER for a35
select group #, member from v $ logfile;
GROUP MEMBER #
— — — — — — — — — — — — — — — — — — — — — — –
1 /ora01_oracle/ORACLE1/redo01.log
2 /ora01_oracle/ORACLE1/redo02.log
3 /ora01_oracle/ORACLE1/redo03.log
2. Proceed to the creation of new groups of redo logs to the desired size:
alter database add logfile group 4 '/ora01_oracle/ORACLE1/redo04.log' size 512M;
alter database add logfile group 5 '/ora01_oracle/ORACLE1/redo05.log' size 512M;
alter database add logfile group 6 '/ora01_oracle/ORACLE1/redo06.log' size 512M;
3. Verify that the new teams have been created and are in a state UNUSED:
select group #, status from v $ log;
GROUP # STATUS
— — — — — — — — — — — — —
1 CURRENT
2 INACTIVE
3 INACTIVE
4 UNUSED
5 UNUSED
6 UNUSED
4. Run the command (alter system switch logfile;) up to make active the newly created groups
5. Run the command (alter system checkpoint;) to make inactive groups 1,2 and 3
6. Now it is ready to drop the "old" groups:
alter database drop logfile group 1;
alter database drop logfile group 2;
alter database drop logfile group 3;
7. Now you have to go in the path of the dropped file groups and run rm.
cd / ora01_oracle / ORACLE1 /
rm redo01.log
rm redo02.log
rm redo03.log