Oracle Restore Points
Version 18.2.0.0

General Information
Library Note Morgan's Library Page Header
Coming to OpenWorld 2018? Be sure to visit the TidalScale booth in Moscone South and learn how to solve performance problems and lower costs with Software Defined Servers. Before you visit the booth, or if you can't make it this year, check out TidalScale at www.tidalscale.com. Be sure to click on the Solutions link and look through the Oracle resources.
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 12cR2
What's New In 18cR1

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-2017 Daniel A. Morgan All Rights Reserved