Fung's DBA World

DBA knowledge,standing on the shoulders of giants.

Logminer简单用法

June 15, 2013

1. 安装Logminer

1
2
3
SQL>@?/rdbms/admin/dbmslm.sql 
SQL>@?/rdbms/admin/dbmslmd.sql 
SQL>@?/rdbms/admin/dbmslms.sql

2. 创建数据字典

1
2
3
4
5
SQL> alter system set utl_file_dir='/oradata/lgmnr' scope=spfile; 
--restart the instance 
-- Create a dictionary file  
--   (init.ora parameter utl_file_dir must be set) 
exec dbms_logmnr_d.build('mydictfile', '/oradata/lgmnr');

3. 添加日志

1
2
3
4
5
-- Register log files, can be from a different db 
--   (NEWFILE=start new list/ ADDFILE=add next file) 
exec dbms_logmnr.add_logfile('/arch/1_141_810359695.dbf', dbms_logmnr.new);  
exec dbms_logmnr.add_logfile('/arch/1_142_810359695.dbf', dbms_logmnr.addfile);  
exec dbms_logmnr.add_logfile('/arch/1_143_810359695.dbf', dbms_logmnr.addfile);

4. 使用字典分析日志

1
2
-- Start the logminer session 
exec dbms_logmnr.start_logmnr(DictFileName => '/oradata/lgmnr/mydictfile');

5. 查看日志

1
2
3
4
5
-- Query v_$logmnr_contents view to extract required info 
select timestamp, sql_undo 
from   sys.v_$logmnr_contents 
where  seg_name = 'T'; 
select operation,sql_redo,sql_undo from v$logmnr_contents where seg_owner='BBED' and seg_name='T' and rownum<=10;

6. 结束日志挖掘

1
SQL> execute dbms_logmnr.end_logmnr;
EOF

Permalink: http://www.oraclema.com/oracle/logminer-simple-use.html