Flashback Database – Normal and Guaranteed Restore Points

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.

select name,scn,time,database_incarnation#,guarantee_flashback_database,storage_size
from v$restore_point
where guarantee_flashback_database = ‘YES’;

The following two tabs change content below.

• Bio
• Latest Posts

Gavin Soorma

Latest posts by Gavin Soorma (see all)