1 2 Previous Next 26 Replies Latest reply: Apr 4, 2013 3:48 AM by moslee Go to original post RSS
      • 15. Re: Achivelog Mode Performance
        Hemant K Chitale
        You shouldn't ever run an AWR report for that long a period !

        685GB in 8.5 days is moderately high (But "how high is high" is relative !!. Some sites get by with 10GB/day some require 100GB/day, some require a few hundred GB per day)

        Consider the I/O impact on your storage of writing an additional 80-90GB/day.

        Given your volume, you need to calculate the disk space required to hold archivelogs for 'n' days -- depending on how many days you want to retain them on disk. Or you could choose to have RMAN delete archivelogs immediately after a backup.

        Note : If your backups go to disk, you need to size disk space for the backups !

        Hemant K Chitale

        If this is a Windows environment, it seems quite busy for Windows.

        Edited by: Hemant K Chitale on Mar 28, 2013 3:23 PM
        Added "If this is a Windows environment...."
        • 16. Re: Achivelog Mode Performance
          Jonathan Lewis
          moslee wrote:

          Many thanks for those advices. I'm amazed that there are so many things to check BEFORE implementing archivelog mode alone, by not checking these things will cause a huge impact on the db.
          Read Robert's advice again, and consider it carefully.
          There aren't "many things to do" - there are only the consequences of designing a RECOVERY and backup strategy.
          a) How often and what mechanism will you use for backup
          b) How would you intend to perform a recovery
          c) How much extra I/O does your strategy introduce at what times of the day and do you have to take evasive action as a consequence

          I note that Robert's first suggestion was to understand the redo generated per day, and the variation during the day; yet your response was to Hemant's note about AWR/Statspack/Redo to quote a number relating to a 9 day interval. That doesn't give me a lot of confidence in your desire to work through the task thoroughly.


          Regards
          Jonathan Lewis
          • 17. Re: Achivelog Mode Performance
            moslee
            Jonathan Lewis wrote:
            moslee wrote:

            Many thanks for those advices. I'm amazed that there are so many things to check BEFORE implementing archivelog mode alone, by not checking these things will cause a huge impact on the db.
            Read Robert's advice again, and consider it carefully.
            There aren't "many things to do" - there are only the consequences of designing a RECOVERY and backup strategy.
            a) How often and what mechanism will you use for backup
            b) How would you intend to perform a recovery
            c) How much extra I/O does your strategy introduce at what times of the day and do you have to take evasive action as a consequence

            I note that Robert's first suggestion was to understand the redo generated per day, and the variation during the day; yet your response was to Hemant's note about AWR/Statspack/Redo to quote a number relating to a 9 day interval. That doesn't give me a lot of confidence in your desire to work through the task thoroughly.


            Regards
            Jonathan Lewis
            Strange...do I need to give you any confidence at all in the first place? Something to ponder...hmmm..
            • 18. Re: Achivelog Mode Performance
              moslee
              Hi Hemant

              Thanks for your advice. Finally back to work on this Achivelog again.

              Will continue to keep you posted of my research. Thanks.
              • 19. Re: Achivelog Mode Performance
                moslee
                Hi Hemant

                You are right. This is a busy Windows. I run the AWR report on 3 single days and redo size tends to double itself due to month end batch jobs. I intend to enable the Flashback database feature (db_flashback_retention_target for 1 day) to complement the current ShadowProtect image backup. Is it true that I only need 1 day of archivelogs? If so, this would mean that I need to size the FRA as well for 1 of day flashback log and 1 day of archivelogs. Based on my current redo size behaviour (162GB at month end), the space for 1 day of archivelogs will suffice?

                My current free space in the server is, (C:) 215GB, (D:) 157GB, (E:) 222GB.

                +30/3/2013 1:00 to 1/4/2013 1:00+
                Redo size:      942,164.5 (per second)     
                     66,815.1 (per transaction)     
                Redo size:      162,769,052,536 (total)     
                     942,164.53 (per second)     
                     66,815.12 (per Trans)     

                +27/3/2013 1:00 to 28/3/2013 1:00+
                Redo size:      943,278.5 (per second)     
                     63,112.4 (per transaction)     
                Redo size:      81,481,945,576 (total)     
                     943,278.52 (per second)     
                     63,112.44 (per Trans)     

                +26/3/2013 1:00 to 27/3/2013 1:00+
                Redo size:      939,696.4 (per second)     
                     66,609.9 (per transaction)     
                Redo size:      81,182,671,432 (total)     
                     939,696.39 (per second)     
                     66,609.89 (per Trans)
                • 20. Re: Achivelog Mode Performance
                  Hemant K Chitale
                  Generally, in my opinion and experience, the reserved space is for a few days of logs. For example 7 days of flashback logs and 2-3 days for archivelogs.
                  You must cater for the possibility of the backup script failing on a day, therefore requiring the archivelogs to be present till the next backup run.

                  It was wise of you to compute the peak archivelog generation based on the month-end.


                  Hemant K Chitale
                  • 21. Re: Achivelog Mode Performance
                    moslee
                    Hi Hemant

                    Thanks for that.

                    I think db_flashback_retention_target for 1 day will be sufficient for my current DB. Reason being if logical error do happen, it is usually the users who will first alert me before I am even aware. Problem will be investigated and I can always flashback the DB as the last resort. So, the time that the logical error starts, to the time investigation ends, I reckon that this will not take more than 1 working day (8hrs). So I think db_flashback_retention_target for 1 day should be sufficient. I can always flashback the database minutes before the cause of the problem. As for the archivelogs, I understand your point that it will fail on a day. So having 2 days of archivelogs should suffice I think. However, I stand to be corrected. Would like to hear your opinion on this. Could you also explain more on why 7 days of flashback logs and 2-3 days for archivelogs?


                    On another note, if db_flashback_retention_target= n day, is there any way that I can know how much flashback log size will be generated for n day?


                    Now I have the "redo size" which will tell me about the volume of archivelogs that will be generated. To be on the safer side, I will take reference from the month end redo size which is 162GB per day. However, from my current free space in the server (C:) 215GB, (D:) 157GB, (E:) 222GB, it is true that I can only have space for 1 day of archivelogs?
                    • 22. Re: Achivelog Mode Performance
                      Hemant K Chitale
                      You'd have to actually monitor flashback area usage to determine the volume of flashback logs after you enable flashback.

                      I believe that 1440 minutes -- 1 day -- is the default value for flashback retention.
                      How much archivelog space you wish to cater for depends on your confidence level in archivelog sizing and backups.


                      Hemant K Chitale
                      • 23. Re: Achivelog Mode Performance
                        Data Boy
                        Monitor your disk space closely after enabling archive log mode. This is big challenge and only reason i can see why databases run in noarchivelog mode. Also if you want to USE RMAN effectively you need have the database in ARCHIVE LOG Mode.
                        • 24. Re: Achivelog Mode Performance
                          Jonathan Lewis
                          moslee wrote:

                          Strange...do I need to give you any confidence at all in the first place? Something to ponder...hmmm..
                          To help you ponder ...
                          If you don't give any indication that you're giving some thought to the advice you're getting then the people giving it are going to stop helping you.

                          Regards
                          Jonathan Lewis
                          • 25. Re: Achivelog Mode Performance
                            Jonathan Lewis
                            moslee wrote:
                            I run the AWR report on 3 single days and redo size tends to double itself due to month end batch jobs.

                            ...
                            +30/3/2013 1:00 to 1/4/2013 1:00+
                            Redo size:      942,164.5 (per second)     
                                 66,815.1 (per transaction)     
                            Redo size:      162,769,052,536 (total)     
                                 942,164.53 (per second)     
                                 66,815.12 (per Trans)     
                            
                            +27/3/2013 1:00 to 28/3/2013 1:00+
                            Redo size:      943,278.5 (per second)     
                                 63,112.4 (per transaction)     
                            Redo size:      81,481,945,576 (total)     
                                 943,278.52 (per second)     
                                 63,112.44  (per Trans)     
                            
                            +26/3/2013 1:00 to 27/3/2013 1:00+
                            Redo size:      939,696.4 (per second)     
                                 66,609.9 (per transaction)     
                            Redo size:      81,182,671,432 (total)     
                                 939,696.39 (per second)     
                                 66,609.89  (per Trans)
                            I'm pleased to see that you've tried to pay more attention to Robert's advice about daily usage.

                            Unfortunately you say "three single days", but show us details from two reports for a single day each, and one report that covers two consecutive days at the end of the month, telling us that month end processing generates double the redo.

                            Should we infer that these are all supposed to represent month-end processing, or should we worry that you've misinterpreted the significance of the redo size because you didn't notice that one of the reports was twice the duration of the others ?


                            Regards
                            Jonathan Lewis.
                            • 26. Re: Achivelog Mode Performance
                              moslee
                              I take off my hat to you there... You have sharp eyes..
                              1 2 Previous Next