Oracle Logminer配置使用图文详解(5)

1 CONN LOGMINER/LOGMINER@PRACTICE AS SYSDBA 2 EXECUTE dbms_logmnr_d.build(dictionary_filename => 'dictionary.ora', dictionary_location =>'D:\oracle\oradata\practice\LOGMNR');

3.1.3 确认当前处于联机状态的日志文件

--需要确认当前处于联机状态的日志文件

SELECT group#, sequence#, status, first_change#, first_time FROM V$log ORDER BY first_change#;

 

从上图可以看出在线日志REDO03处于ACTIVE状态中

3.1.4 加入需分析的日志文件

--加入解析在线日志文件

1 BEGIN 2 dbms_logmnr.add_logfile(logfilename=>'D:\oracle\oradata\practice\REDO03.LOG',options=>dbms_logmnr.NEW); 3 END; 4 /

 

3.1.5 使用LogMiner进行分析

--启动LogMiner进行分析

EXECUTE dbms_logmnr.start_logmnr(dictfilename=>'D:\oracle\oradata\practice\LOGMNR\dictionary.ora');

3.1.6 观察分析结果

--查询相关操作日志

1 SELECT sql_redo, sql_undo, seg_owner 2 FROM v$logmnr_contents 3 WHERE seg_name='AAAAA' 4 AND seg_owner='LOGMINER';

3.2 使用LogMiner读取归档日志 3.2.1 测试数据准备

--以LOGMINER用户登录（非DBA权限）创建EMP表(Oracle11g请注意用户名、密码大小写)

1 CONN LOGMINER/ LOGMINER@PRACTICE 2 CREATE TABLE EMP 3 (EMPNO NUMBER(4) CONSTRAINT PK_EMP PRIMARY KEY, 4 ENAME VARCHAR2(10), 5 JOB VARCHAR2(9), 6 MGR NUMBER(4), 7 HIREDATE DATE, 8 SAL NUMBER(7,2), 9 COMM NUMBER(7,2), 10 DEPTNO NUMBER(2));

--插入EMP数据

1 INSERT INTO EMP VALUES (7369,'SMITH','CLERK',7902,to_date('17-12-1980','dd-mm-yyyy'),800,NULL,20); 2 INSERT INTO EMP VALUES (7499,'ALLEN','SALESMAN',7698,to_date('20-2-1981','dd-mm-yyyy'),1600,300,30); 3 INSERT INTO EMP VALUES (7521,'WARD','SALESMAN',7698,to_date('22-2-1981','dd-mm-yyyy'),1250,500,30); 4 INSERT INTO EMP VALUES (7566,'JONES','MANAGER',7839,to_date('2-4-1981','dd-mm-yyyy'),2975,NULL,20); 5 COMMIT;

--从v$log视图中找出日志文件的序号