How to use LOGMNR to make log mining reasonably and minimize the impact on production library

Oracle LOGMNR How to use this tool here is not detailed, you can view the official documents, online documents are a lot of, find it yourself. I'm going to go straight to the dry.

--Create an Oracle directory

SELECT * from Dba_directories;

Create directory Archivelog_dir as '/archivelog1/temp_archivelog/';

--grant read, write on directory archivelog_dir to Zhanghui;

--Archive Log Path Information table (Tmp_archive_log), mainly to save the path of the log to be mined

--Note: To add a status flag for this table, 0 is not mined, 1 is mined

CREATE TABLE Tmp_archive_log as

Select name, 0 flag from V$archived_log

where First_time>=to_date (' 2012-12-25 13:55:00 ', ' yyyy-mm-dd hh24:mi:ss ')

and First_time<to_date (' 2012-12-25 15:05:00 ', ' yyyy-mm-dd hh24:mi:ss ');

--Create a table for the contents of the mining log, and select the fields to extract according to your specific needs

CREATE TABLE Zhanghui.logmnr_contents

As

Select Timestamp,log_id,seg_owner,seg_name,table_name,username,operation,sql_redo,sql_undo from V$logmnr_contents where 1=2;

--Batch processing mining log, only a few archive log manual processing Fortunately, if you want to analyze hundreds, but also hand-made, it is silly, so the way to do things, 2 minutes 1.5G archive, analysis two hours, how to play?

--Note: Because the log content is stored in memory, the operation session exits is lost, so here a single log mining, and then save the data to a table, completed after commit. In order to avoid the impact of the memory of the DB, the impact of performance, production library operations must ensure that no impact on the business of the situation to carry out various operations.

$ sqlplus/as SYSDBA

create procedure proc_logmnr_batchis  vcur                       sys_refcursor;   V_SQL_DIRARCLOG&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;VARCHAR2 (&NBSP;&NBSP;V_); SQL_ARCLOG&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;VARCHAR2 (2000); begin  open vcur for select name from zhanghui.tmp_archive_log  where flag=0;  loop    fetch vcur       into v_sql_dirarclog;    exit when vcur%notfound;  v_sql_arclog:= ' begin sys.dbms_logmnr.add_logfile  (logfilename=> ' | | v_sql_dirarclog| | ", OPTIONS=&GT;SYS.DBMS_LOGMNR. NEW); end; ';   execute immediate v_sql_arclog;  begin sys.dbms_logmnr.start_logMNR (options =>dbms_logmnr.dict_from_online_catalog);  end;    insert into  /*+ append */ zhanghui.logmnr_contents     select timestamp ,log_id,seg_owner,seg_name,table_name,username,operation,sql_redo,sql_undo from      v$logmnr_contents    --where table_name= ' <TABLE_NAME> '  and  operation= ' DELETE ';  begin sys.dbms_logmnr.end_logmnr; end;  update  zhanghui.tmp_archive_log set flag = 1 where name= v_sql_dirarclog;   commit;  end loop;    --ran out of clockwork SMS, call SMS interface     close  vCur; exception  when others then    rollback;    -- Error A clockwork SMS, call SMS Interface     --dbms_output.put_line (SQLERRM); end proc_logmnr_batch;

--Using the operating system to invoke stored procedure scripts

$cat proc_logmnr_batch.sh

#!/bin/bashsqlplus/as sysdba <<eofset newpage 0set linesize set pagesize 0spool proc_logmnr_batch . txt;exec Proc_logmnr_batch;spool Offquit; Eof

--Give Execute permission

$chmod +x proc_logmnr_batch.sh

--Background call execution

$nohup./proc_logmnr_batch.sh &

OK, wait to receive the processing completed SMS, log in to the database to view.

How to use LOGMNR to make log mining reasonably and minimize the impact on production library