Oracle 的LogMiner

参考文档：

Using LogMiner to Analyze Redo Log Files (oracle.com)

前几天，因为工作需要，在11g上使用logminer挖了一些日志，记录一下。今天在19c环境中试了一下，也适用于19c 

过程如下：

指定Logminer的数据字典：

EXECUTE DBMS_LOGMNR.START_LOGMNR(OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG);    ## 使用在线目录
EXECUTE DBMS_LOGMNR_D.BUILD( OPTIONS=> DBMS_LOGMNR_D.STORE_IN_REDO_LOGS);                          ## 抽取字典到redo日志文件中
EXECUTE DBMS_LOGMNR_D.BUILD('dictionary.ora', '/oracle/database/', DBMS_LOGMNR_D.STORE_IN_FLAT_FILE);  ## 抽取字典到平面文件中，需要设置UTL_FILE_DIR，

在Logminer中添加归档日志文件，也可以添加online日志文件

EXECUTE DBMS_LOGMNR.ADD_LOGFILE( LOGFILENAME => '/u01/XXX.dbf', OPTIONS => DBMS_LOGMNR.NEW);    ## 可指定ASM文件路径 
select filename from V$LOGMNR_LOGS;  ##查看添加的日志列表　

开始挖日志：

EXECUTE DBMS_LOGMNR.START_LOGMNR(OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG);
EXECUTE DBMS_LOGMNR.START_LOGMNR(OPTIONS => DBMS_LOGMNR.DICT_FROM_REDO_LOGS );
EXECUTE DBMS_LOGMNR.START_LOGMNR(DICTFILENAME =>'/oracle/database/dictionary.ora');

或者按照时间来挖日志，（摘自官方文档）

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

-- 获取挖掘结果 （或创建一个表t_logminer，可以从其他的会访问）

select * from  V$LOGMNR_CONTENTS 

-- 结束日志挖掘

EXECUTE DBMS_LOGMNR.END_LOGMNR;

参考文档：

23.2.1.1 Objects in LogMiner Configuration Files

DataMiner Configuration files have four objects: the source database, the mining database, the LogMiner dictionary, and the redo log files containing the data of interest.

• The source database is the database that produces all the redo log files that you want LogMiner to analyze.

• The mining database is the database that LogMiner uses when it performs the analysis.

• The LogMiner dictionary enables LogMiner to provide table and column names, instead of internal object IDs, when it presents the redo log data that you request.

  LogMiner uses the dictionary to translate internal object identifiers and data types to object names and external data formats. Without a dictionary, LogMiner returns internal object IDs, and presents data as binary data.

  For example, consider the following SQL statement

 

23.4.1 LogMiner Dictionary Options

LogMiner requires a dictionary to translate object IDs into object names when it returns redo data to you.

LogMiner gives you three options for supplying the dictionary:

• Using the Online Catalog

  Oracle recommends that you use this option when you will have access to the source database from which the redo log files were created and when no changes to the column definitions in the tables of interest are anticipated. This is the most efficient and easy-to-use option.

• Extracting a LogMiner Dictionary to the Redo Log Files

  Oracle recommends that you use this option when you do not expect to have access to the source database from which the redo log files were created, or if you anticipate that changes will be made to the column definitions in the tables of interest.

• Extracting the LogMiner Dictionary to a Flat File

  This option is maintained for backward compatibility with previous releases. This option does not guarantee transactional consistency. Oracle recommends that you use either the online catalog or extract the dictionary to redo log files instead.

 

The following sections provide instructions on how to specify each of the available dictionary options.

• Using the Online Catalog
  To direct LogMiner to use the dictionary currently in use for the database, specify the online catalog as your dictionary source when you start LogMiner.
• Extracting a LogMiner Dictionary to the Redo Log Files
  To extract a LogMiner dictionary to the redo log files, the database must be open and in ARCHIVELOG mode and archiving must be enabled.
• Extracting the LogMiner Dictionary to a Flat File
  When the LogMiner dictionary is in a flat file, fewer system resources are used than when it is contained in the redo log files. Oracle recommends that you regularly back up the dictionary extract to ensure correct analysis of older redo log files.

END .