Guaranteed restore points

Time Travel with Restore Points in Oracle

Leave a Comment / Backup and Recovery / By /

What is a Restore Point in Oracle Database?

A restore point is a name assigned to a system change number (SCN) in Oracle. An SCN is a number that uniquely identifies each new change to an Oracle database; this number is incremented whenever users commit a new transaction to the database.

The restore point and the SCN are stored together in a control file, which is a small binary file that contains information about the physical structure of an Oracle database.

Oracle restore points let you “rewind” an Oracle database to a given point in the past. This helps protect you from errors and accidental mishaps, preserving your data in the event of a mistake or failure.

Restore points can also revert the database back to a previous state. This functionality is useful during benchmark testing, which may require the database to be in the same pristine state before each iteration of the test.

There are two types of restore points in Oracle Database: a normal restore point and a guaranteed restore point. The differences are:

  • Guaranteed restore points let you revert to the restore point regardless of the DB_FLASHBACK_RETENTION_TARGET initialization parameter. In other words, using guaranteed restore points is always possible as long as you have enough space in the fast recovery area.
  • Guaranteed restore points never “age out” of the control file, as normal restore points do. Instead, you must always explicitly drop (i.e delete) them.

This post talks about guaranteed restore points only

How To Create A Restore Point

Three conditions must be satisfied for this to work

  1. YOU MUST HAVE SYSDBA PRIVILEGES
  2. YOU MUST HAVE A Fast Recovery Area configured
  3. DATABASE MUST BE IN ARCHIVELOG MODE

Steps

  1. Create the guaranteed restore point
  2. flashback to the guaranteed restore point
  3. drop the restore point once no longer needed

To create the restore point, the fast revovery area must already exist. After a flashback to a guaranteed restore point, the reset logs database must be open with the resetlogs option.

Why resetlogs?

We have to reset the logs because the logs are not “rewound” as part of flashback. Let’s say you took a restore point at 9am. At 11am, when you want to run a flashback:

  • the datafiles are at 11am
  • the redo logs are at 11am

   After the flashback processing, the datafiles are now at 9am, and redo logs are now of no use they no longer reflect the state of the database. Hence the need to reset them.

A flashback to a restore point can be done with RMAN as well.

As mentioned earlier, Once a restore point is no longer needed, the best practice is to drop (i.e. remove) the restore point,
because it uses a good deal of space in the fast recovery area.

Some important considerations

  • With a guaranteed restore point, you are always guaranteed that you can flash back your database to that prior time.
  • Guaranteed restore points don’t depend on the Flashback logs. Thus, you can create a guaranteed restore point even if Flashback logging is turned off. Guaranteed restore points use a logging mechanism that’s somewhat similar to the Flashback logs, but it’s separate from them.
  • If you use a guaranteed restore point, Oracle won’t delete any Flashback logs that are created after you create the guaranteed restore point. Therefore, you can end up filling up your flash recovery area and causing the database to stop its operations if Flashback logging is enabled when you’re using guaranteed restore points. You’re better off turning off Flashback logging if you’re using guaranteed restore points.
  • You can use guaranteed restore points with or without Flashback logging enabled—you must, however, configure a fast recovery area.
  • Although you can flash back your database to a guaranteed restore point even if Flashback Database wasn’t originally enabled and therefore no Flashback logs were collected, you can only recover exactly to the guaranteed restore point. If you want to recover to any point before that, you must still use a point-in-time recovery technique using backups and archived log files.

An Example

Let’s look at an example of how to create a guaranteed restore point, flashback to it and confirm state is the same as before.
(The example below assumes FRA is already configured and Archive log mode is turned on)

Prior state

  • First I set my environment to the database I want to configure the guaranteed restore point for
    [oracle@localhost ~]$ . oraenv
    ORACLE_SID = [orcl] ? ORCL
  • Next I connect as sys and run the following queries to get information about restore points
