利用flashBack恢复误删除(delete)的表数据

时间:2021-03-14 21:46:07

Flashback query(闪回查询)原理

Oracle根据undo信息,利用undo数据,类似一致性读取方法,可以把表置于一个删除前的时间点(或SCN),从而将数据找回。

Flashback query(闪回查询)前提

SQL> show parameter undo;

利用flashBack恢复误删除(delete)的表数据

其中undo_management = auto,设置自动undo管理(AUM),该参数默认设置为:auto;
Undo_retention = n(秒),设置决定undo最多的保存时间,其值越大,就需要越多的undo表空间的支持。

修改undo_retention的命令如下:
SQL> alter system set undo_retention = 3600;

操作步骤:
1:获取数据删除前的一个时间点或scn

--获取一个时间点
SELECT to_timestamp('2013-08-16 09:40:00','yyyy-MM-dd hh24:mi:ss') AS TIMESTAMP FROM DUAL;
--获取scn
SELECT TO_CHAR(DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER) SCN FROM DUAL;

2:查询该时间点(或scn)的数据

--通过时间点查询已删除的数据
SELECT * FROM T_FLASHBACK AS OF TIMESTAMP TO_TIMESTAMP('2013-08-16 09:40:00','yyyy-MM-dd hh24:mi:ss')
--通过scn查询已删除数据
SELECT * FROM T_FLASHBACK AS OF SCN +步骤1中获得的scn

3:将查询出来的数据插入回原来的表

INSERT INTO T_FLASHBACK  SELECT * FROM T_FLASHBACK AS OF TIMESTAMP TO_TIMESTAMP('2013-08-16 09:40:00','yyyy-MM-dd hh24:mi:ss')

INSERT INTO T_FLASHBACK  SELECT * FROM T_FLASHBACK AS OF SCN +步骤1中获得的scn

示例:

--新建一个表用于测试
CREATE TABLE T_FLASHBACK(
  ID  NUMBER(2)
)

--插入测试数据
INSERT INTO T_FLASHBACK(ID) VALUES (1);
INSERT INTO T_FLASHBACK(ID) VALUES (2);
INSERT INTO T_FLASHBACK(ID) VALUES (3);
INSERT INTO T_FLASHBACK(ID) VALUES (4);
INSERT INTO T_FLASHBACK(ID) VALUES (5);

--查看测试数据
SELECT * FROM T_FLASHBACK;

利用flashBack恢复误删除(delete)的表数据

--删除数据(并提交commite)
DELETE FROM  T_FLASHBACK;

--再次查看数据
SELECT * FROM T_FLASHBACK;

利用flashBack恢复误删除(delete)的表数据
--查找未删除数据前某个时间点或SCN
SELECT to_timestamp('2013-08-16 09:40:00','yyyy-MM-dd hh24:mi:ss') AS TIMESTAMP FROM DUAL;
SELECT TO_CHAR(DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER) SCN FROM DUAL WHERE ; --14677482

--根据这个SCN(或时间点)查找出已删除的数据
SELECT * FROM T_FLASHBACK AS OF SCN 14677482;

利用flashBack恢复误删除(delete)的表数据
--把已删除的数据重新插回到原来的表(并提交)
INSERT INTO T_FLASHBACK SELECT * FROM T_FLASHBACK AS OF SCN 14677482;

--查看操作结果
SELECT * FROM T_FLASHBACK;

利用flashBack恢复误删除(delete)的表数据

Falshback query查询的局限:

1. 不能Falshback到5天以前的数据。
2. 闪回查询无法恢复到表结构改变之前,因为闪回查询使用的是当前的数据字典。
3. 受到undo_retention参数的影响,对于undo_retention之前的数据,Flashback不保证能Flashback成功。
4. 对drop,truncate等不记录回滚的操作,不能恢复。
5. 普通用户使用dbms_flashback包,必须通过管理员授权。命令如下:
SQL>grant execute on dbms_flashback to orcl(拥有当前表的用户);