OraFAQ Forum: Server Administration » rare log archiization issue

Home » RDBMS Server » Server Administration » rare log archiization issue

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

rare log archiization issue [message #516391]

Sun, 17 July 2011 09:14

piotrtal
Messages: 168
Registered: June 2011
Location: Poland/Czestochowa

Senior Member

hi,

on one of my production environment there is something interesthing which i can't understand. the database is OLTP type used for salary calculations. during the salary calculations period LWR process makes many logswitches, and ARCH process generates about 10 G per hour. and this situation is repetitive for each execution of this calculations. redologs are 100MB.

but for the rest period there is one, two, maybe three maximum archived logs per day. this is a little confusing for me because i know that there is some activity on database and i would like to have one archivelog per half an hour even if the redolog is not full.

how can i force oracle to do archivization thing every half an hour.

MTTR target is set for database for (as i remember) 900 secound, but i think that it don't have anythinkg with my issue.

Report message to a moderator

Re: rare log archiization issue [message #516392 is a reply to message #516391]

Sun, 17 July 2011 09:20

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/

I strongly suspect the LWR process make (extensive?) use of "temporary" tables during salary calculations NEEDLESSLY!

>how can i force oracle to do archivization thing every half an hour.
Make much, much larger REDO logfiles; which I would advise against doing.

Report message to a moderator

Re: rare log archiization issue [message #516393 is a reply to message #516391]

Sun, 17 July 2011 09:31

John Watson
Messages: 8960
Registered: January 2010
Location: Global Village

Senior Member

If peak redo generation is 10G per hour, I would use logfiles of 5G, and set my archive_lag_target parameter to 1800 to force log switches every 30 minutes.

Report message to a moderator

Re: rare log archiization issue [message #516394 is a reply to message #516393]

Sun, 17 July 2011 09:41

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>I would use logfiles of 5G,
If this is done, how often will REDO log file switch during the rest of the day?
Will they EVER switch?
How big are the REDO log files now?

Report message to a moderator

Re: rare log archiization issue [message #516395 is a reply to message #516394]

Sun, 17 July 2011 09:48

John Watson
Messages: 8960
Registered: January 2010
Location: Global Village

Senior Member

The instance will logswitch when a group fills, or after the archive_lag_target has passed: whichever is first.

Report message to a moderator

Re: rare log archiization issue [message #516396 is a reply to message #516393]

Sun, 17 July 2011 09:51

piotrtal
Messages: 168
Registered: June 2011
Location: Poland/Czestochowa

Senior Member

John Watson wrote on Sun, 17 July 2011 09:31

If peak redo generation is 10G per hour, I would use logfiles of 5G, and set my archive_lag_target parameter to 1800 to force log switches every 30 minutes.

Hmm... "archive_lag_target" - thats what i needed.
This is answer on my question. Thanks guys. I will try it on Monday.

When I tryed to get data from RedoLogAdvisor, I was supprised because it recommends me about 10 GB redolog size. I didn' follow this recomendations because I think that 10 GB is value from the sky - it is too far in weight than this database needs, but this is only my supposition. Whole database is about 100 GB so making 10 GB only for Redologs is for me unintelligible.

Thanks guys once again for quick response.

[Updated on: Sun, 17 July 2011 09:52]

Report message to a moderator

Re: rare log archiization issue [message #516397 is a reply to message #516396]

Sun, 17 July 2011 10:05

John Watson
Messages: 8960
Registered: January 2010
Location: Global Village

Senior Member

Quote:

Whole database is about 100 GB so making 10 GB only for Redologs is for me unintelligible.

No, man. Redo size is related to the amount of DML, not to the size of the database. Do what the advisor says. I was only guessing when I wrote 5G.
Right now, you must be log switching every couple of seconds. That is a disaster for performance. Your alert log will have zillions of messages saying something like "checkpoint incomplete".

btw, are you sure you are allowed to use the redolog advisor with your licence?

Report message to a moderator

Re: rare log archivization issue [message #516398 is a reply to message #516397]

Sun, 17 July 2011 11:04

piotrtal
Messages: 168
Registered: June 2011
Location: Poland/Czestochowa

Senior Member

John Watson wrote on Sun, 17 July 2011 10:05

Quote:
Whole database is about 100 GB so making 10 GB only for Redologs is for me unintelligible.
No, man. Redo size is related to the amount of DML, not to the size of the database.

I know this John, and I will tune this instance as soon as possible. This is new installation - the implementation was done by external vendor, and finished about two months ago, but I don't know well this application which is working on this instance yet. Still doing investigation.

John Watson wrote on Sun, 17 July 2011 10:05

Do what the advisor says. I was only guessing when I wrote 5G.
Right now, you must be log switching every couple of seconds. That is a disaster for performance. Your alert log will have zillions of messages saying something like "checkpoint incomplete".

Where do you know that from? You are fortune-teller or something Wink

there isn't too much "checkpoint incomplete" messages in my logs but they occures (i would say) frequently enought. I wondered why they appear in my database logs.

But this messages occures only during not peak period.

John Watson wrote on Sun, 17 July 2011 10:05

btw, are you sure you are allowed to use the redolog advisor with your licence?

hmm...

[Updated on: Sun, 17 July 2011 11:06]

Report message to a moderator

Re: rare log archiization issue [message #516399 is a reply to message #516391]

Sun, 17 July 2011 11:45

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Quote:

would like to have one archivelog per half an hour even if the redolog is not full.

ARCHIVE_LAG_TARGET

Regards
Michel

Report message to a moderator

Re: rare log archiization issue [message #516419 is a reply to message #516397]

Mon, 18 July 2011 01:04

piotrtal
Messages: 168
Registered: June 2011
Location: Poland/Czestochowa

Senior Member

John Watson wrote on Sun, 17 July 2011 17:05

Your alert log will have zillions of messages saying something like "checkpoint incomplete".

btw, are you sure you are allowed to use the redolog advisor with your licence?

sorry John, I was wrong. I didn't have "checkpoint incomplete" messages in my log but somethink like that:

Incremental checkpoint up to RBA [0x41f6.e891.0], current log tail at RBA [0x41f6.ec9d.0]
Mon Jul 18 06:53:30 2011
Incremental checkpoint up to RBA [0x41f6.fb2d.0], current log tail at RBA [0x41f6.ff4f.0]
Mon Jul 18 07:23:36 2011
Incremental checkpoint up to RBA [0x41f6.12153.0], current log tail at RBA [0x41f6.1260f.0]
Mon Jul 18 07:53:43 2011
Incremental checkpoint up to RBA [0x41f6.13530.0], current log tail at RBA [0x41f6.13925.0]

i just checked it.

Report message to a moderator

Re: rare log archiization issue [message #516421 is a reply to message #516419]

Mon, 18 July 2011 01:10

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Just informative message, you can ignore them, or remove them setting log_checkpoints_to_alert to FALSE.

Regards
Michel

Report message to a moderator

Previous Topic:	ORA-02778: Name given for the log directory is invalid
Next Topic:	Grant SELECT through synonym

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Nov 29 06:53:56 CST 2024