v$logmnr_contents session_info為null 或unknown

数据被更新或删除了,你的boss要你找出是谁干的,可是数据库又没开审计,那就试试通过 log miner看能不能找到一些操作信息.
大概如下:
1.查找准备miner的日志
select * from v$archived_log
where first_time>=to_date('201412260800','yyyymmddhh24mi') and next_time<=to_date('201412261000','yyyymmddhh24mi')
order by stamp desc
2.添加要miner的日志
begin
dbms_logmnr.add_logfile('+ARCHLOG/rh10g02/archivelog/2014_12_25/thread_1_seq_53.320.867334233',dbms_logmnr.NEW);
end;
begin
dbms_logmnr.add_logfile('+ARCHLOG/rh10g02/archivelog/2014_12_25/thread_1_seq_51.318.867333879',dbms_logmnr.ADDFILE);
end;
select * from v$logmnr_logs; --查看已加入的日志
3.开始log miner
begin
dbms_logmnr.start_logmnr(options=>sys.dbms_logmnr.DICT_FROM_ONLINE_CATALOG);
end;
4.按需要查找操作记录
select * from v$logmnr_contents
where operation='DELETE' and seg_owner not in ('SYSMAN','SYS');
5.结束log miner
begin
dbms_logmnr.end_logmnr;
end;
在查看谁做的操作时,比较重要的栏位是v$logmnr_contents中的session_info,这里可以显示类似下面信息:
login_username=SCOTT client_info= OS_username=Administrator Machine_name=WORKGROUP\S_WIN2003CN01 OS_terminal=S_WIN2003CN01 OS_process_id=3836:592 OS_program_name=plsqldev.exe,一般就能满足需要了.
但在使用中发现一个问题,oracle 10.2.0.1中v$logmnr_contents中的session_info是空的,解决的方法是:
Column USERNAME and SESSION_INFO are UNKNOWN or NULL in V$LOGMNR_CONTENTS [ID 110301.1]
You have just built the LogMiner dictionary and started LogMiner.  You query V$LOGMNR_CONTENTS expecting to see the USERNAME and SESSION_INFO for some particular redo or undo operation. However, the USERNAME field is NULL.  This is NOT a recursive operation, and the USERNAME column is expected to contain a non-null value.
Solution:=========This can result from your database parameter settings and also from the methodyou are using to mine redo logs using LogMiner.
1. Ensure that database was in minimum supplemental logging at the time that   the redo information was created.  
2. Ensure that all archive redo logs containing the necessary redo   information have been added to the LogMiner session.
1.也就是需要添加supplemental log;
sql>ALTER DATABASE ADD SUPPLEMENTAL LOG DATA; #11g建议是alter database add supplemental log data (primary key,unique index) columns;
10.2.0.1有可以ALTER DATABASE ADD SUPPLEMENTAL LOG DATA; 时可能会报ora-600或hang住,一般启动到mount下就可解决.
STARTUP MOUNT
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
ALTER DATABASE OPEN;
2.我的理解是要有这个会话开始时的日志,不然依然会发现v$logmnr_contents中的session_info值为空或unknown,
所以一般只需要通过dbms_logmnr.add_logfile('+ARCHLOG/rh10g02/archivelog/2014_12_25/thread_1_seq_51.318.867333879',dbms_logmnr.ADDFILE);向后
加一份日志就可以了.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/28539951/viewspace-1381439/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/28539951/viewspace-1381439/