oracle 日志挖掘

gaojinguan

　　日志挖掘：
　　logminer
　　通过对日志的分析，能够获得用户的dml操作语句，用来修复用户数据的丢失或者是错误；
　　能够通过日志挖掘，获得用户操作的反向操作(undo_sql)或者正向操作（redo_sql);
　　数据库的附加日志：
　　SYS@orcl11g> alter database add supplemental log data;
　　Database altered.
　　样例演示：
　　1.创建一个表
　　SCOTT@orcl11g> drop table e_logminer purge;
　　SCOTT@orcl11g> create table e_logminer as select * from emp;
　　2.对这个表进行更新，使用了错误的更新条件
　　SCOTT@orcl11g> update e_logminer set sal=12345;
　　SCOTT@orcl11g> commit;
　　3.切换日志，模拟这个操作过了很久这样的状态
　　SCOTT@orcl11g> conn / as sysdba
　　Connected.
　　SYS@orcl11g> archive log list;
　　Database log mode              Archive Mode
　　Automatic archival             Enabled
　　Archive destination            /u01/app/oracle/arch2
　　Oldest online log sequence     8
　　Next log sequence to archive   10
　　Current log sequence           10
　　SYS@orcl11g> alter system switch logfile;
　　。。。。 --经过n次切换
　　4.根据大致的用户错误时间，找到所需要的所有的日志（归档日志和在线重做日志）；
　　--假设用户操作的时间为2013-07-01 12：00：00
　　SYS@orcl11g>select name from v$archived_log
　　where first_time >=
　　(select max(first_time) from v$archived_log
　　where first_time =
　　(select max(first_time) from v$archived_log
　　where first_time  set trim on
　　SYS@orcl11g> set trims on
　　SYS@orcl11g> set term off
　　SYS@orcl11g> set heading off
　　SYS@orcl11g> set feedback off
　　SYS@orcl11g> set echo off
　　SYS@orcl11g> set linesize 200
　　SYS@orcl11g> set pagesize 0
　　SYS@orcl11g> spool /home/oracle/logmnr.sql
　　SYS@orcl11g> @log.sql
　　SYS@orcl11g> spool off
　　SYS@orcl11g> quit
　　--修改logmnr.sql
　　[oracle@db253 ~]$ cat logmnr.sql
　　exec dbms_logmnr.add_logfile('/u01/app/oracle/arch2/1_10_819218658.dbf',dbms_logmnr.new);
　　exec dbms_logmnr.add_logfile('/u01/app/oracle/arch2/1_11_819218658.dbf',dbms_logmnr.addfile);
　　exec dbms_logmnr.add_logfile('/u01/app/oracle/arch2/1_12_819218658.dbf',dbms_logmnr.addfile);
　　exec dbms_logmnr.add_logfile('/u01/app/oracle/arch2/1_13_819218658.dbf',dbms_logmnr.addfile);
　　exec dbms_logmnr.add_logfile('/u01/app/oracle/arch2/1_14_819218658.dbf',dbms_logmnr.addfile);
　　exec dbms_logmnr.add_logfile('/u01/app/oracle/arch2/1_15_819218658.dbf',dbms_logmnr.addfile);
　　exec dbms_logmnr.add_logfile('/u01/app/oracle/arch2/1_16_819218658.dbf',dbms_logmnr.addfile);
　　exec dbms_logmnr.add_logfile('/u01/app/oracle/arch2/1_17_819218658.dbf',dbms_logmnr.addfile);
　　exec dbms_logmnr.add_logfile('/u01/app/oracle/oradata/orcl11g/redo03.log',dbms_logmnr.addfile);
　　6.执行挖掘队列脚本
　　SYS@orcl11g> @logmnr.sql
　　--不要退出这个会话
　　7.开始挖掘
　　SYS@orcl11g> exec dbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_catalog);
　　PL/SQL procedure successfully completed.
　　8.获取挖掘结果
　　SYS@orcl11g> set trim on
　　SYS@orcl11g> set trims on
　　SYS@orcl11g> set term off
　　SYS@orcl11g> set heading off
　　SYS@orcl11g> set feedback off
　　SYS@orcl11g> set echo off
　　SYS@orcl11g> set linesize 500
　　SYS@orcl11g> set pagesize 0
　　SYS@orcl11g> spool /home/oracle/undo_logmnr.sql
　　SYS@orcl11g> select sql_undo from v$logmnr_contents
　　2  where table_name='E_LOGMINER'
　　3* and lower(sql_redo) like 'update%12345%';
　　SYS@orcl11g> spool off
　　SYS@orcl11g> quit
　　--修改undo脚本
　　[oracle@db253 ~]$ cat undo_logmnr.sql
　　update "SCOTT"."E_LOGMINER" set "SAL" = '800' where "EMPNO" = '7369' and "ENAME" = 'SMITH' and "JOB" = 'CLERK' and "MGR" = '7902' and "HIREDATE" = TO_DATE('1980-12-17 00:00:00', 'yyyy-mm-dd hh24:mi:ss') and "SAL" = '12345' and "COMM" IS NULL and "DEPTNO" = '20' and ROWID = 'AAATQQAAEAAAAIjAAA';
　　update "SCOTT"."E_LOGMINER" set "SAL" = '1600' where "EMPNO" = '7499' and "ENAME" = 'ALLEN' and "JOB" = 'SALESMAN' and "MGR" = '7698' and "HIREDATE" = TO_DATE('1981-02-20 00:00:00', 'yyyy-mm-dd hh24:mi:ss') and "SAL" = '12345' and "COMM" = '300' and "DEPTNO" = '30' and ROWID = 'AAATQQAAEAAAAIjAAB';
　　update "SCOTT"."E_LOGMINER" set "SAL" = '1250' where "EMPNO" = '7521' and "ENAME" = 'WARD' and "JOB" = 'SALESMAN' and "MGR" = '7698' and "HIREDATE" = TO_DATE('1981-02-22 00:00:00', 'yyyy-mm-dd hh24:mi:ss') and "SAL" = '12345' and "COMM" = '500' and "DEPTNO" = '30' and ROWID = 'AAATQQAAEAAAAIjAAC';
　　update "SCOTT"."E_LOGMINER" set "SAL" = '2975' where "EMPNO" = '7566' and "ENAME" = 'JONES' and "JOB" = 'MANAGER' and "MGR" = '7839' and "HIREDATE" = TO_DATE('1981-04-02 00:00:00', 'yyyy-mm-dd hh24:mi:ss') and "SAL" = '12345' and "COMM" IS NULL and "DEPTNO" = '20' and ROWID = 'AAATQQAAEAAAAIjAAD';
　　update "SCOTT"."E_LOGMINER" set "SAL" = '1250' where "EMPNO" = '7654' and "ENAME" = 'MARTIN' and "JOB" = 'SALESMAN' and "MGR" = '7698' and "HIREDATE" = TO_DATE('1981-09-28 00:00:00', 'yyyy-mm-dd hh24:mi:ss') and "SAL" = '12345' and "COMM" = '1400' and "DEPTNO" = '30' and ROWID = 'AAATQQAAEAAAAIjAAE';
　　update "SCOTT"."E_LOGMINER" set "SAL" = '2850' where "EMPNO" = '7698' and "ENAME" = 'BLAKE' and "JOB" = 'MANAGER' and "MGR" = '7839' and "HIREDATE" = TO_DATE('1981-05-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss') and "SAL" = '12345' and "COMM" IS NULL and "DEPTNO" = '30' and ROWID = 'AAATQQAAEAAAAIjAAF';
　　update "SCOTT"."E_LOGMINER" set "SAL" = '2450' where "EMPNO" = '7782' and "ENAME" = 'CLARK' and "JOB" = 'MANAGER' and "MGR" = '7839' and "HIREDATE" = TO_DATE('1981-06-09 00:00:00', 'yyyy-mm-dd hh24:mi:ss') and "SAL" = '12345' and "COMM" IS NULL and "DEPTNO" = '10' and ROWID = 'AAATQQAAEAAAAIjAAG';
　　update "SCOTT"."E_LOGMINER" set "SAL" = '3000' where "EMPNO" = '7788' and "ENAME" = 'SCOTT' and "JOB" = 'ANALYST' and "MGR" = '7566' and "HIREDATE" = TO_DATE('1987-04-19 00:00:00', 'yyyy-mm-dd hh24:mi:ss') and "SAL" = '12345' and "COMM" IS NULL and "DEPTNO" = '20' and ROWID = 'AAATQQAAEAAAAIjAAH';
　　update "SCOTT"."E_LOGMINER" set "SAL" = '5000' where "EMPNO" = '7839' and "ENAME" = 'KING' and "JOB" = 'PRESIDENT' and "MGR" IS NULL and "HIREDATE" = TO_DATE('1981-11-17 00:00:00', 'yyyy-mm-dd hh24:mi:ss') and "SAL" = '12345' and "COMM" IS NULL and "DEPTNO" = '10' and ROWID = 'AAATQQAAEAAAAIjAAI';
　　update "SCOTT"."E_LOGMINER" set "SAL" = '1500' where "EMPNO" = '7844' and "ENAME" = 'TURNER' and "JOB" = 'SALESMAN' and "MGR" = '7698' and "HIREDATE" = TO_DATE('1981-09-08 00:00:00', 'yyyy-mm-dd hh24:mi:ss') and "SAL" = '12345' and "COMM" = '0' and "DEPTNO" = '30' and ROWID = 'AAATQQAAEAAAAIjAAJ';
　　update "SCOTT"."E_LOGMINER" set "SAL" = '1100' where "EMPNO" = '7876' and "ENAME" = 'ADAMS' and "JOB" = 'CLERK' and "MGR" = '7788' and "HIREDATE" = TO_DATE('1987-05-23 00:00:00', 'yyyy-mm-dd hh24:mi:ss') and "SAL" = '12345' and "COMM" IS NULL and "DEPTNO" = '20' and ROWID = 'AAATQQAAEAAAAIjAAK';
　　update "SCOTT"."E_LOGMINER" set "SAL" = '950' where "EMPNO" = '7900' and "ENAME" = 'JAMES' and "JOB" = 'CLERK' and "MGR" = '7698' and "HIREDATE" = TO_DATE('1981-12-03 00:00:00', 'yyyy-mm-dd hh24:mi:ss') and "SAL" = '12345' and "COMM" IS NULL and "DEPTNO" = '30' and ROWID = 'AAATQQAAEAAAAIjAAL';
　　update "SCOTT"."E_LOGMINER" set "SAL" = '3000' where "EMPNO" = '7902' and "ENAME" = 'FORD' and "JOB" = 'ANALYST' and "MGR" = '7566' and "HIREDATE" = TO_DATE('1981-12-03 00:00:00', 'yyyy-mm-dd hh24:mi:ss') and "SAL" = '12345' and "COMM" IS NULL and "DEPTNO" = '20' and ROWID = 'AAATQQAAEAAAAIjAAM';
　　update "SCOTT"."E_LOGMINER" set "SAL" = '1300' where "EMPNO" = '7934' and "ENAME" = 'MILLER' and "JOB" = 'CLERK' and "MGR" = '7782' and "HIREDATE" = TO_DATE('1982-01-23 00:00:00', 'yyyy-mm-dd hh24:mi:ss') and "SAL" = '12345' and "COMM" IS NULL and "DEPTNO" = '10' and ROWID = 'AAATQQAAEAAAAIjAAN';
　　--执行undo脚本
　　[oracle@db253 ~]$ sqlplus scott/tiger  @undo_logmnr.sql
　　9.结束挖掘
　　SYS@orcl11g> exec dbms_logmnr.end_logmner;