Oracle Restore Points
Version 19.3

General Information
Library Note Morgan's Library Page Header
Which has the higher priority in your organization: Deploying a new database or securing the ones you already have? Looking for a website, and resources, dedicated solely to securing Oracle databases? Check out DBSecWorx.
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;

Related Topics
DBMS_RCVMAN.GETRESTOREPOINT
Flashback Database
Flashback Query
Recycle Bin
Table Flashback
What's New In 18c
What's New In 19c

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2019 Daniel A. Morgan All Rights Reserved
  DBSecWorx