基于scn的数据恢复

不小心delete掉一些数据,而且已经commit了,怎么办?我们可以基于回闪(flashback)和scn进行一些数据的恢复。

--查询当前scn

SQL> select dbms_flashback.get_system_change_number from dual;


GET_SYSTEM_CHANGE_NUMBER

------------------------

4787371


SQL> SELECT COUNT(*) FROM hjm_tb_test01;


COUNT(*)

----------

10072

--模拟误删除数据:

SQL> delete from hjm_tb_test01 where object_id<=5000;


已删除4940行。


SQL> commit;


提交完成。

--删除数据后只有5132行了,原为10072行

SQL> SELECT COUNT(*) FROM hjm_tb_test01;


COUNT(*)

----------

5132

--创建用于恢复的表,将恢复数据插入其中

SQL> create table hjm_tb_test01_recov as select * from hjm_tb_test01 where 1=0;


表已创建。


SQL> select dbms_flashback.get_system_change_number from dual;


GET_SYSTEM_CHANGE_NUMBER

------------------------

4788873


SQL> select count(*) from hjm_tb_test01 as of scn &scn;

输入 scn 的值: 4788873

原值 1: select count(*) from hjm_tb_test01 as of scn &scn

新值 1: select count(*) from hjm_tb_test01 as of scn 4788873


COUNT(*)

----------

5132


SQL> /

输入 scn 的值: 4787873

原值 1: select count(*) from hjm_tb_test01 as of scn &scn

新值 1: select count(*) from hjm_tb_test01 as of scn 4787873


COUNT(*)

----------

10072


SQL> /

输入 scn 的值: 4788872

原值 1: select count(*) from hjm_tb_test01 as of scn &scn

新值 1: select count(*) from hjm_tb_test01 as of scn 4788872


COUNT(*)

----------

5132


SQL> /

输入 scn 的值: 4788772

原值 1: select count(*) from hjm_tb_test01 as of scn &scn

新值 1: select count(*) from hjm_tb_test01 as of scn 4788772


COUNT(*)

----------

5132


SQL> /

输入 scn 的值: 4787972

原值 1: select count(*) from hjm_tb_test01 as of scn &scn

新值 1: select count(*) from hjm_tb_test01 as of scn 4787972


COUNT(*)

----------

10072


SQL> /

输入 scn 的值: 4788072

原值 1: select count(*) from hjm_tb_test01 as of scn &scn

新值 1: select count(*) from hjm_tb_test01 as of scn 4788072


COUNT(*)

----------

10072


SQL>

SQL> /

输入 scn 的值: 4788999

原值 1: select count(*) from hjm_tb_test01 as of scn &scn

新值 1: select count(*) from hjm_tb_test01 as of scn 4788999


COUNT(*)

----------

5132


SQL> /

输入 scn 的值: 4788500

原值 1: select count(*) from hjm_tb_test01 as of scn &scn

新值 1: select count(*) from hjm_tb_test01 as of scn 4788500


COUNT(*)

----------

5132


SQL> /

输入 scn 的值: 4788700

原值 1: select count(*) from hjm_tb_test01 as of scn &scn

新值 1: select count(*) from hjm_tb_test01 as of scn 4788700


COUNT(*)

----------

5132


SQL> /

输入 scn 的值: 4788800

原值 1: select count(*) from hjm_tb_test01 as of scn &scn

新值 1: select count(*) from hjm_tb_test01 as of scn 4788800


COUNT(*)

----------

5132


SQL> /

输入 scn 的值: 4788900

原值 1: select count(*) from hjm_tb_test01 as of scn &scn

新值 1: select count(*) from hjm_tb_test01 as of scn 4788900


COUNT(*)

----------

5132


SQL> /

输入 scn 的值: 4788950

原值 1: select count(*) from hjm_tb_test01 as of scn &scn

新值 1: select count(*) from hjm_tb_test01 as of scn 4788950


COUNT(*)

----------

5132


SQL> /

输入 scn 的值: 4788990

原值 1: select count(*) from hjm_tb_test01 as of scn &scn

新值 1: select count(*) from hjm_tb_test01 as of scn 4788990


COUNT(*)

----------

5132


SQL> /

输入 scn 的值: 4788995

原值 1: select count(*) from hjm_tb_test01 as of scn &scn

新值 1: select count(*) from hjm_tb_test01 as of scn 4788995


COUNT(*)

----------

5132


SQL> /

输入 scn 的值: 4788998

原值 1: select count(*) from hjm_tb_test01 as of scn &scn

新值 1: select count(*) from hjm_tb_test01 as of scn 4788998


COUNT(*)

----------

5132


SQL> /

输入 scn 的值: 4788999

原值 1: select count(*) from hjm_tb_test01 as of scn &scn

新值 1: select count(*) from hjm_tb_test01 as of scn 4788999


COUNT(*)

----------

5132


SQL> /

输入 scn 的值: 4788572

原值 1: select count(*) from hjm_tb_test01 as of scn &scn

新值 1: select count(*) from hjm_tb_test01 as of scn 4788572


COUNT(*)

----------

5132


SQL> /

输入 scn 的值: 4788200

原值 1: select count(*) from hjm_tb_test01 as of scn &scn

新值 1: select count(*) from hjm_tb_test01 as of scn 4788200


COUNT(*)

----------

10072


SQL> /

输入 scn 的值: 4788300

原值 1: select count(*) from hjm_tb_test01 as of scn &scn

新值 1: select count(*) from hjm_tb_test01 as of scn 4788300


COUNT(*)

----------

10072


SQL> /

输入 scn 的值: 4788400

原值 1: select count(*) from hjm_tb_test01 as of scn &scn

新值 1: select count(*) from hjm_tb_test01 as of scn 4788400


COUNT(*)

----------

5132


SQL> /

输入 scn 的值: 4788350

原值 1: select count(*) from hjm_tb_test01 as of scn &scn

新值 1: select count(*) from hjm_tb_test01 as of scn 4788350


COUNT(*)

----------

10072


SQL> insert into hjm_tb_test01_recov select * from hjm_tb_test01 as of scn 4788350;


已创建10072行。


SQL> commit;


提交完成。


SQL>

相关文章

发表评论

电子邮件地址不会被公开。 必填项已用*标注