A 'restore point' is a designated point of recovery for an Oracle database. It can be a timestamp, or an SCN (system change number).
One might want to create a restore point to identify a significant point where data was in a certain condition. Typically, this is before an upgrade, or application changes, or patching.
Some other reasons to create one might be before a migration, or perhaps an OS change of some kind.
While a database can be recovered to a point in time should any of the above activities cause a problem, it can be difficult to identify the correct moment before the trouble started after the fact.
Using restore points, most often when database activity is paused, or ideally, when all connections to the database are stopped and suspended, assures a quicker valid recovery. Usually much faster than a standard RMAN recovery!
There are three main types of recovery in the Oracle documentation. Even an experienced DBA can be unsure of which one to use, since they've sprouted up at different periods and database versions. These are, for a better word "standard" recovery points (the word standard is implied, and not included in the syntax), guaranteed recovery points. and "clean" recovery points. Each one represents a different concept and presents different capabilities to aide in a quick recovery to a designated point in time. Using each one for their specific purpose is the ideal way to assure a quick recovery, including the ability to repeat it, as necessary.
GUARANTEED RESTORE POINTS:
Any of these options must be used with care. Any and all manipulations involving database recovery can have side-effects. One big issue is the use of redo. Redo logs will be necessary to perform any of these options. If the length of time between the creation of a restore point is extended, these logs will remain in the FRA (fast recovery area), and could cause space pressure. The database could even stop until space is made available in such a case. THIS IS WHY GUARANTEED RESTORE POINTS SHOULD BE DROPPED within seven days of creation! They should be used for a planned period of time. They do not expire on their own.
"NORMAL" RESTORE POINTS:
A restore point with out the guaranteed option will automatically expire once the backup retention period is expired. "Normal" is not an Oracle syntax, but is a restore point without the "guarantee flashback database" is what is referred to here.
CLEAN RESTORE POINTS:
The Oracle documentation defines a "clean restore point" as a point in time when the database is in closed or mounted mode. This assures that no transactions are running before or during the creation of this restore point. The limits on this type of restore point, as well as a normal one, is that they will automatically expire after the period defined by the spfile parameter DB_FLASHBACK_RETENTION_TARGET.
Complete documentation of this complex topic, including the implications of using it in a multitenant environment, is available using this link.
