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

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 产生数据库操作

[sql] view plain copy

1. hr@ORCL>droptabletpurge;
3. Tabledropped.
5. hr@ORCL>createtablelogmnr_test(idnumber,namevarchar2(20));
7. Tablecreated.
9. hr@ORCL>insertintologmnr_testvalues(1,'think');
11. 1rowcreated.
13. hr@ORCL>insertintologmnr_testvalues(2,'water');
15. 1rowcreated.
17. hr@ORCL>commit;
19. Commitcomplete.
21. hr@ORCL>selectsequence#,statusfromv$log;
23. SEQUENCE#STATUS
24. --------------------------
25. 14CURRENT
26. 13INACTIVE
27. 12INACTIVE
28. hr@ORCL>updatelogmnr_testsetname='think_pad'whereid=2;
30. 1rowupdated.
32. hr@ORCL>commit;
34. Commitcomplete.
36. hr@ORCL>altersystemswitchlogfile;
38. Systemaltered.
40. hr@ORCL>selectsequence#,namefromv$archived_log;
42. SEQUENCE#
43. ----------
44. NAME
45. ----------------------------------------------------------------------------------------------------
46. .............................
47. 14
48. /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2012_09_09/o1_mf_1_14_84qrj5co_.arc

2 为分析指定日志文件

[sql] view plain copy

1. sys@ORCL>selectdb_name,thread_sqn,filename
2. 2fromv$logmnr_logs;
4. norowsselected
6. sys@ORCL>execDBMS_LOGMNR.ADD_LOGFILE('/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2012_09_09/o1_mf_1_14_84qrj5co_.arc',dbms_logmnr.NEW);
8. PL/SQLproceduresuccessfullycompleted.
10. 若想接着分析更多的日志，把dbms_logmnr.NEW改成dbms_logmnr.addfile即可。
12. sys@ORCL>selectdb_name,thread_sqn,filenamefromv$logmnr_logs;
14. DB_NAMETHREAD_SQN
15. ------------------
16. FILENAME
17. ----------------------------------------------------------------------------------------------------
18. ORCL14
19. /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2012_09_09/o1_mf_1_14_84qrj5co_.arc

3 启动logminer

[sql] view plain copy

1. sys@ORCL>execDBMS_LOGMNR.START_LOGMNR(OPTIONS=>SYS.DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG);
3. PL/SQLproceduresuccessfullycompleted.
5. 若是大数据量的分析，可以指定SCN或者时间的范围。

4 分析日志的内容
1)检查数据更改的细节
数据库里面的数据可能意想不到的原因或者错误而发生改变，在重做日志文件中可以找到这些更改的细节，比如：谁做了这些改变？什么时候改的？怎么改的？

[sql] view plain copy

1. selectoperation,timestamp,scnfromv$logmnr_contents
2. whereseg_name='LOGMNR_TEST'and
3. seg_owner='HR'and
4. seg_type_name='TABLE';
6. OPERATIONTIMESTAMPSCN
7. -------------------------------------------------------------
8. DDL2012/09/0908:20:47721905
10. selectsql_redo,sql_undofromv$logmnr_contents
11. whereseg_name='LOGMNR_TEST'and
12. seg_owner='HR'and
13. seg_type_name='TABLE';
15. SQL_REDO
16. ----------------------------------------------------------------------------------------------------
17. SQL_UNDO
18. ----------------------------------------------------------------------------------------------------
19. createtablelogmnr_test(idnumber,namevarchar2(20));
21. selectusername,session_infofromv$logmnr_contents
22. whereseg_name='LOGMNR_TEST'and
23. seg_owner='HR'and
24. seg_type_name='TABLE'

2)执行容量分析
如分析表产生DML的频数和频率

[sql] view plain copy

1. selectoperation,timestamp,count(*)totalfromv$logmnr_contents
2. whereseg_name='LOGMNR_TEST'and
3. seg_owner='HR'and
4. seg_type_name='TABLE'
5. groupbyoperation,timestamp;
7. OPERATIONTIMESTAMPTOTAL
8. -------------------------------------------------------------
9. DDL2012/09/0908:20:471

3)寻找DDL命令的细节
例如，使用logminer，可以找出删除表的具体时间和scn，便于media recovery。

[sql] view plain copy

1. selectseg_name,operation,scn,timestamp,count(*)
2. fromv$logmnr_contents
3. whereoperation='DELETE'
4. groupbyseg_name,operation,scn,timestamp
5. orderbyscn;

5 关闭logminer
如果需要进一步的分析，可将v$logmnr_contents内容保存下来
create table logmnr_contents as select * from v$logmnr_contents;
然后，执行关闭：
exec dbms_logmnr.end_logmnr;

附图：