使用LOG Miner挖掘日誌基本步驟---02

1.基本操作

SELECT * FROM v$logmnr_contents;

10g的

begin
  dbms_logmnr.add_logfile(logfilename=>'/C11/archive1/1_35673_635950047.dbf');
end;

begin
  DBMS_LOGMNR.START_LOGMNR(options=>sys.DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG);
end;

begin
  dbms_logmnr.end_logmnr;
end;

2.詳細描述如下：

使用LOG Miner挖掘日誌基本步驟
SQL> execute dbms_logmnr.add_logfile(logfilename=>'&name',options=>dbms_logmnr.new);   
Enter value for name:
PL/SQL procedure successfully completed.

SQL> execute dbms_logmnr.add_logfile(logfilename=>'&name',options=>dbms_logmnr.addfile);
Enter value for name:
PL/SQL procedure successfully completed.

SQL> execute dbms_logmnr.add_logfile(logfilename=>'&name',options=>dbms_logmnr.addfile);
Enter value for name:
PL/SQL procedure successfully completed.

SQL> execute dbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_catalog);

PL/SQL procedure successfully completed.

SQL> create table sfism4.log_hist tablespace log_data nologging as select * from v$logmnr_contents;

Table created.

SQL> execute dbms_logmnr.end_logmnr();

PL/SQL procedure successfully completed.

具體說明請參考
Oracle9i Database Administrator's Guide Release 2 (9.2)
Part Number A96521-01
Chapter 9 Using LogMiner to Analyze Redo Logs

 

日誌分析與LogMiner
LogMiner工具的主要用途有：
（1）跟蹤資料庫的變化：可以離線的跟蹤資料庫的變化，而不會影響線上系統的效能。
（2）回退資料庫的變化：回退特定的變化資料，減少point-in-time recovery的執行。
（3）優化和擴容計劃：可通過分析日誌檔案中的資料以分析資料增長模式。
Oracle9i LogMiner的增強功能
（1）支援更多資料/儲存型別：連結/遷移行、CLUSTER表操作、DIRECT PATH插入以及DDL操作。
（2）提取和使用資料字典的選項：現在資料字典不僅可以提取到一個外部檔案中，還可以直接提取到重做日誌流中，它在日誌流中提供了操作當時的資料字典快照，這樣就可以實現離線分析。
（3）允許對DML操作按事務進行分組：可以在START_LOGMNR（）中設定COMMITTED_DATA_ONLY選項，實現對DML操作的分組，這樣將按SCN的順序返回已經提交的事務。
（4）支援SCHEMA的變化：在資料庫開啟的狀態下，如果使用了LogMiner的DDL_DICT_TRACKING選項，Oracle9i的LogMiner將自動對比最初的日誌流和當前系統的資料字典，並返回正確的DDL語句，並且會自動偵察並標記當前資料字典和最初日誌流之間的差別，這樣即使最初日誌流中所涉及的表已經被更改或者根本已經不存在，LogMiner同樣會返回正確的DDL語句。
（5）在日誌中記錄更多列資訊的能力：例如對於UPDATE操作不僅會記錄被更新行的情況，還可以捕捉更多已發生的更新操作資訊。
（6）支援基於數值的查詢：Oracle9i LogMiner在支援原有基於後設資料（操作、物件等）查詢的基礎上，開始支援基於實際涉及到的資料的查詢。例如涉及一個工資表，現在可以很容易地查出員工工資由1000變成2000的原始更新語句，而在之前只能選出所有的更新語句。
1 備份資料庫
vi usualbk.rcv
connect targetsys/oracle@clonedb
run{
allocate channel d1 device type disk;
backup as compressed backupset
incremental level=0
format='/rmanbak/inc0_%d_%U'
tag='inc0'
channel=d1
database;
backup as compressed backupset
format='/rmanbak/arch_%d_%U'
tag='arch'
channel=d1
archivelog all delete input;
backup as compressed backupset
format='/rmanbak/backup.ctl'
tag='ctl'
channel=d1
current controlfile reuse;}
使用命令備份資料庫 rman cmdfile=/home/oracle/usualbk.rcv log=/home/oracle/rman.log append
2 初始化設定
A 在初始化引數檔案中init.ora設定引數utl_file_dir,
D:\oracle\product\10.2.0\admin\oradb\pfile\init.ora 修改utl_file_dir=/logmnr
sql> shutdown immediate
sql> startup pfile='D:\oracle\product\10.2.0\admin\oradb\pfile\init.ora';
sql> create spfile from pfile='D:\oracle\product\10.2.0\admin\oradb\pfile\init.ora';
sql> shutdown immediate
sql> startup
sql> show parameter utl
或者alter system set utl_file_dir='/logmnr' scope=spfile;
B 安裝LogMiner工具，這兩個指令碼必須均以SYS使用者身份執行
@$ORACLE_HOME/rdbms/admin/dbmslmd.sql  用來建立DBMS_LOGMNR_D包建立資料字典檔案，用於提取資料字典資訊到外部平面檔案或者是聯機日誌中
 @$ORACLE_HOME/rdbms/admin/dbmslm.sql 用來建立DBMS_LOGMNR包  