[oracle@localhost ~]$ 
[oracle@localhost ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Mar 4 05:32:08 2026
Version 19.23.0.0.0

Copyright (c) 1982, 2023, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.23.0.0.0

SQL> show parameter flashback

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
db_flashback_retention_target	     integer	 1440
SQL> 
SQL> 
SQL> select flashback_on from V$database;

FLASHBACK_ON
------------------
NO

SQL> 
SQL> 
SQL> col timestamp for a40
SQL> 
SQL> 
SQL> select current_scn, scn_to_timestamp(current_scn) timestamp from v$database;

CURRENT_SCN TIMESTAMP
----------- ----------------------------------------
   23408738 04-MAR-26 05.32.34.000000000 AM

SQL>

The above output shows that flashback is not currently turned on, The db_flashback_retention_target specifies the upper limit (in minutes) on how far back in time the database may be flashed back

Creating the restore point

  • Now we create the guaranteed restore point
SQL> 
SQL> create restore point test_rp guarantee flashback database;

Restore point created.

SQL>

In the fast recovery area a new directory called flashback will be created and populated with some log files to keep track of changes and guarantee a flashback to this point at a later time.

SQL> 
SQL> ! ls -ltr /u02/fast_recovery_area/ORCL/flashback/
total 409616
-rw-r-----. 1 oracle oinstall 209723392 Mar  4 05:39 o1_mf_ntj6hnd9_.flb
-rw-r-----. 1 oracle oinstall 209723392 Mar  4 05:41 o1_mf_ntj6hjm5_.flb

SQL> 
SQL> 
SQL>

We can also see some more information about the guaranteed restore point using the v$restore_point data dictionary view 

SQL> set linesize 120
SQL> col name for a20
SQL> col name for a20
SQL> col storage_size for a20
SQL> col time for a35
SQL> select name, scn, guarantee_flashback_database, 
  2  time, storage_size/1024/1024 storage_size_mb  
  3  from v$restore_point;

NAME			    SCN GUA TIME				STORAGE_SIZE_MB
-------------------- ---------- --- ----------------------------------- ---------------
TEST_RP 	       23409684 YES 04-MAR-26 05.39.28.000000000 AM		    200

SQL>

the above query displays the scn, timestamp and size of the guaranteed restore point. as more activity takes place the larger the storage requirements will be to preserve this restored point. Also, if we run the commands from earlier to get info about restore points we see some changes

SQL> 
SQL> show parameter flashback

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
db_flashback_retention_target	     integer	 1440
SQL> 
SQL> 
SQL> select flashback_on from V$database;

FLASHBACK_ON
------------------
RESTORE POINT ONLY

SQL>

We can see that flashback is now turned on, but we can only flashback to the restore point. The db_flashback_retention_target parameter does not affect guaranteed restore points i.e regardless of what it is set to we can always recover back to our guaranteed restore point.

Now let’s make some changes and flashback to the restore point to confirm that it works (data before changes will be recovered)

suppose as part of some change to the HR application we have to drop our current HR user in order to install a new application
our guaranteed restore point can preserve the state of our application before making the changes and flashback to the prior point in case of any error with the upgrade.

SQL> select username from dba_users
where username = 'HR';
  2  
USERNAME
------------------------------------------------------------------------------------------------------------------------
HR

SQL> 

SQL> drop user hr cascade;

User dropped.

SQL> 

SQL> select username from dba_users
where username = 'HR';
  2  
no rows selected

SQL>

Flashing back

We have dropped the HR user from our database and suppose the new application install didnt go as planned. we are not neccessarily doomed. we can revert back to our old application using the restore point we created prior.

SQL> 
SQL> col timestamp for a40
select current_scn, scn_to_timestamp(current_scn) timestamp from v$database;
SQL> 
CURRENT_SCN TIMESTAMP
----------- ----------------------------------------
   23415869 04-MAR-26 06.01.43.000000000 AM

SQL> 
SQL> 
SQL> 
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> 
SQL> 
SQL> 
SQL> startup mount;
ORACLE instance started.

Total System Global Area 1660943096 bytes
Fixed Size		    8940280 bytes
Variable Size		 1358954496 bytes
Database Buffers	  285212672 bytes
Redo Buffers		    7835648 bytes
Database mounted.
SQL> 
SQL> 
SQL> set linesize 120
SQL> col name for a20
SQL> col time for a35
SQL> col storage_size for a20
SQL> 
SQL> select name, scn, guarantee_flashback_database, 
  2  time, storage_size/1024/1024 storage_size_mb  
  3  from v$restore_point;

NAME			    SCN GUA TIME				STORAGE_SIZE_MB
-------------------- ---------- --- ----------------------------------- ---------------
TEST_RP 	       23409684 YES 04-MAR-26 05.39.28.000000000 AM		    200

SQL> 
SQL> 
SQL> flashback database to restore point test_rp;

Flashback complete.

SQL> alter database open resetlogs;

Database altered.

SQL> 
SQL> 
SQL> 
SQL> select current_scn from v$database;

CURRENT_SCN
-----------
   23409900

SQL> select current_scn, scn_to_timestamp(current_scn) timestamp from v$database;

CURRENT_SCN TIMESTAMP
----------- ----------------------------------------
   23409917 04-MAR-26 06.16.24.000000000 AM

SQL> select username from dba_users
where username = 'HR';
  2  
USERNAME
------------------------------------------------------------------------------------------------------------------------
HR

As we can see above, to flashback to a guaranteed restore point, the database must be in archivelog mode and open in the mount mode. after the flashback, we open the database using resetlogs and we can confirm that the HR user still exists.

Dropping a Restore point

Once a restore point is no longer needed, the best practice is to drop (i.e. remove) the restore point,
because it uses a good deal of space in the fast recovery area.

SQL> set linesize 120
SQL> col name for a20
SQL> col time for a35
SQL> col storage_size a20
SQL> col storage_size for a20
SQL> 
SQL> select name, scn, guarantee_flashback_database, 
time, storage_size/1024/1024 storage_size_mb  
from v$restore_point;
  2    3  
NAME			    SCN GUA TIME				STORAGE_SIZE_MB
-------------------- ---------- --- ----------------------------------- ---------------
TEST_RP 	       23409684 YES 04-MAR-26 05.39.28.000000000 AM		    200

SQL> 
SQL> drop restore point test_rp;

Restore point dropped.

SQL>

Once it is dropped, the log go away too

SQL> 
SQL> select name, scn, guarantee_flashback_database, time, storage_size/1024/1024 storage_size_mb 
  2  from v$restore_point;

no rows selected

SQL> 
SQL> 
SQL> show parameter flashback

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
db_flashback_retention_target	     integer	 1440
SQL> 
SQL> 
SQL> 
SQL> select flashback_on from V$database;

FLASHBACK_ON
------------------
NO

SQL> 
SQL> 
SQL> ! ls -ltrh /u02/fast_recovery_area/ORCL/flashback
total 0

Conclusion

Restore points are a neat feature in oracle that can be useful for various scenarios such as

  • Iterative testing and QA
  • Application upgrades and patches (prevent you from having to do a database point in time recovery)
  • Alternative to snapshots
  • User error recovery

Depending on your needs, this can serve as a “lightweight” alternative to a full backup when you want some protection against a fairly risky process or a continuous starting point for some testing and fall back to after every iteration of the test.
By preserving a precise point in time; these allow you to experiment, recover quickly and protect business continuity while minimizing downtime. When used thoughtfully, they can be a powerful data recovery tool.

Cheers 🎊

Leave a Comment

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