Printable View
Hi.
I have a slight irritation. I have set the size of the logfiles on the Oracle database to 2048K (2MB). However, the Oracle database insists on switching the logfile every 3->5 seconds, even though the 2MB logfile is not full up.
This occurs because of a checkpoint - I have checked the init.ora file and looked at the LOG_CHECKPOINT_INTERVAL parameter - what should this be set to as to avoid the continous switching.
Thanks
Keith and Ken
if ur log is 2048K then set interval to 2048K/512=4000 and set log_checkpoint_timeout to zero
Pando,
Just wondering, How did you come up with that figure ? Remember the solution we have come up couple of days ago for 'Standby' thread.
Jmodic is talking about the same.
[Edited by sreddy on 01-03-2001 at 04:21 PM]
The checkpoint never triggers the log switch! It's the other way around: log switch allways implicitly triggers checkpoint. So you must have at least one checkpoint per log switch, but if the init parameters LOG_CHECKPOINT_INTERVAL or LOG_CHECKPOINT_TIMEOUT are set to proper values then checkpoints can occur while log file is not full yet, but this doesn't trigger a log switch.
You say your log switches occur while log files are not completely full. How do you know this? Are you in archivelog mode? What are the sizes of archived log files? AFAIK log switch occurs only when the current log file is full unless you force it with ALTER SYSTEM SWITCH LOG FILE. There is no init parameters that can force a periodic log switches.
You say you have a log switch switch every 2-3 seconds. This is a far too frequent. I would increase the size of log files by at least 10-20 times. How many log groups do you have? Do you see any messages like "Can not allocate new log. Checkpoint not complete" in your alert file? If you have archiving on, can the archiver cope with such log switch frequency?
About LOG_CHECKPOINT_INTERVAL: it means how many blocks in the log file have to be written before a checkpoint is forced. Blocks here mean operating system blocks (usualy 512 bytes), not db blocks. If this parameter is set to a higher value then the size of your log files (in your case 4096) then the checkpoint will occure only when log switch occurs, unless the checkpoint is triggered by the LOG_CHECKPOINT_TIMEOUT parameters, which means the period in seconds between the forced checkpoints.
HTH,
Thanks for your response.
I have done this - I set LOG_CHECKPOINT_INTERVAL to 999999999, and then to 5000 - the logs keep switching. I rebooted the Oracle Instance both times, and checked the parameters :(
log_checkpoint_timeout is set to 0 - this is VERY CURIOUS.
WHY SHOULD THEY BE SWITCHING SO MUCH? IS THERE ANY OTHER PARAMETER I SHOULD SET?
Thanks again
Keith
The log file size was INITIALLY 512K - the switching was 2->3 seconds.
log_checkpoint_interval = 32000
log_checkpoint_timeout = 1800
I then increased the log file size to 8192 (8MB) - the switching was 2->3 seconds.
log_checkpoint_interval = 9999999
log_checkpoint_timeout = 0
I then decreased this to 2096K (2MB) - the switching is still 2->3 seconds.
log_checkpoint_interval = 5000
log_checkpoint_timeout = 0
I have not got the log files multiplexed - are there any known problems with Oracle?
Thanks, Keith
R U sure you don't have that much transaction rate on your database ? Why I am asking this Q is, we confirmed, that there is no otherway of switching the log file except using command 'ALTER SYSTEM SWITCH LOG FILE'
LOG_CHECKPOINT_TIMEOUT=0 disables the parameter. Has no effect.Not recommended as it disables time based check points.
LOG_CHECKPOINT_INTERVAL should be a # interms of your OS block size. If this value exceeds redolog size checkpoint occurs only when 'LOG SWITCH' occurs.
Try LOG_CHECKPOINT_INTERVAL = 4 if your redolog size is 2048K.
Looks something goofy goin on behind the scenes...
[Edited by sreddy on 01-03-2001 at 04:45 PM]
I am positive the amount of transactions is not this high. I am the only one connected to the instance, testing some code.
The fact that the same switching regualrity occurs no matter the size of the log file is VERY CONFUSING.
Thanks again.
Keith
How did you come to the conclusion that the logs are switching every 2-3 seconds?
I queried the v$log dictionary table.
Over a couple of minutes, the logs (I have 3 logfiles) were switching at this rate.
I have just added another member to each group, to see if multiplexing fixed the problem - BUT THIS MADE NO DIFFERENCE.
:(((
true indeed... thinking about checkpoint because karmst mentioned the log interval parameter and the log is not full :D
karmst when u query v$log which column are you looking at to determine that it has switched? Log files doesnt switch unless it´s full or you do it manually.
I would check alert log to see if it´s switching every 3 seconds
No multiplexing will not make a difference.
The rate at which the logs get filled is directly proportional to the number of transactions that have taken place. The only way a logfile will not get filled is if someone issues an "alter system switch logfile".
If you look at v$loghist and post the results of:
select thread#, sequence#, first_change#, to_char(first_time,'mm/dd/yyyy hh24:mi:ss') first_time, switch_change#
from v$loghist
where first_time > to_date('01/02/2001','mm/dd/yyyy')
If you see that the logs are indeed switching every 2-3 seconds, I would then look in my alert.log file. If you are getting a bunch of "Checkpoint not complete" messages, your logs are not big enough and you probably don't have enough of them.
Keith (karmst),
Obviously there is something extremely odd going on with your instance ;-). Since online redo files are allways the same size, the only way (AFAIK) to be shure how much information is written in the log when log switch occur is to look at archived redo logs. Archived logs are not simply a coppy of online logs - their size reflects the amount of data actually written in the redo log when log switch occured. That is why I asked you if you have archiving turned on.
If you run in noarchive mode then just for testing purposes set the database in archivelog mode and switch automatic archiving on. Then observe the sizes of archived logs. Are they of uniform size? Equal in size to online redo logs or smaller?
BTW, I just remembered in some previous versions there were problems when LOG_CHECKPOINT_INTERVAL vas set to 0. It doesn't behaved like it shud, i.e. it didn't disabled time-based log switches. In fact it had the oppositeeffect - it caused extremely frequent checkpoints (checkpoints, not log switches). If you have this parameter set to 0 try it with a very high value instead.
My db is in no archive log mode and yet consistent log switch happens every 14 minutes..
My init.ora parameters related to log..
log_archive_start boolean FALSE
log_archive_trace integer 0
log_buffer integer 163840
log_checkpoint_interval integer 999999999
log_checkpoint_timeout integer 0
log_checkpoints_to_alert boolean FALSE
I tried posing the scenario once last month and I got answers that could not solve this..
looks like this is a better time..
My db is 8.1.6
Thanks
Maybe there is a running script that is forcing log switch like hot backup script etc. Can you verify? Also verify if your hot backup is running, provided ARCHIVELOG mode is on, by doing select on v$backup and check for ACTIVE status. Backup also generate stuff for redo logs.
[Edited by goodhealth on 01-03-2001 at 10:23 PM]
Nothing of that sort. (No backups available) Its a new database and is recently furnished..
Thx..
Are you running Management Server that happens to have its Repository on that database (or any other such repository) ? If yes then that generates a lot of transactions depending upon what the refresh rate of the monitored jobs is.
You may want to look into the v$sessions view at different times to see what sessions (unusual sessions) are there and what are they doing.
In case of Management server, you may want to increase the refresh interval to reduce transaction information.
- Rajeev