来自 IT技术分享 2018-11-12 09:47 的文章

Oracle日志挖掘命令

Oracle日志挖掘命令   1.日志挖掘 1.1补充日志命令(日志中有了rowid)  

  alter databaseadd supplemental logdata;

 

  1.2基于DML的日志挖掘    例子:SCOTT下UPDATE某记录,我想挖掘出这个操作的时间以及SCN,如  
   update emp set sal=9000;

 

     步骤:   1.2.1查看当前redo日志
col member for a40



    select v1.group#,v1.sequence#,v1.first_change#,v1.status,v2.member

    from v$log v1,v$logfile v2

    where v1.group#=v2.group#

    order by 1;

   

    结果:

    GROUP#  SEQUENCE# FIRST_CHANGE# STATUS           MEMBER

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

         1          7       1346456 CURRENT         /u01/oradata/mike/redo01.log

         1          7       1346456 CURRENT         /u01/oradata/mike/redo01b.log

         2          5       1346098 INACTIVE         /u01/oradata/mike/redo02b.log

         2          5       1346098 INACTIVE         /u01/oradata/mike/redo02.log

         3          6       1346283 INACTIVE         /u01/oradata/mike/redo03b.log

         3          6       1346283 INACTIVE         /u01/oradata/mike/redo03.log



6 rows selected.

 

  1.2.2使用当前日志建立分析列表     Exec sys.dbms_鼎盛国际logmnr.add_logfile(logfilename=>'/u01/oradata/mike/redo01.log');   1.2.3使用数据字典进行日志分析     如果没有数据字典,使用dbms_logmnr.dict_from_online_catalog选项参数,意思是从指定的dbms_logmnr.add_logfile或重做日志中找到数据字典。       exec sys.dbms_logmnr.start_logmnr(options=>sys.dbms_logmnr.dict_from_online_catalog);         1.2.4查看分析结果    
 select scn,to_char(timestamp,'yyyy-mm-dd hh24:mi:ss'),sql_redo,sql_undo

    from v$logmnr_contents

    where seg_name='EMP'and seg_owner='SCOTT';

    结果:

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

    SQL_REDO

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

    SQL_UNDO

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

       1352504 2013-06-20 11:26:38

    update "SCOTT"."EMP"set "SAL"= '9000'where "SAL"= '2800'and ROWID= 'AAASb2AAEAAAACXAAA';

    update "SCOTT"."EMP"set "SAL"= '2800'where "SAL"= '9000'and ROWID= 'AAASb2AAEAAAACXAAA';



       1352504 2013-06-20 11:26:38

    update "SCOTT"."EMP"set "SAL"= '9000'where "SAL"= '2800'and ROWID= 'AAASb2AAEAAAACXAAB';

