Logmnr 用法介绍及其注意事项

数据运维 2023-10-12 大树手机阅读

Logmnr 用法介绍及其注意事项

Environment：linux + oracle10g two nodes rac

安装LogMiner工具，以下两个脚本以SYSDBA身份运行

SQL> @$ORACLE_HOME/rdbms/admin/dbmslm.sql;

Package created.

Grant succeeded.

##创建DBMS_LOGMNR包，该包用来分析日志文件。

SQL> @$ORACLE_HOME/rdbms/admin/dbmslmd.sql;

Package created.

##创建DBMS_LOGMNR_D包，该包用来创建数据字典文件。

使用LogMiner工具
下面将详细介绍如何使用LogMiner工具。

1、创建数据字典文件（data-dictionary）
1).首先在init.ora初始化参数文件中，指定数据字典文件的位置，也就是添加一个参数 UTL_FILE_DIR，该参数值为服务器中放置数据字典文件的目录。如： UTL_FILE_DIR = ($ORACLE_HOME\logs) ,重新启动数据库，使新加的参数生效：

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount

ORACLE instance started.

Total System Global Area 922746880 bytes

Fixed Size 1222624 bytes

Variable Size 209717280 bytes

Database Buffers 704643072 bytes

Redo Buffers 7163904 bytes

Database mounted.

SQL> alter system set utl_file_dir='/u01/app/oracle/product/10.2.0/db_1/log' scope=spfile;

System altered.

SQL> shutdown immediate

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

SQL>

SQL> startup mount

ORACLE instance started.

Total System Global Area 922746880 bytes

Fixed Size 1222624 bytes

Variable Size 209717280 bytes

Database Buffers 704643072 bytes

Redo Buffers 7163904 bytes

Database mounted.

SQL>

SQL> show parameter utl

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

create_stored_outlines string

utl_file_dir string /u01/app/oracle/product/10.2.0

/db_1/log

SQL>

SQL> alter database open;

Database altered.

SQL>

Ps: LogMiner uses a dictionary file, which is a special file that

indicates the database that created it as well as the time the file was

created. The dictionary file is not required, but is recommended. Without a

dictionary file, the equivalent SQL statements will use Oracle internal object

IDs for the object name and present column values as hex data.

也可以不使用数据字典，但是oracle推荐使用。

From ：How to Setup LogMiner [ID 111886.1]

转载请注明出处：http://blog.csdn.net/xiaofan23z

2). 然后创建数据字典文件

SQL> execute dbms_logmnr_d.build(dictionary_filename => 'dict.ora',dictionary_location => '/u01/app/oracle/product/10.2.0/db_1/log');

PL/SQL procedure successfully completed.

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

1).创建分析列表，即所要分析的日志

SQL> execute dbms_logmnr.add_logfile(LogFileName => '+DATA/ldbrac/onlinelog/group_3.266.732154759',Options => dbms_logmnr.new); ##根据时间点或者在v$log，v$archived_log中查询出要分析的log

PL/SQL procedure successfully completed.

###为了验证效果删除一个表然后切换log

drop table askey_id；

SQL> alter system switch logfile;

System altered.

将切换的log 加入到分析列表

SQL> execute dbms_logmnr.add_logfile(LogFileName => '+DATA/ldbrac/onlinelog/group_2.262.732154617',Options => dbms_logmnr.ADDFILE);

PL/SQL procedure successfully completed.

Alert log中会有如下信息显示

Sat Apr 21 11:35:19 2012

LOGMINER: Begin mining logfile: +DATA/ldbrac/onlinelog/group_2.262.732154617

Sat Apr 21 11:35:19 2012

LOGMINER: Begin mining logfile: +DATA/ldbrac/onlinelog/group_3.266.732154759

3、使用logMiner进行日志分析

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

PL/SQL procedure successfully completed.

    2).带限制条件，可以用scn号或时间做限制条件,也可组合使用
    --分析日志列表中时间从20120420从10:00到13:00的内容
SQL> execute dbms_logmnr.start_logmnr(startTime => to_date('20120421100000','yyyy-mm-dd hh24:mi:ss'),endTime => to_date('20120421130000','yyyy-mm-dd hh24:mi:ss'),DictFileName => '/u01/app/oracle/product/10.2.0/db_1/log/dict01.ora');

PL/SQL procedure successfully completed

  dbms_logmnr.start_logmnr函数的原型为：
  PROCEDURE start_logmnr(
     startScn           IN  NUMBER default 0 ,
     endScn IN  NUMBER default 0,
     startTime       IN  DATE default '',
     endTime         IN  DATE default '',
     DictFileName     IN  VARCHAR2 default '',
     Options IN  BINARY_INTEGER default 0 );

4.分析数据
Logmnr 用法介绍及其注意事项-1