1 2 Previous Next 20 Replies Latest reply: Feb 14, 2013 8:24 PM by Hemant K Chitale RSS

    Big Redo

    yxes2013
      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
          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
            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
              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
                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
                  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
                    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
                      >
                      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
                        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
                          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
                            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
                              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
                                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
                                  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
                                    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