Resize Oracle Database Online Redo Logfiles

1. check no of redo log groups active.

SELECT vl.group#, member, bytes, vl.status FROM v$log vl, v$logfile vlf WHERE vl.group#=vlf.group#

Sample Output
=============

SQL> SELECT vl.group#, member, bytes, vl.status FROM v$log vl, v$logfile vlf WHERE vl.group#=vlf.group#;

GROUP# MEMBER BYTES STATUS
---------- ---------------------------------------- ---------- ----------------
4 /oradata/data03/log04a.dbf 52428800 CURRENT
4 /oradata/data03/log04b.dbf 52428800 CURRENT
3 /oradata/data03/log03a.dbf 52428800 INACTIVE
3 /oradata/data03/log03b.dbf 52428800 INACTIVE
2 /oradata/data03/log02a.dbf 52428800 INACTIVE
2 /oradata/data03/log02b.dbf 52428800 INACTIVE
1 /oradata/data03/log01a.dbf 52428800 INACTIVE
1 /oradata/data03/log01b.dbf 52428800 INACTIVE


2. Add two new groups (with two members each). The group's log file size will be 1G:

alter database add logfile group 5 ('/oradata/data03/log05a.dbf','/oradata/data03/log05b.dbf') size 1G;

alter database add logfile group 6 ('/oradata/data03/log06a.dbf','/oradata/data03/log06b.dbf') size 1G;

3. Switch until we are into log group 5 or 6, so we can drop log groups 1, 2, 3 and 4

alter system switch logfile;
select group#, status from v$log;

** repeat as necessary until group 5 or 6 is CURRENT **

Sample Output
============

SQL> select group#, status from v$log;

GROUP# STATUS
--------- ----------------
1 INACTIVE
2 INACTIVE
3 INACTIVE
4 INACTIVE
5 CURRENT
6 UNUSED

4. Now drop redo log groups 1,2,3 and 4.

** only do this step when groups are INACTIVE

alter database drop logfile group 1;
alter database drop logfile group 2;
alter database drop logfile group 3;
alter database drop logfile group 4;

Verify the groups were dropped, and the new groups' sizes are correct.

select group#, bytes, status from v$log;


Sample Output
============

GROUP# BYTES STATUS
--------- --------- ----------------
5 10485760 CURRENT
6 10485760 UNUSED

5. Drop redo log file from OS leave.

rm /oradata/data03/log04a.dbf
rm /oradata/data03/log04b.dbf
rm /oradata/data03/log03a.dbf
rm /oradata/data03/log03b.dbf
rm /oradata/data03/log02a.dbf
rm /oradata/data03/log02b.dbf
rm /oradata/data03/log01a.dbf
rm /oradata/data03/log01b.dbf

6. Add groups 1, 2, 3 and 4 (with two members each). The group's log file size will be 1G:

alter database add logfile group 1 ('/oradata/data03/log01a.dbf','/oradata/data03/log01b.dbf') size 1G;
alter database add logfile group 2 ('/oradata/data03/log02a.dbf','/oradata/data03/log02b.dbf') size 1G;
alter database add logfile group 3 ('/oradata/data03/log03a.dbf','/oradata/data03/log03b.dbf') size 1G;
alter database add logfile group 4 ('/oradata/data03/log04a.dbf','/oradata/data03/log04b.dbf') size 1G;


7. Switch until we are into log group 1 or 2 or 3 or 4, so we can drop log groups 5 and 6.

alter system switch logfile;
select group#, status from v$log;

** repeat as necessary until group 5 or 6 is CURRENT **

Sample Output
============

SQL> select group#, status from v$log;

GROUP# STATUS
--------- ----------------
1 CURRENT
2 UNUSED
3 UNUSED
4 UNUSED
5 INACTIVE
6 INACTIVE

8. Now drop redo log groups 5 and 6.

** only do this step when groups are INACTIVE

alter database drop logfile group 5;
alter database drop logfile group 6;


Verify the groups were dropped, and the new groups' sizes are correct.

select group#, bytes, status from v$log;


Sample Output
============

SQL> select group#, bytes, status from v$log;

GROUP# BYTES STATUS
---------- ---------- ----------------
1 52428800 CURRENT
2 52428800 UNUSED
3 52428800 UNUSED
4 52428800 UNUSED

9. Drop redo log file from OS leave.


rm /oradata/data03/log05a.dbf
rm /oradata/data03/log05b.dbf
rm /oradata/data03/log06a.dbf
rm /oradata/data03/log06b.dbf