oracle工具：logminer的简单使用
我的环境：
[root@localhost ~]# 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
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 产生数据库操作 www.2cto.com
[sql]
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;
www.2cto.com
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
www.2cto.com
2 为分析指定日志文件
[sql]
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);
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 www.2cto.com
3 启动logminer
[sql]
sys@ORCL> exec DBMS_LOGMNR.START_LOGMNR(OPTIONS => SYS.DBMS_LOGMNR.DICT_FROM_ONLINE_
CATALOG);
CATALOG);
PL/SQL procedure successfully completed.
若是大数据量的分析，可以指定SCN或者时间的范围。
4 分析日志的内容
1)检查数据更改的细节
数据库里面的数据可能意想不到的原因或者错误而发生改变，在重做日志文件中可
以找到这些更改的细节，比如：谁做了这些改变？什么时候改的？怎么改的？
以找到这些更改的细节，比如：谁做了这些改变？什么时候改的？怎么改的？
[sql]
select operation,timestamp,scn from v$logmnr_contents
where seg_name='LOGMNR_TEST' and
seg_owner='HR' and
seg_type_name='TABLE';
OPERATION TIMESTAMP SCN
-------------------------------- ------------------- ----------
DDL 2012/09/09 08:20:47 721905
select sql_redo,sql_undo from v$logmnr_contents
where seg_name='LOGMNR_TEST' and
seg_owner='HR' and
seg_type_name='TABLE';
SQL_REDO www.2cto.com
----------------------------------------------------------------------------------------
SQL_UNDO
----------------------------------------------------------------------------------------
create table logmnr_test (id number,name varchar2(20));
select username,session_info from v$logmnr_contents
where seg_name='LOGMNR_TEST' and
seg_owner='HR' and
seg_type_name='TABLE'
2)执行容量分析
如分析表产生DML的频数和频率
[sql]
select operation,timestamp,count(*) total from v$logmnr_contents
where seg_name='LOGMNR_TEST' and
seg_owner='HR' and
seg_type_name='TABLE'
group by operation,timestamp;
OPERATION TIMESTAMP TOTAL
-------------------------------- ------------------- ----------
DDL 2012/09/09 08:20:47 1
3)寻找DDL命令的细节
例如，使用logminer，可以找出删除表的具体时间和scn，便于media recovery。
[sql]
select seg_name,operation,scn,timestamp,count(*)
from v$logmnr_contents www.2cto.com
where peration='DELETE'
group by seg_name,operation,scn,timestamp
order by scn;
5 关闭logminer
如果需要进一步的分析，可将v$logmnr_contents内容保存下来
create table logmnr_contents as select * from v$logmnr_contents;
然后，执行关闭：
exec dbms_logmnr.end_logmnr;
附图：
