In Oracle formal documentation, redo switch should be between 6-8 in 1 hour. If you have a number than this( its also related what you are running) you need to change your redolog file size.
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;
Sample 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 increasing 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 /
Yorum bırakın
Comments feed for this article