Oracle Flashback & Restore Points: What Every DBA Should Know

Posted by

1. What Is a Restore Point?

A Restore Point is a named marker in the database timeline. Oracle records the SCN and time so the database can be flashed back to that exact moment. 
 
There are two types:
  • Normal Restore Point
  • Guaranteed Restore Point (GRP)

2. Normal Restore Point

Key Characteristics 
  • A normal restore point depends on Flashback Database being enabled. 
  • It uses flashback logs, which can be automatically deleted by Oracle if space is required in the Fast Recovery Area (FRA). 
  • Because of this behavior, there is no guarantee that a flashback operation to a normal restore point will succeed at a later time.
When to Use  
  • Short-term testing
  • Low-risk changes
  • When FRA space is limited
create restore point pre_upgrade;
Note : If flashback logs are deleted, flashing back to this restore point may fail. 

3. Guaranteed Restore Point (GRP)

Key Characteristics
  • A Guaranteed Restore Point ensures that the database can always be flashed back to the restore point. 
  • Oracle retains all required flashback logs and does not delete them until the restore point is dropped. 
  • A GRP does not require Flashback Database to remain enabled after creation, but it consumes more FRA space.
When to Use
  • Database upgrades
  • Patching
  • Application releases
  • Any change where rollback must be 100% reliable

3.1 Steps to create GRP

  • Ensure the database is in ARCHIVELOG mode
sqlplus / as sysdba 
archivelog list
  • Ensure flashback_database is enabled(Optional but recommended)
select flashback_on from v$database;
alter database flashback on;
  • Set DB_RECOVERY_FILE_DEST and DB_RECOVERY_FILE_DEST_SIZE to accommodate the guaranteed restore point files.
alter system set db_recovery_file_dest_size=**G;
alter system set db_recovery_file_dest='<your-location>';
  • Create Guaranteed Restore Point
create restore point pre_upgrade guarantee flashback database;
  • Validate Guaranteed Restore Point
select name, scn, time, guarantee_flashback_database from v$restore_point;

4. Non-CDB vs CDB Restore Points

Non-CDB

  • Restore points are created at the database level
  • Flashback affects the entire database

CDB Environment

  • Restore point behavior depends on scope:

CDB Level 

alter session set container=CDB$ROOT;
create restore point TEST1 for pluggable database PDB1;
create restore point TEST2 for pluggable database PDB1 guarantee flashback database;
  • Applies to entire CDB
  • All PDBs flash back together

PDB Level (Local Restore Point) 

The type of PDB Level Restore point to create will depend on 

UNDO Mode

  • Before attempting a PDB restore point, verify whether the CDB is using shared or local undo, as this affects restore point behavior.
select property_name, property_value
from   database_properties
where  property_name = 'LOCAL_UNDO_ENABLED';
Local undo mode
  • This should be the case by default with 12.2+ 
  • Local undo in use;  Preferably choose the CDB Restore point
 select name, open_mode from v$pdbs;
 alter pluggable database my_pdb close;
--from pdb
 ALTER SESSION SET CONTAINER=my_pdb;
 create restore point before_upgrade guarantee flashback database;
or 
-- from cdb$root
 ALTER SESSION SET CONTAINER=CDB$ROOT;
 create restore point mypdb_grp_before_upgrade for pluggable database my_pdb guarantee flashback database;
Shared undo mode

Clean PDB Restore Point (Preferred)

  • A clean PDB restore point is a PDB restore point that is created when the PDB is closed and when there are no outstanding transactions for that PDB. 

  • Clean PDB restore points are only applicable to CDBs that use shared undo. 

  • If the PDB is in a suitable state (no active transactions), create a clean restore point.
Key Characteristics
  • No need for media recovery
  • Faster and safer rollback
  • Ideal for maintenance or testing scenarios
alter pluggable database pdb_name close immediate;
alter pluggable database pdb_name open read write;
create restore point pdb_rp_name for pluggable database pdb_name;

Regular PDB Restore Point

  • If a clean restore point is not possible (active sessions, transactional workload, or availability constraints), create a regular restore point instead.
Key Characteristics
  • May require instance recovery during flashback
  • Still provides point-in-time recovery
  • More flexible, but heavier
create restore point pdb_rp_name for pluggable database pdb_name;
  • Affects only that PDB
  • Useful for isolated PDB testing
  • Requires Oracle 12.2+

5. Archive Log Best Practice for GRP

  • For Guaranteed Restore Points, archive logs must be retained from:
  • One hour before the restore point creation
  • One hour after the restore point creation
  • This practice ensures recovery consistency and supports redo application during flashback or media recovery scenarios.

6. Flashback to a Restore Point

Verify Restore Point: 
 
select name, scn, time, guarantee_flashback_database from v$restore_point;
Shutdown the Database: 
 
Shut immediate;
Start the instance and mount the database
 
startup mount
Flashback the Database: 
 
flashback database to restore point before_upgrade;
Open with Resetlogs: 
 
alter database open resetlogs;
⚠️ The database must be opened with RESETLOGS after a flashback operation. 

7. Verification After Flashback

Always validate database and file states after flashback. 
 
Datafile Status 
  • All datafiles should be in ONLINE status. 
select distinct status from v$datafile;
select distinct status from dba_data_files;
Recovery Issues
  • This query should return no rows. 
select * from v$recover_file;
Temporary Files 
  • Ensure that all tempfiles are in ONLINE status. 
select file_name, status from dba_temp_files;
Redo and Checkpoint
  • Force redo log switches and issue a checkpoint to stabilize the database state.
alter system switch logfile;
alter system switch logfile;
alter system checkpoint;
PDB Plug-in Violations Check 
 
In a CDB environment, always check for plug-in violations after a flashback.

select * from pdb_plug_in_violations;
Common reasons:
  • Time zone mismatches
  • Component version differences
  • Invalid objects after flashback
  • Invalid alter statements
Resolve any ERROR or WARNING entries before proceeding.

9. Cleanup: Dropping Restore Points

Once the database is confirmed to be stable and no rollback is required, the restore point should be dropped.

drop restore point before_upgrade;
This frees flashback logs and FRA space. 
 
Note : 
  • Guaranteed Restore Points can significantly increase FRA usage.
  • FRA space must be continuously monitored while a GRP exists.
  • Failure to manage FRA may result in database hang or archive log issues.

Comments

Post a Comment