RAC ASM - Resizing online redo log files
It could be somewhat confusing to try to resize the redolog files when you are new on RAC, especially with ASM. Moreover, the procedures I've seen are not clear enough for new "RACers". So let's simplify this task as much as possible:
# We must drop the redolog 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 redolog files
# Check the redolog groups and their members:
SQL> set linesize 200 pagesize 999
SQL> col member format a80
SQL> select * from v$logfile order by 1, 4;
# We must drop the redolog 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 redolog files
# Check the redolog 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 BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------- ------------ ----------
1 1 227683 52428800 512 2 YES ACTIVE 9923568924 05/02/2014 9923760849 05/02/2014
2 1 227684 52428800 512 2 NO CURRENT 9923760849 05/02/2014 2,8147E+14
3 2 143283 52428800 512 2 YES INACTIVE 9923488956 05/02/2014 9923620049 05/02/2014
4 2 143284 52428800 512 2 NO CURRENT 9923620049 05/02/2014 2,8147E+14
5 3 34875 52428800 512 2 YES INACTIVE 9923271031 05/02/2014 9923515094 05/02/2014
6 3 34876 52428800 512 2 NO CURRENT 9923515094 05/02/2014 2,8147E+14
# Great, groups 1 and 2 belong to thread 1, groups 2 and 3 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 BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------- ------------ ----------
10 1 227689 157286400 512 2 NO CURRENT 9924015408 05/02/2014 2,8147E+14
20 1 227686 157286400 512 2 YES INACTIVE 9923967008 05/02/2014 9923982967 05/02/2014
30 2 143286 157286400 512 2 YES INACTIVE 9923894563 05/02/2014 9923983496 05/02/2014
40 2 143287 157286400 512 2 NO CURRENT 9923983496 05/02/2014 2,8147E+14
50 3 34878 157286400 512 2 YES INACTIVE 9923983073 05/02/2014 9923998604 05/02/2014
60 3 34879 157286400 512 2 NO CURRENT 9923998604 05/02/2014 2,8147E+14
# Now we can recreate the redolog files with the new size, and drop the "temporary" ones:
SQL> alter database add logfile thread 1 group 1 size 150M, group 2 size 150M;
SQL> alter database add logfile thread 2 group 3 size 150M, group 4 size 150M;
SQL> alter database add logfile thread 3 group 5 size 150M, group 6 size 150M;
# Switch logfiles in all instances until none of the "temporary" groups are 'CURRENT' (execute in all the instances as many times as needed):
SQL> alter system switch logfile;
# The "temporary" 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 "temporary" groups are 'INACTIVE', so we can drop them:
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 BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------- ------------ ----------
1 1 227690 157286400 512 2 YES INACTIVE 9924177798 05/02/2014 9924189055 05/02/2014
2 1 227691 157286400 512 2 NO CURRENT 9924189055 05/02/2014 2,8147E+14
3 2 143288 157286400 512 2 YES INACTIVE 9924178388 05/02/2014 9924202862 05/02/2014
4 2 143289 157286400 512 2 NO CURRENT 9924202862 05/02/2014 2,8147E+14
5 3 34880 157286400 512 2 YES INACTIVE 9924178491 05/02/2014 9924217395 05/02/2014
6 3 34881 157286400 512 2 NO CURRENT 9924217395 05/02/2014 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.
Reference:
http://danielrobertosoto.blogspot.com.ar/
No comments:
Post a Comment