oracle 日志分析

1. 确定那个日志文件

2.日志分析

[@more@]

SQL> show parameter utl

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
utl_file_dir string /u01/oracle/admin/test/cdump

sqlplus /nolog

SQL*Plus: Release 9.2.0.1.0 - Production on Sun Nov 20 21:58:37 2005

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

SQL> connect / as sysdba
Connected.
SQL> exec dbms_logmnr_d.build('oradic.ora','/u01/oracle/admin/test/cdump');

PL/SQL procedure successfully completed.

SQL> alter package DBMS_LOGMNR_D compile body;

Package body altered.

SQL> exec dbms_logmnr.add_logfile(logfilename=>'/u01/oracle/oradata/test/archive/1_3373.dbf', options=>dbms_logmnr.new);

PL/SQL procedure successfully completed.

SQL> exec dbms_logmnr.start_logmnr(dictfilename=>'/u01/oracle/admin/test/cdump/oradic.ora');

PL/SQL procedure successfully completed.

SQL> create table crbt.log_contents3373 as select * from v$logmnr_contents where operation='DELETE' AND SEG_NAME LIKE 'CORP%';

Table created.

select * from log_contents507 WHERE SEG_NAME LIKE 'VBEX_VTB%' and operation='INSERT'

]执行exec dbms_logmnr_d.build('Logminer.ora','file directory')，提示下标超界，怎么办[A]完整错误信息如下,
SQL> exec dbms_logmnr_d.build('Logminer.ora','file directory')
BEGIN dbms_logmnr_d.build('Logminer.ora','file directory'); END;
*
ERROR 位于第 1 行:
ORA-06532: 下标超出限制
ORA-06512: 在"SYS.DBMS_LOGMNR_D", line 793
ORA-06512: 在line 1
解决办法为：
1.编辑位于"$ORACLE_HOME/rdbms/admin"目录下的文件"dbmslmd.sql"
改变行:
TYPE col_desc_array IS VARRAY(513) OF col_description;
为
TYPE col_desc_array IS VARRAY(700) OF col_description;
并保存文件
2. 运行改变后的脚本
SQLPLUS> Connect internal
SQLPLUS> @$ORACLE_HOME/rdbms/admin/dbmslmd.sql
3.重新编译该包
SQLPLUS> alter package DBMS_LOGMNR_D compile body;

======================

以下引 piper
[Q]执行execute dbms_logmnr.start_logmnr(DictFileName=>'DictFileName')提示ORA-01843:无效的月份，这个是什么原因
[A]我们分析start_logmnr包
PROCEDURE start_logmnr(
startScn IN NUMBER default 0 ,
endScn IN NUMBER default 0,
startTime IN DATE default TO_DATE('01-jan-1988','DD-MON-YYYY'),
endTime IN DATE default TO_DATE('01-jan-2988','DD-MON-YYYY'),
DictFileName IN VARCHAR2 default '',
Options IN BINARY_INTEGER default 0 );
可以知道，如果TO_DATE('01-jan-1988','DD-MON-YYYY')失败，将导致以上错误
所以解决办法可以为
1、Alter session set NLS_LANGUAGE=American
2、用类似如下的方法执行
execute dbms_logmnr.start_logmnr (DictFileName=> 'f:temp2TESTDICT.ora', starttime => TO_DATE(
'01-01-1988','DD-MM-YYYY'), endTime=>TO_DATE('01-01-2988','DD-MM-YYYY'));

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

转载于:http://blog.itpub.net/66233/viewspace-991214/