Home » RDBMS Server » Server Administration » Ideal size of Redo logfiles
Ideal size of Redo logfiles [message #268067] Mon, 17 September 2007 07:37 Go to next message
Aalap
Messages: 4
Registered: September 2007
Location: India
Junior Member
Hello,

We were facing a problem with redo logs.
The alert file showed that the Redo logs were being switched very fast (approx every 2 mins).
Due to fast switching, it sometimes could not find an inactive log file and it gave the error of "Cannot allocate new log". The database became sluggish and users couldnt even connect.

We found a solution to it - keep increasing the size until the error goes away... we did it and it is working fine now...

Now we have 3 redo logfiles of size 300MB each.

The question is:
How can we estimate the best size of redo logfiles ?

Is "Trial and Error" the only best method to find the correct size?

Thanks in advance...
Re: Ideal size of Redo logfiles [message #268078 is a reply to message #268067] Mon, 17 September 2007 08:11 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Increase the redo size to have only 3 to 4 switches per hour.
If this is not possible for you, then ALSO increase the number of logs to avoid "Cannot allocate new log" which is a DBWR problem (it does not write fast enough to record all modified blocks).

Regards
Michel
Re: Ideal size of Redo logfiles [message #268205 is a reply to message #268078] Mon, 17 September 2007 23:59 Go to previous messageGo to next message
Aalap
Messages: 4
Registered: September 2007
Location: India
Junior Member
Michel, Thanks for the note, we increased the size of redo logs and the problem got solved...
My question is - Is there an ideal size for redo logfiles? or are there any rules/queries to find the ideal size of redo logfiles ?

What are the sizes of redo logs in general ?

We have 12 servers and we can't keep doing trial & error on every server to find out the ideal size...

Thanks
Re: Ideal size of Redo logfiles [message #268208 is a reply to message #268067] Tue, 18 September 2007 00:04 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
>We have 12 servers and we can't keep doing trial & error on every server to find out the ideal size...

Why not?

Do all 12 servers have the exact same number of clients & application? If not, how do you quantify the differences?
Re: Ideal size of Redo logfiles [message #268270 is a reply to message #268205] Tue, 18 September 2007 01:49 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
are there any rules/queries to find the ideal size of redo logfiles ?

Michel Cadot wrote on Mon, 17 September 2007 15:11
Increase the redo size to have only 3 to 4 switches per hour.

Isn't this a rule?

Regards
Michel

Re: Ideal size of Redo logfiles [message #268693 is a reply to message #268067] Wed, 19 September 2007 06:58 Go to previous messageGo to next message
Aalap
Messages: 4
Registered: September 2007
Location: India
Junior Member
I guess you guys are right...
We will have to do a trial & error on every server to find out how much does each server require as redo logs..

thanks for your help... looks like i got something to start with atleast Smile
Re: Ideal size of Redo logfiles [message #268707 is a reply to message #268067] Wed, 19 September 2007 07:46 Go to previous messageGo to next message
ara.oracle
Messages: 142
Registered: August 2007
Senior Member
can any one say how to reduce the size of redo log file?
Re: Ideal size of Redo logfiles [message #268710 is a reply to message #268707] Wed, 19 September 2007 07:50 Go to previous messageGo to next message
NandKumar
Messages: 92
Registered: June 2007
Location: v$hyderabad
Member
You cant resize the redo log files... you have to create new ones with the desired size and drop the old ones.

[Updated on: Wed, 19 September 2007 07:51]

Report message to a moderator

Re: Ideal size of Redo logfiles [message #268715 is a reply to message #268067] Wed, 19 September 2007 07:54 Go to previous messageGo to next message
ara.oracle
Messages: 142
Registered: August 2007
Senior Member
so for creating redo log files with desired size,are we need to install the database again?
how you will specify the desired size for redo log files?
Re: Ideal size of Redo logfiles [message #268718 is a reply to message #268715] Wed, 19 September 2007 07:57 Go to previous messageGo to next message
joy_division
Messages: 4642
Registered: February 2005
Location: East Coast USA
Senior Member
http://psoug.org/reference/log_files.html
Re: Ideal size of Redo logfiles [message #268719 is a reply to message #268715] Wed, 19 September 2007 08:05 Go to previous messageGo to next message
NandKumar
Messages: 92
Registered: June 2007
Location: v$hyderabad
Member
Iam afraid....No no need to create the database...You have to create new redo log files using

SQL> alter database add logfile group 4 'd:\nand\redo04.log' size 10m;

For dropping make sure the group is not the current/active one using the v$log view.

SQL> alter database drop logfile group 1;

will drop the logfile.



Re: Ideal size of Redo logfiles [message #268734 is a reply to message #268067] Wed, 19 September 2007 09:01 Go to previous messageGo to next message
ara.oracle
Messages: 142
Registered: August 2007
Senior Member
thank you

if i add new redo logs tell me what must be the database state at that time.
wheather it must in mounted state or open or just the instance had to be started up?
Re: Ideal size of Redo logfiles [message #268735 is a reply to message #268734] Wed, 19 September 2007 09:08 Go to previous messageGo to next message
DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland
Senior Member
Open
Read/write
Re: Ideal size of Redo logfiles [message #268979 is a reply to message #268693] Thu, 20 September 2007 05:38 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
There shouldn't be too much guessing involved for the sizing.
You can use the archive logs, or v$loghist to see when the logfiles switched. You know the size of the log files, so this will let you know how much Redo you are generating per hour. If you want to switch about 4 times an hour at most, size your logfiles to each be 1/4 the size of the Max redo generated per hour.
Previous Topic: Audit Trail specific action
Next Topic: patches
Goto Forum:
  


Current Time: Thu Dec 08 02:37:05 CST 2016

Total time taken to generate the page: 0.09216 seconds