LOGMINER日誌分析

hky87發表於2009-10-20

10G LOGMINER使用

1、 安裝LOGMINER工具

執行%ORACLE_HOME% \RDBMS\ADMIN目錄下的檔案dbmslm.sql(用來建立DBMS_LOGMNR包)

2、使用聯機目錄分析歸檔日誌

1)      開啟資料庫的追加日誌

select supplemental_log_data_min from v$database;

SUPPLEME

--------

NO ---若結果為YES則不需要追加

alter database add supplemental log data;

------刪除SUPPLEMENTAL LOG DATA功能

ALTER DATABASE DROP SUPPLEMENTAL LOG DATA (PRIMARY KEY, UNIQUE INDEX) COLUMNS;

2)      先建立表t，切換日誌，然後執行事務刪除

create table t as select * from emp;

alter system switch logfile;

sys@ORCL>conn scott/tiger

scott@ORCL>DELETE FROM T WHERE  EMPNO=7521;

scott@ORCL>commit;

scott@ORCL>conn / as sysdba

sys@ORCL>alter system switch logfile;

sys@ORCL>select name from v$archived_log;

NAME

D:\ORACLE\ORADATA\ORCL\ARCHIVE_LOG\ARC00028_0699724578.001

D:\ORACLE\ORADATA\ORCL\ARCHIVE_LOG\ARC00029_0699724578.001

3)      新增日誌檔案到LOGMINER列表中

exec dbms_logmnr.ADD_LOGFILE(options=>dbms_logmnr.new,logfilename=>'D:\oracle\oradata\orcl\ARCHIVE_LOG\ARC00028_0699724578.001');

exec dbms_logmnr.ADD_LOGFILE(options=>dbms_logmnr.addfile,logfilename=>'D:\oracle\oradata\orcl\ARCHIVE_LOG\ARC00029_0699724578.001');

4)      為LOGMINER指定將要使用的聯機目錄。如果源資料處於開啟或者可用狀態，那它也是可用的。

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

5)      檢視日誌分析結果

select username,sql_redo,sql_undo from v$logmnr_contents WHERE USERNAME='SCOTT' AND PERATION='DELETE';

USERNAME,SQL_REDO,SQL_UNDO

SCOTT, delete from "SCOTT"."T" where "EMPNO" = '7521' and "ENAME" = 'WARD' and "JOB" = 'SALESMAN' and "MGR" = '7698' and "HIREDATE" = TO_DATE('22-2月 -81', 'DD-MON-RR') and "SAL" = '1250' and "COMM" = '500' and "DEPTNO" = '30' and ROWID = 'AAAM4AAAEAAAAIkAAC';, insert into "SCOTT"."T"("EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO") values ('7521','WARD','SALESMAN','7698',TO_DATE('22-2月 -81', 'DD-MON-RR'),'1250','500','30');

6)      關閉LOGMINER

execute dbms_logmnr.end_logmnr;

 

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/10314474/viewspace-617041/，如需轉載，請註明出處，否則將追究法律責任。

相關文章