flashback restore points in oracle 10g release 2

Oracle Database Developer Choice Awards

I'm pleased to announce that I've been selected as a finalist in the inaugural Oracle Database Developer Choice Awards. To quote, these awards "celebrate and recognize technical expertise and contributions in the Oracle Database community".

I've been selected for the PL/SQL category, primarily for the articles and utilities that I share here on oracle-developer.net. I'm very pleased to be recognised by the Oracle Community in this way and if you have found oracle-developer.net to be a helpful and/or interesting resource, then I'd appreciate your vote (my voting page is here).

More information on the Awards, including an explanatory video, the rules, other categories and finalists can all be found on the main page.

Thanks for your support!

Flashback table was introduced in Oracle 10g Release 1. This technology provides a means of restoring data as it existed at a specific point in time (defined by either a timestamp or System Change Number). Flashback table also enables us to "undrop" tables that we might have inadvertently dropped. In 10g Release 2 (10.2), Oracle has extended the capabilities of flashback table by introducing restore points. This technology is provided for both flashback table and flashback database, but this article will concentrate on the former.

restore points

A restore point is a named point in time that can be used as a flashback target. When we create a restore point we are essentially naming the current SCN (System Change Number). According to the documentation, we can create up to 2,048 named restore points and these can either be "normal" or "guaranteed". A guaranteed restore point is used in flashback database, so we will be concentrating on normal restore points only in this article.


To create a normal restore point, we require either one of the following privileges.

To use a restore point, either the SELECT_CATALOG_ROLE role or one of the above system privileges is required.

getting started: sample data

We will now look at an example of using restore points. We will create a scratch table with just 5 rows of data. To be able to flashback this table, we must enable row movement.

  3  AS
  4     SELECT object_id
  5     ,      object_name
  6     ,      object_type
  7     FROM   all_objects
  8     WHERE  ROWNUM <= 5;

Table created.


         X Y                              Z
---------- ------------------------------ -----------------
        20 ICOL$                          TABLE
        44 I_USER1                        INDEX
        28 CON$                           TABLE
        15 UNDO$                          TABLE
        29 C_COBJ#                        CLUSTER

5 rows selected.

creating and using a restore point

We will now create and use a normal restore point. We will commit a change to our data but then flash it back to its state at the restore point. This is no different in concept to using a timestamp or SCN as a flashback target, aside from having a convenient name for such a point in time. All flashback operations are essentially resolved to an SCN by Oracle; timestamps and now restore points are just convenient ways of addressing it.

We can now create a normal restore point as follows.

SQL> CREATE RESTORE POINT before_we_do_anything;

Restore point created.

Now we have a restore point, we can change our data safe in the knowledge that if we make a mistake, we can flashback. Given this, we can now make a permanent change to our data by removing a couple of rows and committing.


2 rows deleted.


Commit complete.


         X Y                              Z
---------- ------------------------------ -------------------
        28 CON$                           TABLE
        15 UNDO$                          TABLE
        29 C_COBJ#                        CLUSTER

3 rows selected.

To restore our data, we can use flashback table using a timestamp (TO TIMESTAMP), SCN (TO SCN) and now, as of 10.2, a restore point (TO RESTORE POINT), as follows.

SQL> FLASHBACK TABLE t TO RESTORE POINT before_we_do_anything;

Flashback complete.


         X Y                              Z
---------- ------------------------------ -------------------
        20 ICOL$                          TABLE
        44 I_USER1                        INDEX
        28 CON$                           TABLE
        15 UNDO$                          TABLE
        29 C_COBJ#                        CLUSTER

5 rows selected.

We have managed to restore our data. If we are finished with the restore point, we can drop it as follows.

SQL> DROP RESTORE POINT before_we_do_anything;

Restore point dropped.


Note that a restore point is not a database object; we will not find it in DBA_OBJECTS, for example. Oracle provides a new V$RESTORE_POINT view to expose the metadata for restore points. Using Tom Kyte's print_table procedure to make the format easier to read, we can see the information available from this view during the flashback example above (before we dropped the restore point, that is).

SQL> exec print_table( 'SELECT * FROM v$restore_point' );

SCN                           : 586453
STORAGE_SIZE                  : 0
TIME                          : 29-AUG-05
NAME                          : BEFORE_WE_DO_ANYTHING

PL/SQL procedure successfully completed.

retention target

The time period within which we can flashback to restore points is governed by the db_flashback_retention_target parameter. Normal restore points are removed automatically after the period specified by the oddly-named initialization parameter control_file_record_keep_time (guaranteed restore points need to be manually dropped). The default values of these database parameters are as follows.


NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_flashback_retention_target        integer     1440


NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time        integer     7

further reading

For more information on restore points in general, read the SQL Reference. For a more detailed introduction to flashback table in 10g Release 1, read this article which includes information on how Oracle implements the technology. The same is true when using restore points. General tips on using restore points and flashback technology can be found in the Backup and Recovery Basics documentation.

source code

The source code for the examples in this article can be downloaded from here.

Adrian Billington, August 2005

Back to Top