Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
Restore Point is used to create a return point when flashback is active in Oracle 11g and above databases.
While the flashback feature of our database is on, it is possible to create a restore point for any time and return to that moment again.
We can use Restore Point For Some Reasons:
- In particular, it is a very useful feature to enable standby databases and to revert to the state before starting the test after performing our tests.
- Or the new version of your application will be deployed and a serious modification of the database is required. If you create a restore point before making the changes, simply return to the restored point.
In order to create a restore point, the flashback feature of our database must be open.
We can use the following query to check flashback status.
1 2 3 4 5 | SQL> select FLASHBACK_ON from v$database; FLASHBACK_ON ------------------ YES |
If FLASHBACK_ON returns to YES, the flashback feature of our database is open. Otherwise we need to turn on the flashback feature as below.
1 2 3 | SQL> alter database flashback on; Database altered. |
Creating and Using a Restore Point:
You can query the existing restore points from v$restore_point.
To create a new restore point;
1 2 3 | SQL> create restore point before_deployment guarantee flashback database; Restore point created. |
When we create a Restore point, we need to pay attention to the fact that our archivelog directory is large enough.
When we create with the guarantee as above, all the backup, archive log etc. files needed to return to this restored point will be kept and will not be deleted. The files will be kept until we drop this restore point.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | SQL> select * from v$restore_point; SCN DATABASE_INCARNATION# GUA STORAGE_SIZE ---------- --------------------- --- ------------ TIME --------------------------------------------------------------------------- RESTORE_POINT_TIME PRE --------------------------------------------------------------------------- --- NAME -------------------------------------------------------------------------------- 1.1834E+13 2 YES 419430400 07-MAR-16 07.29.59.000000000 PM YES BEFORE_DEPLOYMENT |
Return To the Restore Point:
The following commands will be enough to return to the restore point we have created.
1 2 3 4 | shutdown immediate; startup mount; flashback database to restore point BEFORE_DEPLOYMENT; alter database open; |
Our database will quickly return to the time we created the restore point.
Drop Restore Point:
When we’re done, we have to drop the restore point.
1 2 3 | SQL> drop restore point BEFORE_DEPLOYMENT; Restore point dropped. |
Turn Off Flashback:
If we don’t use Flashback, we can turn off this feature of our database.
1 2 3 | SQL> alter database flashback off; Database altered. |
