使用logminer分析oracle日志

分析oracle的日志
脚本安装logminer
  @$ORACLE_HOME/rdbms/admin/dbmslm.sql
  @$ORACLE_HOME/rdbms/admin/dbmslmd.sql
查看是否开启扩充日志
  select SUPPLEMENTAL_LOG_DATA_MIN from v$database;
  结果应该为yes  如果为no的话，手动开启，
  开启方法 alter database add supplemental log data;
创建日志分析用户并授权
  创建CREATE  USER  logminer IDENTIFIED BY logminer;
  授权GRANT CONNECT, RESOURCE,DBA TO logminer;
建立logminer的使用目录
  cd /opt/oracle/oradata/
  mkdir logminer
创建logminer字典文件路径
  CREATE DIRECTORY utlfile AS '/opt/oracle/oradata/logminer';
  alter system set utl_file_dir='/opt/app/oracle/oradata/logminer' scope=spfile;
  (第二步可能会出现ORA-32001: write to SPFILE requested but no SPFILE is in use的错误 重新创建一下spfile  命令create spfile from pfile;重启数据库，即可解决此错误 ，使用show parameter utl_file_dir可以查看字典路径是否设置完成)


开始分析
 分析在线日志文件
  准备测试数据
    conn logminer/logminer
    create table student(username varchar2(20),password varchar2(20));
    insert into student(username,password) values('qqq','111');
    insert into student(username,password) values('www','222');
    commit;
  创建数字字典文件
    EXECUTE dbms_logmnr_d.build(dictionary_filename => 'student.ora', dictionary_location =>'/opt/oracle/oradata/logminer');
  查看数据库当前使用的联机日志文件
    SELECT group#, sequence#, status, first_change#, first_time FROM V$log ORDER BY first_change#;(status为current的日志文件是正在使用的日志文件)
  将要解析的日志文件加入
    EXEC dbms_logmnr.add_logfile('/opt/oracle/oradata/orcl/redo02.log', dbms_logmnr.new);(redo02.log为正在使用的日志文件)
  启用logminer进行分析
    EXEC dbms_logmnr.start_logmnr( dictfilename=>'/opt/oracle/oradata/logminer/student.ora');
  查看日志分析结果
    SQL> col sql_redo for a50
    SQL> col sql_undo for a50
    SQL> col seg_owner for a10
    SQL> set linesize 5000 pagesize 5000
    SQL> SELECT sql_redo, sql_undo, seg_owner  FROM v$logmnr_contents WHERE seg_name='STUDENT' AND seg_owner='LOGMINER';


 分析归档日志文件-(需要去确认数据库的归档日志是否开启 开启命令alter database archivelog;)
  准备测试数据
  将当前日志归档
    alter system switch logfile;
  查看归档日志文件
    select sequence#, FIRST_CHANGE#, NEXT_CHANGE#,name from v$archived_log order by sequence# desc;
  创建数字字典文件
    EXECUTE dbms_logmnr_d.build(dictionary_filename => 'student2.ora', dictionary_location =>'/opt/oracle/oradata/logminer');
  将归档日志文件加入
     exec dbms_logmnr.add_logfile('/opt/oracle/flash_recovery_area/ORCL/archivelog/2018_03_07/o1_mf_1_13_f9zbdcgz_.arc', dbms_logmnr.new);
  启用logminer进行分析
     exec dbms_logmnr.start_logmnr(dictfilename=>'/opt/oracle/oradata/logminer/student2.ora');
  查看分析结果
     SELECT sql_redo, sql_undo, seg_owner  FROM v$logmnr_contents WHERE seg_name='STUDENT' AND seg_owner='LOGMINER';