logminer使用步驟二(分析DML操作)
2024-07-21 02:40:54
供稿：網友
本步主要用于分析DML操作分析DML
1.創建要分析的文件列表，并將log文件添加到分析列表中
SQL> execute dbms_logmnr.add_logfile (logfilename=>'/Oracle/oradata/szdb/archive/1_291.dbf',options=>dbms_logmnr.new);
PL/SQL PRocedure sUCcessfully completed.
SQL> execute dbms_logmnr.add_logfile (logfilename=>'/oracle/oradata/szdb/archive/1_292.dbf',options=>dbms_logmnr.addfile);
PL/SQL procedure successfully completed.
SQL> execute dbms_logmnr.add_logfile (logfilename=>'/oracle/oradata/szdb/archive/1_293.dbf',options=>dbms_logmnr.addfile);
PL/SQL procedure successfully completed.
SQL> execute dbms_logmnr.add_logfile (logfilename=>'/oracle/oradata/szdb/archive/1_294.dbf',options=>dbms_logmnr.addfile);
PL/SQL procedure successfully completed.
#這里的options選項有三個參數可以用：
NEW - 表示創建一個新的日志文件列表
ADDFILE - 表示向這個列表中添加日志文件，如下面的例子
REMOVEFILE - 和addfile相反。
2.執行重做日志分析，分析DML操作
SQL> execute dbms_logmnr.start_logmnr(dictfilename=>'/opt/oraclelog/szdb1.ora');
PL/SQL procedure successfully completed.
SQL> execute dbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_catalog);
PL/SQL procedure successfully completed.
3.根據指定的用戶名、表名、操作進行分析
SQL> select scn,sql_redo from v$logmnr_contents where seg_owner='GAMETEST' and seg_name='ENDUSER' and upper(Operation)='UPDATE';
SCN
----------
SQL_REDO
--------------------------------------------------------------------------------
139430695
update "GAMETEST"."ENDUSER" set "USERID" = '123456' where "USERID" = '98077418'
and ROWID = 'AAAH4fAANAAA4gUAAA';
SQL> select to_char(timestamp,'YYYY-MM-DD HH24:MI:SS') time,sql_redo from v$logmnr_contents where seg_owner='GAMETEST' and seg_name='ENDUSER' and upper(operation)='UPDATE';
TIME
-------------------
SQL_REDO
--------------------------------------------------------------------------------
2005-07-27 15:27:32
update "GAMETEST"."ENDUSER" set "USERID" = '123456' where "USERID" = '98077418'
and ROWID = 'AAAH4fAANAAA4gUAAA';
注重：seg_name='ENDUSER';中的ENDUSER是被update的表名，并且一定要大寫,同時update該表的操作一定要已經被數據庫歸檔，假如沒有被歸檔，可以在日志分析前用SQL> Alter System Archive Log Current;手動歸檔。
4.結束LogMiner會話
SQL> execute dbms_logmnr.end_logmnr;
PL/SQL procedure successfully completed.
