Blog

Today in this article, we will discuss about how to create and drop restore point in Oracle Database. Lets go through it in detail.

First of all, we need to know what Restore Point is. It is nothing but a name associated with a timestamp or an SCN of the database.
It is of two types :

• normal restore point
• Guaranteed Restore Point

The difference is that the guaranteed restore point allows you to flashback to the restore point regardless of the DB_FLASHBACK_RETENTION_TARGET initialization parameter whereas in a normal restore point, logs older than the specified parameter will be deleted, which can result in insufficient information to perform a flashback.

Create a Guaranteed Restore Point in Oracle :

Step 1: Prerequisites:

Creating a guaranteed restore point needs the following prerequisites:

1) The database must have a flash recovery area and flashback should be on.
2)The database must be in ARCHIVELOG mode

Find out if ARCHIVELOG is enabled:

SQL> select log_mode from v$database;

If its in archivelog mode, then you can continue to the below step. Otherwise, enable the archive log first.

Now, check flashback too :

SQL>select log_mode,flashback_on from v$database;

If it’s off, then turn on the flashback of the database :

SQL>alter database flashback on;

Step 2:  Create Restore Point :

Create a restore point UPGRADE_19C guarantee flashback database; where UPGRADE_19C is the name given to the guaranteed restore point.

 SQL>create restore point UPGRADE_19C guarantee flashback database;

Step 3: Check the Restore Point :

SQL> select * from v$restore_point;

Drop Guaranteed Restore Point in Oracle :

Step 1: Check the Guaranteed Restore Point

SQL> select GUARANTEE_FLASHBACK_DATABASE,NAME ,TIME from v$restore_point;
GUARANTEE_FLASHBACK_DATABASE   NAME             TIME
YES                            UPGRADE_19C     17-NOV-23 06.30.27.000000000 PM

Step 2: Check the flashback status

SQL> select name,flashback_on from v$database;
NAME           FLASHBACK_ON
ORCLCDB        RESTORE POINT ONLY

Step 3: Drop the Guaranteed Restore Point

SQL> drop restore point UPGRADE_19C;
Restore point dropped.
SQL> 

Step 4: Now check the Restore Point :

SQL> select GUARANTEE_FLASHBACK_DATABASE,NAME ,TIME from v$restore_point;
no rows selected
SQL> select name,flashback_on from v$database;
NAME        FLASHBACK_ON
ORCLCDB         NO
SQL> 

Hope this helps!! Also, you can get more information about this from the Oracle Doc ID 2338328.1