logminer使用步骤二(分析DML操作)

本步主要用于分析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.