OraFAQ Forum: Server Administration » Ideal size of Redo logfiles

Home » RDBMS Server » Server Administration » Ideal size of Redo logfiles

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Ideal size of Redo logfiles [message #268067]

Mon, 17 September 2007 07:37

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...

Report message to a moderator

Re: Ideal size of Redo logfiles [message #268078 is a reply to message #268067]

Mon, 17 September 2007 08:11

Michel Cadot
Messages: 68686
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

Report message to a moderator

Re: Ideal size of Redo logfiles [message #268205 is a reply to message #268078]

Mon, 17 September 2007 23:59

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

Report message to a moderator

Re: Ideal size of Redo logfiles [message #268208 is a reply to message #268067]

Tue, 18 September 2007 00:04

BlackSwan
Messages: 26766
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?

Report message to a moderator

Re: Ideal size of Redo logfiles [message #268270 is a reply to message #268205]

Tue, 18 September 2007 01:49

Michel Cadot
Messages: 68686
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

Report message to a moderator

Re: Ideal size of Redo logfiles [message #268693 is a reply to message #268067]

Wed, 19 September 2007 06:58

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

Report message to a moderator

Re: Ideal size of Redo logfiles [message #268707 is a reply to message #268067]

Wed, 19 September 2007 07:46

ara.oracle
Messages: 142
Registered: August 2007

Senior Member

can any one say how to reduce the size of redo log file?

Report message to a moderator

Re: Ideal size of Redo logfiles [message #268710 is a reply to message #268707]

Wed, 19 September 2007 07:50

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

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?

Report message to a moderator

Re: Ideal size of Redo logfiles [message #268718 is a reply to message #268715]

Wed, 19 September 2007 07:57

joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA

Senior Member

http://psoug.org/reference/log_files.html

Report message to a moderator

Re: Ideal size of Redo logfiles [message #268719 is a reply to message #268715]

Wed, 19 September 2007 08:05

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.

Report message to a moderator

Re: Ideal size of Redo logfiles [message #268734 is a reply to message #268067]

Wed, 19 September 2007 09:01

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?

Report message to a moderator

Re: Ideal size of Redo logfiles [message #268735 is a reply to message #268734]

Wed, 19 September 2007 09:08

DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland

Senior Member

Open
Read/write

Report message to a moderator

Re: Ideal size of Redo logfiles [message #268979 is a reply to message #268693]

Thu, 20 September 2007 05:38

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.

Report message to a moderator

Previous Topic:	Audit Trail specific action
Next Topic:	patches

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Sep 19 10:34:42 CDT 2024