Resizing online redo log files in Oracle RAC on ASM
We must drop the redo log files and create new ones with different size.
But before dropping the redolog files, we must create “temporary” ones, because we just can’t let the DB without redo log files.
Check the redo log groups and their members:
SQL set linesize 200 pagesize 999
SQL col member format a80
SQL select * from v$logfile order by 1, 4;
GROUP# STATUS TYPE MEMBER IS_
---------- ------- ------- -------------------------------------------------------------------------------- ---
1 ONLINE +DATA/racdb/onlinelog/group_1.261.794500523 NO
1 ONLINE +FRA/racdb/onlinelog/group_1.257.794500523 YES
2 ONLINE +DATA/racdb/onlinelog/group_2.262.794500523 NO
2 ONLINE +FRA/racdb/onlinelog/group_2.258.794500523 YES
3 ONLINE +DATA/racdb/onlinelog/group_3.265.794500567 NO
3 ONLINE +FRA/racdb/onlinelog/group_3.259.794500567 YES
4 ONLINE +DATA/racdb/onlinelog/group_4.266.794500567 NO
4 ONLINE +FRA/racdb/onlinelog/group_4.260.794500569 YES
5 ONLINE +DATA/racdb/onlinelog/group_5.314.827942215 NO
5 ONLINE +FRA/racdb/onlinelog/group_5.295.827942215 NO
6 ONLINE +DATA/racdb/onlinelog/group_6.315.827942215 NO
6 ONLINE +FRA/racdb/onlinelog/group_6.396.827942215 NO
Ok, we have 6 groups with 2 members each (one in diskgroup +DATA, the other in +FRA).
Now let’s see the thread (instance) of each group, and the status of each group:
SQL select * from v$log order by 1, 2;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- -------- --- --------- ------------- ---------- ------------ ----------
1 1 227683 52428800 2 YES ACTIVE 9923568924 12/09/2020 9923760849 12/09/2020
2 1 227684 52428800 2 NO CURRENT 9923760849 12/09/2020 2,8147E+14
3 2 143283 52428800 2 YES INACTIVE 9923488956 12/09/2020 9923620049 12/09/2020
4 2 143284 52428800 2 NO CURRENT 9923620049 12/09/2020 2,8147E+14
5 3 34875 52428800 2 YES INACTIVE 9923271031 12/09/2020 9923515094 12/09/2020
6 3 34876 52428800 2 NO CURRENT 9923515094 12/09/2020 2,8147E+14
Great, groups 1 and 2 belong to thread 1, groups 3 and 4 to thread 2, and so on.
As you can see, each thread has one of its groups with status CURRENT, and the other one with status ACTIVE or INACTIVE.
As seen in the previous query, the names of the members seemed to have been created automatically. Let’s confirm this:
SQL select name, value from v$parameter where name in ('db_create_file_dest', 'db_recovery_file_dest');
NAME VALUE
------------------------------------ ------------------------------
db_create_file_dest +DATA
db_recovery_file_dest +FRA
Ok, then we will also let Oracle choose the names of the redolog files we are going to create
We have 3 threads (3 instances), so we will need 3 commands to create the redolog groups
Create the “temporary” redolog groups, using numbers 10, 20, 30, 40, 50 and 60 (because 1, 2, 3, 4, 5 and 6 already exist)
SQL alter database add logfile thread 1 group 10 size 150M, group 20 size 150M;
SQL alter database add logfile thread 2 group 30 size 150M, group 40 size 150M;
SQL alter database add logfile thread 3 group 50 size 150M, group 60 size 150M;
Switch logfiles in all instances until none of the old groups are ‘CURRENT’ (execute in all the instances as many times as needed):
SQL alter system switch logfile;
The old groups must be ‘INACTIVE’. If any of them is ‘ACTIVE’, create a checkpoint (execute once, in only one instance):
SQL alter system checkpoint;
Good, now all the old groups are ‘INACTIVE’, so we can drop them:
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;
SQL alter database drop logfile group 5;
SQL alter database drop logfile group 6;
Note: We don’t need to drop the files manually since ASM already did this for us (otherwise, use “ALTER DISKGROUP… DROP FILE…;”)
Let’s check the redolog groups we have:
SQL select * from v$log order by 1, 2;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ------- --- --------- ------------- ---------- ------------ ----------
10 1 227689 157286400 2 NO CURRENT 9924015408 12/09/2020 2,8147E+14
20 1 227686 157286400 2 YES INACTIVE 9923967008 12/09/2020 9923982967 12/09/2020
30 2 143286 157286400 2 YES INACTIVE 9923894563 12/09/2020 9923983496 12/09/2020
40 2 143287 157286400 2 NO CURRENT 9923983496 12/09/2020 2,8147E+14
50 3 34878 157286400 2 YES INACTIVE 9923983073 12/09/2020 9923998604 12/09/2020
60 3 34879 157286400 2 NO CURRENT 9923998604 12/09/2020 2,8147E+14
Now we can recreate the redolog files with the new size, and drop the “temporary” ones:
SQL alter database drop logfile group 10;
SQL alter database drop logfile group 20;
SQL alter database drop logfile group 30;
SQL alter database drop logfile group 40;
SQL alter database drop logfile group 50;
SQL alter database drop logfile group 60;
Let’s check the redolog groups we have now:
SQL select * from v$log order by 1, 2;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- -------- --- ---------- ------------- ---------- ------------ ----------
1 1 227690 157286400 2 YES INACTIVE 9924177798 12/09/2020 9924189055 12/09/2020
2 1 227691 157286400 2 NO CURRENT 9924189055 12/09/2020 2,8147E+14
3 2 143288 157286400 2 YES INACTIVE 9924178388 12/09/2020 9924202862 12/09/2020
4 2 143289 157286400 2 NO CURRENT 9924202862 12/09/2020 2,8147E+14
5 3 34880 157286400 2 YES INACTIVE 9924178491 12/09/2020 9924217395 12/09/2020
6 3 34881 157286400 2 NO CURRENT 9924217395 12/09/2020 2,8147E+14
Note: We don’t need to drop the files manually since ASM already did this for us (otherwise, use “ALTER DISKGROUP… DROP FILE…;”)
The end.
I hope my procedure is the simplest and clearer method you’ve found 🙂
