oracle创建视图实时抽取数据,oracle Logmnr抽取日志数据

Logmnr，dba在事后需要审计或者查询一个误操作产生的原因，甚至是找回误操作的数据，这时，flashback不一定能帮上你的忙，因为时间可能过去很久了，还有可能是一个DDL操作，而你想知道是由哪个用户发出来的，那么，只要还有这个时间的归档日志存在，就可以利用Logmnr来分析归档日志。

1、利用logmnr，可以做以下事情

(1)、查明数据库的变更记录，或者是进行变化分析，如有的用户怀疑自己的应用有问题，产生了大量的失误，可以用logmnr来分析这些事务，看看到底发生了些什么事情。

(2)、侦察并更正用户的误操作，如有的用户一不小心误删除了某个表，但是并不承认，这个时候就可利用logmnr来分析谁执行的DML或者是DDL操作。

(3)、找回失去的数据，当不能使用flashback或使用flashback受限的时候，我们可以考虑利用logmnr来找回数据，这时候，只要有归档日志即可。

注意：logminer分析出来的元数据，如update table set a=a+10 实际影响了1000条数据，那么logmnr怎返回1000条undo语句。

2、logmnr包含的内容与数据字典

logminer包含两个PL/SQL包和几个视图

dbms_logmnr_d包：用于提取字典信息到外部平面文件或者联机日志中去。

dbms_logmnr包：包含三个过程。

(1)add_logfile:用来添加、删除用于分析的日志文件

(2)start_logmnr:用于开启日志分析，而且，可以开启很多不同的分析选项，如只分析提交信息commited_data_only等。

(3)end_logmnr:用于开启日志分析，而且，可以开启很多不同的分析选项，如只分析提交信息commited_data_only等。

如果普通用户想使用以上的包，必须先在sys中授权才能使用，如：

grant execute on dbms_logmnr to piner;

那么使用的时候，最好也带上前缀sys。如sys.dbms_logmnr。

与logminer相关的字典如下：

v$logmnr_dirctionary：logminer可能使用的数据字典信息，因logmnr可以有多个字典文件，该视图用于显示这方面的信息。

v$logmnr_parameters：当前logminer所设定的参数信息。

v$logmnr_logs：当前用于分析的日志列表。

v$logmnr_contents：日志分析结果。

3、Logmnr的分析过程

(1)切换日志和建立表、并执行插入删除数据操作。

alter system archive log current;

create table test(a int);

insert into test values(1);

insert into test values(2);

insert into test values(3);

delete from test;

commit;

alter system archive log current;

(2)然后找到刚才两次切换之间的包含这些操作的归档日志。

切换的归档日志如下：/home/oracle/product/flash_recovery_area/ORCL/archivelog/2012_09_22/o1_mf_1_54_8

5tondqb_.arc

然后在线分析这个过程。

1)添加日志并分析日志

exec sys.dbms_logmnr.add_logfile(logfilename=>'/home/oracle/product/flash_recovery_area/ORCL/archivelog/2012_09_22/o1_mf_1_54_85tondqb_.arc',options=>sys.dbms_logmnr.new);

exec sys.dbms_logmnr.start_logmnr(options=> sys.dbms_logmnr.dict_from_online_catalog);

一般如果还有其他的日志，还可以如下添加：

exec sys.dbms_logmnr.add_logfile(logfilename='/home/oracle/product/flash_recovery_area/ORCL/archivelog/2012_09_22/o1_mf_1_54_85tondqb_.arc');

(3)现在就可以查看分析结果了

select t.scn,t.timestamp,t.seg_owner,t.operation,t.sql_undo,t.sql_undo from v$logmnr_contents t where t.seg_name='TEST';

select t.scn,t.timestamp,t.seg_owner,t.operation,t.sql_undo from v$logmnr_contents t where t.seg_name='TEST';

SCN TIMESTAMP SEG_OWNER OPERATION SQL_UNDO

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

1367841 2012/9/22 1 CAIWENCAN DDL

1367848 2012/9/22 1 CAIWENCAN INSERT delete from "CAIWENCAN"."TEST" where "A" = '1' and ROWID = 'AAAM4wAAEAAAAG+AAA';

1367848 2012/9/22 1 CAIWENCAN INSERT delete from "CAIWENCAN"."TEST" where "A" = '2' and ROWID = 'AAAM4wAAEAAAAG+AAB';

1367848 2012/9/22 1 CAIWENCAN INSERT delete from "CAIWENCAN"."TEST" where "A" = '3' and ROWID = 'AAAM4wAAEAAAAG+AAC';

1367859 2012/9/22 1 CAIWENCAN DELETE insert into "CAIWENCAN"."TEST"("A") values ('1');

1367859 2012/9/22 1 CAIWENCAN DELETE insert into "CAIWENCAN"."TEST"("A") values ('2');

1367859 2012/9/22 1 CAIWENCAN DELETE insert into "CAIWENCAN"."TEST"("A") values ('3');

其中，SEG_OWNER是操作人，operation是操作方式，sql_undo是操作语句。

这里的v$logmnr_contents视图信息是session级别的，如果需要保留信息，需要创建临时存储

create table logmnr_contents as select * from v$logmnr_contents;

(4)分析完成以后，可以清空session内存信息。

exec sys.dbms_logmnr.end_logmnr;

(5)如果要恢复，可以编写pl/sql进行回滚恢复。

declare

mysql varchar2(4000);

num number :=0;

begin

from c_tmp in (select sql_undo from logmnr_contents where operation='DELETE') loop

mysql:=replace(c_tmp.sql,':','');

execute immediate mysql;

num:=num+1;

if mod(num,1000)=0 then

commit;

end if;

end loop;

commit;

exception

when others then

....异常处理

end;

(6)如果要更改logmnr的特定表空间，

这里是更改空间为users空间。

exec sys.dbms_logmnr_d.set_tablespace('users');

v$logmnr_dirctionary：logminer可能使用的数据字典信息，因logmnr可以有多个字典文件，该视图用于显示这方面的信息。

v$logmnr_parameters：当前logminer所设定的参数信息。

v$logmnr_logs：当前用于分析的日志列表。

v$logmnr_contents：日志分析结果。