Flashback query(闪回查询)原理
Oracle根据undo信息,利用undo数据,类似一致性读取方法,可以把表置于一个删除前的时间点(或SCN),从而将数据找回。
Flashback query(闪回查询)前提
SQL> show parameter undo;
其中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;
--删除数据(并提交commite)
DELETE FROM T_FLASHBACK;
--再次查看数据
SELECT * FROM T_FLASHBACK;
--查找未删除数据前某个时间点或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;
--把已删除的数据重新插回到原来的表(并提交)
INSERT INTO T_FLASHBACK SELECT * FROM T_FLASHBACK AS OF SCN 14677482;
--查看操作结果
SELECT * FROM T_FLASHBACK;
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(拥有当前表的用户);