Tag: resize redo

Resize Redolog On RAC Instance

Posted on by Osama Mustafa in Database
Before I Posted “How To Resize redo log ” This was on Single Instance But Now I show you how to do it on Oracle Real Application Cluster



1.First Run this Query To check size, redo On your RAC :

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. Now our purpose is increase redosize from 100Mb to 300 Mb

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

4.
alter database add logfile group 5 ‘+ORADATA’ size 300M;    <<   node 2
alter database add logfile group 6 ‘+ORADATA’ size 300M;    <<   node 2
alter database add logfile group 7 ‘+ORADATA’ size 300M;    <<   node 1
alter database add logfile group 8 ‘+ORADATA’ size 300M;    <<   node 1

5. Switch until we are into log group 5,6,7,8 so we can drop log groups 1, 2,3 and 4 Until you see the new redo log used .

Thank you
Osama mustafa

Resize Redo Log

Posted on by Osama Mustafa in Database
Here is a newly created database redo log information:

SQL> select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME
———- ———- ———- ———- ———- — —————- ————- ——————-
1 1 17 52428800 1 NO INACTIVE 996238 06/09/2007 22:01:59
2 1 18 52428800 1 NO INACTIVE 1006432 06/09/2007 22:13:32
3 1 19 52428800 1 NO CURRENT 1036439 07/09/2007 09:56:44

SQL> select * from v$logfile;

GROUP# STATUS TYPE MEMBER
———- ——- ——- ————————————————-
3 ONLINE /database/data/redo03.log
2 ONLINE /database/data/redo02.log
1 ONLINE /database/data/redo01.log

Here is how i changed this to five 200M redo logs:

SQL> alter database add logfile group 4 (‘/database/data/redo04.log’) size 200M;
SQL> alter database add logfile group 5 (‘/database/data/redo05.log’) size 200M;

while running following sql commands, if you hit an error like this:

ORA-01623: log 3 is current log for instance RPTDB (thread 1) – cannot drop
ORA-00312: online log 3 thread 1: ‘/database/data/redo03.log’

you should run ” alter system switch logfile;” until current log is 4 or 5. Then execute “alter system checkpoint;”

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

then move (or maybe drop) old redo logs

mv /database/data/redo01.log /database/data/redo01_old.log
mv /database/data/redo02.log /database/data/redo02_old.log
mv /database/data/redo03.log /database/data/redo03_old.log

finally

SQL> alter database add logfile group 1 (‘/database/data/redo01.log’) size 200M;
SQL> alter database add logfile group 2 (‘/database/data/redo02.log’) size 200M;
SQL> alter database add logfile group 3 (‘/database/data/redo03.log’) size 200M;