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/MIK