grant execute on dbms_logmnr to public;  
在10G之前，logmnr使用的臨時表v$logmnr_contents使用的是system表空間，在10g後使用的是sysaux表空間，可以修改表空間：
exec sys.dbms_logmnr_d.set_tablespace('USERS');
如果要分析歸檔日誌檔案，資料庫要切換到歸檔模式
sql>archive log list
sql>alter system switch logfile current;
SELECT NAME, First_Time
  FROM V$archived_Log
 WHERE Sequence# = (SELECT MAX(Sequence#)
                      FROM V$archived_Log
                     WHERE Dictionary_Begin = 'YES');
最新的一個歸檔日誌是後面要分析的
3 Enable Supplemental Logging
  Database-Level Supplemental Logging
    Minimal Supplemental Logging
           ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
    Database-Level Identification Key Logging
           ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;
           ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;
           ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (UNIQUE) COLUMNS;
           ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (FOREIGN KEY) COLUMNS;
    Disabling Database-Level Supplemental Logging
           disable database-level supplemental logging:
             ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;
             ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (UNIQUE) COLUMNS;
             ALTER DATABASE DROP SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;
             ALTER DATABASE DROP SUPPLEMENTAL LOG DATA;
           disable all database supplemental logging:
             ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;
             ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (UNIQUE) COLUMNS;
             ALTER DATABASE DROP SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;
             ALTER DATABASE DROP SUPPLEMENTAL LOG DATA (UNIQUE) COLUMNS;
             ALTER DATABASE DROP SUPPLEMENTAL LOG DATA;
  Table-Level Supplemental Logging        
    Table-Level Identification Key Logging
           ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;
           ALTER TABLE HR.EMPLOYEES ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;
           ALTER TABLE HR.EMPLOYEES ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;
           ALTER TABLE HR.EMPLOYEES ADD SUPPLEMENTAL LOG DATA (UNIQUE) COLUMNS;
    Table-Level User-Defined Supplemental Log Groups
           ALTER TABLE HR.EMPLOYEES ADD SUPPLEMENTAL LOG GROUP emp_parttime(EMPLOYEE_ID, LAST_NAME,DEPARTMENT_ID) ALWAYS;
           ALTER TABLE HR.EMPLOYEES ADD SUPPLEMENTAL LOG GROUP emp_fulltime(EMPLOYEE_ID, LAST_NAME,DEPARTMENT_ID);
           ALTER TABLE HR.EMPLOYEES ADD SUPPLEMENTAL LOG GROUP emp_parttime(DEPARTMENT_ID NO LOG, EMPLOYEE_ID);
4  提取資料字典，有三個選項，可以任意選擇一個
A 提取字典資料到平面檔案  --9i後不提倡使用
    set UTL_FILE_DIR to use /oracle/database as the directory
    STARTUP
    execute sys.dbms_logmnr_d.build('dictionary.ora','d:\oracle\logminer',options => dbms_logmnr_d.STORE_IN_FLAT_FILE);
B 提取字典資料到重做日誌檔案
    alter database add supplemental log data;
    execute sys.dbms_logmnr_d.build(options => dbms_logmnr_d.STORE_IN_REDO_LOGS);  
   查詢重做日誌檔案寫有資料字典的快照資訊，我們要分析那個時段的歸檔日誌，就選擇離這個分析的歸檔日誌最近的，包含dictionary_begin='YES'的一個日誌，與包含dictionary_end='YES'的一個日誌一起新增到分析的日誌中，這樣就可以脫離原來的資料庫來分析歸檔日誌：
    select name from v$archived_log where dictionary_begin='YES';
    select name from v$archived_log where dictionary_end='YES';
