This discussion is archived
1 2 Previous Next 26 Replies Latest reply: Apr 4, 2013 1:48 AM by moslee Go to original post RSS
  • 15. Re: Achivelog Mode Performance
    Hemant K Chitale Oracle ACE
    Currently Being Moderated
    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 Oracle ACE Director
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Oracle ACE
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Oracle ACE
    Currently Being Moderated
    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
    DataBoy Newbie
    Currently Being Moderated
    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 Oracle ACE Director
    Currently Being Moderated
    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 Oracle ACE Director
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    I take off my hat to you there... You have sharp eyes..
1 2 Previous Next

Legend

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