Guaranteed Restore Points can cause a database hang

If we are using Guaranteed Restore Points with Flashback logging turned on, we need to exercise care that we drop restore points that are not in use or that we allocate sufficient space for the flashback logs as specified by the init.ora parameter db_recovery_file_dest_size. This is because the flashback logs will be retained and not overwritten due to space constraints when we create guaranteed restore points.

To illustrate the same, we create a guaranteed restore point and with the db_recovery_file_dest_size set to a value of 100M, perform a DELETE operation on a million row table.

After some time we notice that the delete statement is hanging. We notice from the alert log that we are getting a warning that the database background process RVWR is stuck as the flashback area is full and space cannot be reused because we have set a guaranteed restore point.

The solution to this problem is to increase the space allocated for the flashback logging by changing the parameter db_recovery_file_dest_size which can be done on the fly.

Once we do that we see that the delete operation completes successfully.

The view V$RESTORE_POINT can be queried to see all the restore points which have been set and if they are normal or guaranteed. The column storage_size will give us an indicator of the required disk space to accomodate the flashback logs based on current database workload.

SQL> show parameter db_recovery

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      /u02/oradata/testdb/
db_recovery_file_dest_size           big integer 100M


SQL> CREATE RESTORE POINT test_guarantee GUARANTEE FLASHBACK DATABASE;

Restore point created.


SQL> DELETE sh.sales;

WE SEE THAT THIS DELETE STATEMENT IS HANGING …..

From the alert log we see the following warning message ….

current recovery area of size 104857600 bytes.
Recovery Writer (RVWR) is stuck until more space is
available in the recovery area.
Unable to write Flashback database log data because the
recovery area is full, presence of a guaranteed
restore point and no reusable flashback logs.
Use ALTER SYSTEM SET db_recovery_file_dest_size command
to add space. DO NOT manually remove flashback log files
to create space.

The V$RESTORE_POINT view shows us that we need about 179 MB of disk space to hold the flashback logs.

SQL> select name,guarantee_flashback_database,storage_size
  2  from v$restore_point
  3   where guarantee_flashback_database = 'YES';

NAME                 GUA STORAGE_SIZE
-------------------- --- ------------
TEST_GUARANTEE       YES    179159040

After we increase the space allocated for the flashback logs, we see that the delete statement which was originally hanging has now completed.

SQL> alter system set db_recovery_file_dest_size=200M;

System altered.


SQL> DELETE sh.sales;

918843 rows deleted