C Using the Online Catalog
     EXECUTE DBMS_LOGMNR.START_LOGMNR(OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG);
5 制定需要分析的日誌檔案列表。即新增需要監視的日誌檔案。
A 分析聯機重做日誌檔案
建立LogMiner要分析的日誌檔案列表。
sql>execute dbms_logmnr.add_logfile(LogFileName => 'D:\oracle\product\10.2.0\oradata\oradb\redo01.log',Options => dbms_logmnr.new);
新增其他日誌檔案到列表
sql>execute dbms_logmnr.add_logfile(LogFileName => 'D:\oracle\product\10.2.0\oradata\oradb\redo02.log',Options => dbms_logmnr.ADDFILE);
sql>execute dbms_logmnr.add_logfile(LogFileName => 'D:\oracle\product\10.2.0\oradata\oradb\redo03.log',Options => dbms_logmnr.ADDFILE);              
如果要從分析的日誌列表中刪除redo03.log，則執行命令：
sql>execute dbms_logmnr.add_logfile(LogFileName => 'D:\oracle\product\10.2.0\oradata\oradb\redo03.log',Options => dbms_logmnr.REMOVEFILE);
sys@ORADB> select filename from v$logmnr_logs;
          FILENAME
          -------------------------------------------------
          D:\oracle\product\10.2.0\oradata\oradb\redo02.log
          D:\oracle\product\10.2.0\oradata\oradb\redo03.log
          D:\oracle\product\10.2.0\oradata\oradb\redo01.log                
B 分析離線歸檔日誌檔案
exec sys.dbms_logmnr_d.build(options=>sys.dbms_logmnr_d.store_in_redo_logs);
現在假定要分析一個/usr/oracle/data/db1arch_1_210_482701534.dbf的日誌，根據日誌的序列號210，應當查詢離210最近的，包含有字典資訊的量個聯機日誌：
SQL>SELECT NAME, Sequence#, Dictionary_Begin d_Beg, Dictionary_End d_End
  FROM V$archived_Log
 WHERE Sequence# = (SELECT MAX(Sequence#)FROM V$archived_Log WHERE Dictionary_End = 'YES' AND Sequence# <= 210);或者是>=210
這裡假如找到了/usr/oracle/data/db1arch_1_208_482701534.dbf，根據得到的208繼續查詢下一個開始點：
SELECT NAME, Sequence#, Dictionary_Begin d_Beg, Dictionary_End d_End
  FROM V$archived_Log
 WHERE Sequence# = (SELECT MAX(Sequence#)FROM V$archived_Log WHERE Dictionary_Begin = 'YES'AND Sequence# <= 208);
假如找到了/usr/oracle/data/db1arch_1_207_482701534.dbf，就可以新增日誌：
exec dbms_logmnr.add_logfile(logfilename => '/usr/oracle/data/db1arch_1_210_482701534.dbf',options => dbms_logmnr.new);
新增另外的日誌檔案到列表,建議每次只新增一個需要分析的日誌檔案到列表。分析完再新增第二個
exec dbms_logmnr.add_logfile(logfilename => '/usr/oracle/data/db1arch_1_208_482701534.dbf',Options => dbms_logmnr.ADDFILE);
exec dbms_logmnr.add_logfile(logfilename => '/usr/oracle/data/db1arch_1_207_482701534.dbf',Options => dbms_logmnr.ADDFILE);
6  啟動LogMiner會話，進行日誌分析：
A 無限制條件分析
execute dbms_logmnr.start_logmnr(DictFileName => 'D:\oracle\logminer\dictionary.ora'); 指定平面檔案目錄名
exec dbms_logmnr.start_logmnr(options =>dbms_logmnr.dict_from_online_catalog + dbms_logmnr.committed_data_only); 
committed_data_only，標識我們只提取已經提交的事務。
execute sys.dbms_logmnr.start_logmnr(options =>dbms_logmnr.DICT_FROM_ONLINE_CATALOG);
dict_from_Redo_logs，LogMiner將在重做日誌檔案中查詢字典資料，該重做日誌檔案由dbms_logmnr.add_logfile過程指定。  
      EXECUTE DBMS_LOGMNR.START_LOGMNR(options => DBMS_LOGMNR.SKIP_CORRUPTION);
