Background & Overview
At times it may become necessary to resize or recreate your Redo logs, especially if you are switching logs to frequently. The following guide shows how to quickly and easily resize the REDO logs with the database up and running. For the purpose of this guide the following REDO logs will be assumed to exist in the following state. The guide will explain how to resize these redo logs to 1Gb.
G Bytes Data File Name Status Status
---- ----------- ------------------------------------------------------------ -------- --------
1 52428800 +REDO01/YXDOMS02/ONLINELOG/group_1.257.895683449 INACTIVE
1 52428800 +REDO02/YXDOMS02/ONLINELOG/group_1.257.895683449 INACTIVE
2 52428800 +REDO01/YXDOMS02/ONLINELOG/group_2.258.894737155 INACTIVE
2 52428800 +REDO02/YXDOMS02/ONLINELOG/group_2.258.894737155 INACTIVE
3 52428800 +REDO01/YXDOMS02/ONLINELOG/group_3.259.894737155 INACTIVE
3 52428800 +REDO02/YXDOMS02/ONLINELOG/group_3.259.894737155 INACTIVE
4 52428800 +REDO01/YXDOMS02/ONLINELOG/group_4.260.894737155 ACTIVE
4 52428800 +REDO02/YXDOMS02/ONLINELOG/group_4.260.894737155 ACTIVE
Step-By-Step Guide
1. Run the following code to check to see what log is currently in use.
set lines 180
COL group# FOR 999 HEA ' G '
COL bytes FOR 9999999999 HEA ' Bytes '
COL member FOR a60 HEA ' Data File Name '
COL status FOR a8 HEA ' Status '
COL status FOR a8 HEA ' Status '
select distinct
l.group#, l.bytes, ll.member, l.status, ll.status
from v$log l, v$logfile ll
where l.group# = ll.group#
order by l.group#
;
2. Make a note of the current log file group
3. Drop and recreate logfile group 1.
- sqlplus / as sysdba
- SQL> alter database drop logfile group 1;
- SQL> alter database add logfile group 1 ('+REDO01','+REDO02') size 1G;
4. Repeat the process for logfile group 2 and 3.
5. Switch log file group to free up log file group 4.
- sqlplus / as sysdba
- SQL> alter system switch logfile;
- SQL> alter system checkpoint global;
6. Drop and recreate logfile group 4
- sqlplus / as sysdba
- SQL> alter database drop logfile group 4;
- SQL> alter database add logfile group 4 ('+REDO01','+REDO02') size 1G;