How to resize Redo Log on RAC instance with ASM option

In this post we will see how we can resize redolog files on RAC instance while we have ASM

1. First check existing status with below query:

select l.group#, l.thread#, f.member, l.archived, l.status, (bytes/1024/1024) fsize
from v$log l, v$logfile f
where f.group# = l.group#
order by 1,2;

Sampe output can be like below:

GROUP# THREAD# MEMBER                                        ARCHIVED    STATUS    MB
—— ——- ———————————             ——– ——— —
1    2    +ORADATA/test/onlinelog/group_8.882.743077403        NO        CURRENT     100
2    2    +ORADATA/test/onlinelog/group_9.610.743077405        YES       INACTIVE    100
3    1    +ORADATA/test/onlinelog/group_10.871.743077407    YES       INACTIVE    100
4    1    +ORADATA/test/onlinelog/group_11.886.743078145    YES       INACTIVE    100

2. Now our purpose is increase redosize from 100Mb to 500 Mb

3. We are going to add our new redo file by below command:

alter database add logfile group 5 ‘+ORADATA’ size 500M;    << run this command on node 2
alter database add logfile group 6 ‘+ORADATA’ size 500M;    << run this command on node 2
alter database add logfile group 7 ‘+ORADATA’ size 500M;    << run this command on node 1
alter database add logfile group 8 ‘+ORADATA’ size 500M;    << run this command on node 1

4. We run below query again

select l.group#, l.thread#, f.member,  l.archived, l.status, (bytes/1024/1024) fsize
from v$log l, v$logfile f
where f.group# = l.group#
order by 1,2;


Sample output can be like below:

GROUP# THREAD# MEMBER                                                                     ARCHIVED     STATUS        MB
—— ——- ———————————                                                     ——–             ———           —
1    2    +ORADATA/test/onlinelog/group_1.882.743077403        NO                CURRENT        100
2    2    +ORADATA/test/onlinelog/group_2.610.743077405       YES              INACTIVE       100
3    1    +ORADATA/test/onlinelog/group_3.871.743077407       YES              INACTIVE       100
4    1    +ORADATA/test/onlinelog/group_4.886.743078145        YES              INACTIVE       100
5    2    +ORADATA/test/onlinelog/group_4.1728.743078149      NO                UNUSED           500
6    2    +ORADATA/test/onlinelog/group_6.1728.743077542     NO                UNUSED           500
7    1    +ORADATA/test/onlinelog/group_7.1728.743078149     NO                  UNUSED         500
8    1    +ORADATA/test/onlinelog/group_5.1728.743077852     NO                  UNUSED        500

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

SQL> alter system switch logfile;

** repeat as necessary until group 5 first is CURRENT **

6. Run the query again to verify the current log group is group 4, if its INACTIVE than you can drop related group safely

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

7. Now drop redo log groups 1, 2, and 3:

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

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

select l.group#, l.thread#, f.member, l.archived, l.status, (bytes/1024/1024) fsize
from v$log l, v$logfile f
where f.group# = l.group#
order by 1,2;

Success..

Thanks,
Kaziul Islam Bulbul
Dhaka, Bangladesh.