使用归档日志分析解决归档日志迅速增长问题(logmnr)

在数据库运行维护中，常常遇到日志迅猛增长的问题，迅猛增长的日志，将会把日志目录撑爆，导致数据库日志无法切换，最终导致数据库停止运行和响应。这个问题的大部分问题是 BAD SQL造成的。我们可以通过归档日志的分析，迅速定位bad sql，提交给开发者进行改进。

使用DBMS_LOGMNR包进行日志分析。

LogMiner包含两个PL/SQL包和几个视图：

1、dbms_logmnr_d包，这个包只包括一个用于提取数据字典信息的过程，即dbms_logmnr_d.build()过程。

2、dbms_logmnr包，它有三个过程：

add_logfile(name varchar2, options number) - 用来添加/删除用于分析的日志文件；

start_logmnr(start_scn number, end_scn number, start_time number,end_time number, dictfilename varchar2, options number) - 用来开启日志分析，同时确定分析的时间/SCN窗口以及确认是否使用提取出来的数据字典信息。

end_logmnr() - 用来终止分析会话，它将回收LogMiner所占用的内存。

与LogMiner相关的数据字典。

1、v$logmnr(dictionary，LogMiner可能使用的数据字典信息，因logmnr可以有多个字典文件，该视图用于显示这方面信息。

2、v$logmnr_parameters，当前LogMiner所设定的参数信息。

3、v$logmnr_logs，当前用于分析的日志列表。

4、v$logmnr_contents，日志分析结果。

[@more@]

日志分析的步骤：

一。产生字典文件（非必须）

如果想要使用字典文件，数据库至少应该出于MOUNT状态。然后执行dbms_logmnr_d.build过程将数据字典信息提取到一个外部文件中。下面是具体分析步骤：

1、确认设置了初始化参数：UTL_FILE_DIR，并确认Oracle对改目录拥有读写权限，然后启动实例。示例中UTL_FILE_DIR参数如下：

SQL> show parameter utl

NAME TYPE VALUE

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

utl_file_dir string /data6/cyx/logmnr

这个目录主要用于存放dbms_logmnr_d.build过程所产生的字典信息文件，如果不用这个，则可以不设，也就跳过下面一步。

2、生成字典信息文件：

exec dbms_logmnr_d.build(dictionary_filename =>'dic.ora',dictionary_location => '/rman/log/');

其中dictionary_location指的是字典信息文件的存放位置,它必须完全匹配UTL_FILE_DIR的值

二。添加需要分析的日志文件

exec dbms_logmnr.add_logfile(logfilename=>'/archivelog/1_93220_643131044.dbf',options=>dbms_logmnr.new);

exec dbms_logmnr.add_logfile(logfilename=>'/archivelog/1_93221_643131044.dbf',options=>dbms_logmnr.addfile);

这里的options选项有三个参数可以用：

NEW - 表示创建一个新的日志文件列表

ADDFILE - 表示向这个列表中添加日志文件，如下面的例子

REMOVEFILE - 和addfile相反。

三。进行日志分析

当你添加了需要分析的日志文件后，我们就可以让LogMiner开始分析了：

SQL> exec dbms_logmnr.start_logmnr(dictfilename=>'/data6/cyx/logmnr/dic.ora');

PL/SQL procedure successfully completed.

如果你没有使用字典信息文件（此时我们只需要启动实例就可以了），那么就不需要跟dictfilename参数：

SQL> exec dbms_logmnr.start_logmnr();

PL/SQL procedure successfully completed.

四。查询日志分析结果，几个视图中v$logmnr_contents（记录日志分析结果）是最重要的。

可以通过下面的语句查询，频繁执行的SQL.

Select substr(sql_redo,1,100),count(1) from v$logmnr_contents group by substr(sql_redo,1,100) having count(1)>100 order by 2;

查询出BAD SQL ，可以通过ALL_SOURCE视图查询出包含这些语句的procedure和function，定位错误源，提供给开发者。

一次日志分析的例子（不使用字典文件）：

SQL>exec dbms_logmnr.add_logfile(logfilename=>'/archivelog/1_93222_643131044.dbf',options=>dbms_logmnr.addfile);

SQL>exec dbms_logmnr.add_logfile(logfilename=>'/archivelog/1_93223_643131044.dbf',options=>dbms_logmnr.addfile);

SQL>exec dbms_logmnr.add_logfile(logfilename=>'/archivelog/1_93224_643131044.dbf',options=>dbms_logmnr.addfile);

SQL>exec dbms_logmnr.add_logfile(logfilename=>'/archivelog/1_93225_643131044.dbf',options=>dbms_logmnr.addfile);

SQL>exec dbms_logmnr.add_logfile(logfilename=>'/archivelog/1_93226_643131044.dbf',options=>dbms_logmnr.addfile);

SQL>exec dbms_logmnr.add_logfile(logfilename=>'/archivelog/1_93227_643131044.dbf',options=>dbms_logmnr.addfile);

SQL>exec dbms_logmnr.add_logfile(logfilename=>'/archivelog/1_93228_643131044.dbf',options=>dbms_logmnr.addfile);

SQL>exec dbms_logmnr.add_logfile(logfilename=>'/archivelog/1_93229_643131044.dbf',options=>dbms_logmnr.addfile);

SQL>exec dbms_logmnr.start_logmnr;

SQL>Select substr(sql_redo,1,100),count(1) from v$logmnr_contents group by substr(sql_redo,1,100) having count(1)>100 order by 2;

update "UNKNOWN"."OBJ# 116803" set "COL 30" = HEXTORAW('3336') where "COL 30" IS

NULL and ROWID = 'A

SUBSTR(SQL_REDO,1,100)

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

COUNT(1)

----------

19754

20567

update "UNKNOWN"."OBJ# 116803" set "COL 29" = HEXTORAW('414141') where "COL 29"

IS NULL and ROWID =

355475

SQL> select object_name,object_type,owner from all_objects where object_id=116803;

OBJECT_NAME OBJECT_TYPE

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

OWNER

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

DM_USER_CREDIT_TEMP TABLE

CREDIT

问题出在credit.dm_user_credit_temp 表，通过查询all_source可以查询出使用了这个表的PROCEDURE和function.

SQL>exec end_logmnr() ；

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/32980/viewspace-1034643/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/32980/viewspace-1034643/