In this post, i try to explain how we can resize redolog files on RAC instance while we have ASM
1. First check existing status wiht 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
/
Sampe 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
/
2 yorum
Comments feed for this article
Kasım 15, 2011 10:54 pm
john
Hello
Assume,
db_create_file_dest=+DATA
db_create_online_log_dest_1=+LOGS
db_create_online_log_dest_2=+FRA
If I issue, alter database add logfile clause,
Does it create a new logfile in the +DATA diskgroup, logfile in the +LOGS disk group, and a logfile in the +FRA disk group.
OR
a new logfile in the +LOGS disk group, and a logfile in the +FRA disk group
?
Kasım 23, 2011 9:44 am
Gunes Erol
Here is the your answer wiht demo:
SQL> select group_number,name from v$asm_diskgroup;
GROUP_NUMBER NAME
———— ——————————
1 DAT
2 SGFRA
3 ORAFRA
4 ORADATA
SQL> show parameter db_create_file_dest
NAME TYPE VALUE
———————————— ———– ——————————
db_create_file_dest string +ORADATA
SQL> alter system set db_create_online_log_dest_1=’+ORAFRA’;
System altered.
SQL> select group#,member from v$logfile;
GROUP# MEMBER
———- ———–
3 +ORADATA/pri/onlinelog/group_3.263.764940777
2 +ORADATA/pri/onlinelog/group_2.262.764940759
1 +ORADATA/pri/onlinelog/group_1.261.764940741
4 +ORADATA/pri/onlinelog/group_4.270.767899263
5 +ORADATA/pri/onlinelog/group_5.269.767899287
6 +ORADATA/pri/onlinelog/group_6.268.767899307
7 +ORADATA/pri/onlinelog/group_7.267.767899329
7 rows selected.
SQL> alter database add logfile size 100M;
System altered.
SQL> select group#,member from v$logfile;
GROUP# MEMBER
———- ———–
3 +ORADATA/pri/onlinelog/group_3.263.764940777
2 +ORADATA/pri/onlinelog/group_2.262.764940759
1 +ORADATA/pri/onlinelog/group_1.261.764940741
4 +ORADATA/pri/onlinelog/group_4.270.767899263
5 +ORADATA/pri/onlinelog/group_5.269.767899287
6 +ORADATA/pri/onlinelog/group_6.268.767899307
7 +ORADATA/pri/onlinelog/group_7.267.767899329
8 +SGFRA/pri/onlinelog/group_3.271.524940785