使用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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/25850100/viewspace-700246/