Resizing redo logs in 10g

[k007]

Hello,

I need some help in knowing how to increase the size for redo logs on a 10g. Can someone post some pointers for that or steps to do so..

Thanks in advance for your help
--K

[Axr2]

Add new big ones, drop old small ones.

[DaPi]

Better do it at a quiet time:
Add big ones
Switch log files until the little ones not in use
Drop the little ones (will fail if in use)

[adrianomp]

The steps didn't change, it's simply the matter of adding new groups and dropping the old ones. If you're not using OMF you'll have to delete the files on the OS too.

Further, you know have the option to use a log file size recommended by the database itself (select optimal_log_file_size from v$instance_recovery) based on FAST_START_MTTR_TARGET.

[k007]

Thank you Axr2 and DaPi,

This is a small step by step process that I have put up after reading your posts and going through the documentation. Would any one kindly go through and verify them and let me know if they seem ok or need any modifications

SHUTDOWN DATABASE

STARTUP MOUNT

ALTER DATABASE ADD LOGFILE '/oracle/dbs/redo04.log' size 100M;
ALTER DATABASE ADD LOGFILE '/oracle/dbs/redo05.log' size 100M;
ALTER DATABASE ADD LOGFILE '/oracle/dbs/redo06.log' size 100M;
ALTER DATABASE ADD LOGFILE '/oracle/dbs/redo07.log' size 100M;

ALTER SYSTEM SWITCH LOGFILE; ( run this till SELECT GROUP#, STATUS FROM V$LOG; shows
all the older smaller logfiles ( redo01, redo02 and redo03 ) as INACTIVE )

ALTER DATABASE DROP LOGFILE MEMBER '/oracle/dbs/redo01.log';
ALTER DATABASE DROP LOGFILE MEMBER '/oracle/dbs/redo02.log';


ALTER DATABASE OPEN;


Rearrange logfiles so as to have them from number 01

SHUTDOWN DATABASE
mv /oracle/dbs/redo05.log /oracle/dbs/redo01.log
mv /oracle/dbs/redo06.log /oracle/dbs/redo02.log
mv /oracle/dbs/redo07.log /oracle/dbs/redo03.log

STARTUP MOUNT

alter database rename file '/oracle/dbs/redo05.log' TO '/oracle/dbs/redo01.log'
alter database rename file '/oracle/dbs/redo06.log' TO '/oracle/dbs/redo02.log'
alter database rename file '/oracle/dbs/redo07.log' TO '/oracle/dbs/redo03.log'

ALTER DATABASE OPEN;


Thanks
-K

[marist89]

You don't need to shutdown and startup. Just make sure the log you want to drop is not current or active, drop it, rm it, re-create it with the correct size.

OABTW, your logfiles should be multiplexed.

[k007]

Thanks all...

It worked..

-K