首先查看服务器是否有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/