利用flashback query恢复dml误操作数据
1使用闪回查询前必须确定下面两个参数
UNDO_MANAGEMENT = AUTO
undo_retention = 1800; #这个时间可以随便设，他表示在系统中保留提交了的UNDO信息的时间，1800就是保留30分钟。
select dbms_flashback.get_system_change_number from dual; ----获取当前scn号
SQL> select scn_to_timestamp(2599788) from dual; ----将scn转为时间
SQL>select timestamp_to_scn(to_timestamp('2016-5-1 6:06:06','yyyy-mm-dd hh24:mi:ss')) FROM dual; ----将时间转为scn
2环境准备(9i)
（1）查看数据库模式
SQL> archive log list
数据库日志模式 非存档模式
自动存档 禁用
存档终点 C:\oracle\ora92\RDBMS
最早的概要日志序列 1
当前日志序列 2
SQL> select log_mode,open_mode from v$database;
LOG_MODE OPEN_MODE
------------ ----------
NOARCHIVELOG READ WRITE
（2）创建闪回用户
SQL> create user shallzhong identified by shall;
用户已创建
SQL> grant connect,resource to shallzhong;
授权成功。
SQL> grant execute on dbms_flashback to shallzhong;
授权成功。
SQL> conn shallzhong/shall
已连接。
（3）创建测试表，并插入数据提交
SQL> create table zhong(id int,name varchar2(10));
表已创建。
SQL> begin
2 for i in 1..100001 loop
3 insert into zhong values(i,'zhong');
4 end loop;
5 commit;
6 end;
7 /
PL/SQL 过程已成功完成。
SQL> select count(*) from zhong;
COUNT(*)
----------
100001
（4）记录删除之前时间或SCN
SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;
TO_CHAR(SYSDATE,'YY
-------------------
2016-05-14 11:30:56
select dbms_flashback.get_system_change_number from dual;
GET_SYSTEM_CHANGE_NUMBER
------------------------
275829
记录时间后，最好等一段时间在做下面的操作，oracle每5分钟才记录一次scn，所以可能会导致闪回查询不到数据
（5）删除数据并提交
SQL> delete zhong;
已删除100001行。
SQL> commit;
提交完成。
3闪回查询（9i）
SQL> execute dbms_flashback.enable_at_time(to_date('2016-05-14 11:30:56','yyyy-mm-dd hh24:mi:ss'));
PL/SQL 过程已成功完成。
SQL> select count(*) from zhong;
COUNT(*)
----------
0
SQL> execute dbms_flashback.disable;
PL/SQL 过程已成功完成。
SQL> execute dbms_flashback.enable_at_system_change_number(275829);
PL/SQL 过程已成功完成。
SQL> select count(*) from zhong;
COUNT(*)
----------
100001
SQL> execute dbms_flashback.disable;
PL/SQL 过程已成功完成。
SQL> select count(*) from zhong;
COUNT(*)
----------
0
（1）为什么基于时间闪回查询不到数据？而基于scn可以？
闪回查询是基于SCN的：（oracle9i中smon_scn_time表每5分钟被更新一次，oracle10g是每6秒更新一次）
Oracle并不会精确的这个时间点，进行恢复。而Oracle 9i是每五分钟记的而是ROUND DOWN到最近的一次SCN，然后从这个SCN开始记录一次SCN，并将SCN和对应时间的映射做个纪录。这正是上面进行dbms_flashback.enable_at_time查询不到删除数据的原因。所以，删除之前等5分钟试试最保险。
select * from smon_scn_time;
SMON_SCN_TIME记录5天的数据，也就是1440 ((5d x 24h x 12m = 1440) 条记录。所以在Oracle9iR2中，表属性修改时间和flashback时间差至少应为5分钟。否则就会出现ORA-01466错误
SQL> select thread,time_mp,to_char(time_dp,'yyyy-mm-dd hh24:mi:ss'),scn_wrp,scn_bas from smon_scn_time ;
THREAD TIME_MP TO_CHAR(TIME_DP,'YY SCN_WRP SCN_BAS
---------- ---------- ------------------- ---------- ----------
1 1463195362 2016-05-14 11:09:24 0 252950
1 1463195670 2016-05-14 11:14:31 0 254047
1 1463196445 2016-05-14 11:27:25 0 274757
1 1463196752 2016-05-14 11:32:33 0 276521
1 1463197061 2016-05-14 11:37:41 0 277760
1 1463197368 2016-05-14 11:42:49 0 278504
1 1463197672 2016-05-14 11:47:56 0 279318
1 1463197983 2016-05-14 11:53:03 0 280126
已选择107行。
（2）truncate后重新测试
SQL> truncate table zhong;
表已截掉。
SQL> execute dbms_flashback.enable_at_system_change_number(275829);
PL/SQL 过程已成功完成。
SQL> select count(*) from zhong;
select count(*) from zhong
*
ERROR 位于第 1 行:
ORA-01466: 无法读数据 - 表定义已更改
SQL> execute dbms_flashback.disable;
PL/SQL 过程已成功完成。
-------------------------------------------------------
（3）环境准备
SQL> begin
2 for i in 1..99999 loop
3 insert into zhong values(i,'shall');
4 end loop;
5 commit;
6 end;
7 /
PL/SQL 过程已成功完成。
SQL> select count(*) from zhong;
COUNT(*)
----------
99999
SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;
TO_CHAR(SYSDATE,'YY
-------------------
2016-05-14 12:03:44
SQL> select dbms_flashback.get_system_change_number from dual;
GET_SYSTEM_CHANGE_NUMBER
------------------------
282784
SQL>
SQL> select thread,time_mp,to_char(time_dp,'yyyy-mm-dd hh24:mi:ss'),scn_wrp,scn_bas from smon_scn_time ;
1 1463197983 2016-05-14 11:53:03 0 280126
1 1463198288 2016-05-14 11:58:10 0 281285
1 1463198596 2016-05-14 12:03:18 0 282698
（4）等待scn记录......
SQL> select thread,time_mp,to_char(time_dp,'yyyy-mm-dd hh24:mi:ss'),scn_wrp,scn_bas from smon_scn_time ;
1 1463197983 2016-05-14 11:53:03 0 280126
1 1463198288 2016-05-14 11:58:10 0 281285
1 1463198596 2016-05-14 12:03:18 0 282698
1 1463198903 2016-05-14 12:08:25 0 283509
（5）删除数据
SQL> delete zhong;
已删除99999行。
SQL> commit;
提交完成。
（6）闪回查询数据
SQL> execute dbms_flashback.enable_at_time(to_date('2016-05-14 12:03:44','yyyy-mm-dd hh24:mi:ss'));
PL/SQL 过程已成功完成。
SQL> select count(*) from zhong;
COUNT(*)
----------
99999
SQL> execute dbms_flashback.disable;
PL/SQL 过程已成功完成。
4利用闪回恢复数据（9i）
（1）利用闪回时间恢复数据
declare
cursor zhong_recover is select * from zhong;
t_recode zhong%rowtype;
begin
dbms_flashback.enable_at_time(to_date('2016-05-14 12:03:44','yyyy-mm-dd hh24:mi:ss'));
open zhong_recover;
dbms_flashback.disable;
loop fetch zhong_recover into t_recode;exit when zhong_recover%notfound;
insert into zhong values(t_recode.id,t_recode.name);
end loop;
close zhong_recover;
commit;
end;
/
PL/SQL 过程已成功完成。
SQL> select count(*) from zhong;
COUNT(*)
----------
99999
（2）利用闪回scn恢复数据
先删除之前恢复的部分数据
SQL> delete zhong where id>400;
已删除99599行。
SQL> select count(*) from zhong;
COUNT(*)
----------
400
SQL> commit;
提交完成。
创建表recover用于存放恢复数据
SQL> create table recover as select * from zhong where 1=0;
表已创建。
SQL> select * from recover;
未选定行
开始闪回恢复
declare
cursor zhong_recover is select * from zhong;
t_recode zhong%rowtype;
begin
dbms_flashback.enable_at_system_change_number(282784);
open zhong_recover;
dbms_flashback.disable;
loop fetch zhong_recover into t_recode;exit when zhong_recover%
notfound;
insert into zhong values(t_recode.id,t_recode.name);
insert into recover values(t_recode.id,t_recode.name);
end loop;
close zhong_recover;
commit;
end;
/
PL/SQL 过程已成功完成。
SQL> select count(*) from recover;
COUNT(*)
----------
99999
SQL> select count(*) from zhong;
COUNT(*)
----------
100399
（3）当恢复的数据再次写入到原表中时，会出现重复的数据，下面进行去重
--------------去重
delete from zhong
where id in (select id from zhong
group by id
having count(id)>1)
and rowid not in(select max(rowid) from zhong
group by id
having count(id)>1);
已删除400行。
SQL> select count(*) from zhong;
COUNT(*)
----------
99999
SQL> commit;
提交完成。
去重也这样写：
delete zhong z1
where z1.rowid <> (select max(rowid) from zhong z2
where z1.id=z2.id);
5.oracle 10g后flashback query新特性恢复数据实验
（1）环境准备
----数据库的模式
sys@ORCL>select log_mode,flashback_on,open_mode from v$database;
LOG_MODE FLASHBACK_ON OPEN_MODE
------------ ------------------ --------------------
NOARCHIVELOG NO READ WRITE
----创建测试表
sys@ORCL>create table shall as select * from dba_objects;
Table created.
sys@ORCL>select count(*) from shall;
COUNT(*)
----------
86565
（2）记录当前时间
sys@ORCL>select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;
TO_CHAR(SYSDATE,'YY
-------------------
2016-05-02 05:40:08
sys@ORCL>select dbms_flashback.get_system_change_number from dual;
GET_SYSTEM_CHANGE_NUMBER
------------------------
2687027
（3）删除数据并提交
sys@ORCL>delete shall;
86565 rows deleted.
sys@ORCL>commit;
Commit complete.
（4）闪回查询
sys@ORCL>select count(*) from shall;
COUNT(*)
----------
0
sys@ORCL>select count(*) from shall as of timestamp to_timestamp('2016-05-02 05:40:08','yyyy-mm-dd hh24:mi:ss');
COUNT(*)
----------
86565
sys@ORCL>select count(*) from shall as of scn 2687027;
COUNT(*)
----------
86565
（5）闪回恢复数据
sys@ORCL>insert into shall select * from shall as of scn 2687027;
86565 rows created.
sys@ORCL>select count(*) from shall;
COUNT(*)
----------
86565
sys@ORCL>commit;
Commit complete.
------如果删除数据后，表做了move或者说truncate表后还能恢复吗？No!
sys@ORCL>select sysdate from dual;
SYSDATE
-------------------
2016-05-02 05:48:02
sys@ORCL>delete shall;
86565 rows deleted.
sys@ORCL>alter table shall move;
Table altered.
sys@ORCL>select count(*) from shall as of timestamp to_timestamp('2016-05-02 05:48:02','yyyy-mm-dd hh24:mi:ss');
select count(*) from shall as of timestamp to_timestamp('2016-05-02 05:48:02','yyyy-mm-dd hh24:mi:ss')
*
ERROR at line 1:
ORA-01466: unable to read data - table definition has changed
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/30130773/viewspace-2102639/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/30130773/viewspace-2102639/
扫一扫
1347
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
