Many years ago, I wrote this blog post about the usage and effectiveness of creating restore points in an Oracle database. They can be effective for a great many things (including database copy creation for another database refresh).
The Oracle documentation goes at great lengths in describing how to create them, and how they may be used, perhaps before patching, or a major application change, or an OS migration. Please use them with due diligence and care. If you have a support account, and can access the link above, Oracle goes to great lengths in describing use cases and syntax for creating restore points, but only casually mentions clean up thereafter.
So PLEASE, no matter what you do, remember to clean up afterward!
(I suggest no longer than one week, in most cases).
We just had two issues of database down time, caused by restore points holding backup media in the flash recovery area, and preventing the database from switching logs or being backed up.
For whatever reason you may be using a restore point, always include a step in your action plan to remove them later.
Here is example of how to identify and remove a restore point (must be done in the root container):
1 SELECT NAME, SCN, GUARANTEE_FLASHBACK_DATABASE
2 , TIME
3 FROM V$RESTORE_POINT
4* WHERE GUARANTEE_FLASHBACK_DATABASE = 'YES'
SQL> /
NAME SCN GUA TIME
------------------------------ ---------- --- ---------------------------------------------------------------------------
BEFORE_CLONE 3.2565E+11 YES 04-FEB-26 04.33.25.000000000 PM
SQL> set timing on
SQL> drop restore point before_clone;
Restore point dropped.
If anyone reading this has any experience with this issue, please feel free to comment or suggest additional recommendations.
