Oracle的日志挖掘技術(shù)

       日志挖掘：logminer
作用：
通過對日志的分析，能夠獲得用戶的dml操作語句，用來修復(fù)用戶數(shù)據(jù)的丟失或者是錯誤；
能夠通過日志挖掘，獲得用戶操作的反向操作(undo_sql)或者正向操作(redo_sql);
實現(xiàn)條件：
執(zhí)行日志挖掘需要打開輔助日志功能（SUPPLEMENTAL_LOG_DATA_MIN）
SYS@orcl11g> select supplemental_log_data_min from v$database; --查看輔助日志開啟的狀態(tài)

SUPPLEME
--------------
YES
SYS@orcl11g>alter database add supplemental log data; --開啟附加日志
啟動最小補(bǔ)充日志的主要目的是為了使logminer具備識別由update命令導(dǎo)致的行遷移、行移動的能力。
***************************************************設(shè)置*場景*************************************
樣例演示：
1.創(chuàng)建一個表
SCOTT@orcl11g> drop table e_logminer purge;
SCOTT@orcl11g> create table e_logminer as select * from emp;

2.對這個表進(jìn)行更新，使用了錯誤的更新條件
SCOTT@orcl11g> update e_logminer set sal=12345;
SCOTT@orcl11g> commit;

3.切換日志，模擬這個操作過了很久這樣的狀態(tài)
SCOTT@orcl11g> conn / as sysdba

SYS@orcl11g> archive log list;
Database log mode                    Archive Mode
Automatic archival                      Enabled
Archive destination                     /u01/app/oracle/arch3
Oldest online log sequence          8
Next log sequence to archive      10
Current log sequence                  10

SYS@orcl11g> alter system switch logfile;
……切換n次
************************************************場景設(shè)置完畢**************************************
開始進(jìn)行日志挖掘：
1.根據(jù)大致的用戶錯誤時間，找到所需要的所有的日志（歸檔日志和在線重做日志）
--判斷大概數(shù)據(jù)還是正確的時間為2015-04-24 13：00：00
SYS@orcl11g>select name from v$archived_log where first_time >=
  (select max(first_time) from v$archived_log
    where first_time <= to_date('2015-04-24 13:00:00','yyyy-mm-dd hh34:mi:ss'))
union all
select member from v$logfile where group#=
  (select group# from v$log where archived='NO') order by name;

NAME
----------------------------------------------------------
/u01/app/oracle/arch4/1_41_857390041.dbf
/u01/app/oracle/arch4/1_42_857390041.dbf
/u01/app/oracle/arch4/1_43_857390041.dbf
/u01/app/oracle/arch4/1_44_857390041.dbf
/u01/app/oracle/arch4/1_45_857390041.dbf
/u01/app/oracle/oradata/orcl11g/redo01.log

9 rows selected.

2.深入修改上一個sql語句，構(gòu)建挖掘隊列所需的腳本
[oracle@db253 ~]$ cat log.sql
select q'[exec dbms_logmnr.add_logfile(']' || name || q'[',dbms_logmnr.addfile);]' from v$archived_log
where first_time >=
  (select max(first_time) from v$archived_log  where first_time <= to_date('2015-04-24 13:00:00','yyyy-mm-dd hh34:mi:ss'))
union all
select q'[exec dbms_logmnr.add_logfile(']' ||member ||q'[',dbms_logmnr.addfile);]' from v$logfile
where group#=
  (select group# from v$log  where archived='NO') order by 1
/

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 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/arch3/1_10_819218658.dbf',dbms_logmnr.new);
exec dbms_logmnr.add_logfile('/u01/app/oracle/arch3/1_11_819218658.dbf',dbms_logmnr.addfile);
exec dbms_logmnr.add_logfile('/u01/app/oracle/arch3/1_12_819218658.dbf',dbms_logmnr.addfile);
exec dbms_logmnr.add_logfile('/u01/app/oracle/arch3/1_13_819218658.dbf',dbms_logmnr.addfile);
exec dbms_logmnr.add_logfile('/u01/app/oracle/arch3/1_14_819218658.dbf',dbms_logmnr.addfile);
exec dbms_logmnr.add_logfile('/u01/app/oracle/arch3/1_15_819218658.dbf',dbms_logmnr.addfile);
exec dbms_logmnr.add_logfile('/u01/app/oracle/arch3/1_16_819218658.dbf',dbms_logmnr.addfile);
exec dbms_logmnr.add_logfile('/u01/app/oracle/arch3/1_17_819218658.dbf',dbms_logmnr.addfile);
exec dbms_logmnr.add_logfile('/u01/app/oracle/oradata/orcl11g/redo03.log',dbms_logmnr.addfile);
--添加日志文件到新的或已經(jīng)存在的日志列表中供日志挖掘器處理

3.執(zhí)行挖掘隊列腳本
SYS@orcl11g> @logmnr.sql        --不要退出這個會話
   --為logminer挖掘會話手動注冊可挖掘的重做日志    
   
4.開始挖掘
SYS@orcl11g> exec dbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_catalog);
--通過載入數(shù)據(jù)字典開始挖掘，這些字典是用于挖掘器將重做記錄中的oracle內(nèi)部對象翻譯成可讀的信息的轉(zhuǎn)換字典

5.獲取挖掘結(jié)果
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
where   table_name='E_LOGMINER'
  and lower(sql_redo) like 'update%12345%';

SYS@orcl11g> spool off
SYS@orcl11g> quit

6.修改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 hh34: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 hh34: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 hh34: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 hh34: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 hh34: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 hh34: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 hh34: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 hh34: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 hh34: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 hh34: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 hh34: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 hh34: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 hh34: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 hh34:mi:ss') and "SAL" = '12345' and "COMM" IS NULL and "DEPTNO" = '10' and ROWID = 'AAATQQAAEAAAAIjAAN';

7.執(zhí)行undo腳本
[oracle@db253 ~]$ sqlplus scott/tiger  @undo_logmnr.sql

8.結(jié)束挖掘
SYS@orcl11g> exec dbms_logmnr.end_logmnr;