This discussion is archived
1 2 Previous Next 20 Replies Latest reply: Feb 14, 2013 6:24 PM by Hemant K Chitale RSS

Big Redo

yxes2013 Newbie
Currently Being Moderated
Hi All,

11gR2
SLES 11

What is the advantage/disadvantage of big REDO?
Supposing I will make my redo log with size of 5Gb, so that Even if I do not make my DB in archivelog mode,
I still can recover my point in time crash recovery for the 1 day transaction. Since all the 1 day changes are contained in the 3 log files of total of 15G.


Thanks a lot,

Xyz
  • 1. Re: Big Redo
    Mihael Pro
    Currently Being Moderated
    Supposing I will make my redo log with size of 5Gb, so that Even if I do not make my DB in archivelog mode,
    I still can recover my point in time crash recovery for the 1 day transaction. Since all the 1 day changes are contained in the 3 log files of total of 15G.
    crash recovery does not depend on size of redo logs, only its speed
    if you mean media recovery from cold backup, then keep in mind that redo log size is limited, but you can add more redo groups
  • 2. Re: Big Redo
    yxes2013 Newbie
    Currently Being Moderated
    Thanks michael :)
    if you mean media recovery from cold backup, then keep in mind that redo log size is limited, but you can add more redo groups
    Yes what i mean is instance recovery. Its the one that you do not need to archivelog because all your trasactions for the day are intact in the online redologs.

    Would you know the largest redo log size I can create?

    Edited by: yxes2013 on 13.2.2013 1:35
  • 3. Re: Big Redo
    Step_Into_Oracle_DBA Explorer
    Currently Being Moderated
    Xyz,

    Keeping Redo Logs a bigger size is not an recommended advice . If a redo log switch is happening 20-30 minutes once then its fine. Bigger Redo logs will take longer time to recover.

    Below doc will help you to estimate the Size of Redo Logs.

    1038851.6 (How to Estimate Size of Redo Logs)

    And Also From 10g onwards we have redo log size advisory.

    10g New Feature: REDO LOGS SIZING ADVISORY [ID 274264.1]


    Mark your Post as Answered or Helpful if Your question is answered.

    Thanks & Regards,
    SID

    (StepIntoOracleDBA)

    Email : stepintooracledba@gmail.com

    http://stepintooracledba.blogspot.in/

    http://www.stepintooracledba.com/
  • 4. Re: Big Redo
    Mihael Pro
    Currently Being Moderated
    Would you know the largest redo log size I can create?
    http://docs.oracle.com/cd/E14072_01/server.112/e10820/limits002.htm#i287915
  • 5. Re: Big Redo
    Nicolas.Gasparotto Oracle ACE
    Currently Being Moderated
    You can easily have huge redolog file. Especially useful when batches are running to avoid overloading of switch.
    And when batches are not running, i.e. day time - transaction, you can always limit the time of switch up to the defined value of init parameter archive_lag_target.

    Nicolas.
  • 6. Re: Big Redo
    yxes2013 Newbie
    Currently Being Moderated
    Thanks nick,

    How do you automate switching of small redo to big redo and vice-versa? I am afraid I might mess up my PROD db if I constantly do in everyday manually. :(
  • 7. Re: Big Redo
    rp0428 Guru
    Currently Being Moderated
    >
    How do you automate switching of small redo to big redo and vice-versa? I am afraid I might mess up my PROD db if I constantly do in everyday manually
    >
    See the last reply in this thread
    How to Resize Redo log groups

    You can't change the size of the files in the log group. You need to create a new group of the size you want and then delete the files from the old group once they become inactive.

    So to automate that you need a procedure that does those steps.
  • 8. Re: Big Redo
    yxes2013 Newbie
    Currently Being Moderated
    Yes dear, I have tried that already but it is so tideous to do manually. I do not recommend it switching redo logs back and forth :(
    I better resize the redo as 1gb each. to accomodate both batch and oltp.
    Maybe I can ask Bill to include this option in 12g database :)

    Thanks
  • 9. Re: Big Redo
    Nicolas.Gasparotto Oracle ACE
    Currently Being Moderated
    I don't know how Bill can help here, but I know that setting archive_lag_target to 20 minutes force the log switch every 20 minutes regardless the size of the redo.

    Nicolas.
  • 10. Re: Big Redo
    yxes2013 Newbie
    Currently Being Moderated
    I am confused :(

    What is the advantage of switching the logs every 20 minutes and switching it every hour?Is it for the goal of speedup instance crash recovery?
  • 11. Re: Big Redo
    Step_Into_Oracle_DBA Explorer
    Currently Being Moderated
    yxes2013 wrote:
    I am confused :(

    What is the advantage of switching the logs every 20 minutes and switching it every hour?Is it for the goal of speedup instance crash recovery?
    Yes when Database crashes and while coming up it has to read the redo logs for the data for last 1 hour to do crash recovery, so duration taken for crash recovery will be more.

    A redo switch which happens approximately every 20 mins is a ideal one.

    Thanks & Regards,
    SID

    (StepIntoOracleDBA)

    Email : stepintooracledba@gmail.com

    http://stepintooracledba.blogspot.in/

    http://www.stepintooracledba.com/
  • 12. Re: Big Redo
    Mihael Pro
    Currently Being Moderated
    I am confused :(

    What is the advantage of switching the logs every 20 minutes and switching it every hour?Is it for the goal of speedup instance crash recovery?
    Yes if you are not using incremental checkpoints.
  • 13. Re: Big Redo
    yxes2013 Newbie
    Currently Being Moderated
    Hi All,

    Can you share what are the sizes of your PROD redo logs? I am just curious how did you size it. I know it depends on how active is your database.
    Is it default 50Mb? or 100Mb or 200Mb or 500Mb or 1Gb.
  • 14. Re: Big Redo
    Step_Into_Oracle_DBA Explorer
    Currently Being Moderated
    In one of our PROD database which is of 1 TB size, we use 1GB redo log size. But it depends upon ur database load. You can start from 100m and then gradually increase if you get frequent errors like checkpoint cannot be completed.

    Thanks & Regards,
    SID

    (StepIntoOracleDBA)

    Email : stepintooracledba@gmail.com

    http://stepintooracledba.blogspot.in/

    http://www.stepintooracledba.com/
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points