In this post, we will discuss, what is restore point in an oracle, how to check restore point in oracle
What is a Restore Point in Oracle?
- A restore point is a name assigned to a system change number (SCN) in Oracle corresponding to the time of the creation of the restore point.
- A restore point can be used to flash a table or the database back to the time of the creation of the restore point without the need to determine the SCN or timestamp.
- The restore point information is stored in a control file
- There are two types of restore points in the Oracle Database: a normal restore point and a guaranteed restore point.
- With 12.2 and local undo enabled, we can set the restore point at the pluggable database level also and restore them
Guaranteed restore points
- It let you revert to the restore point regardless of the DB_FLASHBACK_RETENTION_TARGET initialization parameter.
- They used a good amount of space in the flash recovery area as flashback logs are not deleted and can get accumulated over time. Therefore, Oracle recommends that you create guaranteed restore points only after careful consideration
- Guaranteed restore points never get deleted automatically, as normal restore points do. You have to drop them explicitly
- It does not guarantee that the database will have enough undo to flashback any table to the same restore point.
Normal Restore Points
- A normal restore point enables you to flash the database back to a restore point within the time period determined by the DB_FLASHBACK_RETENTION_TARGET initialization parameter
- The database automatically drops them when the maximum number reached out
- You can explicitly drop them using the drop restore point command
how to check the restore point in Oracle
You can check the restore point in the Oracle database using the below query
sqlplus / as sysdba select * from v$restore_point; You can can at the PDB level also if you are on 12.2 above and local undo enables alter session set container=TEST; select * from v$restore_point;
How to create a restore point in Oracle
Before creating the restore point, you should have the following
- The database is an archive log mode
- Flashback mode is ON
- FRA must be set for the Database
You can create using the below query
sqlplus / as sysdba
create restore point <restore point name>;
How to create a guaranteed restore point in Oracle
Before creating the restore point, you should have the following
- The database is an archive log mode
- Flashback mode is ON. It is not mandatory. For the Guaranteed restore point, it will put the database automatically in flashback mode
- FRA must be set for the Database
You can create using the below query
sqlplus / as sysdba
create restore point <restore point name> guarantee flashback database;
How to drop the restore point
You can drop the restore point using the below query. This will be valid for both Normal and guaranteed restore point
sqlplus / as sysdba
drop restore point <restore point name>
When you are dropping a guaranteed restore point, it might take time as it has to delete all the accumulated flashback logs
Related Articles
Oracle Flashback Database: Oracle Flashback Database is a new feature from 10g onwards which uses past images to back out changes .it is enabled using alter database flashback on
Flashback Database queries: Check out this post to find the top useful oracle Flashback Database queries, how to enable flashback, how to perform flashback
recover table using flashback standby database: Detailed steps to recover table using flashback standby database. Accidental delete,drop, truncate can be recovered using this technique