REDO LOG file size
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 10 of 10

Thread: REDO LOG file size

  1. #1
    Join Date
    Dec 2001
    Location
    USA
    Posts
    620

    Angry

    Hi guys,

    How to determine the file size of REDO LOGs?

    We have DSS kind of system with about 40-50 users and NOT a type of 24X7 environment.

    We had 50 MB of redo log file with 3 members with two groups in each.

    We were observing frequent log checkpointing and too many Archive log files generation.

    Should I increase the size from 50M to 100M?

    Pl. advise.

    Thanks in Adv.

    Sam
    ------------------------
    To handle yourself, use your head. To handle others, use your heart

  2. #2
    Join Date
    May 2000
    Location
    Portsmouth, NH, USA
    Posts
    378

    Talking

    most would say anywhere from 1 checkpoint every 15-30 minutes or so. It would also depend on how important the data is to retrieve. If very important and you have lots of activity, then you want more frequent checkpoints/log-intervals.

    if you take cold-backups every evening, then you don't need the old achived-logs after your cold-backup. Just delete your logs after every cold-backup with a script. I assume you are doing this since you mentioned it is not 24/7.

    - Magnus

  3. #3
    Join Date
    Dec 2001
    Location
    USA
    Posts
    620
    Originally posted by jgmagnus
    most would say anywhere from 1 checkpoint every 15-30 minutes or so. It would also depend on how important the data is to retrieve. If very important and you have lots of activity, then you want more frequent checkpoints/log-intervals.

    if you take cold-backups every evening, then you don't need the old achived-logs after your cold-backup. Just delete your logs after every cold-backup with a script. I assume you are doing this since you mentioned it is not 24/7.

    - Magnus

    Hi Magnus,

    I think, we have very weired log generation pattern. Sometimes, every two minutes, Arch log file is created. Sometimes, it takes 1-2 hours too. In Nov, it generated 450 Arch log files of 50 MB each and in Oct, about 470.

    We take hot backup everyday and weekly cold backup as we cannot shutdown the database every night due to some schedules concurrent programs running that time.

    So, should I increase the size of Redo log file and then change LOG_CHECKPOINT_INTERVAL to the size greater than 100M so that only on filling of Redo log file, log checkpoint occurs?

    Thanks,

    Sam
    ------------------------
    To handle yourself, use your head. To handle others, use your heart

  4. #4
    Join Date
    Dec 2001
    Location
    USA
    Posts
    620
    Originally posted by samdba
    Originally posted by jgmagnus
    most would say anywhere from 1 checkpoint every 15-30 minutes or so. It would also depend on how important the data is to retrieve. If very important and you have lots of activity, then you want more frequent checkpoints/log-intervals.

    if you take cold-backups every evening, then you don't need the old achived-logs after your cold-backup. Just delete your logs after every cold-backup with a script. I assume you are doing this since you mentioned it is not 24/7.

    - Magnus

    Hi Magnus,

    I think, we have very weired log generation pattern. Sometimes, every two minutes, Arch log file is created. Sometimes, it takes 1-2 hours too. In Nov, it generated 450 Arch log files of 50 MB each and in Oct, about 470.

    We take hot backup everyday and weekly cold backup as we cannot shutdown the database every night due to some schedules concurrent programs running that time.

    So, should I increase the size of Redo log file and then change LOG_CHECKPOINT_INTERVAL to the size greater than 100M so that only on filling of Redo log file, log checkpoint occurs?

    Thanks,

    Hi Magnus,

    Are you there?

    Thanks,
    Sam
    ------------------------
    To handle yourself, use your head. To handle others, use your heart

  5. #5
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    Well I wonder how a DSS system can generate arvhive logs
    Or it's a hybrid system?

    If it's DSS you dont really have to put it in archive log mode anyways

  6. #6
    Join Date
    Dec 2001
    Location
    USA
    Posts
    620
    Originally posted by pando
    Well I wonder how a DSS system can generate arvhive logs
    Or it's a hybrid system?

    If it's DSS you dont really have to put it in archive log mode anyways
    Hi Pando,

    I am sorry. It is a kind of hybrid system.

    Thanks,

    Sam
    ------------------------
    To handle yourself, use your head. To handle others, use your heart

  7. #7
    Join Date
    Dec 2001
    Location
    Baltimore, MD
    Posts
    372
    Sam,
    You should have 3 groups with 2 members each.

    You have that type of error because your redo log size is too small to handle the transaction
    load.
    what you need to do is re create your redo log with larger size.
    before you drop your redo log make sure you only drop the redo log with INACTIVE status.

    SQL>COL MEMBER FORMAT A40
    SQL> select f.group#,
    f.member,
    g.status,
    g.bytes bytes_mb
    from v$logfile f, v$log g;

    if they are active do the following.
    SQL> alter system switch logfile;
    SQL> alter database drop logfile group 1;
    REM to delete logfiles from OS
    SQL> HOST DEL C:\oracle\oradata\db_name\redo01.log

    Also LOG_CHECKPOINT_INTERVAL = (set it larger than your redo log) and
    LOG_CHECKPOINT_TIMEOUT=0. These 2 parameters influence how often your log switch.

    Hope this help

    Jean Richard.

  8. #8
    Join Date
    Dec 2001
    Location
    USA
    Posts
    620
    Thanks folks for your precious advise.

    I have increased the size of Redo logs from 50M to 100M. Now, I am monitoring it.

    But, now how can I change the group numbers and name of new log files.

    Earliler, there were 3 groups 1,2 and 3 with 2 members each such as log1.dbf, log1a.dbf, log2.dbf, log2a.dbf, log3.dbf, log3a.dbf.

    I have dropped old groups and their members, files.

    Now, group numbers are 4,5 and 6 and new redo log file names are log4.dbf, log4a.dbf, log5.dbf, log5a.dbf, log6.dbf, log6a.dbf.

    How can I restore old group numbers and old log file names?

    Thanks,
    Sam
    ------------------------
    To handle yourself, use your head. To handle others, use your heart

  9. #9
    Join Date
    Dec 2001
    Location
    USA
    Posts
    620
    Any solution to my renaming thing????

    After increasing Redo log file size from 50m to 100m and also increasing the value of LOG_CHECKPOINT_INTERVAL appropriately, I still see very inconsistancy in Archive log file creation.

    It created some files with 5-10 minutes interval and few files with 1-2 hours.

    So, overall I do not see any advantage of increasing the redo log file size.

    What is the solution?


    Thanks,

    Sam
    ------------------------
    To handle yourself, use your head. To handle others, use your heart

  10. #10
    Join Date
    Oct 2001
    Location
    Pelham, AL
    Posts
    40
    If it is any consolation to you, my redo logs are 256MB each, and my processing load can still make them swap several times per minute (sometimes).

    It is NEVER going to be a constant rate of swapping. Oracle recommends sizing them so that they swap not more than once per hour. It is not a problem if they swap less frequently than that.

    However, it is not always possible to achieve this goal.

    [Edited by ratcheer on 12-11-2001 at 04:11 PM]

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