Oracle日志挖掘解决方案
Oracle日志挖掘解决方案
准备工作
cd c:\oracle\product\10.2.0\db_1\BIN
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;
执行一次就可以在SYS. EXPORT_DATA_LOG查询结果
执行语句
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;
这个过程是只分析上次执行的时间段到这次执行时候的语句。