日志挖掘
来源：互联网 发布：java银行项目面试简历 编辑：程序博客网 时间：2024/06/05 19:42
[oracle@test ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Wed Mar 23 10:15:11 2016
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> show user
USER is "SYS"
SQL> conn scott/tiger
Connected.
SQL> set lines 200
SQL> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 900 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
14 rows selected.
SQL> update emp set sal=sal+100 where empno=7369;
1 row updated.
SQL> commit;
Commit complete.
SQL> update emp set ename='HUYUWU' where empno=7369;
1 row updated.
SQL> commit;
Commit complete.
SQL> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 HUYUWU CLERK 7902 17-DEC-80 1000 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
14 rows selected.
SQL> conn /as sysdba
Connected.
SQL> show user
USER is "SYS"
1.检查库当前是否为追加日志模式
select supplemental_log_data_min from v$database;
结果为NO，则没有开启
2.修改库为追加日志数据模式
alter database add supplemental log data;
SQL> Set linesize 200
SQL> col member format a50
SQL> select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_
---------- ------- ------- -------------------------------------------------- ---
4 ONLINE /u01/app/oracle/oradata/test/redo04.log NO
5 ONLINE /u01/app/oracle/oradata/test/redo05.log NO
6 ONLINE /u01/app/oracle/oradata/test/redo06.log NO
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ ---------
4 1 39 524288000 512 1 YES INACTIVE 1088777 22-MAR-16 1088780 22-MAR-16
5 1 40 524288000 512 1 NO CURRENT 1088780 22-MAR-16 2.8147E+14
6 1 38 524288000 512 1 YES INACTIVE 1088774 22-MAR-16 1088777 22-MAR-16
使用当前日志建立分析列表（添加archive redo log）---#（如果日志被切换使用当前日志则无效，需使用全部日志）
SQL> exec sys.dbms_logmnr.add_logfile(logfilename=>'/u01/app/oracle/oradata/test/redo05.log');
exec sys.dbms_logmnr.add_logfile(logfilename=>'/u01/app/oracle/oradata/test/redo05.log', options=>sys.dbms_logmnr.addfile);
PL/SQL procedure successfully completed.
使用数据字典进行日志分析（日志挖掘）
SQL> exec sys.dbms_logmnr.start_logmnr(options=>sys.dbms_logmnr.dict_from_online_catalog);
PL/SQL procedure successfully completed.
####如果没有数据字典则使用 exec dbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_catalog)
查看挖掘结果
select scn,to_char(timestamp,'yyyy-mm-dd hh24:mi:ss'),sql_redo,sql_undo
from v$logmnr_contents
where seg_name='E'and seg_owner='HUYUWU';
SCN TO_CHAR(TIMESTAMP,'
---------- -------------------
SQL_REDO
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_UNDO
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1091406 2016-03-23 10:04:44
update "SCOTT"."EMP" set "SAL" = '900' where "SAL" = '800' and ROWID = 'AAAVREAAEAAAACXAAA';
update "SCOTT"."EMP" set "SAL" = '800' where "SAL" = '900' and ROWID = 'AAAVREAAEAAAACXAAA';
1091711 2016-03-23 10:16:24
update "SCOTT"."EMP" set "SAL" = '1000' where "SAL" = '900' and ROWID = 'AAAVREAAEAAAACXAAA';
update "SCOTT"."EMP" set "SAL" = '900' where "SAL" = '1000' and ROWID = 'AAAVREAAEAAAACXAAA';
SCN TO_CHAR(TIMESTAMP,'
---------- -------------------
SQL_REDO
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_UNDO
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1091757 2016-03-23 10:18:09
update "SCOTT"."EMP" set "ENAME" = 'HUYUWU' where "ENAME" = 'SMITH' and ROWID = 'AAAVREAAEAAAACXAAA';
update "SCOTT"."EMP" set "ENAME" = 'SMITH' where "ENAME" = 'HUYUWU' and ROWID = 'AAAVREAAEAAAACXAAA';
###################################################################################################################
检查分析结果：
select * from v$logmnr_contents where sql_redo like '%table_name%'---这个where条件，就是你自己要填写查找的内容。
但是在sqlplus中此语句输出结果太乱
-------------------------------------------------------------------------------------------------------------------
查看挖掘结果
select scn,timestamp,sql_redo,sql_undo,username,os_username,machine_name from
v$logmnr_contents where seg_owner='SCOTT';
或select sql_redo from v$logmnr_contents where lower(sql_redo) like '%update%'
and seg_name='DEPT';
####################################################################################################################
停止日志挖掘
SQL> exec sys.dbms_logmnr.end_logmnr;
PL/SQL procedure successfully completed.
SQL*Plus: Release 11.2.0.4.0 Production on Wed Mar 23 10:15:11 2016
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> show user
USER is "SYS"
SQL> conn scott/tiger
Connected.
SQL> set lines 200
SQL> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 900 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
14 rows selected.
SQL> update emp set sal=sal+100 where empno=7369;
1 row updated.
SQL> commit;
Commit complete.
SQL> update emp set ename='HUYUWU' where empno=7369;
1 row updated.
SQL> commit;
Commit complete.
SQL> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 HUYUWU CLERK 7902 17-DEC-80 1000 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
14 rows selected.
SQL> conn /as sysdba
Connected.
SQL> show user
USER is "SYS"
1.检查库当前是否为追加日志模式
select supplemental_log_data_min from v$database;
结果为NO，则没有开启
2.修改库为追加日志数据模式
alter database add supplemental log data;
SQL> Set linesize 200
SQL> col member format a50
SQL> select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_
---------- ------- ------- -------------------------------------------------- ---
4 ONLINE /u01/app/oracle/oradata/test/redo04.log NO
5 ONLINE /u01/app/oracle/oradata/test/redo05.log NO
6 ONLINE /u01/app/oracle/oradata/test/redo06.log NO
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ ---------
4 1 39 524288000 512 1 YES INACTIVE 1088777 22-MAR-16 1088780 22-MAR-16
5 1 40 524288000 512 1 NO CURRENT 1088780 22-MAR-16 2.8147E+14
6 1 38 524288000 512 1 YES INACTIVE 1088774 22-MAR-16 1088777 22-MAR-16
使用当前日志建立分析列表（添加archive redo log）---#（如果日志被切换使用当前日志则无效，需使用全部日志）
SQL> exec sys.dbms_logmnr.add_logfile(logfilename=>'/u01/app/oracle/oradata/test/redo05.log');
exec sys.dbms_logmnr.add_logfile(logfilename=>'/u01/app/oracle/oradata/test/redo05.log', options=>sys.dbms_logmnr.addfile);
PL/SQL procedure successfully completed.
使用数据字典进行日志分析（日志挖掘）
SQL> exec sys.dbms_logmnr.start_logmnr(options=>sys.dbms_logmnr.dict_from_online_catalog);
PL/SQL procedure successfully completed.
####如果没有数据字典则使用 exec dbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_catalog)
查看挖掘结果
select scn,to_char(timestamp,'yyyy-mm-dd hh24:mi:ss'),sql_redo,sql_undo
from v$logmnr_contents
where seg_name='E'and seg_owner='HUYUWU';
SCN TO_CHAR(TIMESTAMP,'
---------- -------------------
SQL_REDO
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_UNDO
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1091406 2016-03-23 10:04:44
update "SCOTT"."EMP" set "SAL" = '900' where "SAL" = '800' and ROWID = 'AAAVREAAEAAAACXAAA';
update "SCOTT"."EMP" set "SAL" = '800' where "SAL" = '900' and ROWID = 'AAAVREAAEAAAACXAAA';
1091711 2016-03-23 10:16:24
update "SCOTT"."EMP" set "SAL" = '1000' where "SAL" = '900' and ROWID = 'AAAVREAAEAAAACXAAA';
update "SCOTT"."EMP" set "SAL" = '900' where "SAL" = '1000' and ROWID = 'AAAVREAAEAAAACXAAA';
SCN TO_CHAR(TIMESTAMP,'
---------- -------------------
SQL_REDO
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_UNDO
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1091757 2016-03-23 10:18:09
update "SCOTT"."EMP" set "ENAME" = 'HUYUWU' where "ENAME" = 'SMITH' and ROWID = 'AAAVREAAEAAAACXAAA';
update "SCOTT"."EMP" set "ENAME" = 'SMITH' where "ENAME" = 'HUYUWU' and ROWID = 'AAAVREAAEAAAACXAAA';
###################################################################################################################
检查分析结果：
select * from v$logmnr_contents where sql_redo like '%table_name%'---这个where条件，就是你自己要填写查找的内容。
但是在sqlplus中此语句输出结果太乱
-------------------------------------------------------------------------------------------------------------------
查看挖掘结果
select scn,timestamp,sql_redo,sql_undo,username,os_username,machine_name from
v$logmnr_contents where seg_owner='SCOTT';
或select sql_redo from v$logmnr_contents where lower(sql_redo) like '%update%'
and seg_name='DEPT';
####################################################################################################################
停止日志挖掘
SQL> exec sys.dbms_logmnr.end_logmnr;
PL/SQL procedure successfully completed.
1 0
- 日志挖掘
- 日志挖掘
- dbms_logmnr日志挖掘器
- Oracle的日志挖掘
- 日志挖掘(logminer)
- logMiner 日志挖掘 测试
- oracle 日志挖掘
- Logmnr--日志挖掘器
- Oracle日志挖掘
- oracle 日志挖掘
- Logminer日志挖掘范例
- 日志挖掘(logminer)
- Linux---AWStats日志挖掘
- web日志挖掘过程
- 日志挖掘 log miner
- Oracle日志挖掘
- oracle日志挖掘dbms_logmnr
- oracle 日志挖掘
- maven构建本地仓库
- Context
- python - 配置idle 的主题效果
- ViewPager自动无限轮播加小圆点
- jvm出现OutOfMemoryError时处理方法/jvm原理和优化参考
- 日志挖掘
- Java多线程之ExecutorService
- Java并发编程系列之十八：读写锁
- HashMap 源码解析
- Appscan---会话标识未更新
- 关于fragment切换失效问题
- app之间的跳转,查看URL的端口号,主机信息
- #pragma和#ifndef的区别
- 【BZOJ4312】立方体
