- Posted by Gavin Soorma
- On June 18, 2009
- 1 Comments
Normal Restore Points
If the db_recovery_dest_file_size has been allocated less space then what is required for the flashback transaction logs as dictated by the db_flashback_retention_target parameter, then the flashback logs are deleted to free up space for the additional logs that are being generated.
Alert log will show also that flashback logs are being deleted
Deleted Oracle managed file /u02/oradata/testdb/TESTDB/flashback/o1_mf_51nc8494_.flb
Deleted Oracle managed file /u02/oradata/testdb/TESTDB/flashback/o1_mf_51nc86n3_.flb
Deleted Oracle managed file /u02/oradata/testdb/TESTDB/flashback/o1_mf_51nc8963_.flb
Flashback then MAY fail
SQL> flashback database to scn 1580000;
flashback database to scn 1580000
ERROR at line 1:
ORA-38729: Not enough flashback database log data to do FLASHBACK.
Guaranteed Restore Points
With guaranteed restore points, logs are not deleted, but if the space allocated for the flashback logs is not adequate, the database may hang until more space is allocated
The parameter db_recovery_file_dest_size is dynamic and can be increased on the fly to allocate more space for the flashback logs.
This query should be run when we have enabled a guaranteed restore point – the STORAGE_SIZE column will show the disk space that is currently being used by the flashback logs and this should be compared with the db_recovery_file_dest_size value so as to ensure that we have allocated sufficient space for the flashback logs.
where guarantee_flashback_database = ‘YES’;