基于LOGMINER 的表DML误操作恢复

 

经常遇到开发人员错误删除，修改,插入等误操作,事后又不知道具体操作哪些数据,希望把恢复到某某时间点,在数据库开归档并有备份的情况的下有一下方法

1 闪回数据库(需要开启flashback)

2 异机数据库不完全恢复(可基于部分表空间)

3 LOGMINER 日志挖掘(需要设置supplemental logging)

对DML误操作,如果flashback没有开启,只有选择后两种方法,如果数据库较大对于第二种方法将比较笨重,需要准备新的机器,安装软件,拷贝备份,执行恢复。在开启supplemental logging(建议数据库开启supplemental logging最小模式,对日志量影响较小)，情况下则可以选择LOGMINER。

关于使用logminer 需要开启supplemental logging 官方文档有明确说明

Database-Level Supplemental Logging

There are two types of database-level supplemental logging: minimal supplemental logging and identification key logging, as described in the following sections. Minimal supplemental logging does not impose significant overhead on the database generating the redo log files. However, enabling database-wide identification key logging can impose overhead on the database generating the redo log files. Oracle recommends that you at least enable minimal supplemental logging for LogMiner

 

---挖掘过程

一 创建用户

create user logmnr_user identified by logmnr_user;

grant connect,resource,dba to logmnr_user;

alter user logmnr_user default tablespace TBS_XINHUA40_MZMX_1

二 复制源表

  create table LOGMNR_USER.D405 nologging as select * from FANGCHENG40.D405 where 1=2;

  alter table LOGMNR_USER.D405 add r_rowid varchar2(1000);

  create index LOGMNR_USER.D405_ROWID on LOGMNR_USER.D405(r_rowid);

把源表的rowid 作为新表的一个字段,方便快速更新

  insert /*+ append */ into LOGMNR_USER.D405(D405_01,

                                                    D405_02,

                                                    D405_03,

                                                    D405_04,

                                                    D405_05,

                                                    D405_06,

                                                    D405_07,

                                                    D405_08,

                                                    D405_09,

                                                    D405_10,

                                                    r_rowid) Select D405_01,

                                                    D405_02,

                                                    D405_03,

                                                    D405_04,

                                                    D405_05,

                                                    D405_06,

                                                    D405_07,

                                                    D405_08,

                                                    D405_09,

                                                    D405_10,

                                                    rowid

                                               From FANGCHENG40.D405;

  commit;

 

exec  dbms_stats.gather_table_stats(ownname => 'LOGMNR_USER',tabname => 'D405',granularity => 'ALL',cascade => true);

三 修改参数

alter system set utl_file_dir='/home/oracle' scope=spfile;

shutdown immediate

startup

四 生成字典

exec dbms_logmnr_d.build('dictionary.ora','/home/oracle');

五 添加需要的日志

查询需要添加的日志

select name from v$archived_log a where a.FIRST_TIME>=to_date('&DT','yyyy-mm-dd HH24:mi:ss')

添加日志

 

exec dbms_logmnr.add_logfile(LogFileName=>'/data3/archivelog/1_56140_690352144.dbf',Options=>dbms_logmnr.new);

exec dbms_logmnr.add_logfile(LogFileName=>'/data3/archivelog/1_56141_690352144.dbf',Options=>dbms_logmnr.addfile);

exec dbms_logmnr.add_logfile(LogFileName=>'/data3/archivelog/1_56142_690352144.dbf',Options=>dbms_logmnr.addfile);

exec dbms_logmnr.add_logfile(LogFileName=>'/data3/archivelog/1_56143_690352144.dbf',Options=>dbms_logmnr.addfile);

exec dbms_logmnr.add_logfile(LogFileName=>'/data3/archivelog/1_56144_690352144.dbf',Options=>dbms_logmnr.addfile);

exec dbms_logmnr.add_logfile(LogFileName=>'/data3/archivelog/1_56145_690352144.dbf',Options=>dbms_logmnr.addfile);

exec dbms_logmnr.add_logfile(LogFileName=>'/data3/archivelog/1_56146_690352144.dbf',Options=>dbms_logmnr.addfile);

六 挖日志

exec dbms_logmnr.start_logmnr(dictfilename=>'/home/oracle/dictionary.ora');

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

create table logmnr_user.tab_logmnr_contents  nologging as select * from v$logmnr_contents where 1=2;

insert /*+ append */ into logmnr_user.tab_logmnr_contents select * from v$logmnr_contents;

commit;

八 结束日志挖掘

exec dbms_logmnr.end_logmnr;

---处理数据阶段

一 检测是否发生过DDL

select operation

from LOGMNR_USER.TAB_LOGMNR_CONTENTS

WHERE

SEG_NAME = 'D405' AND

SEG_OWNER = 'FANGCHENG40'

group by operation

 二 处理数据

根据新增加的R_ROWID列UPDATE DELETE

 

  declare i int;

begin

  i:=0;

for aa in (select replace(replace(SQL_UNDO,'"FANGCHENG40"','"LOGMNR_USER"'),'ROWID','R_ROWID') sql_undo

from LOGMNR_USER.TAB_LOGMNR_CONTENTS

WHERE

SEG_NAME = 'D405' AND

SEG_OWNER = 'FANGCHENG40'

and operation in ('INSERT','UPDATE','DELETE')

and TIMESTAMP>=to_TIMESTAMP('2013-12-02 09:17:00','yyyy-mm-dd hh24:mi:ss')

order by to_char(TIMESTAMP,'yyyy-mm-dd hh24:mi:ss') desc) loop

execute immediate replace(aa.sql_undo,';','');

i:=i+1;

end loop;

dbms_output.put_line(to_char(i));

end;

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

转载于:http://blog.itpub.net/15747463/viewspace-1062487/