Learn How To Use LogMiner(Practice)

Steps in a Typical LogMiner Session:

Enable Supplemental Logging
Extract a LogMiner Dictionary (unless you plan to use the online catalog)
Specify Redo Log Files for Analysis
Start LogMiner
Query V$LOGMNR_CONTENTS
End the LogMiner Session

Enable Supplemental Logging

SQL> SELECT SUPPLEMENTAL_LOG_DATA_MIN FROM V$DATABASE;
SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

　　If you wanna to Use the Online Catalog

　　Step1:Load the redo logs for analyse:

begin
  DBMS_LOGMNR.ADD_LOGFILE( LOGFILENAME => '/u02/oradata/ann01/redo01.log',OPTIONS => dbms_logmnr.NEW);
  DBMS_LOGMNR.ADD_LOGFILE(LogFileName => '/u02/oradata/ann01/redo02.log',Options => dbms_logmnr.ADDFILE);
end;

　　Step2:Use the dictionary in the redo logs and start to mine:

begin
  DBMS_LOGMNR.start_logmnr(Options => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG);
end;

　　Then you can find the information in the V$LOGMNR_CONTENTS which you wanted.

　　If you wanna extracting a logminer dictionary to the redo log files

　　Step1:Extract the Dictionary:

begin
  DBMS_LOGMNR_D.BUILD(OPTIONS=> DBMS_LOGMNR_D.STORE_IN_REDO_LOGS);
end;

　　After you excute this statements,it will Extract the Dictionary into the current redo log and archive it to the archive logs dest,then you can load the archive logs to analyse.

　　Step2:Load the archive logs:

begin
  DBMS_LOGMNR.ADD_LOGFILE( LOGFILENAME => '/u02/oradata/ann01/ archive_log /1_11_798135898.dbf ',OPTIONS => dbms_logmnr.NEW);
  DBMS_LOGMNR.ADD_LOGFILE( LogFileName => '/u02/oradata/ann01/archive_log/1_11_798135897.dbf ',Options => dbms_logmnr.ADDFILE);
end;

　　Notice:You must put the archivelog which include the Dictionary on the first place with the “new” option,then LogMiner can use the dictionary to translate internal object identifiers and datatypes to object names and external data formats.

　　Step3:Start to analyse the archived log files:

begin
  DBMS_LOGMNR.START_LOGMNR(Options => dbms_logmnr.DICT_FROM_REDO_LOGS);
end;

　　The following list is a summary of LogMiner settings that you can specify with the OPTIONS parameter to DBMS_LOGMNR.START_LOGMNR:

DICT_FROM_ONLINE_CATALOG
CONTINUOUS_MINE
COMMITTED_DATA_ONLY
SKIP_CORRUPTION
NO_SQL_DELIMITER
PRINT_PRETTY_SQL
NO_ROWID_IN_STMT
DDL_DICT_TRACKING

　　Also,you can specify a time or SCN range in automatic mode,LogMiner will use the database control file to find and add redo log files that satisfy your specified time or SCN range to the LogMiner redo log file list.

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
EXECUTE DBMS_LOGMNR.START_LOGMNR(STARTTIME => '2012-11-21 08:30:00',ENDTIME => '2012-11-21 08:45:00',
　　OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG + DBMS_LOGMNR.CONTINUOUS_MINE);

Extracting the LogMiner Dictionary to a Flat File

　　Like chapter "Extracting a LogMiner Dictionary to the RedoLog File",so,balabalabala```````

Query V$LOGMNR_CONTENTS

SELECT scn,timestamp,start_timestamp,commit_timestamp,username,os_username,data_obj#,sql_redo,sql_undo 
FROM V$LOGMNR_CONTENTS 
WHERE DATA_OBJ#=77033
ORDER BY TIMESTAMP DESC;

　　There you can find the useful infomation which you interested in the v$logmnr_contents view.(with the right "where" clause).List of views:

V$LOGMNR_CONTENTS,V$LOGMNR_DICTIONARY,V$LOGMNR_LOGS,V$LOGMNR_PARAMS.

End the LogMiner Session

begin
  DBMS_LOGMNR.end_logmnr;
end;

　　If you excute some ddl statment,you must end the logminer session and start a new session to analyse the log files with a new Dictionary.

心有猛虎，细嗅蔷薇。