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;