CODE
Oracle Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
PSOUG Home Code Snippets Oracle Reference Oracle Functions PSOUG Forum Oracle Blogs Bookmark and Share
 
 Search the Reference Library pages:  

Free
Oracle Magazine
Subscriptions
and Oracle White Papers

Oracle Restore Points
Version 11.1
 
General
Related Data Dictionary Objects
GV$DATABASE GV$PARAMETER GV$RESTORE_POINT
Related Privileges SELECT ANY DICTIONARY
FLASHBACK ANY TABLE
SELECT CATALOG ROLE
Syntax FLASHBACK [STANDBY] DATABASE [<database_name>]
TO [BEFORE] RESTORE POINT <restore_point_name>
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 / 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

truncate table airplanes;
drop table servers;

-- log back in as SYS and repair the DB
conn / as sysdba

shutdown immediate;

startup mount;

flashback database to restore point before_damage;

alter database open resetlogs;

conn uwclass/uwclass

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
Flashback Database
Flashback Query
Recycle Bin
Table Flashback
 
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us [111 users online]    © 2010 psoug.org