当前页面位置: » 丰搜网 » 文档中心 » 详细内容
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.
 
