1.安装logminer 以dba用户身份运行下面2个脚本： dbmslm.sql用来创建dbms_logmnr包，该包用来分析日志文件。 sql @$oracle_home/rdbms/admin/dbmslm.sql;package created.grant succeeded.synonym created. dbmslmd.sql用来创建dbms_logmnr_d包，该包用来创
1.安装logminer
以dba用户身份运行下面2个脚本：
dbmslm.sql用来创建dbms_logmnr包，该包用来分析日志文件。
sql> @$oracle_home/rdbms/admin/dbmslm.sql;package created.grant succeeded.synonym created.
dbmslmd.sql用来创建dbms_logmnr_d包，该包用来创建数据字典文件。
sql> @$oracle_home/rdbms/admin/dbmslmd.sql;package created.synonym created.
2.设置参数utl_file_dir
添加参数utl_file_dir,该参数值为服务器中放置数据字典文件的目录，
sql> create directory utlfile as '/home/oracle/logminer';directory created.sql> alter system set utl_file_dir='/home/oracle/logminer' scope=spfile;system altered.
重启数据库，生效上面的设置。
sql> shutdown immediate;sql> startup;sql> show parameters utl_file_dir;
3.启动补充日志
补充日志不是logminer日志分析必须的步骤，但是如果不启用补充日志，分析日志的的很多结果集信息就会显示为“unknown”，下面是开启最小补充日志。
sql> alter database add supplemental log data;database altered.sql> select supplemental_log_data_min from v$database;suppleme -------- yes
4.创建专门的logminer用户
不是必须，实为管理安全方便。
5.创建数据字典
sql> execute dbms_logmnr_d.build(dictionary_filename => 'dictionary.ora', dictionary_location =>'/home/oracle/logminer');
6.添加需要分析的在线日志或者归档日志
sql> execute dbms_logmnr.add_logfile(logfilename =>'/home/oracle/flash_recovery_area/primary/archivelog/2016_04_25/o1_mf_1_199_ckv04o6w_.arc',options => dbms_logmnr.new);sql> execute dbms_logmnr.add_logfile(logfilename =>'/home/oracle/flash_recovery_area/primary/archivelog/2016_04_25/o1_mf_1_201_ckv08jyp_.arc',options => dbms_logmnr.addfile);pl/sql procedure successfully completed.sql> execute dbms_logmnr.add_logfile(logfilename =>'/home/oracle/flash_recovery_area/primary/archivelog/2016_04_25/o1_mf_1_200_ckv05pmp_.arc',options => dbms_logmnr.addfile);pl/sql procedure successfully completed.
7. 使用字典分析归档日志文件
sql> execute dbms_logmnr.start_logmnr(dictfilename=>'/home/oracle/logminer/dictionary.ora');
8.在线日志分析实例
插入数据
sql> insert into nn.t1 values(1000,'succ');1 row created.sql> commit;commit complete.
查询当前日志序列号
sql> select group#, sequence#, status, first_change#, first_time from v$log ord er by first_change#; group# sequence# status first_change# first_tim---------- ---------- ---------------- ------------- --------- 1 208 inactive 2825768 25-apr-16 2 209 inactive 2825872 25-apr-16 3 210 current 2845771 25-apr-16
加入当前日志组
sql> execute dbms_logmnr.add_logfile(logfilename =>'/home/oracle/oradata/orcl/redo03.log',options => dbms_logmnr.addfile);pl/sql procedure successfully completed.
使用logminer进行分析
sql> execute dbms_logmnr.start_logmnr(dictfilename=>'/home/oracle/logminer/dictionary.ora');pl/sql procedure successfully completed.
查询分析结果
sql> select sql_redo, sql_undo, seg_owner from v$logmnr_contents where seg_name='t1';sql_redo sql_undo seg_ownerinsert into nn.t1(tid,tname) values ('1000','succ');delete from nn.t1 where tid = '1000' and tname = 'succ' and rowid = 'aaar7yaaeaaaacraad';nn
9.关闭logminer会话
sql> execute dbms_logmnr.end_logmnr;