oracle 日志内容分析,Oracle异机logminer日志分析

Oracle LogMiner是一个较为常见的日志分析工具，使用该工具可以较为方便地获取在线或归档日志中的内容。通常都是在数据库本机上进行，通过数据字典文件来解析，配置过程需要重启，然而实际生产环境有时是不能重启的，这个时候就需要把日志拿到其它数据库中进行分析。其它环境也可以挖日志，前提是数据库版本、DB NAME 和生成的数据字典的dbid要与归档日志的一致。

一      环境介绍

操作系统：Oracle Linux Server release 6.5

日志源库：Oracle Database 11g Enterprise Edition Release 11.2.0.4.0  RAC

db_name: orcl

分析库：Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 单实例

db_name: test

二      使用nid修改db_name

首先需要关闭数据库，重新启动到mount状态，然后使用nid进行修改db name，这里将原来的db name从test修改为orcl，与日志来源库一致。

[oracle@ OLE65 dbs]$ nid target=system/Oracle888 dbname=orcl

DBNEWID: Release 11.2.0.3.0 - Production on Tue Dec 22 10:05:26 2015

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to database TEST (DBID=2197266420)

Connected to server version 11.2.0

Control Files in database:

/backupascopy/control001.ctl

Change database ID and database name TEST to ORCL? (Y/[N]) => Y

Proceeding with operation

Changing database ID from 2197266420 to 1426925158

Changing database name from TEST to ORCL

Control File /backupascopy/control001.ctl - modified

Datafile /backupascopy/system001.db - dbid changed, wrote new name

Datafile /backupascopy/sysaux001.db - dbid changed, wrote new name

Datafile /backupascopy/undotbs001.db - dbid changed, wrote new name

Datafile /backupascopy/users001.db - dbid changed, wrote new name

Datafile /backupascopy/shaw_data001.db - dbid changed, wrote new name

Datafile /backupascopy/temp001.db - dbid changed, wrote new name

Control File /backupascopy/control001.ctl - dbid changed, wrote new name

Instance shut down

Database name changed to ORCL.

Modify parameter file and generate a new password file before restarting.

Database ID for database ORCL changed to 1426925158.

All previous backups and archived redo logs for this database are unusable.

Database has been shutdown, open database with RESETLOGS option.

Succesfully changed database name and ID.

DBNEWID - Completed succesfully.

这里将dbid也一起修改了，启动前记得修改pfile中的db_name参数，改为orcl。

SQL> startup mount

ORACLE instance started.

Total System Global Area  417546240 bytes

Fixed Size                  2228944 bytes

Variable Size             239078704 bytes

Database Buffers          171966464 bytes

Redo Buffers                4272128 bytes

Database mounted.

SQL> alter database open resetlogs

Database altered.

SQL> show parameter db_name

NAME                   TYPE        VALUE

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

db_name                string      orcl

三      配置logminer

3.1      安装LogMiner工具

SQL> @?/rdbms/admin/dbmslm.sql

Package created.

Grant succeeded.

Synonym created.

SQL> @?/rdbms/admin/dbmslmd.sql

Package created.

Synonym created.

SQL> @?/rdbms/admin/dbmslms.sql

Package created.

No errors.

Grant succeeded.

3.2      设置参数UTL_FILE_DIR

SQL> alter system set utl_file_dir='/u01/logminer' scope=spfile;

System altered.

该参数重启实例生效。

确认参数生效：

SQL> show parameter utl_file_dir

NAME                   TYPE        VALUE

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

utl_file_dir                string      /u01/logminer

3.3      创建数据字典文件

SQL> BEGIN

2  dbms_logmnr_d.build(

3  dictionary_filename => 'logminer_dict.ora',

4  dictionary_location => '/u01/logminer');

5  END;

6  /

PL/SQL procedure successfully completed.

这个时候，就可以在/u01/logminer目录下，看到生成的数据字典文件。

3.1      修改数据字典文件

到日志源库查看dbid:

SQL> select dbid from v$database;

DBID

----------

1407969520

修改其中一段：

[oracle@ OLE65 logminer]$ vi /u01/logminer/logminer_dict.ora

INSERT_INTO DICTIONARY_TABLE VALUES ('ORCL',1407969520,'08/17/2015 14:48:25','12/22/2015 09:39:00',1242147,'12/21/2015 13:19:35','08/18/2015 08:18:13','','','AL32UTF8','11.2.0.4.0','Production',76251,74664,1291867,,1292259,0);

3.2      创建要日志文件列表

SQL> BEGIN

2  dbms_logmnr.add_logfile(options=>dbms_logmnr.new,

3  logfilename=>'/u01/1_45_880188790.dbf');

4  END;

5  /

PL/SQL procedure successfully completed.

3.3      进行分析

SQL> BEGIN

2  dbms_logmnr.start_logmnr(

3  dictfilename => '/u01/logminer/logminer_dict.ora',

4  starttime => to_date('2015-05-22 23:00:00','YYYY-MM-DD HH24:MI:SS'),

5  endtime => to_date('2015-05-22 23:18:00','YYYY-MM-DD HH24:MI:SS'));

6  END;

7  /

PL/SQL procedure successfully completed.

四      操作总结

到3.2这一步，日志文件列表创建成功，则后续的操作基本就没有问题了，这里最重要的一步，就是修改数据字典中的DBID。当然，网上还有一种方法，跳过数据字典分析日志，也是可行的，有兴趣的可以测试一下。