1、首先确认db_recovery_file_dest和db_recovery_file_dest_size 有值。
sys@TEST0910> show parameter recovery
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string /u01/app/oracle/fast_recovery_ area
db_recovery_file_dest_size big integer 4122M
db_recovery_file_dest 放闪回日志，默认路径/u01/app/oracle/fast_recovery_ area
db_recovery_file_dest_size 存放闪回日志空间的大小，默认4122M
2、开启闪回
sys@TEST0910> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
sys@TEST0910> startup mount
ORACLE instance started.
Total System Global Area 2505338880 bytes
Fixed Size 2230952 bytes
Variable Size 587203928 bytes
Database Buffers 1895825408 bytes
Redo Buffers 20078592 bytes
Database mounted.
sys@TEST0910> alter database flashback on;
Database altered.
sys@TEST0910> alter database open;
Database altered.
3、查询确认是否开启闪回
sys@TEST0910> select name,flashback_on from v$database;
NAME FLASHBACK_ON
-------------------------------------------------- ------------------
TEST0910 YES
闪回四种不同原理
- flashback drop：利用recyelebin 回收站原理
- flashback table TEST10 to before drop [rename to test11];可以重命名
- flashback query ，依赖undo数据，先查询，在做insert
- insert into test10 select * from test10 as of timestamp to_timestamp('2013-09-23 11:52:06','yyyy-mm-dd hh24:mi:ss');
- flashback table 依赖undo块
- flashback table scott.test10 to scn 1952615
- insert into test10 select * from scott.test10 as of scn 1952615;
- flashback database DDL语句，依赖闪回区的闪回日志
- flashback database to timestamp to_timestamp('2013-09-23 15:09:52','yyyy-mm-dd hh24:mi:ss');时间减一秒。
- 闪回归档--create as