flashback query和logmnr用于恢复数据的误删除

摘自网络

 

当用户误删除ORACLE数据的时候,作为DBA我们应该怎么去处理呢?通常有三种处理方法:

1.FLASHBACK

2.LOGMINER

3.不完全恢复

第三中方法这里不打算讲,可以参考ORACLE相关的备份恢复文档.前两种恢复方法我到网上搜集了两个案例,读完后觉的有参考价值,特摘录了下来.

 

Oracle9i 中新增的闪回查询（Flashback Query）功能对于误删除或者误更新并且已经commit 了的情况，提供了简便快捷的恢复方法，而在Oracle 提供闪回查询之前，碰到这种情况只 能通过备份来进行基于时间点的恢复，无疑这比闪回查询要麻烦而且费时。

什么是Flashback Query

利用Oracle 多版本读一致的特性，在需要的时候通过undo 来提供所需的前镜像中的数据。 利用这个功能，可以看到历史数据（呵呵，就像时光倒流。月光宝盒？），甚至用历史数据 来修复误操作引起的错误。可以通过指定时间或者SCN 来检索需要的数据。
前提条件
数据库必须处于Automatic Undo Management 状态。
最大可以闪回查询的时间段由UNDO_RETENTION 初始化参数（单位为秒）指定
可以通过ALTER SYSTEM SET UNDO_RETENTION = ;来修改参数值
如何使用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
Uses Oracle's multiversion read-consistency capabilities to
restore data by applying undo as needed. You can view and
repair historical data, and you can perform queries on the
database as of a certain wall clock time or user-specified
system commit number (SCN)
备注：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;   --as of scn &scn 也可以通过SCN
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.

=====================================================

摘录:http://tolywang.itpub.net/post/48/451660

 

Logmnr在Oracle 9i以后做了众多的改进，如可以不需要build flat文本文件就可以分析日志了，也就表示可以不需要修改参数utl_file_dir就可以分析日志了，避免了修改参数utl_file_dir导致的重起数据库问题。另外也开始支持把字典信息build到联机日志中，在异地分析归档日志。

在flashback也不能帮上忙的时候，logmnr却是非常有用的。因为只要误操作时期的归档日志存在，就可以通过归档日志来恢复误删除（delete）的数据。

如果在Oracle 9i以上，采用在本地的在线数据字典分析归档日志，就这么简单：

1. Piner@9iR2>exec -
2. sys.dbms_logmnr.add_logfile(LogFileName=>'/archive_log/archive/1_9.arc',-
3. options => dbms_logmnr.new);
4. PL/SQL procedure successfully completed.
6. Piner@9iR2>exec sys.dbms_logmnr.start_logmnr( -
7. Options => sys.dbms_logmnr.dict_from_online_catalog);
8. PL/SQL procedure successfully completed.

可以看到，在线分析其实就只需要这两步，添加日志并分析日志。注意以上OPTIONS => DBMS_LOGMNR.NEW，表示添加第一个日志，如需要另外添加更多的日志，可以用如下方式即可。

1. Piner@9iR2>exec sys.dbms_logmnr.add_logfile(LogFileName=>'/archive_log/archive/1_10.arc');

如果日志分析完成，可以把需要的信息保存到临时表，如

1. Piner@9iR2>create table tmp_logmnr as
2. 2 select operation,sql_redo,sql_undo from v$logmnr_contents
3. 3 where seg_name='TEST';

然后，终止日志分析过程。

1. Piner@9iR2>exec sys.dbms_logmnr.end_logmnr

对于临时表中的SQL_UNDO，可以选择性的恢复，如采用如下的脚本来恢复，这里为了减少阻塞，每1000条提交一次。另外，需要注意的是，SQL_UNDO中的分号，如果想用动态SQL来执行的话，是需要去掉的。

1. declare
2. mysql varchar2(4000);
3. num number :=0;
4. begin
5. for c_tmp in (select sql_undo from tmp_logmnr where operation = 'DELETE') loop
6. --去掉语句中的分号,这里假定只有语句结尾有分号，语句中间并没有分号。
7. --如果语句中也有分号，则可以考虑替换语句结尾的;'（分号单引号）为'（单引号）。
8. mysql := replace(c_tmp.sql_undo,';','');
9. execute immediate mysql;
10. num := num + 1;
11. if mod(num,1000)=0 then
12. commit;
13. end if;
14. end loop;
15. commit;
16. exception
17. when others then
18. --异常处理
19. end;

以上的PL/SQL代码其实还可以加强，如恢复成功一条，就设置状态值为1，否则，设置状态值为-1，方便跟踪那些记录恢复成功，哪些记录恢复失败了。

另外，需要注意的是，在Oracle 10g以下，LOGMNR的临时表v$logmnr_contents，使用的是system表空间，在Oracle 10g以后改为sysaux表空间。可以使用如下的命令，更改logmnr的特定表空间，防止system表空间出现空间不够。