logminer的简单使用

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 产生数据库操作  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);  
  
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);  
  
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;
   
    附图:




 
请使用浏览器的分享功能分享到微信等