0.查undo情况, 闪回用到undo
SQL> show parameter undo

1.查scn
SQL> select dbms_flashback.get_system_change_number from dual;
或SQL> select current_scn from v$database;

2.查scn与timestamp对应表
SQL>select scn,to_char(time_dp,'yyyy-mm-dd hh24:mi:ss') from sys.smon_scn_time;

3.根据timestamp查看t1表5分钟以前情况
SQL> select * from t1 as of timestamp sysdate-5/1440

4.根据scn查看t1表
SQL> select * from t1 as of scn 344197;

5.根据scn查看t1表记录的变化情况
SQL> select id,vl,versions_startscn,versions_endscn,versions_operation from t1 versions between scn 372466 and 372538 order by 2;

6.根据timestamp查看t1表记录的变化情况
SQL> select * from t1 versions between timestamp sysdate-5/1440 and sysdate;

7.从RECYCLEBIN中恢复
SQL> select object_name,original_name from recyclebin;

8.根据scn恢复被删数据行
SQL> insert into t1 select *from t1 as of scn 344197 where id not in(select id from t1);

9.恢复到指定scn
SQL> flashback table t1 to scn 593480;
若提示: ORA-08189: 因为未启用行移动功能, 不能闪回表,则执行以下语句启用row movement,.
SQL> alter table t1 enable row movement;

10.恢复到指定timestamp
SQL> flashback table t1 to timestamp sysdate-20/12400;

11.根据timestamp恢复被删除数据行
SQL> insert into t1 select *from t1 as of timestamp sysdate-5/1440 where id not in(select id from t1);

12.恢复被删除数据表t1
SQL> flashback table t1 to before drop;

13.真正删除表(添加purge), 不能恢复.
SQL> drop table t7 purge;
Table dropped

SQL> commit;
 
Commit complete
 
SQL> flashback table t7 to before drop;
 
flashback table t7 to before drop
 
ORA-38305: 对象不在回收站中
 
SQL> flashback table t7 to scn 2474345;
 
flashback table t7 to scn 2474345
 
ORA-00942: 表或视图不存在

14.开启/关闭自动回收(recyclebin)
SQL> alter session set recyclebin=on;
SQL> alter session set recyclebin = off;

15.Transaction query事务查询
SQL> select xid,commit_scn,commit_timestamp,operation,undo_sql from flashback_transaction_query q where q.xid in( select versions_xid from t1 versions

between scn 2474922 and 2475048);

16.恢复数据库到指定scn
数据库必须处于 ARCHIVELOG 模式
必须激活数据库的 FLASHBACK 特征
必须合理配置初始化参数 db_flashback_retention_target,该初始化参数用于控制可以恢复到的最早时间点
SQL> alter database flashback on;
SQL> shutdown immediate;
SQL> startup mount exclusive;
SQL> flashback database to scn 686384;
SQL> alter database open resetlogs;