linux/unxi LOGMNR安装说明

晚上很多介绍是windows下如何使用LOGMNR，很少linux.我在linux下安装logmnr做个说明

安装两个包 dbmslm.sql dbmslmd.sql
SQL> conn sys/pt500416@mistest as sysdba;
Connected.

SQL> @dbmslm.sql

Package created.

Grant succeeded.

SQL> @dbmslmd.sql

Procedure created.

No errors.

Grant succeeded.

PL/SQL procedure successfully completed.

Package created.

SQL> show parameter utl

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
utl_file_dir string
SQL> alter system set utl_file_dir='/oracle/logmrn' scope=spfile;

System altered.

SQL> show parameter spfile;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string ?/dbs/[email protected]

关闭和重启数据库，是修改的参数生效
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 537990604 bytes
Fixed Size 452044 bytes
Variable Size 268435456 bytes
Database Buffers 268435456 bytes
Redo Buffers 667648 bytes
Database mounted.
Database opened.

查看参数utl_file_dir
SQL> show parameter utl_file

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
utl_file_dir string /oracle/logmrn

创建数据字典文件（data-dictionary）
SQL> conn /as sysdba;
Connected.
SQL> execute dbms_logmnr_d.build(dictionary_filename=>'dict.ora',dictionary_location=>'/oracle/logmrn');

PL/SQL procedure successfully completed.

2、创建要分析的日志文件列表：

（1）创建分析列表，即所要分析的日志：

SQL> execute dbms_logmnr.add_logfile(LogFileName=>'/oracle/oradata/mistest/redo01.log',Options=>dbms_logmnr.new);

PL/SQL procedure successfully completed.
（2）添加分析日志文件（一次添加1个为宜）：
SQL> execute dbms_logmnr.add_logfile(LogFileName=>'/oracle/oradata/mistest/redo02.log',Options => dbms_logmnr.ADDFILE);

PL/SQL procedure successfully completed.

3、使用logMiner进行日志分析：

（1）无限制条件，即用数据字典文件对要分析的日志文件所有内容做分析：
SQL> execute dbms_logmnr.start_logmnr
(DictFileName => '/oracle/lgmrn/dict.ora');

PL/SQL procedure successfully completed.

（2）带限制条件：

可以用scn号或时间做限制条件,就是制定日志的起始时间和结束时间;

SQL> execute dbms_logmnr.start_logmnr(startTime => to_date('2011-03-14 10:00:00','yyyy-mm-dd hh24:mi:ss'),endTime => to_date('2011-03-14 15:00:00','yyyy-mm-dd hh24:mi:ss'),DictFileName => '/oracle/logmrn/dict.ora');

SQL> execute dbms_logmnr.start_logmnr(startTime => to_date('2011-03-14 10:00:00','yyyy-mm-dd hh24:mi:ss'),endTime => to_date('2011-03-14 15:00:00','yyyy-mm-dd hh24:mi:ss'),DictFileName => '/oracle/logmrn/dict.ora');

PL/SQL procedure successfully completed.

4、分析后释放内存：
SQL> execute dbms_logmnr.end_logmnr;

PL/SQL procedure successfully completed.