Skipping Redo Corruptions:
B 有限制條件分析
       Automatically:
ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';
BEGIN
  Dbms_Logmnr.Start_Logmnr(Starttime => '01-Jan-2003 08:30:00',
                           Endtime   => '01-Jan-2003 08:45:00',
                           Options   => Dbms_Logmnr.Dict_From_Online_Catalog +Dbms_Logmnr.Continuous_Mine);
END;
    Manually:
      Filtering Data by Time:
BEGIN
  Dbms_Logmnr.Start_Logmnr(Dictfilename => 'D:\oracle\logminer\dictionary.ora',
                           Starttime    => To_Date('2007-12-27 00:00:00','yyyy-mm-dd hh24:mi:ss'),
                           Endtime      => To_Date('2007-12-27 23:00:00','yyyy-mm-dd hh24:mi:ss'),
      PTIONS => DBMS_LOGMNR.CONTINUOUS_MINE);
END;
  Filtering Data by SCN:                                
  EXECUTE DBMS_LOGMNR.START_LOGMNR(STARTSCN => 621047,
                                   ENDSCN   => 625695,
                                   OPTIONS  => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG + DBMS_LOGMNR.CONTINUOUS_MINE);  
BEGIN
  Dbms_Logmnr.Start_Logmnr(Dictfilename => 'D:\oracle\logminer\dictionary.ora',
                           Startscn     => 7520,
                           Endscn       => 8950);
END;
7 查詢v$logmnr_contents
           SELECT ANY TRANSACTION privilege to query V$LOGMNR_CONTENTS;
 select sql_redo,sql_undo from v$logmnr_contents where username='SCOTT';          查詢撤消和重做的SQL
  SELECT Sql_Redo
    FROM V$logmnr_Contents
   WHERE Seg_Name = 'EMPLOYEES'
     AND Seg_Owner = 'HR'
     AND peration = 'UPDATE'
     AND Dbms_Logmnr.Mine_Value(Redo_Value, 'HR.EMPLOYEES.SALARY') >
         2 * Dbms_Logmnr.Mine_Value(Undo_Value, 'HR.EMPLOYEES.SALARY');
  SELECT (Xidusn || '.' || Xidslt || '.' || Xidsqn) AS Xid,
         (Dbms_Logmnr.Mine_Value(Redo_Value, 'HR.EMPLOYEES.SALARY') -
         Dbms_Logmnr.Mine_Value(Undo_Value, 'HR.EMPLOYEES.SALARY')) AS Incr_Sal
    FROM V$logmnr_Contents
   WHERE peration = 'UPDATE'
     AND Dbms_Logmnr.Column_Present(Redo_Value, 'HR.EMPLOYEES.SALARY') = 1
     AND Dbms_Logmnr.Column_Present(Undo_Value, 'HR.EMPLOYEES.SALARY') = 1;
8 結束logminer會話,釋放所有資源和清除PGA  
  EXECUTE DBMS_LOGMNR.END_LOGMNR;
    
9 不完全恢復資料庫
10 驗證恢復結果
   在另一個會話中開啟資料庫，查詢恢復後的結果。
11 相關的資料字典
v$logmnr_contents
v$logmnr_logs
v$logmnr_dictionary
v$logmnr_parameters
v$lolist
  
