REDO LOG File Resize

You can't resize logfile, you have to create new ones and drop the old ones.

before you begin ensure your database is not experiencing heavy traffic or do it at the time of least traffic probably at night [with peace at your alongside].

 

1.have a look at your current log groups/members:

SELECT a.group#, a.member, b.bytes FROM v$logfile a, v$log b WHERE a.group# = b.group#;

 

2.Make your last redolog group as current [very important] by using manual log switch until you get desired result.

alter system switch logfile;

select group#, status from v$log;

GROUP# STATUS
---------- ----------------
1 INACTIVE
2 INACTIVE
3 INACTIVE
4 CURRENT

 

3 .After making the last group as current start dropping the groups one by one beginning from group number 1. Do it for all except the last group which has been recently made current.If you get any recovery related

312/624 error than carry out a fresh manual checkpoint and proceed again to drop the group.

alter database drop logfile group 1;

4.Recreate your new loggroups with desired sizes & locations. Ensure you do not create any thing below minimum size [10g : minimum 4Mb, default 50Mb, OMF 100MB]. Ensure a correct log size as it may impact

your database performance. Create number of groups as desired

alter database add logfile group 1 (
'path/member1.log', 
'path/member2.log') size 500M reuse;

 

 

5.Carry out a manual log switch. See that newly created group becomes current. Carry out manual checkpoint & log switch until your last old log(the only one left) becomes inactive than drop it.Veryfy new logs. If you

are using RMAN than rman may search for dropped logfiles during your next backup.

 
 do i need to restart my database after these operations. ???

 No need to restart the Database