備份與恢復系列 六 續 日誌挖掘（Log Miner）找回update語句

snowdba發表於2014-08-16
在上一篇的試驗中使用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,THREAD_SQN,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;

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29047826/viewspace-1252474/，如需轉載，請註明出處，否則將追究法律責任。

相關文章