DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 17

Thread: Logfile switching

  1. #1
    Join Date
    Feb 2000
    Location
    Alexandria, VA, 22314
    Posts
    41

    Red face

    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

  2. #2
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    if ur log is 2048K then set interval to 2048K/512=4000 and set log_checkpoint_timeout to zero

  3. #3
    Join Date
    Feb 2000
    Location
    Washington DC
    Posts
    1,843
    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]

  4. #4
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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,
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  5. #5
    Join Date
    Feb 2000
    Location
    Alexandria, VA, 22314
    Posts
    41
    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

  6. #6
    Join Date
    Feb 2000
    Location
    Alexandria, VA, 22314
    Posts
    41

    Response to jmodic

    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

  7. #7
    Join Date
    Feb 2000
    Location
    Washington DC
    Posts
    1,843
    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]

  8. #8
    Join Date
    Feb 2000
    Location
    Alexandria, VA, 22314
    Posts
    41
    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

  9. #9
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    How did you come to the conclusion that the logs are switching every 2-3 seconds?
    Jeff Hunter

  10. #10
    Join Date
    Feb 2000
    Location
    Alexandria, VA, 22314
    Posts
    41
    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.

    :(((

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width