Reduce db_flashback_retention_target

[groucho47]

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.

[LKBrwn_DBA]

Try this:

alter system set db_flashback_retention_target=10080;

[PAVB]

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.

[groucho47]

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.

[LKBrwn_DBA]

I have reduced the flashback retention target and observed exactly what PAVB stated.