Oracle DB 11g Restore Points

Question

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?

Answer 1

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:

begin
  for rp in (SELECT name FROM V$RESTORE_POINT where restore_point_time < sysdate - 30)
  loop
    execute immediate 'drop restore point ' || rp.name;
  end loop;
end;

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:

show parameter db_flashback_retention_target;
show parameter db_recovery_file_dest;
show parameter db_recovery_file_dest_size;

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:

SELECT ESTIMATED_FLASHBACK_SIZE/1024/1024/1024 --in gigabytes
FROM V$FLASHBACK_DATABASE_LOG;

Therefore, if you want to keep flashback logs for 30 days, you would:

alter system set db_flashback_retention_target = 43200 scope=both; 
--43200 = 1440 minutes in a day times 30

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:

select * from  V$FLASH_RECOVERY_AREA_USAGE;

You can read up on restore points, flashback database, and the FRA from the Oracle docs here.