LOGMINER日志分析

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;

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/，如需转载，请注明出处，否则将追究法律责任。