oracle的日志分析工具,Oracle logminer 日志分析工具使用

前几天去面试ORACLE DBA 面试官问我这样一个问题：在生产环境中，有一个表，里面没有日期相关的字段，最后一次修改这个表可能在一个月前，你如何找出这个表最后修改的记录。我听到这个题目蒙了，我想到了如果能够分析数据库归档日志就好了，于是就这样答了一句，就答不上来了，回来后上网查了下才知道可以通过logminger分析，下面是我今天整理的使用笔记

1.安装LOGMNR包，脚本在$ORACLE_HOME/rdbms/admin路径下

SQL> @/u01/app/oracle/product/11.2.0/rdbms/admin/dbmslm.sql

Package created.

Grant succeeded.

Synonym created.

SQL> @/u01/app/oracle/product/11.2.0/rdbms/admin/dbmslmd.sql

Package created.

Synonym created.

SQL> @/u01/app/oracle/product/11.2.0/rdbms/admin/dbmslms.sql

Package created.

No errors.

Grant succeeded.

SQL>

2.设定UTL_FILE_DIR的初始值，该参数值为服务器中放置数据字典文件的目录，若过设置的目录不存在，需要创建。

SQL> alter system set utl_file_dir='/u01/app/oracle/dict/logs' scope=spfile;

System altered.

SQL> host mkdir -p /u01/app/oracle/dict/logs

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

Total System Global Area  434249728 bytes

Fixed Size                  2214096 bytes

Variable Size             339740464 bytes

Database Buffers           88080384 bytes

Redo Buffers                4214784 bytes

Database mounted.

Database opened.

重启完成检查修改是否生效

SQL> show parameter utl_file_dir

NAME                                 TYPE        VALUE

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

utl_file_dir                         string      /u01/app/oracle/dict/logs

3.通过oracle提供的包dbms_logmnr_d创建数据字典文件，通过调用存储过程build来创建，下面贴出了，该过程调用的变量名，这里传入两个变量的值就可以了，变量dictionary_filename为生成字典的名字，变量dictionary_location就是utl_file_dir设置的值

PROCEDURE  build

(dictionary_filename IN VARCHAR2 DEFAULT '',

dictionary_location IN VARCHAR2 DEFAULT '',

options IN NUMBER DEFAULT 0);

在创建数据字典文件前，首先创建一个测试表用作实验分析步骤如下

(1)首先切换下日志，当然这不是必须的

SQL> alter system switch logfile;

System altered.

(2)查看当前在线日志number

SQL> select group#,status from v$log;

GROUP# STATUS

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

1 CURRENT

2 INACTIVE

3 ACTIVE

(3)创建测试表

SQL> create table test_1 (id int, name varchar2(10));

Table created.

(4)插入3行测试数据

SQL> insert into test_1 values (1,'a');

1 row created.

SQL> insert into test_1 values (2,'b');

1 row created.

SQL> insert into test_1 values (3,'c');

1 row created.

SQL> commit;

Commit complete.

(5)我们知道当commit的时候，一定会触发lgwr进程写日志，所以查询当前日志组的文件名,member的值就是我们需要分析的日志，这里1号日志组有2个成员，由于日志成员互为镜像，所以使用其中的一个成员来分析就可以了。

SQL> col member for a30;

SQL> select group#,member from v$logfile where group#=(select group# from v$log where status='CURRENT');

GROUP# MEMBER

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

1 +DATA/orcl/onlinelog/group1_1.

ora

1 +FRA/orcl/onlinelog/group1_2.o

ra

(6)执行下面语句生成字典到/u01/app/oracle/dict/logs/dict.ora

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

PL/SQL procedure successfully completed.

存储过程执行完成后到/u01/app/oracle/dict/logs这个目录下验证是否生成了dict.ora文件

[oracle@localhost logs]$ pwd;ls

/u01/app/oracle/dict/logs

dict.ora

4.数据字典文件成功生成后就可以通过调用dbms_logmnr包提供的存储过程add_logfile来添加需要分析的redo和归档日志，下面是存储过程add_logfile的变量

PROCEDURE add_logfile(

LogFileName    IN  VARCHAR2,

OptionsIN  BINARY_INTEGER default ADDFILE );

执行下面命令添加日志文件

SQL> execute dbms_logmnr.add_logfile(logfilename=>'+FRA/orcl/archivelog/2013_08_16/thread_1_seq_20.321.823621023',options=>dbms_logmnr.new);

PL/SQL procedure successfully completed.

5.日志文件添加完成后，调用dbms_logmnr.start_logmnr过程分析该日志，这个过程只需要传入一个变量值，就是前面创建的数据字典名。如果日志比较大可能分析的时间较长

SQL> execute dbms_logmnr.start_logmnr(dictfilename=>'/u01/app/oracle/dict/logs/dict.ora');

PL/SQL procedure successfully completed.

6. 执行完成后我们可以从v$logmnr_contents视图中得到刚才分析的结果

SQL> select sql_redo from v$logmnr_contents where table_name='TEST_1';

SQL_REDO

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

create table test_1 (id int, name varchar2(10));

insert into "SYS"."TEST_1"("ID","NAME") values ('1','a');

insert into "SYS"."TEST_1"("ID","NAME") values ('2','b');

insert into "SYS"."TEST_1"("ID","NAME") values ('3','c');

7.退出logmnr

SQL> execute dbms_logmnr.end_logmnr;

PL/SQL procedure successfully completed.

在前面反复的实验过程中我又重新创建了测试表发现分析出来的结果没有我们执行的sql，我在网上查了一些资料才发现，如果要分析新的对象必须重新创建数据库字典，注意，v$logmnr_contents内容保存的日志的内容存放在pga中，只在当前会话有效，如果想长期保存分析，可以在当前会话用create table tablename as select * from v$logmnr_contents语句来持久保存。

如果只想获取dml的操作可以通过执行下面语句进行分析：

SQL> execute dbms_logmnr.start_logmnr (options=>dbms_logmnr.dict_from_online_catalog);

如果想添加多个日志列表，例如添加一个归档日志如下：

SQL> execute dbms_logmnr.add_logfile(logfilename=>'+FRA/orcl/archivelog/2013_08_16/thread_1_seq_23.324.823621999',options=>dbms_logmnr.addfile);

限制日志分析内容，例如分析2013/8/16的如下：

EXECUTE dbms_logmnr.start_logmnr(DictFileName => '/u01/data1/oracle/logs/dict.ora ',  StartTime => to_date('2013-08-16 00:00:00','YYYY-MM-DD HH24:MI:SS'), EndTime => to_date('2013-08-16 23:59:59','YYYY-MM-DD HH24:MI:SS '));