| General |
| Related Data Dictionary Objects |
|
| 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
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;
-- 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;
-- 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;
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; |
| |
| 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; |
Free