I am posting here on the forum for the first time… Please feel free to point me to the correct section of the website, if "DBA" is the wrong one.
So I work as a Business Intelligence Consultant, but my current project role focuses more on various database development tasks. One of these (it is more of a housekeeping task) is removing all restore points from the database that are older than 30 days.
I am not very familiar with databases in general.
- Could someone point me in the right direction?
- Is there some way to achieve this using PLSQL/SQL?
- For example, a loop that runs every day at a specific time and gets rid of too old restore points in the DB?
Best Answer
That depends:
Restore Points are nothing more than pointers to flashback logs in the FRA. If you are creating these pointers via:
create restore point <rpname>
, then you can do something like:However, if you are not explictly creating these restore points, and you are just concerned about space consumption and reclamation, then take a look at the FRA:
The relationship between these parameters is as follows:
The flashback logs will be stored in db_recovery_file_dest.
db_flashback_retention_target is how long (in minutes) you want to keep these flashback logs for.
However, the logs will only consume space until the size specified in db_recovery_file_dest_size is reached.
You can estimate how large to make db_recovery_file_dest_size with the following query:
Therefore, if you want to keep flashback logs for 30 days, you would:
After some amount of time, check the estimated_flash_back_size in v$flashback_database_log, and size db_recovery_file_dest_size appropriately. The flashback logs will be cleaned up automatically without the need for any sort of cleanup script. When sizing the FRA, keep in mind that you may be storing other things in the FRA (like RMAN backups), which you can check with:
You can read up on restore points, flashback database, and the FRA from the Oracle docs here.