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

首先查看服务器是否有dbms_logmnr与dbms_logmnr_d两个包.是否已设置如下参数:

sys@ORCL> show parameter UTL_FILE_DIR

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

utl_file_dir string /home/oracle/logminer

若sys下无dbms_logmnr与dbms_logmnr_d此两个包,则要以sys用户运行如下脚本:

$ORACLE_HOME/rdbms/admin/dbmslm.sql

$ORACLE_HOME/rdbms/admin/dbmslmd.sql

$ORACLE_HOME/rdbms/admin/dbmslms.sql

若未设置UTL_FILE_DIR参数值,则如下操作进行设置:

alter system set UTL_FILE_DIR ='/home/oracle/logminer' scope=spfile;

shutdown immediate

startup

模拟删除数据:

ctoc@ORCL> select count(*) from esms;

COUNT(*)

----------

207

ctoc@ORCL> delete esms;

207 rows deleted.

ctoc@ORCL> commit;

Commit complete.

ctoc@ORCL> select count(*) from esms;

COUNT(*)

----------

0

将dbms_logmnr与dbms_logmnr_d两个包的执行权限授给出ctoc用户:

sys@ORCL> grant execute on dbms_logmnr to ctoc;

Grant succeeded.

sys@ORCL> grant execute on dbms_logmnr_d to ctoc;

Grant succeeded.

分析日志(可以是离线日志和在线日志,方法几乎一样,以下是对在线日志进行分析)

sys@ORCL> select group#,sequence#,archived,status from v$log;

GROUP# SEQUENCE# ARC STATUS

---------- ---------- --- ----------------

1 166 YES INACTIVE

2 167 YES INACTIVE

3 168 NO CURRENT

sys@ORCL> col member for a40

sys@ORCL> select * from v$logfile;

GROUP# STATUS TYPE MEMBER

---------- ------- ------- ----------------------------------------

2 ONLINE /oracle/oradata/orcl/redo02.log

1 ONLINE /oracle/oradata/orcl/redo01.log

3 ONLINE /oracle/oradata/orcl/redo03.log

ctoc@ORCL> exec sys.dbms_logmnr_d.build('dictionary_esms.ora','/home/oracle/logminer');

LogMnr Dictionary Procedure started

LogMnr Dictionary File Opened

TABLE: OBJ$ recorded in LogMnr Dictionary File

TABLE: TAB$ recorded in LogMnr Dictionary File

TABLE: COL$ recorded in LogMnr Dictionary File

TABLE: TS$ recorded in LogMnr Dictionary File

TABLE: IND$ recorded in LogMnr Dictionary File

TABLE: USER$ recorded in LogMnr Dictionary File

TABLE: TABPART$ recorded in LogMnr Dictionary File

TABLE: INDPART$ recorded in LogMnr Dictionary File

TABLE: TABSUBPART$ recorded in LogMnr Dictionary File

TABLE: TABCOMPART$ recorded in LogMnr Dictionary File

TABLE: TYPE$ recorded in LogMnr Dictionary File

TABLE: COLTYPE$ recorded in LogMnr Dictionary File

TABLE: ATTRIBUTE$ recorded in LogMnr Dictionary File

TABLE: ENCRYPTION_PROFILE$ ORA-00942: table or view does not exist

TABLE: ENCRYPTED_OBJ$ ORA-00942: table or view does not exist

TABLE: LOB$ recorded in LogMnr Dictionary File

TABLE: CDEF$ recorded in LogMnr Dictionary File

TABLE: CCOL$ recorded in LogMnr Dictionary File

TABLE: ICOL$ recorded in LogMnr Dictionary File

TABLE: ATTRCOL$ recorded in LogMnr Dictionary File

Procedure executed successfully - LogMnr Dictionary Created

PL/SQL procedure successfully completed.

ctoc@ORCL>exec sys.dbms_logmnr.add_logfile('/oracle/oradata/orcl/redo03.log',sys.dbms_logmnr.new);

PL/SQL procedure successfully completed.

ctoc@ORCL>execute sys.dbms_logmnr.add_logfile('/oracle/oradata/orcl/redo03.log',sys.dbms_logmnr.new);

PL/SQL procedure successfully completed.

ctoc@ORCL>execute sys.dbms_logmnr.add_logfile('/oracle/oradata/orcl/redo02.log',sys.dbms_logmnr.addfile);

PL/SQL procedure successfully completed.

ctoc@ORCL>execute sys.dbms_logmnr.add_logfile('/oracle/oradata/orcl/redo01.log',sys.dbms_logmnr.addfile);

PL/SQL procedure successfully completed.

ctoc@ORCL>execute sys.dbms_logmnr.start_logmnr(dictfilename=>'/home/oracle/logminer/dictionary_esms.ora');

PL/SQL procedure successfully completed.

恢复数据:

ctoc@ORCL> create table esms_logmnr as

2 select operation,sql_redo,sql_undo from v$logmnr_contents

3 where seg_name='ESMS';

Table created.

ctoc@ORCL> select count(*) from esms_logmnr;

COUNT(*)

----------

207

ctoc@ORCL> execute sys.dbms_logmnr.end_logmnr

PL/SQL procedure successfully completed.

ctoc@ORCL> declare

2 mysql varchar2(4000);

3 num number :=0;

4 begin

5 for c_tmp in (select sql_undo from esms_logmnr where peration = 'DELETE') loop

6 mysql := replace(c_tmp.sql_undo,';','');

7 execute immediate mysql;

8 num := num + 1;

9 if mod(num,100)=0 then

10 commit;

11 end if;

12 end loop;

13 commit;

14 end;

15 /

PL/SQL procedure successfully completed.

ctoc@ORCL> select count(*) from esms;

COUNT(*)

----------

207

至些删除的数据数据全部恢复完成.

[原创]:oracle9i delete后的恢复实验(二)--logminer