Home » RDBMS Server » Server Administration » rare log archiization issue
rare log archiization issue [message #516391] Sun, 17 July 2011 09:14 Go to next message
piotrtal
Messages: 152
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.
Re: rare log archiization issue [message #516392 is a reply to message #516391] Sun, 17 July 2011 09:20 Go to previous messageGo to next message
BlackSwan
Messages: 23070
Registered: January 2009
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.
Re: rare log archiization issue [message #516393 is a reply to message #516391] Sun, 17 July 2011 09:31 Go to previous messageGo to next message
John Watson
Messages: 4812
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.
Re: rare log archiization issue [message #516394 is a reply to message #516393] Sun, 17 July 2011 09:41 Go to previous messageGo to next message
BlackSwan
Messages: 23070
Registered: January 2009
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?
Re: rare log archiization issue [message #516395 is a reply to message #516394] Sun, 17 July 2011 09:48 Go to previous messageGo to next message
John Watson
Messages: 4812
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.
Re: rare log archiization issue [message #516396 is a reply to message #516393] Sun, 17 July 2011 09:51 Go to previous messageGo to next message
piotrtal
Messages: 152
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 Go to previous messageGo to next message
John Watson
Messages: 4812
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?
Re: rare log archivization issue [message #516398 is a reply to message #516397] Sun, 17 July 2011 11:04 Go to previous messageGo to next message
piotrtal
Messages: 152
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 Go to previous messageGo to next message
Michel Cadot
Messages: 59821
Registered: March 2007
Location: Nanterre, France, http://...
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
Re: rare log archiization issue [message #516419 is a reply to message #516397] Mon, 18 July 2011 01:04 Go to previous messageGo to next message
piotrtal
Messages: 152
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.
Re: rare log archiization issue [message #516421 is a reply to message #516419] Mon, 18 July 2011 01:10 Go to previous message
Michel Cadot
Messages: 59821
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Just informative message, you can ignore them, or remove them setting log_checkpoints_to_alert to FALSE.

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


Current Time: Fri Nov 28 20:55:02 CST 2014

Total time taken to generate the page: 0.12410 seconds