我的环境:
[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 产生数据库操作
- hr@ORCL>droptabletpurge;
- Tabledropped.
- hr@ORCL>createtablelogmnr_test(idnumber,namevarchar2(20));
- Tablecreated.
- hr@ORCL>insertintologmnr_testvalues(1,'think');
- 1rowcreated.
- hr@ORCL>insertintologmnr_testvalues(2,'water');
- 1rowcreated.
- hr@ORCL>commit;
- Commitcomplete.
- hr@ORCL>selectsequence#,statusfromv$log;
- SEQUENCE#STATUS
- --------------------------
- 14CURRENT
- 13INACTIVE
- 12INACTIVE
- hr@ORCL>updatelogmnr_testsetname='think_pad'whereid=2;
- 1rowupdated.
- hr@ORCL>commit;
- Commitcomplete.
- hr@ORCL>altersystemswitchlogfile;
- Systemaltered.
- hr@ORCL>selectsequence#,namefromv$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>selectdb_name,thread_sqn,filename
- 2fromv$logmnr_logs;
- norowsselected
- 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);
- PL/SQLproceduresuccessfullycompleted.
- 若想接着分析更多的日志,把dbms_logmnr.NEW改成dbms_logmnr.addfile即可。
- sys@ORCL>selectdb_name,thread_sqn,filenamefromv$logmnr_logs;
- DB_NAMETHREAD_SQN
- ------------------
- FILENAME
- ----------------------------------------------------------------------------------------------------
- ORCL14
- /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2012_09_09/o1_mf_1_14_84qrj5co_.arc
3 启动logminer
- sys@ORCL>execDBMS_LOGMNR.START_LOGMNR(OPTIONS=>SYS.DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG);
- PL/SQLproceduresuccessfullycompleted.
- 若是大数据量的分析,可以指定SCN或者时间的范围。
4 分析日志的内容
1)检查数据更改的细节
数据库里面的数据可能意想不到的原因或者错误而发生改变,在重做日志文件中可以找到这些更改的细节,比如:谁做了这些改变?什么时候改的?怎么改的?
- selectoperation,timestamp,scnfromv$logmnr_contents
- whereseg_name='LOGMNR_TEST'and
- seg_owner='HR'and
- seg_type_name='TABLE';
- OPERATIONTIMESTAMPSCN
- -------------------------------------------------------------
- DDL2012/09/0908:20:47721905
- selectsql_redo,sql_undofromv$logmnr_contents
- whereseg_name='LOGMNR_TEST'and
- seg_owner='HR'and
- seg_type_name='TABLE';
- SQL_REDO
- ----------------------------------------------------------------------------------------------------
- SQL_UNDO
- ----------------------------------------------------------------------------------------------------
- createtablelogmnr_test(idnumber,namevarchar2(20));
- selectusername,session_infofromv$logmnr_contents
- whereseg_name='LOGMNR_TEST'and
- seg_owner='HR'and
- seg_type_name='TABLE'
2)执行容量分析
如分析表产生DML的频数和频率
- selectoperation,timestamp,count(*)totalfromv$logmnr_contents
- whereseg_name='LOGMNR_TEST'and
- seg_owner='HR'and
- seg_type_name='TABLE'
- groupbyoperation,timestamp;
- OPERATIONTIMESTAMPTOTAL
- -------------------------------------------------------------
- DDL2012/09/0908:20:471
3)寻找DDL命令的细节
例如,使用logminer,可以找出删除表的具体时间和scn,便于media recovery。
- selectseg_name,operation,scn,timestamp,count(*)
- fromv$logmnr_contents
- whereoperation='DELETE'
- groupbyseg_name,operation,scn,timestamp
- orderbyscn;
5 关闭logminer
如果需要进一步的分析,可将v$logmnr_contents内容保存下来
create table logmnr_contents as select * from v$logmnr_contents;
然后,执行关闭:
exec dbms_logmnr.end_logmnr;
附图: