If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

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.

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.

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.