資料被更新或刪除了,你的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);向後
加一份日誌就可以了.

v$logmnr_contents session_info為null 或unknown

相關文章