闪回数据库
SYS@aux 22-DEC-13> select systimestamp from dual;
SYSTIMESTAMP
---------------------------------------------------------------------------
22-DEC-13 09.45.30.872782 PM +08:00
SYS@aux 22-DEC-13> select current_scn from v$database;
CURRENT_SCN
-----------
32517356
SYS@aux 22-DEC-13> create restore point test_dbflashback;
Restore point created.
SYS@aux 22-DEC-13> show parameter retention;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_flashback_retention_target integer 1440
undo_retention integer 86400
SYS@aux 22-DEC-13> create restore point test_guarantee guarantee flashback database;
Restore point created.
SYS@aux 22-DEC-13> l
1* select * from v$restore_point where name like '%TEST_GUARANTEE%'
SYS@aux 22-DEC-13> /
SCN DATABASE_INCARNATION# GUA STORAGE_SIZE TIME RESTORE_POINT_TIME PRE NAME
---------- --------------------- --- ------------ ------------------------------ -------------------- --- ----------
32517462 7 YES 7962624 22-DEC-13 09.48.48.000000000 P YES TEST_GUARA
M NTEE
SYS@aux 22-DEC-13> flashback database to restore point test_dbflashback;
Flashback complete.
SYS@aux 22-DEC-13> flashback database to scn 32517356;
Flashback complete.
SYS@aux 22-DEC-13> flashback database to timestamp to_timestamp('2013-12-22 09:45:30','YYYY-MM-DD hh24:mi:ss');
Flashback complete.
SYS@aux 22-DEC-13> recover database;
Media recovery complete.
SYS@aux 22-DEC-13> alter database open;
Database altered.
SYS@aux 22-DEC-13>
guarantee restore point 不依赖flashback log,有自己单独的log机制, 但是也必须设置flash recovery area , 可以在不开启flashback database的情况下使用guarantee restore point , 也可以闪回数据库。
闪回到过去某一点时候, 要使用alter database open resetlog 来打开数据库。
可以使用 SCN ,restore point , timestamp 做为闪回点。
JULIA@aux 22-DEC-13> flashback table juliatest to timestamp to_timestamp('2013-12-22 21:21:35','YYYY-MM-DD HH:MI:SS');
flashback table juliatest to timestamp to_timestamp('2013-12-22 21:21:35','YYYY-MM-DD HH:MI:SS')
*
ERROR at line 1:
ORA-01849: hour must be between 1 and 12
JULIA@aux 22-DEC-13> flashback table juliatest to timestamp to_timestamp('2013-12-22 21:21:35','YYYY-MM-DD hh24:mi:ss');
Flashback complete.
JULIA@aux 22-DEC-13> flashback table juliatest to restore point test_insert;
Flashback complete.
JULIA@aux 22-DEC-13> select current_scn from v$database;
CURRENT_SCN
-----------
32517203
JULIA@aux 22-DEC-13> flashback table juliatest to scn 32517203;
Flashback complete.