What are restore points and how are they used?

Definition:

A restore point is an alias to the system change number (SCN) of the database at the time the restore point was created.

Types of restore points:

1. Normal
2. Guaranteed

For both types, the name of the restore point and the SCN are recorded in the database control file.

Normal restore points are very lightweight. The control file can maintain a record of thousands of normal restore points with no significant impact upon database performance. Normal restore points eventually age out of the control file if not manually deleted, so they require no ongoing maintenance.

Guaranteed restore points on the other hand, guarantees that Oracle will retain the flashback logs for a Flashback Database operation.

A guaranteed restore point does not age out of the control file and must be explicitly dropped.
Guaranteed restore points will utilize space in the flash recovery area.
The flash recovery area must be therefore be defined and large enough to hold the flashback logs for the duration of the guaranteed restore point's existence.

Creation example:

CREATE RESTORE POINT PRE_EOD_201208;
CREATE RESTORE POINT PRE_RELEASE2 GUARANTEE FLASHBACK DATABASE;

Usage:

You can use restore points with any commands that recognize a RESTORE POINT clause as a shorthand for specifying an SCN.

Examples:

FLASHBACK DATABASE TO RESTORE POINT < restore point name >;
RESTORE DATABASE TO RESTORE POINT < restore point name >;
FLASHBACK TABLE emp TO RESTORE POINT < restore point name >;

To view the restore points created in your database, use:

select name,scn,time,database_incarnation#, 
guarantee_flashback_database,storage_size/1024/1024 "MB"
from v$restore_point;

NAME                SCN               TIME                                     DATABASE_INCARNATION# GUA        MB
------------------- ---------------- ---------------------------------------- --------------------- --- ----------
AKSEPT_TEST_START   153050263689      17-NOV-14 07.53.33.000000000 AM                              2 YES      14336

To create a normal restore point, you need either

the SELECT ANY DICTIONARY system privilege, or

the FLASHBACK ANY TABLE system privilege

To create a guaranteed restore point, you need SYSDBA privileges.

To view or use a restore point, the user need either of the following:

The SELECT ANY DICTIONARY system privilege

The FLASHBACK ANY TABLE system privilege

The SELECT_CATALOG_ROLE role

To drop a restore point:

DROP RESTORE POINT AKSEPT_TEST_START;