闪回查询

Automatic Undo Management

[@more@]

闪回查询（Flashback Query）功能对于误删除或者误更新并且已经commit 了的情况，提供了简便快捷的恢复方法:

前提条件

数据库必须处于 Automatic Undo Management 状态。

SQL> show parameter undo_management

最大可以闪回查询的时间段由 UNDO_RETENTION 初始化参数（单位为秒）指定

SQL> show parameter undo_retention

可以通过 ALTER SYSTEM SET UNDO_RETENTION = ;来修改参数值

使用AS OF 关键字来对表，视图，或者物化视图进行Flashback Query，如果指定了SCN，那么expr 部分必须是一个数字，如果指定了TIMESTAMP，那么expr 必须是一个timestamp类型的值。查询结果将返回在指定的SCN 或者时间点上的数据。

下面我们使用 scott 方案来作一个实验。

SQL> select sal from emp where empno=7369;

SQL> update emp set sal=4000 where empno=7369;

SQL> commit;

SQL> select sal from emp where empno=7369;

SQL> select sal from emp

2 AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '1' DAY);

3 where empno=7369;

SQL> update emp set sal=

2 (select sal from emp

3 AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '1' DAY)

4 where empno=7369)

5 where empno=7369;

SQL> select sal from emp where empno=7369;

SQL> commit;

备注：TIMESTAMP (SYSTIMESTAMP - INTERVAL '1' DAY)指查询距当前时间一天以前的时间点的数据，如果我们要查询一小时以前的，那么需要将DAY 替换成HOUR 即可，查询10 分钟以前的将'1' DAY 替换'10' MINUTE。以上演示了对于误更新的字段进行恢复的方法，但是如果想在update 的子查询部分使用AS OF更新大量数据，那么因为该查询只能一次更新一条记录，将会报错。对于这种情况我们可以有两种处理方法，一种是使用DBMS_FLASHBACK 包，将在后面介绍，另外一种方法仍然是直接使用SQL，但是添加一个临时表作为中转，如下：

SQL> create table empsal_temp as

2 select empno,sal from emp

3 AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '1' DAY);

SQL> update emp a set sal =

2 (select sal from empsal_temp b

3 where a.empno=b.empno);

SQL> select empno,sal from emp;

SQL> commit;

SQL> drop table empsal_temp;

这样我们就完成了错误数据的恢复。

介绍DBMS_FLASHBACK 包

下面我们用一个例子说明如何使用 DBMS_FLASHBACK 包来恢复数据。

假设由于误操作删除了 SCOTT.EMP 表中的所有数据，现在我们要恢复。

SQL> delete from emp;

SQL> commit;

SQL> select count(*) from emp;

然后执行下面的 SQL 创建一个存储过程用于恢复数据

CREATE OR REPLACE PROCEDURE prc_recoveremp IS

CURSOR c_emp IS

SELECT * FROM scott.emp;

v_row c_emp%ROWTYPE;

BEGIN

DBMS_FLASHBACK.ENABLE_AT_TIME(SYSTIMESTAMP - INTERVAL '1' DAY);

OPEN c_emp;

DBMS_FLASHBACK.DISABLE;

LOOP

FETCH c_emp

INTO v_row;

EXIT WHEN c_emp%NOTFOUND;

INSERT INTO scott.emp

VALUES

(v_row.EMPNO,

v_row.ENAME,

v_row.JOB,

v_row.MGR,

v_row.HIREDATE,

v_row.SAL,

v_row.COMM,

v_row.DEPTNO);

END LOOP;

CLOSE c_emp;

COMMIT;

END prc_recoveremp;

SQL> execute prc_recoveremp;

SQL> select count(*) from emp;

到此成功结束，检查 EMP 表可以看到所有的数据已经全部都恢复了。

备注：在存储过程中我们创建了游标之后就将执行了DBMS_FLASHBACK.DISABLE，只有这样我们才能在这个SESSION 中进行DML 操作。否则将产生ORA-08182 错误，In Flashback mode, user cannot perform DML or DDL operations。以上例子中的所有恢复都是基于时间点的，下面介绍基于 SCN 的闪回查询。既然已经有基于时间点的恢复，为什么还需要基于 SCN 呢，我们先来看一个例子。

SQL> select * from dept;

SQL> set time on;

02:26:50 SQL> insert into dept values(60,'FLASH','BEIJING');

02:27:53 SQL> commit;

02:27:57 SQL> delete from dept where deptno=60;

02:28:19 SQL> commit;

02:28:21 SQL> select * from dept as of TIMESTAMP (SYSTIMESTAMP -

INTERVAL '1' MINUTE);

02:31:39 SQL> select * from dept as of TIMESTAMP (SYSTIMESTAMP -

INTERVAL '6' MINUTE);

可以发现我们闪回查询了1 分钟到6 分钟之前的所有前镜像都没有找到新插入的那条

DEPTNO＝60 的记录，虽然TIMESTAMP 可以精确到毫秒，但是很显然我们很难准确地

定位到毫秒级的时间点。我们要使用LOGMINER 来对redolog 进行分析，得到当时错误地update 或者delete 数据时的SCN。

对于 LOGMINER 的安装和使用方法本文不进行详细的叙述，请自行查阅文档。

下面是结合 LOGMINER 进行闪回查询的例子，为了描述简便，假设从删除数据到目前

ONLINE REDO LOG 没有进行LOG SWITCH，也就是我们只需要分析当前ACTIVE 的

ONLINE REDO LOG 就可以了。

SQL> select b.MEMBER,a.STATUS from v$log a,v$logfile b where a.GROUP#=b.GROUP#;

SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LogFileName =>

'/oracle/oradata/oralinux/redo03.log',Options => DBMS_LOGMNR.NEW);

SQL> EXECUTE DBMS_LOGMNR.START_LOGMNR(DictFileName =>

'/oracle/admin/oralinux/orcldict.ora');

SQL> select scn,sql_redo from (select * from v$logmnr_contents where

sql_redo like 'delete%' order by scn desc) where rownum<2;

现在我们已经找到了删除那条记录时候的 SCN 是543523。

SQL> select * from scott.dept as of scn 543523;

我们利用SCN 进行闪回查询找到了删除前的那条数据，此时利用上文描述过的SQL

方法或者DBMS_FLASHBACK 方法都可以进行数据恢复了。

注意： 1. Flashback Query 对于DDL 操作（比如DROP）无效，只能适用于DML 的误操作（UPDATE，DELETE）

2. 可能需要给其它用户授权才能允许其它用户执行 DBMS_FLASHBACK 包，需要执行：

GRANT EXECUTE ON DBMS_FLASHBACK TO SCOTT;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/630733/viewspace-1026083/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/630733/viewspace-1026083/