Reduce db_flashback_retention_target
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Reduce db_flashback_retention_target

  1. #1
    Join Date
    Apr 2008
    Location
    Pennsylvania
    Posts
    15

    Reduce db_flashback_retention_target

    We have a 3.2 TB data warehouse, v. 10.2.0.3. on Solaris 10. Archivelogging and flashback features are enabled. Our db_recovery_file_dest (FRA) is 1.3 TB, and on a separate array. Of the 1.3 TB, currently about 910G is used, including 870G for flashback files, and 35G for archived logs. RMAN backups are sent to a third array due to a lack of space in the FRA. Each compressed full backup is over 400G in size, and that does not include archive log backups, which are done weekly and usually are another 40 - 60G.

    When I first enabled flashback database features, I did not fully understand the limitations; e.g. you cannot flash back beyond structural changes like dropping tablespaces and datafiles. We have lots of partitioned tables, and each week some partitions and tablespaces are dropped from the database, and new ones added.

    I initially set the db_flashback_retention_target to 14 days, which in our case is overkill. Of course, Oracle will keep even more flashback files in the FRA if space allows. Right now, we have files that in THEORY would allow us to flashback to April 10th, but that is moot, since numerous structural changes were made on May 4th.

    I want to reduce the db_flashback_retention_target to 7 days, since that is likely the longest amount of time we could flash back to. Many times, it would be less. I have reservations about whether it would be safe to use an alter system command to change that parameter; e.g., what would Oracle do in terms of deleting flashback logs older than the target? I have read that there is no way you can directly delete flashback logs.

    Any advice on how to proceed in resetting that parameter will be appreciated. I have no place to test this. I would like to free up space in the FRA and begin backing up RMAN to that location very soon, as the array where those backups are going is being taken out of service.

  2. #2
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,476

    Cool

    Try this:
    Code:
    alter system set db_flashback_retention_target=10080;
    "The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb

  3. #3
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    My take is that by reducing flashback retention target from 14 to 7 days Oracle will figure out there are a lot of logs that are not longer needed to satisfy the new -shorter -flashback retention target then they will become expired.

    I would test it in test environment.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  4. #4
    Join Date
    Apr 2008
    Location
    Pennsylvania
    Posts
    15

    Oh, well

    I already knew the syntax, thank you.
    As stated, I have no place to test this.

    I was just wondering if anyone else out there had ever lowered the value for this parameter, and what they observed afterward.

  5. #5
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,476

    Cool

    I have reduced the flashback retention target and observed exactly what PAVB stated.
    "The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb

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