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

利用Flashback Query 恢复误操作的数据Author：Kamus Seraphim（张乐奕）Date：2003-10Mail：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_retentionNAME TYPE VALUE------------------------------------ ----------- ------------------------------undo_retention integer 10800SQL> show parameter undo_managementNAME TYPE VALUE------------------------------------ ----------- ------------------------------undo_management string AUTO使用AS OF 关键字来对表，视图，或者物化视图进行Flashback Query，如果指定了SCN，那么expr 部分必须是一个数字，如果指定了TIMESTAMP，那么expr 必须是一个timestamp类型的值。查询结果将返回在指定的SCN 或者时间点上的数据。下面我们使用scott 方案来作一个实验。[zhangleyi@linux9 bin]$ sqlplus /nologSQL*Plus: Release 9.2.0.1.0 - Production on Thu Oct 9 23:44:07 2003Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.SQL> connect scott/tigerConnected.SQL> select sal from emp where empno=7369;SAL----------800SQL> 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 的子查询部分使用ASOF 那么该查询只能返回一条记录，否则将会报错。如下：SQL> select empno,sal from emp;EMPNO SAL---------- ----------7369 8007499 16007521 12507566 29757654 12507698 28507782 24507788 3000SQL> select sal from emp2 AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '1' DAY);3 where empno=7369;SAL----------800SQL> update emp set sal=2 (select sal from emp3 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----------800SQL> commit;7839 50007844 15007876 1100EMPNO SAL---------- ----------7900 9507902 30007934 130014 rows selected.SQL> update emp set sal=4000;14 rows updated.SQL> commit;Commit complete.SQL> select empno,sal from emp;EMPNO SAL---------- ----------7369 40007499 40007521 40007566 40007654 40007698 40007782 40007788 40007839 40007844 40007876 4000EMPNO SAL---------- ----------7900 40007902 40007934 400014 rows selected.SQL> select empno,sal from emp2 AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '1' DAY);EMPNO SAL---------- ----------7369 8007499 16007521 12507566 29757654 12507698 28507782 24507788 30007839 50007844 15007876 1100EMPNO SAL---------- ----------7900 9507902 30007934 130014 rows selected.SQL> update emp a set sal =2 (select sal from emp b3 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 as2 select empno,sal from emp3 AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '1' DAY);Table created.SQL> update emp a set sal =2 (select sal from empsal_temp b3 where a.empno=b.empno);14 rows updated.SQL> select empno,sal from emp;EMPNO SAL---------- ----------7369 8007499 16007521 12507566 29757654 12507698 28507782 24507788 30007839 50007844 15007876 1100EMPNO SAL---------- ----------7900 9507902 30007934 130014 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 的闪回查询SCNGET_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 ISCURSOR c_emp ISSELECT * FROM scott.emp;v_row c_emp%ROWTYPE;BEGINDBMS_FLASHBACK.ENABLE_AT_TIME(SYSTIMESTAMP - INTERVAL '1' DAY);OPEN c_emp;DBMS_FLASHBACK.DISABLE;LOOPFETCH c_empINTO v_row;EXIT WHEN c_emp%NOTFOUND;INSERT INTO scott.empVALUES(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 错误，InFlashback mode, user cannot perform DML or DDL operations。以上例子中的所有恢复都是基于时间点的，下面介绍基于SCN 的闪回查询。既然已经有基于时间点的恢复，为什么还需要基于SCN 呢，我们先来看一个例子。[zhangleyi@linux9 oralinux]$ sqlplus scott/tigerSQL*Plus: Release 9.2.0.1.0 - Production on Sat Oct 11 02:26:20 2003Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.Connected to:Oracle9i Enterprise Edition Release 9.2.0.1.0 - ProductionWith the Partitioning, OLAP and Oracle Data Mining optionsJServer Release 9.2.0.1.0 - ProductionSQL> select * from dept;DEPTNO DNAME LOC---------- -------------- -------------10 ACCOUNTING NEW YORK20 RESEARCH DALLAS30 SALES CHICAGO40 OPERATIONS BOSTONSQL> 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 YORK20 RESEARCH DALLAS30 SALES CHICAGO40 OPERATIONS BOSTON02:29:49 SQL> select * from dept as of TIMESTAMP (SYSTIMESTAMP -INTERVAL '2' MINUTE);DEPTNO DNAME LOC---------- -------------- -------------10 ACCOUNTING NEW YORK20 RESEARCH DALLAS30 SALES CHICAGO40 OPERATIONS BOSTON02:31:11 SQL> select * from dept as of TIMESTAMP (SYSTIMESTAMP -INTERVAL '3' MINUTE);DEPTNO DNAME LOC---------- -------------- -------------10 ACCOUNTING NEW YORK20 RESEARCH DALLAS30 SALES CHICAGO40 OPERATIONS BOSTON02:31:19 SQL> select * from dept as of TIMESTAMP (SYSTIMESTAMP -INTERVAL '4' MINUTE);DEPTNO DNAME LOC---------- -------------- -------------10 ACCOUNTING NEW YORK20 RESEARCH DALLAS30 SALES CHICAGO40 OPERATIONS BOSTON02:31:30 SQL> select * from dept as of TIMESTAMP (SYSTIMESTAMP -INTERVAL '5' MINUTE);DEPTNO DNAME LOC---------- -------------- -------------10 ACCOUNTING NEW YORK20 RESEARCH DALLAS30 SALES CHICAGO40 OPERATIONS BOSTON02:31:39 SQL> select * from dept as of TIMESTAMP (SYSTIMESTAMP -INTERVAL '6' MINUTE);DEPTNO DNAME LOC---------- -------------- -------------10 ACCOUNTING NEW YORK20 RESEARCH DALLAS30 SALES CHICAGO40 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 sysdbaConnected.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 CURRENTSQL> 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 wheresql_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 YORK20 RESEARCH DALLAS30 SALES CHICAGO40 OPERATIONS BOSTON60 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 SYS3．可能需要给其它用户授权才能允许其它用户执行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．如果需要恢复的表中有巨大的数据量，那么闪回查询会是一个极为昂贵的操作，此时可能作基于时间点的恢复反而更有效。