利用flashback query 恢复误操作的数据

利用Flashback Query 恢复误操作的数据
Author：Kamus Seraphim（张乐奕）
Date：2003-10
Mail：kamus@itpub.net
转载请注明出处及作者
Oracle9i 中新增的闪回查询（Flashback Query）功能对于误删除或者误更新并且已经commit
了的情况，提供了简便快捷的恢复方法，而在Oracle 提供闪回查询之前，碰到这种情况只
能通过备份来进行基于时间点的恢复，无疑这比闪回查询要麻烦而且费时。
什么是Flashback Query
利用Oracle 多版本读一致的特性，在需要的时候通过undo 来提供所需的前镜像中的数据。
利用这个功能，可以看到历史数据（呵呵，就像时光倒流。月光宝盒？），甚至用历史数据
来修复误操作引起的错误。可以通过指定时间或者SCN 来检索需要的数据。
前提条件
数据库必须处于Automatic Undo Management 状态。
最大可以闪回查询的时间段由UNDO_RETENTION 初始化参数（单位为秒）指定
可以通过ALTER SYSTEM SET UNDO_RETENTION = <seconds>;来修改参数值
如何使用Flashback Query
通过SQL
使用SELECT 语句的AS OF 来进行闪回查询，语法如下：
SQL> show parameter undo_retention
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_retention integer 10800
SQL> show parameter undo_management
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
使用AS OF 关键字来对表，视图，或者物化视图进行Flashback Query，如果指定了SCN，
那么expr 部分必须是一个数字，如果指定了TIMESTAMP，那么expr 必须是一个timestamp
类型的值。查询结果将返回在指定的SCN 或者时间点上的数据。
下面我们使用scott 方案来作一个实验。
[zhangleyi@linux9 bin]$ sqlplus /nolog
SQL*Plus: Release 9.2.0.1.0 - Production on Thu Oct 9 23:44:07 2003
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
SQL> connect scott/tiger
Connected.
SQL> select sal from emp where empno=7369;
SAL
----------
800
SQL> update emp set sal=4000 where empno=7369;
1 row updated.
SQL> commit;
Commit complete.
SQL> select sal from emp where empno=7369;
SAL
----------
4000
备注：TIMESTAMP (SYSTIMESTAMP - INTERVAL '1' DAY)指查询距当前时间一天以
前的时间点的数据，如果我们要查询一小时以前的，那么需要将DAY 替换成HOUR 即可，
查询10 分钟以前的将'1' DAY 替换'10' MINUTE。
以上演示了对于误更新的字段进行恢复的方法，但是如果想在update 的子查询部分使用AS
OF 那么该查询只能返回一条记录，否则将会报错。如下：
SQL> select empno,sal from emp;
EMPNO SAL
---------- ----------
7369 800
7499 1600
7521 1250
7566 2975
7654 1250
7698 2850
7782 2450
7788 3000
SQL> select sal from emp
2 AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '1' DAY);
3 where empno=7369;
SAL
----------
800
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;
1 row updated.
SQL> select sal from emp where empno=7369;
SAL
----------
800
SQL> commit;
7839 5000
7844 1500
7876 1100
EMPNO SAL
---------- ----------
7900 950
7902 3000
7934 1300
14 rows selected.
SQL> update emp set sal=4000;
14 rows updated.
SQL> commit;
Commit complete.
SQL> select empno,sal from emp;
EMPNO SAL
---------- ----------
7369 4000
7499 4000
7521 4000
7566 4000
7654 4000
7698 4000
7782 4000
7788 4000
7839 4000
7844 4000
7876 4000
EMPNO SAL
---------- ----------
7900 4000
7902 4000
7934 4000
14 rows selected.
SQL> select empno,sal from emp
2 AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '1' DAY);
EMPNO SAL
---------- ----------
7369 800
7499 1600
7521 1250
7566 2975
7654 1250
7698 2850
7782 2450
7788 3000
7839 5000
7844 1500
7876 1100
EMPNO SAL
---------- ----------
7900 950
7902 3000
7934 1300
14 rows selected.
SQL> update emp a set sal =
2 (select sal from emp b
3 AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '1' DAY)
4 where a.empno=b.empno);
AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '1' DAY)
*
ERROR at line 3:
ORA-00907: missing right parenthesis
其实上面的语法是没有问题的，但是可能是因为闪回查询的特殊性导致上面的SQL 报错，
而这种错误update 了大量数据时候的恢复才真正是闪回查询的方便所在，对于这种情况我
们可以有两种处理方法，一种是使用DBMS_FLASHBACK 包，将在后面介绍，另外一种方
法仍然是直接使用SQL，但是添加一个临时表作为中转，如下：
SQL> create table empsal_temp as
2 select empno,sal from emp
3 AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '1' DAY);
Table created.
SQL> update emp a set sal =
2 (select sal from empsal_temp b
3 where a.empno=b.empno);
14 rows updated.
SQL> select empno,sal from emp;
EMPNO SAL
---------- ----------
7369 800
7499 1600
7521 1250
7566 2975
7654 1250
7698 2850
7782 2450
7788 3000
7839 5000
7844 1500
7876 1100
EMPNO SAL
---------- ----------
7900 950
7902 3000
7934 1300
14 rows selected.
SQL> commit;
Commit complete.
SQL> drop table empsal_temp;
Table dropped.
这样我们就完成了错误数据的恢复。COOL!! RIGHT? :D
介绍DBMS_FLASHBACK 包
DBMS_FLASHBACK 包提供了以下几个函数：
ENABLE_AT_TIME：设置当前SESSION 的闪回查询时间
ENABLE_AT_SYSTEM_CHANGE_NUMBER：设置当前SESSION 的闪回查询SCN
GET_SYSTEM_CHANGE_NUMBER：取得当前数据库的SCN
比如：select dbms_flashback.get_system_change_number from dual；
DISABLE：关闭当前SESSION 的闪回查询
当将一个SESSION 设置为闪回查询模式之后，后续的查询都会基于那个时间点或者SCN 的
数据库状态，如果SESSION 结束，那么即使没有明确指定DISABLE，闪回查询也会自动失
效。
当SESSION 运行在闪回查询状态时，不允许进行任何DML 和DDL 操作。如果要用DML
操作来进行数据恢复就必须使用PL/SQL 游标。
即使SESSION 运行在闪回查询模式，SYSDATE 函数也不会受到影响，仍然会返回当前正
确的系统时间。
下面我们用一个例子说明如何使用DBMS_FLASHBACK 包来恢复数据。
假设由于误操作删除了SCOTT.EMP 表中的所有数据，现在我们要恢复。
SQL> delete from emp;
14 rows deleted.
SQL> commit;
Commit complete.
SQL> select count(*) from emp;
COUNT(*)
----------
0
然后执行下面的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;
PL/SQL procedure successfully completed.
SQL> select count(*) from emp;
COUNT(*)
----------
14
到此成功结束，检查EMP 表可以看到所有的数据已经全部都恢复了。
备注：在存储过程中我们创建了游标之后就将执行了DBMS_FLASHBACK.DISABLE，只
有这样我们才能在这个SESSION 中进行DML 操作。否则将产生ORA-08182 错误，In
Flashback mode, user cannot perform DML or DDL operations。
以上例子中的所有恢复都是基于时间点的，下面介绍基于SCN 的闪回查询。
既然已经有基于时间点的恢复，为什么还需要基于SCN 呢，我们先来看一个例子。
[zhangleyi@linux9 oralinux]$ sqlplus scott/tiger
SQL*Plus: Release 9.2.0.1.0 - Production on Sat Oct 11 02:26:20 2003
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
SQL> select * from dept;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SQL> set time on;
02:26:50 SQL> insert into dept values(60,'FLASH','BEIJING');
1 row created.
02:27:53 SQL> commit;
Commit complete.
02:27:57 SQL> delete from dept where deptno=60;
1 row deleted.
02:28:19 SQL> commit;
Commit complete.
02:28:21 SQL> select * from dept as of TIMESTAMP (SYSTIMESTAMP -
INTERVAL '1' MINUTE);
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
02:29:49 SQL> select * from dept as of TIMESTAMP (SYSTIMESTAMP -
INTERVAL '2' MINUTE);
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
02:31:11 SQL> select * from dept as of TIMESTAMP (SYSTIMESTAMP -
INTERVAL '3' MINUTE);
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
02:31:19 SQL> select * from dept as of TIMESTAMP (SYSTIMESTAMP -
INTERVAL '4' MINUTE);
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
02:31:30 SQL> select * from dept as of TIMESTAMP (SYSTIMESTAMP -
INTERVAL '5' MINUTE);
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
02:31:39 SQL> select * from dept as of TIMESTAMP (SYSTIMESTAMP -
INTERVAL '6' MINUTE);
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
可以发现我们闪回查询了1 分钟到6 分钟之前的所有前镜像都没有找到新插入的那条
DEPTNO＝60 的记录，虽然TIMESTAMP 可以精确到毫秒，但是很显然我们很难准确地
定位到毫秒级的时间点。至于为什么如此，biti 在论坛上的某个帖子中有所探讨，这里暂时
先不追究产生这种结果的原因。在这种情况下，使用基于SCN 的闪回查询是解决问题的最
好办法。
以往的一些测试例子，都是在insert 数据之后立刻使用DBMS_FLASHBACK 包中的
GET_SYSTEM_CHANGE_NUMBER 函数来返回当时的SCN，然后再利用AS OF SCN 来进
行闪回查询，但是实际应用中这是不可能的，因为在误操作之前不会运行这个函数。所以我
们要使用LOGMINER 来对redolog 进行分析，得到当时错误地update 或者delete 数据时的
SCN。
对于LOGMINER 的安装和使用方法本文不进行详细的叙述，请自行查阅文档。
下面是结合LOGMINER 进行闪回查询的例子，为了描述简便，假设从删除数据到目前
ONLINE REDO LOG 没有进行LOG SWITCH，也就是我们只需要分析当前ACTIVE 的
ONLINE REDO LOG 就可以了。
SQL> connect / as sysdba
Connected.
SQL> select b.MEMBER,a.STATUS from v$log a,v$logfile b where a.GROUP#=b.GROUP#;
MEMBER STATUS
-------------------------------------------- ----------------
/oracle/oradata/oralinux/redo01.log INACTIVE
/oracle/oradata/oralinux/redo02.log INACTIVE
/oracle/oradata/oralinux/redo03.log CURRENT
SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LogFileName =>
'/oracle/oradata/oralinux/redo03.log',Options => DBMS_LOGMNR.NEW);
PL/SQL procedure successfully completed.
SQL> EXECUTE DBMS_LOGMNR.START_LOGMNR(DictFileName =>
'/oracle/admin/oralinux/orcldict.ora');
PL/SQL procedure successfully completed.
SQL> select scn,sql_redo from (select * from v$logmnr_contents where
sql_redo like 'delete%' order by scn desc) where rownum<2;
SCN SQL_REDO
---------- -------------------------------------------------------------------
543523 delete from "SCOTT"."DEPT" where "DEPTNO" = '60' and "D
现在我们已经找到了删除那条记录时候的SCN 是543523。
SQL> select * from scott.dept as of scn 543523;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
60 FLASH BEIJING
搞定！我们利用SCN 进行闪回查询找到了删除前的那条数据，此时利用上文描述过的SQL
方法或者DBMS_FLASHBACK 方法都可以进行数据恢复了。
几个注意点：
1． Flashback Query 对于DDL 操作（比如DROP）无效，只能适用于DML 的误操作
（UPDATE，DELETE）
2． SYS 用户不允许执行DBMS_FLASHBACK 包，将会产生ORA-08185 错误，
Flashback not supported for user SYS
3．可能需要给其它用户授权才能允许其它用户执行DBMS_FLASHBACK 包，需要执
行：GRANT EXECUTE ON DBMS_FLASHBACK TO SCOTT;
4．闪回查询的功能不止是适用于数据恢复，同样适用于DSS 和OLAP，比如需要查询
在前一个小时内生成的新订单，那么就可以利用AS OF 来取得两个时间点的查询结
果的差集。
5．如果结合使用LOGON TRIGGERS，那么可以实现不更改代码就支持各个时间点的
报表查询功能。
6． Flashback Query 的查询速度依赖于需要执行多少UNDO，也就是想查询多长时间以
前的数据库快照，回溯的时间越久可能执行的速度就越慢。
7． Flashback Query 不会真正的UNDO 任何数据，仅仅是一个查询的机制而已。
8． Flashback Query 不会告诉你到底数据发生了哪些变化，这是LOGMINER 的功能。
9．如果需要恢复的表中有巨大的数据量，那么闪回查询会是一个极为昂贵的操作，此
时可能作基于时间点的恢复反而更有效。