update "SCOTT"."EMP"set "SAL"= '2800'where "SAL"= '9000'and ROWID= 'AAASb2AAEAAAACX………

 

  1.2.5结束logmnr exec dbms_logmnr.end_logmnr;   1.3基于DDL的日志挖掘 1.3.1数据字典在归档日志中 1.3.1.1将数据字典归档到日志         execute dbms_logmnr_d.build(options=>dbms_logmnr_d.store_in_redo_logs);   1.3.1.2做DDL操作         SCOTT用户下删除表test;           drop table test;   1.3.1.3查看redo日志和archive日志         select group#,sequence#,statusfrom v$log;           结果:               GROUP# SEQUENCE# STATUS   -------------------- ----------------            1         13 INACTIVE            2         14 CURRENT            3         12 INACTIVE                     select name,dictionary_begin,dictionary_endfrom v$archived_log;           结果:           NAME                                                                            DIC DIC           ----------------------------------------------------------------------------------- ---           /u01/fast_recovery_area/MIKE/archivelog/2013_06_20/o1_mf_1_3_8w4proqh_.arc      NO NO           /u01/fast_recovery_area/MIKE/archivelog/2013_06_20/o1_mf_1_4_8w4psx1j_.arc      NO NO           /u01/fast_recovery_area/MIKE/archivelog/2013_06_20/o1_mf_1_5_8w4q3sol_.arc      NO NO           /u01/fast_recovery_area/MIKE/archivelog/2013_06_20/o1_mf_1_6_8w4qc0yo_.arc      NO NO           /u01/fast_recovery_area/MIKE/archivelog/2013_06_20/o1_mf_1_7_8w564nnh_.arc      NO NO           /u01/fast_recovery_area/MIKE/archivelog/2013_06_20/o1_mf_1_8_8w56c7gz_.arc      NO NO           /u01/fast_recovery_area/MIKE/archivelog/2013_06_20/o1_mf_1_9_8w56d13f_.arc      NO NO           /u01/fast_recovery_area/MIKE/archivelog/2013_06_20/o1_mf_1_10_8w56mrcn_.arc     NO NO           /u01/fast_recovery_area/MIKE/archivelog/2013_06_20/o1_mf_1_11_8w5bskbb_.arc     NO NO           /u01/fast_recovery_area/MIKE/archivelog/2013_06_20/o1_mf_1_12_8w5bstfx_.arc     YES YES           /u01/fast_recovery_area/MIKE/archivelog/2013_06_20/o1_mf_1_13_8w5byo0v_.arc     NO NO               11 rows selected.           发现有一个归档日志中dictionary_begin和dictionary_end是YES,所以数据字典的信息在这个归档日志中,一会要加入分析队列。   1.3.1.4使用日志建立分析列表         首先把当前的redo文件加入分析列表:           execute dbms_logmnr.add_logfile(logfilename=>'/u01/oradata/mike/redo02.log',options=>dbms_logmnr.new);           然后把包含数据字典信息的归档日志也加进去:           execute dbms_logmnr.add_logfile(logfilename=>'/u01/fast_recovery_area/MIKE/archivelog/2013_06_20/o1_mf_1_12_8w5bstfx_.arc',options=>dbms_logmnr.addfile);   1.3.1.5使用数据字典进行分析         execute sys.dbms_logmnr.start_logmnr(options=>sys.dbms_logmnr.dict_from_online_catalog);   1.3.1.6查看分析结果         selectscn,to_char(timestamp,'yyyy-mm-dd hh24:mi:ss'),sql_redo from v$logmnr_contents           where seg_name='TEST' andseg_owner='SCOTT';           结果:          SCN TO_CHAR(TIMESTAMP,'   -----------------------------   SQL_REDO   ----------------------------------------------------------------------------------------------------      1384524 2013-06-20 15:14:59   ALTER TABLE"SCOTT"."TEST" RENAME TO"BIN$35G0QjKlCuLgRAgAJ3D0FA==$0" ;          1384527 2013-06-20 15:14:59   drop table test AS"BIN$35G0QjKlCuLgRAgAJ3D0FA==$0" ;   1.3.1.7结束logmnr execdbms_logmnr.end_logmnr;   1.3.2数据字典在utl_file_dir中 1.3.2.1设置utl_file_dir     show parameter utl_file_dir       若为空,则设置一下值:       alter systemset utl_file_dir='/u01/logmnr'scope=spfile;       重启实例:       startup force       再次查看show parameter utl_file_dir   1.3.2.2创建数据字典到指定目录     execute dbms_logmnr_d.build(dictionary_filename=>'newdict.ora',dictionary_location=> '/u01/logmnr');   1.3.2.3查看当前redo日志和archive日志     select group#,sequence#,first_change#,statusfrom v$log;       结果:        GROUP#  SEQUENCE# FIRST_CHANGE# STATUS   -------------------- ------------- ----------------            1         16       1389548 CURRENT            2         14       1384482 ACTIVE            3         15       1389541 ACTIVE             select name,dictionary_begin,dictionary_endfrom v$archived_log;       结果:             NAME                                                                            DIC DIC       ----------------------------------------------------------------------------------- ---       /u01/fast_recovery_area/MIKE/archivelog/2013_06_20/o1_mf_1_3_8w4proqh_.arc      NO NO       /u01/fast_recovery_area/MIKE/archivelog/2013_06_20/o1_mf_1_4_8w4psx1j_.arc      NO NO       /u01/fast_recovery_area/MIKE/archivelog/2013_06_20/o1_mf_1_5_8w4q3sol_.arc      NO NO       /u01/fast_recovery_area/MIKE/archivelog/2013_06_20/o1_mf_1_6_8w4qc0yo_.arc      NO NO       /u01/fast_recovery_area/MIKE/archivelog/2013_06_20/o1_mf_1_7_8w564nnh_.arc      NO NO       /u01/fast_recovery_area/MIKE/archivelog/2013_06_20/o1_mf_1_8_8w56c7gz_.arc      NO NO       /u01/fast_recovery_area/MIKE/archivelog/2013_06_20/o1_mf_1_9_8w56d13f_.arc      NO NO       /u01/fast_recovery_area/MIKE/archivelog/2013_06_20/o1_mf_1_10_8w56mrcn_.arc     NO NO       /u01/fast_recovery_area/MIKE/archivelog/2013_06_20/o1_mf_1_11_8w5bskbb_.arc     NO NO       /u01/fast_recovery_area/MIKE/archivelog/2013_06_20/o1_mf_1_12_8w5bstfx_.arc     YES YES       /u01/fast_recovery_area/MIKE/archivelog/2013_06_20/o1_mf_1_13_8w5byo0v_.arc     NO NO       /u01/fast_recovery_area/MIKE/archivelog/2013_06_20/o1_mf_1_14_8w5klf20_.arc     NO NO       /u01/fast_recovery_area/MIKE/archivelog/2013_06_20/o1_mf_1_15_8w5klxm9_.arc     NO NO           13 rows selected.   1.3.2.4做DDL操作     SCOTT用户下删除一个表       drop table test;   1.3.2.5将使用日志建立分析列表     因为在Drop一个表之前,我已经把数据字典信息写入到了/u01/logmnr/newdict.ora中,所以此时我只要将当前日志和几个归档日志加入分析列表即可。       execute dbms_logmnr.add_logfile(logfilename=>'/u01/oradata/mike/redo01.log',options=>dbms_logmnr.new);       execute dbms_logmnr.add_logfile(logfilename=>'/u01/fast_recovery_area/MIKE/archivelog/2013_06_20/o1_mf_1_15_8w5klxm9_.arc',options=>dbms_logmnr.addfile);       execute dbms_logmnr.add_logfile(logfilename=>'/u01/fast_recovery_area/MIKE/archivelog/2013_06_20/o1_mf_1_14_8w5klf20_.arc',options=>dbms_logmnr.addfile);   1.3.2.6使用数据字典进行分析     execute dbms_logmnr.start_logmnr(dictfilename=>'/u01/logmnr/newdict.ora');   1.3.2.6查询查看分析结果     select scn,to_char(timestamp,'yyyy-mm-dd hh24:mi:ss'),sql_redo from v$logmnr_contents       where seg_name='TEST'and seg_owner='SCOTT';       结果:        SCN TO_CHAR(TIMESTAMP,'   -----------------------------   SQL_REDO   ----------------------------------------------------------------------------------------------------      1384524 2013-06-20 15:14:59   ALTER TABLE"SCOTT"."TEST" RENAME TO"BIN$35G0QjKlCuLgRAgAJ3D0FA==$0" ;          1384527 2013-06-20 15:14:59   drop table test AS"BIN$35G0QjKlCuLgRAgAJ3D0FA==$0" ;          1389502 2013-06-20 17:06:50   create table test(id int) tablespace stu;          1389516 2013-06-20 17:07:00   insert into"SCOTT"."TEST"("ID") values ('99');          1389980 2013-06-20 17:18:56   ALTER TABLE"SCOTT"."TEST" RENAME TO"BIN$35G0QjKmCuLgRAgAJ3D0FA==$0" ;          1389982 2013-06-20 17:18:56   drop table test AS"BIN$35G0QjKmCuLgRAgAJ3D0FA==$0" ;           6 rows selected.   1.3.2.7结束logmnr execdbms_logmnr.end_logmnr;   1.4日志挖掘总结 show parameter utl       alter system set utl_file_dir='/u01/logmnr'scope=spfile;(设置参数并重启实例)       startup force       execute dbms_logmnr_d.build(options=>dbms_logmnr_d.store_in_redo_logs);(数据字典信息归档到日志)       executedbms_logmnr_d.build('dict.ora','/u01/logmnr',options=>dbms_logmnr_d.store_in_flat_file);(数据字典信息归档到指定目录)       executedbms_logmnr_d.build(dictionary_filename => 'mcdict.ora',dictionary_location=> '/u01/logmnr');(创建数据字典到指定目录)       executedbms_logmnr.add_logfile(logfilename=>'',options=>dbms_logmnr.new);(将日志加入分析列表)       executedbms_logmnr.add_logfile(logfilename=>'',options=>dbms_logmnr.addfile);       executedbms_logmnr.start_logmnr(dictfilename=>'/u01/logmnr/dict.ora');(使用指定路径的数据字典分析)       executesys.dbms_logmnr.start_logmnr(options=>sys.dbms_logmnr.dict_from_online_catalog);(使用日志中的数据字典分析)       select xxx from v$logmnr_contents wherexxx;(查询挖掘后的信息)       exec dbms_logmnr.end_logmnr;(结束挖掘)

http://www.bkjia.com/oracle/480976.htmlwww.bkjia.comtruehttp://www.bkjia.com/oracle/480976.htmlTechArticleOracle日志挖掘命令 1.日志挖掘 1.1补充日志命令(日志中有了rowid) alter databaseadd supplemental logdata; 1.2基于DML的日志挖掘 例子:SCOTT下UPDATE某...

本文源自: AG88平台