Oracle工具：logminer的简单使用

最新推荐文章于 2022-10-15 23:47:42 发布

iteye_19543

最新推荐文章于 2022-10-15 23:47:42 发布

阅读量256

点赞数

CC 4.0 BY-SA版权

文章标签： oracle培训 oracle学习 cuug培训 ocp com

本文链接：https://blog.youkuaiyun.com/iteye_19543/article/details/82569608

本文介绍了如何使用Oracle LogMiner工具进行数据库日志分析。包括创建测试表、插入数据、切换日志文件、添加日志文件到LogMiner、启动LogMiner等步骤，并提供了具体的SQL命令实例。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

我的环境：

# uname -a

Linux localhost.localdomain 2.6.18-308.el5xen #1 SMP Fri Jan 27 17:59:00 EST 2012 i686 i686 i386 GNU/Linux

sys@ORCL> select * from v$version where rownum=1;

BANNER

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

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod

10g其实不需要生成dictionary文件了。只要直接把日志文件加载，然后分析即可。

1 产生数据库操作

hr@ORCL> drop table t purge;

Table dropped.

hr@ORCL> create table logmnr_test (id number,name varchar2(20));

Table created.

hr@ORCL> insert into logmnr_test values(1,'think');

1 row created.

hr@ORCL> insert into logmnr_test values(2,'water');

1 row created.

hr@ORCL> commit;

Commit complete.

hr@ORCL> select sequence#,status from v$log;

SEQUENCE# STATUS
---------- ----------------
14 CURRENT
13 INACTIVE
12 INACTIVE
hr@ORCL> update logmnr_test set name='think_pad' where id=2;

1 row updated.

hr@ORCL> commit;

Commit complete.

hr@ORCL> alter system switch logfile;

System altered.

hr@ORCL> select sequence#,name from v$archived_log;

SEQUENCE#
----------
NAME
----------------------------------------------------------------------------------------------------
.............................
14
/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2012_09_09/o1_mf_1_14_84qrj5co_.arc
2 为分析指定日志文件

sys@ORCL> select db_name,thread_sqn,filename
2 from v$logmnr_logs;

no rows selected

sys@ORCL> exec DBMS_LOGMNR.ADD_LOGFILE('/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2012_09_09/o1_mf_1_14_84qrj5co_.arc',dbms_logmnr.NEW);

PL/SQL procedure successfully completed.

若想接着分析更多的日志，把dbms_logmnr.NEW改成dbms_logmnr.addfile即可。

sys@ORCL> select db_name,thread_sqn,filename from v$logmnr_logs;

DB_NAME THREAD_SQN
-------- ----------
FILENAME
----------------------------------------------------------------------------------------------------
ORCL 14
/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2012_09_09/o1_mf_1_14_84qrj5co_.arc
3 启动logminer

sys@ORCL> exec DBMS_LOGMNR.START_LOGMNR(OPTIONS => SYS.DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG);

PL/SQL procedure successfully completed.

若是大数据量的分析，可以指定SCN或者时间的范围。