利用LOGMNR恢复DELETE的数据

利用LOGMNR恢复DELETE的数据

  (2011-07-31 13:20:44)

▼

测试如下：

create table ta.test3 as select * from user_objects;

select count(*) from ta.test3;

delet ta.test3;

commit;

1、假设由于时间的问题，用闪回报错。

SELECT count(*) from ta.test3 AS OF TIMESTAMP TO_TIMESTAMP('2011-06-09 15:29:00','YYYY-MM-DD HH24:MI:SS');
ERROR at line 1:
ORA-01555: snapshot too old: rollback segment number 1 with name "_SYSSMU1$"
too small

2、利用LOGMNR来恢复

一、备份原表

二、修改参数，用于挖日志，重启数据库

SQL> show parameter utl;
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
create_stored_outlines               string     
utl_file_dir                         string    

SQL> show parameter spfile;
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      /oracle/app/product/10.2.0/dbs/spfileofdata.ora
 
SQL> alter system set utl_file_dir='/arclogs/' scope=spfile;

三、生成数据字典（用SYS用户执行）

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

sql>  sys.dbms_logmnr_d.set_tablespace('USERS');

SQL> exec dbms_logmnr_d.build('dictionary.ora','/arclogs/');
 PL/SQL procedure successfully completed
 做这一步之前注意需要修改LD_LIBRARY_PATH和LIBPATH，使得lib的变量在lib32前面。不然会有报错ORA-00600: internal error code, arguments: [unable to load XDB library], [], [], [], [], [], [], []

四、检查需要回滚的日志；

[oracle@tatuxtest arclogs]$ ll
-rw-r----- 1 oracle oinstall 199549952 07-22 13:30 archi_1_4717_714205044.dbf
-rw-r----- 1 oracle oinstall 199549952 07-22 13:33 archi_1_4718_714205044.dbf
-rw-r----- 1 oracle oinstall 199549952 07-22 16:04 archi_1_4719_714205044.dbf
-rw-r----- 1 oracle oinstall 199549440 07-22 16:07 archi_1_4720_714205044.dbf
-rw-r----- 1 oracle oinstall 199641088 07-24 08:21 archi_1_4721_714205044.dbf
-rw-r----- 1 oracle oinstall 199549952 07-26 15:38 archi_1_4722_714205044.dbf
-rw-r----- 1 oracle oinstall 199549952 07-26 15:56 archi_1_4723_714205044.dbf
-rw-r----- 1 oracle oinstall 199549952 07-27 10:54 archi_1_4724_714205044.dbf
-rw-r----- 1 oracle oinstall 199549952 07-29 11:39 archi_1_4725_714205044.dbf
-rw-r----- 1 oracle oinstall 158471168 07-31 12:26 archi_1_4726_714205044.dbf
-rw-r----- 1 oracle oinstall      1024 07-31 12:26 archi_1_4727_714205044.dbf
-rw-r----- 1 oracle oinstall      3584 07-31 12:26 archi_1_4728_714205044.dbf
-rw-r----- 1 oracle oinstall    299520 07-31 12:26 archi_1_4729_714205044.dbf
-rw-r----- 1 oracle oinstall      1024 07-31 12:26 archi_1_4730_714205044.dbf
-rw-r----- 1 oracle oinstall      3584 07-31 12:26 archi_1_4731_714205044.dbf
五、添加归档日志

SQL> exec dbms_logmnr.add_logfile(LogFileName => '/arclogs/archi_1_4726_714205044.dbf',Options=>dbms_logmnr.new);
 
PL/SQL procedure successfully completed
 
SQL> exec dbms_logmnr.add_logfile(LogFileName => '/arclogs/archi_1_4727_714205044.dbf',Options=>dbms_logmnr.addfile);
 
PL/SQL procedure successfully completed
 
SQL> exec dbms_logmnr.add_logfile(LogFileName => '/arclogs/archi_1_4728_714205044.dbf',Options=>dbms_logmnr.addfile);
 
PL/SQL procedure successfully completed
 
SQL> exec dbms_logmnr.add_logfile(LogFileName => '/arclogs/archi_1_4729_714205044.dbf',Options=>dbms_logmnr.addfile);
 
PL/SQL procedure successfully completed
 
SQL> exec dbms_logmnr.add_logfile(LogFileName => '/arclogs/archi_1_4730_714205044.dbf',Options=>dbms_logmnr.addfile);
 
PL/SQL procedure successfully completed
 
SQL> exec dbms_logmnr.add_logfile(LogFileName => '/arclogs/archi_1_4731_714205044.dbf',Options=>dbms_logmnr.addfile);
 PL/SQL procedure successfully completed

六：开始挖日志

SQL> exec dbms_logmnr.start_logmnr(dictfilename=>'/arclogs/dictionary.ora');
 PL/SQL procedure successfully completed

七、将logmnr的数据暂时保存在一个表里面，免得再次查询时候不用再次添加归档日志

SQL> create table test_logmnr nologging as select * from v$logmnr_contents where 1=2;
 
Table created
 
SQL> insert into test_logmnr select * from v$logmnr_contents;
 419563 rows inserted

做这一步之前注意将nls_date_format改成'yyyy-mm-dd hh24:mi:ss'，不然乱码，时间会变成问号。

八、终止日志分析过程。

sql>exec sys.dbms_logmnr.end_logmnr；

九、利用脚本导入

declare
  mysql varchar2(4000);
  num number :=0;
begin
  for c_tmp in (select sql_undo from test_logmnr  where seg_name='TEST3' and OPERATION LIKE 'DELETE') loop
      mysql := replace(c_tmp.sql_undo,';','');
execute immediate mysql;
    num := num + 1;
    if mod(num,1000)=0 then
      commit;
    end if;
  end loop;
  commit;
exception
when others then
   dbms_output.put_line('errors');
end;
/

十、检查数据。

注意：如果是DROP或者TRUNCATE，不能用此方法。但可以用ODU恢复，当然10G，有闪回表的功能,ODU，或者利用恢复DATAGUARD。

转至Ryan的BLOG

http://blog.sina.com.cn/congyalinux