12 其他注意事項
A LogMiner生成行級sql,並不是實際的語句
B 所有資訊都在PGA中，會話結束，資訊丟失，可以用create table as select * from v$logmnr_contents儲存資訊。
C 不支援下面的資料結構
簡單的和巢狀的抽象資料結構
巢狀表和varry
索引組織表
一個帶有族建的create table as select
D LogMiner必須使用被分析資料庫例項產生的字典檔案，而不是安裝LogMiner的資料庫產生的字典檔案，另外必須保證安裝LogMiner資料庫的字符集和被分析資料庫的字符集相同。
E 被分析資料庫平臺必須和當前LogMiner所在資料庫平臺一樣。
F LogMiner日誌分析工具僅能夠分析Oracle 8以後的產品。
13 分析的例項：
alter system archive log current;
craete table test ( a int);
insert into test values(1);
insert into test values(2);
insert into test values(3);
commit;
delete from test;
commit;
alter system archive log current;
找到剛才兩次切換之間包含這些操作的歸檔日誌，假定找到檔案為：/archive_log/archive/1_9.arc。
scott@oradb>execsys.dbms_logmnr.add_logfile(LogFileName=>'/archive_log/archive/1_9.arc',options => dbms_logmnr.new);
新增更多的日誌檔案用下面的方式：
scott@oradb> execsys.dbms_logmnr.add_logfile(LogFileName=>'/archive_log/archive/1_10.arc',options => dbms_logmnr.ADDFILE); 
scott@oradb>execsys.dbms_logmnr.start_logmnr(Options => sys.dbms_logmnr.dict_from_online_catalog);
檢視分析結果：
scott@oradb>selectt.SCN,t.TIMESTAMP,t.SEG_OWNER,t.OPERATION,
2  t.SQL_REDO,t.SQL_UNDO
3  from v$logmnr_contents t where t.SEG_NAME='TEST';
如果要查詢會話的資訊，可以查詢session_info，如果想儲存分析結果：
create table logmnr_contents as select * from v$logmnr_contents;
如果分析完成清空session記憶體資訊：
exec sys.dbms_logmnr.end_Logmnr

附件：回滾誤操作的資料方式：
DECLARE
  Mysql VARCHAR2(4000);
  Num   NUMBER := 0;
BEGIN
  FOR c_Tmp IN (SELECT Sql_Undo FROM Logmnr_Contents  WHERE peration = 'DELETE') LOOP
    Mysql := REPLACE(c_Tmp, Sql_Undo, ';', '');
    EXECUTE IMMEDIATE Mysql;
    Num := Num + 1;
    IF MOD(Num, 1000) = 0 THEN
      COMMIT;
    END IF;
  END LOOP;
  COMMIT;
EXCEPTION
  WHEN OTHERS THEN
    NULL;
END;

不完全恢復資料庫
root#su - oracle
用RMAN恢復資料庫到第一個刪除表時間點（logmnr查詢得到結果）
oracle$uniread rman nocatalog targetsys/oracle@clonedb
rman>run {
allocate channel d1 device type disk;
set until scn 1469380;
restore database;
recover database;
}
rman>
 
 
恢復大量誤刪除的資料
建立一個表來儲存提取出來的sql
create table logmnr_content tablespace tools as scn,cscn,TIMESTAMP,sql_undo from v$logmnr_contents where 1=0;
通過dba_objects 查到表對應的 object_id and data_object_id ,用來在log中提取該表相關的sql_undo
select object_id,data_object_id from dba_objects where object_name = ???
通過在os中找到的歸檔日誌範圍，進行聯機提取
將undo sql 插入一個表中,比如我的日誌序號範圍是 5813 到 5850為了防止臨時空間不足，一個一個歸檔日誌檔案處理。
BEGIN
  FOR i IN 5813 .. 5850 LOOP
    Dbms_Logmnr.Add_Logfile(Logfilename => '/disk2/oradata/arch/crmcn/crmcn_1_' || i ||'.arc');
    Dbms_Logmnr.Start_Logmnr(Options => Sys.Dbms_Logmnr.Dict_From_Online_Catalog);
    Dbms_Logmnr.Start_Logmnr();
    INSERT INTO Logmnr_Content
      (Scn, Cscn, TIMESTAMP, Sql_Undo)
      SELECT Scn, Cscn, TIMESTAMP, Sql_Undo
        FROM V$logmnr_Contents
       WHERE Data_Objd# = 67540;
    COMMIT;
    Dbms_Logmnr.End_Logmnr();
  END LOOP;
END;
將提取出來的sql通過動態sql執行插入表
DECLARE
  Sql_Str VARCHAR2(4000);
BEGIN
  FOR c IN (SELECT * FROM Logmnr_Content) LOOP
    Sql_Str := REPLACE(c.Sql_Undo, ';', '');
    EXECUTE IMMEDIATE Sql_Str;
  END LOOP;
  COMMIT;
END;

注意：如果在這段日誌中還有其他對該表的操作的話，可以結合操作型別 OPERATION 和 提交scn cscn 來判斷 到底是不是該恢復的這部分資料。

 

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/25850100/viewspace-700246/，如需轉載，請註明出處，否則將追究法律責任。