Oracle Restore Points
Version 12.1.0.1

General Information
Library Note Morgan's Library Page Footer
The Library is currently in the process of being upgraded from Oracle Database Version 11.2.0.3 to 12.1.0.1. Demos are being upgraded to reflect the new Container paradigm as well as EBR (Edition Based Redefinition) and may contain references to CDBs, PDBs, and other objects you may not be familiar with such as CDB_OBJECTS_AE: Welcome to 12c.
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 RESTORE POINT <restore_point_name>
[AS OF <TIMESTAMP | SCN> <timestamp_or_scn_value>]
[PRESERVED];
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

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