Oracle日志挖掘解决方案

sqlplus sys/slims as sysdba;


--检查是否归档模式

archive log list;


--如果没有归档

------------------------------

shutdown immediate;


startup mount;


alter database archivelog;


alter database open;

-------------------------------


--创建时刻记录表

CREATE TABLE slims_bak_time(bak_time timestamp);


insert into slims_bak_time values(sysdate);


commit;


select * from slims_bak_time;


--检查审计

show parameter audit;


alter system set audit_sys_operations=TRUE scope=spfile;


alter system set audit_trail=db,extended scope=spfile;


--执行语句与过程

@C :\oracle\product\10.2.0\db_1\RDBMS\ADMIN\dbmslm.sql


@C :\oracle\product\10.2.0\db_1\RDBMS\ADMIN\dbmslms.sql


@C :\oracle\product\10.2.0\db_1\RDBMS\ADMIN\dbmslmd.sql


show parameter utl_file_dir;


--指定字典文件夹

alter system set utl_file_dir='C:\oracle\product\10.2.0\logminer' scope=spfile;


alter database add supplemental log data;


shutdown immediate;


startup open;


--------------PLSQL


alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

--封装好一个做日志分析挖掘的存储过程


CREATE OR REPLACE PROCEDURE EXPORT_DATA AS

  NOW_TIME TIMESTAMP := SYSDATE;

  TB_COUNT INT;

  TB_NAME  VARCHAR(50);


  CURSOR CURRENT_REDO_CUR IS

    SELECT MEMBER

      FROM V$LOGFILE

     WHERE GROUP# IN (SELECT GROUP# FROM V$LOG WHERE STATUS = 'CURRENT');


  CURSOR NO_ARCH_REDO_CUR IS

    SELECT MEMBER

      FROM V$LOGFILE

     WHERE GROUP# IN (SELECT GROUP#

                        FROM V$LOG

                       WHERE ARCHIVED = 'NO'

                         AND STATUS <> 'CURRENT');

  CURSOR ARCH_CUR IS

    SELECT NAME

      FROM V$ARCHIVED_LOG

     WHERE FIRST_TIME > (SELECT MAX(BAK_TIME) FROM SLIMS_BAK_TIME)

       AND FIRST_TIME < NOW_TIME

     ORDER BY STAMP DESC;

BEGIN

  INSERT INTO SLIMS_BAK_TIME VALUES (NOW_TIME);

  COMMIT;

  EXECUTE IMMEDIATE 'alter session set nls_date_format = ' ||

                    '''yyyy-mm-dd hh24:mi:ss''';

  EXECUTE IMMEDIATE 'alter session set nls_timestamp_format = ' ||

                    '''yyyy-mm-dd hh24:mi:ss''';

                   

  DBMS_LOGMNR_D.BUILD('dictionary.ora',

                      'C:\oracle\product\10.2.0\logminer');


  FOR CURRENT_REDO_REC IN CURRENT_REDO_CUR LOOP

    DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => CURRENT_REDO_REC.MEMBER,

                            OPTIONS     => DBMS_LOGMNR.NEW);

  END LOOP;


  FOR NO_ARCH_REC IN NO_ARCH_REDO_CUR LOOP

    DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => NO_ARCH_REC.MEMBER,

                            OPTIONS     => DBMS_LOGMNR.ADDFILE);

  END LOOP;


  FOR ARCH_REC IN ARCH_CUR LOOP

    DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => ARCH_REC.NAME,

                            OPTIONS     => DBMS_LOGMNR.ADDFILE);

  END LOOP;

  DBMS_LOGMNR.START_LOGMNR(DICTFILENAME => 'C:\oracle\product\10.2.0\logminer\dictionary.ora',

                           OPTIONS      => DBMS_LOGMNR.DDL_DICT_TRACKING);


  TB_NAME := 'EXPORT_DATA_LOG';

  SELECT COUNT(*)

    INTO TB_COUNT

    FROM USER_TABLES

   WHERE TABLE_NAME = TB_NAME;

  IF (TB_COUNT > 0) THEN

    EXECUTE IMMEDIATE 'drop table ' || TB_NAME;

  END IF;


  EXECUTE IMMEDIATE 'create table ' || TB_NAME ||

                    ' nologging as select * from v$logmnr_contents where 1=2';


  EXECUTE IMMEDIATE 'insert /*+append */ into ' || TB_NAME ||

                    ' select * from v$logmnr_contents';



                      EXECUTE IMMEDIATE 'commit';


  DBMS_LOGMNR.END_LOGMNR;

END;

alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

alter session set nls_timestamp_format='yyyy-mm-dd hh24:mi:ss';

exec export_data;