Thursday , September 28 2023

Oracle Restore Point Creation and Usage


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.


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.

Creating and Using a Restore Point:

You can query the existing restore points from v$restore_point.

To create a new restore point;


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.


Return To the Restore Point:

The following commands will be enough to return to the restore point we have created.


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.


Turn Off Flashback:

If we don’t use Flashback, we can turn off this feature of our database.


About dbtut

We are a team with over 10 years of database management and BI experience. Our Expertises: Oracle, SQL Server, PostgreSQL, MySQL, MongoDB, Elasticsearch, Kibana, Grafana.

Leave a Reply

Your email address will not be published. Required fields are marked *