备份与恢复系列 六 续 日志挖掘（Log Miner）找回update语句

在上一篇的试验中使用logminer可以很容易的找到误删除的表，下面来看一个关于update的例子。 1.创建一个数据字典 EXECUTE dbms_logmnr_d.build( - ? ? ? ? dictionary_filename => 'dictionary.ora',- ? ? ? ? dictionary_location => '/home/oracle'); 2.产生update语句 conn scott/tiger update emp set sal=10,comm=20 where empno=7844; commit; 3，为分析制定日志文件 conn / as sysdba select member from v$logfile where group#=(select group# from v$log where status='CURRENT'); EXECUTE dbms_logmnr.add_logfile( - ? ? ? ? logfilename => '/oradata/PRACTICE/redo03.log',- ? ? ? ? options => dbms_logmnr.NEW); 查看将要分析的日志 select DB_NAME,THREAD_SQN,FILENAME from v$logmnr_logs; DB_NAME ? ? ? ? ? ? ? ? ?THREAD_SQN FILENAME ------------------------ ---------- ----------------------------------------- PRACTICE ? ? ? ? ? ? ? ? ? ? ? ? 24 /oradata/PRACTICE/redo03.log 4，启动log miner conn / as sysdba EXECUTE dbms_logmnr.start_logmnr( - ? ? ? ? dictfilename => '/home/oracle/dictionary.ora'); 查看统计信息，没有挖掘出任何数据 SELECT sql_redo,sql_undo from v$logmnr_contents where seg_name='EMP' and seg_owner='SCOTT' and seg_type_name='TABLE'; no rows selected 接下来有请今天的主角supplemental log 1，增加supplemental log conn / as sysdba alter database add supplemental log data; SELECT supplemental_log_data_min FROM v$database; SUPPLEMENTAL_LOG_DATA_MI ------------------------ YES 2，产生update语句 conn scott/tiger SCOTT@PRACTICE >update emp set sal=10,comm=20 where empno=7844; commit; 3,为分析制定日志文件 select member from v$logfile where group#=(select group# from v$log where status='CURRENT'); MEMBER -------------------------------------------------------------------------------- /oradata/PRACTICE/redo03.log SYS@PRACTICE >EXECUTE dbms_logmnr.add_logfile( - > ? ? ? ? logfilename => '/oradata/PRACTICE/redo03.log',- > ? ? ? ? options => dbms_logmnr.NEW); SYS@PRACTICE >select DB_NAME,FILENAME from v$logmnr_logs; DB_NAME ? ? ? ? ? ? ? ? ?THREAD_SQN ------------------------ ---------- FILENAME -------------------------------------------------------------------------------- PRACTICE ? ? ? ? ? ? ? ? ? ? ? ? 24 /oradata/PRACTICE/redo03.log 4,启动log miner SYS@PRACTICE >EXECUTE dbms_logmnr.start_logmnr( - > ? ? ? ? dictfilename => '/home/oracle/dictionary.ora'); PL/SQL procedure successfully completed. SYS@PRACTICE >SELECT sql_redo,sql_undo ? 2 ?from v$logmnr_contents ? 3 ?where seg_name='EMP' ? 4 ?and seg_owner='SCOTT' ? 5 ?and seg_type_name='TABLE'; SQL_REDO -------------------------------------------------------------------------------- SQL_UNDO -------------------------------------------------------------------------------- update "SCOTT"."EMP" set "SAL" = '10',"COMM" = '20' where "SAL" = '100' and "CO MM" = '200' and ROWID = 'AAASZHAAEAAAACXAAJ'; update "SCOTT"."EMP" set "SAL" = '100',"COMM" = '200' where "SAL" = '10' and "C OMM" = '20' and ROWID = 'AAASZHAAEAAAACXAAJ'; 实验成功，挖掘到了update语句。 关闭日志挖掘 execute dbms_logmnr.end_logmnr; 删除supplemental log alter database drop supplemental log data;