Resizing redo log files!

Hi all!

Working on Oracle v8i with OS as Win2k/NT.

Is it possible to Resize redo log files!What is the syntax to do it.

Regards,

Amit.

Got this off the net, there is a world of info there if you look.



Assuming you wish to add 2 new groups of 2 members each and each logfile is
going to be 10 meg is size, try this :


code:--------------------------------------------------------------------------------
select max(group) from v$logfile;
--------------------------------------------------------------------------------

This gets the current highest group number - assume 3 for the following.

code:--------------------------------------------------------------------------------
alter database add logfile group 4
('new_logfile_4a.log' size 10m,
'new_logfile_4b.log') size 10m;

alter database add logfile group 5
('new_logfile_5a.log' size 10m,
'new_logfile_5b.log') size 10m;
--------------------------------------------------------------------------------

If you want to add an extra logfile member to each group :

code:--------------------------------------------------------------------------------
alter database add logfile member
'new_logfile_4c' to group 4;

alter database add logfile member
'new_logfile_5c' to group 5;
--------------------------------------------------------------------------------

Next you can get rid of the old ones :

First find out which is current :

code:--------------------------------------------------------------------------------
Select group# from v$log where status = 'CURRENT';
--------------------------------------------------------------------------------

This tells you the curent active group number - you can get rid of the old ones which are
not current as follows :

code:--------------------------------------------------------------------------------
alter database drop logfile group N;
--------------------------------------------------------------------------------

Repeat until all old ones are dropped - except for the current one of course,
which you can't drop !

Then, if a new logfile group is not current :

code:--------------------------------------------------------------------------------
alter system switch logfile;
--------------------------------------------------------------------------------

And check the current group number again - it should be one of the new ones and you
can drop the old group again.

There you have it - an instance with new bigger logfiles. BUT, if the database
is not in archivelog mode, all the redo in the old logs is gone
( so take a backup. Also, because you have changed the database structure,
you will need :

code:--------------------------------------------------------------------------------
alter database backup controlfile to trace;
--------------------------------------------------------------------------------

I presume that if a recovery is required and archivelog mode is on, and one of
the old redo logs (as archived) is required, Oracle will handle it no problems at
all and simply askfor/use the smaller copy of the old logfiles.

Many thanks Sureshy!

I was under the impression that Oracle supported the command: ALTER DATABASE DATAFILE ...... RESIZE.

Won't ALTER DATABASE CLEAR UNARCHIVED LOG FILE work so that it would automatically drop the old ones and recreate new ones.

By issuing the above commands,how would Oracle behave during
recovery.My database is at present in NOArchivelog mode.What happens in case it's in Archivelog mode!

Regards,

Amit.

redo log files are NOT datafiles. They are redo log files.

ALTER DATABASE DATAFILE ...... RESIZE - resizes datafiles inside tablespaces.


ALTER DATABASE CLEAR UNARCHIVED LOG FILE - will NOT work this is equivelant of dropping the redo log file and adding it again exactly the same size. Usually used when a log file is corrupt.

Regarding recovery - It doesn't matter either way.

Archive Log Mode :

if a recovery is required and archivelog mode is on, and one of the old redo logs (as archived) is required, Oracle will handle it no problems at all and simply askfor/use the smaller copy of the old logfiles

No archive log mode

Since you can only go back to a cold, offline backup (out of date) you will have to resize your redo log files again after restore.

I suggest you back your database ASAP after a redo log resize.

is there a need to alter the size of the redo log files? i read somewhere that 20M is actually the standard of one redo log file. and redo log works in a circular way so it is being reused all the time.. with the copy written to the arch directory..

please clarify..

http://download-west.oracle.com/docs...a76965/toc.htm

Quote:

---

Originally posted by yls177
is there a need to alter the size of the redo log files? i read somewhere that 20M is actually the standard of one redo log file. and redo log works in a circular way so it is being reused all the time.. with the copy written to the arch directory..

please clarify..

---

In our production environment one database has redo log file size of 30M and other one 100M.

The key word is 'it depends'. It depends on your application. Look for 'log file switch' wait event and 'check point not completed" msg in your alert log file to tune your redo log files.

wow.. thats great.. thanks for providing the key words to watch out for tuning...

Unless you have lots of massive updates, you might want to consider MORE rather than BIGGER redologs.

We discussed this recently:
http://www.dbasupport.com/forums/sho...threadid=34596