Oracle | Restore Points and Guaranteed Restore Points

Oracle Restore Points Version 21c

General Information

Library Note

Morgan's Library Page Header

The best Oracle News for FY2026

Oracle Database 26ai will be available on generic Linux platforms in January and soon on AIX and Windows

Purpose Restore points are used with Flashback Database to create points-in-time to which the database can be flashed back at a later time if desired.

Related Data Dictionary Objects

GV$PARAMETER

GV$RESTORE_POINT

V$DATABASE

Related Privileges

FLASHBACK ANY TABLE

SELECT ANY DICTIONARY

SELECT CATALOG ROLE

Security Model To create a normal restore point, you must have either SELECT ANY DICTIONARY or FLASHBACK ANY TABLE privilege. To create a guaranteed restore point, you must have the SYSDBA system privileges. To view or use a restore point, you must have the SELECT ANY DICTIONARY or FLASHBACK ANY TABLE system privilege or the SELECT_CATALOG_ROLE role.

Normal Restore Point

Create regular restore point CREATE [CLEAN] RESTORE POINT <restore_point_name> [FOR PLUGGABLE DATABASE <pdb_name>] [AS OF <TIMESTAMP <timestamp_value> | SCN <scn_value>] [<PRESERVE | GUARANTEE FLASHBACK DATABASE>];

conn sys@pdbdev as sysdba

      

      set linesize 121

      col name format a30

      col value format a30

      

      SELECT name, value

      FROM gv$parameter

      WHERE name LIKE '%flashback%';

      

      shutdown immediate;

      

      startup mount exclusive;

      

      alter database archivelog;

      

      alter database flashback on;

      

      alter database open;

      

      SELECT flashback_on, log_mode

      FROM v$database;

      

      -- normal restore point

      CREATE RESTORE POINT  before_damage;

      

      set linesize 121

      col name format a15

      col time format a32

      

      SELECT name, scn, time, database_incarnation#, guarantee_flashback_database, storage_size

      FROM gv$restore_point;

      

      -- do some serious damage

      conn uwclass/uwclass@pdbdev

      

      truncate table airplanes;

      drop table servers;

      

      -- log back in as SYS and repair the DB

      conn sys@pdbdev as sysdba

      

      shutdown immediate;

      

      startup mount;

      

      flashback database to restore point before_damage;

      

      alter database open resetlogs;

      

      conn uwclass/uwclass@pdbdev

      

      desc airplanes

      

      SELECT COUNT(*) FROM airplanes;

      

      desc servers

      

      SELECT COUNT(*) FROM servers;

      

      CREATE RESTORE POINT del_rec;

      

      SELECT name, scn, time, database_incarnation#, guarantee_flashback_database, storage_size

      FROM gv$restore_point;

      

      ALTER TABLE servers ENABLE ROW MOVEMENT;

      

      DELETE FROM servers WHERE rownum < 101;

      

      COMMIT;

      

      FLASHBACK TABLE servers TO RESTORE POINT del_rec;

      

      SELECT COUNT(*) FROM servers;

Guaranteed Restore Point

Create a guaranteed restore point CREATE RESTORE POINT <restore_point_name> [AS OF <TIMESTAMP | SCN> <timestamp_or_scn_value>] GUARANTEE FLASHBACK DATABASE;

-- This demo assumes previous setup so database is already in archivelog mode with flashback on

CREATE RESTORE POINT before_damage GUARANTEE FLASHBACK DATABASE;

      

      set linesize 121

      col scn format 99999999

      col time format a32

      

      SELECT scn, database_incarnation#, guarantee_flashback_database, storage_size, time, name

      FROM gv$restore_point;

Drop Restore Point

Drop an existing regular or guaranteed restore point DROP RESTORE POINT <restore_point_name>;

CREATE RESTORE POINT before_damage;

      

      DROP RESTORE POINT BEFORE_DAMAGE;

      

      DROP RESTORE POINT del_rec;

      

      SELECT scn, database_incarnation#, guarantee_flashback_database, storage_size, time, name

      FROM gv$restore_point;