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 that corresponds to the time the Restore Point was created.
• Restore Points can be used to flash back tables or databases to the time the Restore Point was created without needing to specify the SCN or timestamp.
• Restore Point information is stored in the control file.
• There are two types of Restore Points in an Oracle Database: normal Restore Points and guaranteed Restore Points.
• With version 12.2 and local undo enabled, you can set Restore Points at the pluggable database level as well and flash them back.

Guaranteed Restore Points

• They allow you to flash back to the Restore Point regardless of the DB_FLASHBACK_RETENTION_TARGET initialization parameter.
• They use a significant amount of space in the flash recovery area because flashback logs are not deleted and can accumulate over time. Therefore, Oracle recommends creating guaranteed Restore Points only after careful consideration.
• Guaranteed Restore Points are never automatically deleted, unlike normal Restore Points. You must explicitly drop them.
• It does not guarantee that the database will have enough undo to flash back any table to the same Restore Point.

Normal Restore Points

• Normal Restore Points allow you to flash back the database to the Restore Point within the period specified by the DB_FLASHBACK_RETENTION_TARGET initialization parameter.
• The database automatically drops them when the maximum number is reached.
• You can explicitly drop them using the drop restore point command.

How to Check Restore Points in Oracle

You can check Restore Points in an Oracle database using the query below:

sqlplus / as sysdba
select * from v$restore_point;
You can also check at the PDB level if you are on 12.2 or above and local undo is enabled
alter session set container=TEST;
select * from v$restore_point;

How to Create a Restore Point in Oracle

Before creating a Restore Point, you must have the following:

• The database is in archive log mode.
• Flashback mode is ACTIVE.
• FRA (Flash Recovery Area) must be set for the database.

You can create it using the query below:

sqlplus / as sysdba
create restore point <restore point name>;

How to Create a Guaranteed Restore Point in Oracle

Before creating a Restore Point, you must have the following:

• The database is in archive log mode.
• Flashback mode is ACTIVE. It is not mandatory. For a Guaranteed Restore Point, it will automatically place the database in flashback mode.
• FRA (Flash Recovery Area) must be set for the database.

You can create it using the query below:

sqlplus / as sysdba
create restore point <restore point name> guarantee flashback database;

How to Drop a Restore Point

You can drop a Restore Point using the query below. This applies to both normal and guaranteed Restore Points:

sqlplus / as sysdba
drop restore point <restore point name>