Home » Oracle » Oracle Database » how to check the restore point in Oracle

how to check the restore point in Oracle

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.
See also  Gather Schema Statistics Using FND_STATS in EBS 11i and R12

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

  1. The database is an archive log mode
  2. Flashback mode is ON
  3. 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

See also  how to create sql baseline in oracle 19c

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

Leave a Comment

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

Scroll to Top