Restore Point

Configuration:

• Database in Archivelog Mode

• For regular Restore point - Database in Flashback Mode

• Database using Force Logging - To prevent corruptions in Flashback Opperations

• Understand how your Data Guard will behave - Explained in the end

Limitations:

• Needs to be created before the opperation/maintenance

• Consumes a lot of space in transactional environments - Flashback Logs

Considerations:

• If your restore point is important, use the Guarantee option

Operation:

• Database Rollback

Version:

• 10gR2 / 2005

Licensing:

• Requires Enterprise Edition

Summary

The Guarantee Restore Point, introduced in Oracle 10gR2 (2005), allows DBAs to rewind a database to a precise moment in time with minimal downtime and no backup restore. It ensures Oracle retains all necessary flashback logs until the restore point is dropped, regardless of retention policies. This feature is essential for critical operations such as large data structure changes, patching, or version upgrades, and relies on ARCHIVELOG, Flashback logs (not flashback mode on), and FORCE LOGGING being enabled.

Guarantee Restore Point

Something I often see in different environments is poorly executed application updates, performing several DDLs/DMLs/DCLs and resulting in application unavailability.

For those working with Oracle Database and who have Enterprise Edition, this should already be a common practice. DBAs, Senior Developers, and DevOps professionals in general need to know that this feature exists and how to use it.

We are talking about a feature launched by Oracle in 2005, with version 10gR2.

The Guarantee Restore Point can and should be used in many situations to prevent headaches, sleepless nights, and lost workdays. Before any maintenance involving major changes in your database structure—or even in patching and upgrade processes—it is important to use this advantage of being able to roll back the database in time with minimal downtime and without a backup. Which other database can deliver this? And since 2005...

To use the Guarantee Restore Point, your database must be in ARCHIVELOG mode, it will use flashback logs - yes, but it doesn't require your database to run on Flashback On mode, and using FORCE LOGGING (this is very important).

How It Works

Before you or the DevOps team perform maintenance, it is necessary to create a Guarantee Restore Point. From that moment, everything that happens in the database can be rolled back to when the restore point was created. Regardless of the DB_FLASHBACK_RETENTION_TARGET parameter, Oracle will keep all flashback logs until the Guarantee Restore Point is removed.

This feature essentially makes Oracle retain all flashback logs required to always guarantee a return to that specific point, regardless of subsequent operations.

FORCE LOGGING is critical, especially with Data Guard, as it forces all operations to generate redo—even those that normally would not (like NOLOGGING operations). Without this, flashback operations may corrupt data files.

Also, ensure that tablespaces are in flashback mode. By default, they are enabled, but if someone disabled them to reduce log growth, that tablespace will be corrupted during the flashback operation.

Guarantee vs Non-Guarantee Restore Points

There are two types of Restore Points: Guarantee and Non-Guarantee. The default is Non-Guarantee.

Non-Guarantee requires the database to be on flashback mode, and the control of the flashback logs will be done by the DB_FLASHBACK_RETENTION_TARGET parameter.

- If you don’t specify GUARANTEE, Oracle will try to keep logs, but if space runs out or the retention period passes by, logs may be deleted and your flashback will fail.

- If the restore point is important, always use Guarantee - it will hold the flashback logs as long as the guarantee restore point lives - and as it doesn't rely on DB_FLASHBACK_RETENTION_TARGET, so your database doesn't need to be in Flashback On mode.

- If the restore point is not important, why are you using in the first place?

Examples

Check database and tablespace configuration:

SQL> alter session set container=pdb1;

SQL> SELECT log_mode, force_logging, flashback_on FROM v$database;

LOG_MODE     FORCE_LOGGING   FLASHBACK_ON
------------ --------------- ------------------
ARCHIVELOG   YES             YES

SQL> select name, flashback_on from v$tablespace;

NAME                           FLA
------------------------------ ---
SYSTEM                         YES
SYSAUX                         YES
UNDOTBS1                       YES
TEMP                           YES
USERS                          YES
GUOB                           YES
FDA_TS                         YES

Check if flashback logs are being generated:

SQL> SELECT COUNT(*) FROM v$flashback_database_logfile;

  COUNT(*)
----------
         2

Create a Guarantee Restore Point:

SQL> CREATE RESTORE POINT before_patch GUARANTEE FLASHBACK DATABASE;

Verify restore points:

SQL> SELECT name, guarantee_flashback_database,
       to_char(time,'YYYY-MM-DD HH:MI:SS') time
FROM v$restore_point;

NAME                 GUARANTE TIME
-------------------- -------- ----------------------------------------
BEFORE_PATCH         YES      2025-08-06 01:00:52

Perform a flashback to the restore point:

SQL> SHUTDOWN IMMEDIATE;
    
SQL> STARTUP MOUNT;

SQL> FLASHBACK DATABASE TO RESTORE POINT before_patch;

SQL> ALTER DATABASE OPEN RESETLOGS;

And if you are using PDB GRP instead:

SQL> ALTER PLUGGABLE DATABASE pdb1 CLOSE;

SQL> FLASHBACK PLUGGABLE DATABASE pdb1 TO RESTORE POINT before_patch;

SQL> ALTER PLUGGABLE DATABASE pdb1 OPEN RESETLOGS;

Drop the restore point if it is no longer needed:

SQL> DROP RESTORE POINT before_patch;

Note that you will need to review and fix your Standby after rolling back to a restore point, since this will break synchronization because your Primary will have a higher SCN than your Standby.