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

       在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

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

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

转载于:http://blog.itpub.net/12045182/viewspace-432982/