Resizing Redo Logs

[anuragmin]

Hi,
Is there a command to resize redo logs or I need to create news ones with a specified size and drop the older ones.

Are there any precautions I need to take.

Thanks
Anurag

[pando]

you have to create then drop, if you want to be safe shutdown the database then mount it and proceed your create & drop process

[marist89]

You must recreate your logs with the larger size. When dropping your old logs, check the status column of v$log to make sure the log you are trying to drop is not "CURRENT" or "ACTIVE".

select l.group#, lf.member, l.bytes/1024/1024 mb, l.status
from v$logfile lf, v$log l
where l.group# = lf.group#

[amar]

"You must recreate your logs with the larger size. "

Must I, Jeff ?

-amar

[sree_sri]

Hi Amar,
I did't understand why you are laughing. If it is valid then we want to know.

Hi Anuragmin,
Previously if you have 3 redolog groups then

1) Create a new redo log group that has a file size of M
(repeat this step for the 3 groups)

2) Force a couple of log switches so that the current redo log is one of the new ones.

3) Drop the old 3 redo log groups

Fisrst Step
-------------------------------
ALTER DATABASE TESTDB
ADD LOGFILE
GROUP 4 (
'/hfs/oracle/disk01/oradata/TESTDB/redo401TESTDB.log',
'/hfs/oracle/disk03/oradata/TESTDB/redo402TESTDB.log'
) SIZE 2M,
GROUP 5 (
'/hfs/oracle/disk02/oradata/TESTDB/redo501TESTDB.log',
'/hfs/oracle/disk01/oradata/TESTDB/redo502TESTDB.log'
) SIZE 2M,
GROUP 6 (
'/hfs/oracle/disk03/oradata/TESTDB/redo601TESTDB.log',
'/hfs/oracle/disk02/oradata/TESTDB/redo602TESTDB.log'
) SIZE 2M;

Second Step
---------------------------------------
ALTER SYSTEM SWITCH LOGFILE;

Now the group 4 is current and all other 5 groups are not active.

Third Step
---------------------------------
alter database TESTDB
drop logfile group 1;

alter database TESTDB
drop logfile group 2;

alter database TESTDB
drop logfile group 3;

I think these is one of the good way to change logfile size. Good Luck.