oracle版本：Oracle Database 10g Enterprise Edition Release 10.2.0.1.0

最近在分析日志的时候，发现没有DML语句，只能分析出DDL语句。后来发现没有打开补充日志功能

是否打开补充日志，可以查看 v$database

SELECT "SUPPLEMENTAL_LOG_DATA_MIN",
"SUPPLEMENTAL_LOG_DATA_PK",
"SUPPLEMENTAL_LOG_DATA_UI",
"SUPPLEMENTAL_LOG_DATA_FK",
"SUPPLEMENTAL_LOG_DATA_ALL"
FROM v$database

SUPPLEMENTAL_LOG_DATA_MIN SUPPLEMENTAL_LOG_DATA_PK SUPPLEMENTAL_LOG_DATA_UI SUPPLEMENTAL_LOG_DATA_FK SUPPLEMENTAL_LOG_DATA_ALL
YES NO NO NO NO

完全可以参考 oracle 官方文档，来了解logminer的用途、使用帮助

Oracle® Database
Utilities
10g Release 2 (10.2)
B14215-01

1、使用logminer分析日志，必须打开补充日志功能
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA; 这个是最小的补充日志选项

Note: You must enable supplemental logging prior to generating log files that will be analyzed by LogMiner.
When you enable supplemental logging, additional information is recorded in the redo stream that is needed to make the information
in the redo log files useful to you. Therefore, at the very least, you must enable minimal supplemental logging, as the following SQL statement shows:
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
To determine whether supplemental logging is enabled, query the V$DATABASE view, as the following SQL statement shows:
SELECT SUPPLEMENTAL_LOG_DATA_MIN FROM V$DATABASE;

Redo log files are generally used for instance recovery and media recovery. The data
needed for such operations is automatically recorded in the redo log files. However, a
redo-based application may require that additional columns be logged in the redo log
files. The process of logging these additional columns is called supplemental logging.

By default, Oracle Database does not provide any supplemental logging, which means
that by default LogMiner is not usable. Therefore, you must enable at least minimal
supplemental logging prior to generating log files which will be analyzed by
LogMiner.

There are two types of database-level supplemental logging: minimal supplemental
logging and identification key logging, as described in the following sections. Minimal
supplemental logging does not impose significant overhead on the database
generating the redo log files. However, enabling database-wide identification key
logging can impose overhead on the database generating the redo log files. Oracle
recommends that you at least enable minimal supplemental logging for LogMiner.

2、可以分析在线日志和归档日志

logminer使用的前提
打开增量日志功能。否则只能分析DDL语句，不能分析DML语句
9.2版本以后是默认关闭的，如果把dml操作记录在redo log中，必须打开最小的补充日志功能
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

logminer的用途

1、应用错误导致的数据丢失或错误，
2、辅助性能分析，可以看到哪些table操作比较多，insert update 等
3、进行事后审计，跟踪哪些数据被操作，最好能给定时间区间，否则分析量将非常大

分析库、源库、分析字典、待分析的日志

要求
分析库和源库硬件相同
分析库的字符集是源库的超集
分析库的版本要高于或等于源库
分析库和源库可以在一起或独立开

dictionary的来源

Using the Online Catalog
EXECUTE DBMS_LOGMNR.START_LOGMNR(OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG);

Extracting a LogMiner Dictionary to the Redo Log Files
EXECUTE DBMS_LOGMNR_D.BUILD(OPTIONS=> DBMS_LOGMNR_D.STORE_IN_REDO_LOGS);
SELECT NAME FROM V$ARCHIVED_LOG WHERE DICTIONARY_BEGIN='YES';
SELECT NAME FROM V$ARCHIVED_LOG WHERE DICTIONARY_END='YES';

Extracting the LogMiner Dictionary to a Flat File

EXECUTE DBMS_LOGMNR_D.BUILD('dictionary.ora', '/oracle/database/', DBMS_LOGMNR_D.STORE_IN_FLAT_FILE);

UTL_FILE_DIR = /oracle/database
SQL> show parameter utl

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
create_stored_outlines string
utl_file_dir string /oracle/database
SQL>

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

分析结果可以放入表中，便于后续分析
create table log_result as select * from V$LOGMNR_CONTENTS;

==下面的例子分析对表test1的操作=========================

select * from v$logfile

execute sys.DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => '+DG_DATA/orcl/redo01.log',OPTIONS => sys.DBMS_LOGMNR.NEW);
execute SYS.DBMS_LOGMNR.START_LOGMNR(OPTIONS => sys.DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG + sys.DBMS_LOGMNR.COMMITTED_DATA_ONLY);
SELECT SQL_REDO,sql_undo FROM V$LOGMNR_CONTENTS WHERE lower(SQL_reDO) like '%test1%';
===========================

根据时间分析

ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';
EXECUTE DBMS_LOGMNR.START_LOGMNR( -
DICTFILENAME => '/oracle/database/dictionary.ora', -
STARTTIME => '01-Jan-1998 08:30:00', -
ENDTIME => '01-Jan-1998 08:45:00'-
OPTIONS => DBMS_LOGMNR.CONTINUOUS_MINE);

根据scn分析
EXECUTE DBMS_LOGMNR.START_LOGMNR(-
STARTSCN => 621047, -
ENDSCN => 625695, -
OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG + -
DBMS_LOGMNR.CONTINUOUS_MINE);

9.2版本以后是默认关闭的，如果把dml操作记录在redo log中，必须打开
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

分下面2个层次
Database-Level Supplemental Logging
Table-Level Supplemental Logging

In Oracle Database release 9.0.1, minimal supplemental
logging was the default behavior in LogMiner. In release 9.2 and
later, the default is no supplemental logging. Supplemental logging
must be specifically enabled.

Disabling Database-Level Supplemental Logging

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (UNIQUE) COLUMNS;
ALTER DATABASE DROP SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;
ALTER DATABASE DROP SUPPLEMENTAL LOG DATA (UNIQUE) COLUMNS;
ALTER DATABASE DROP SUPPLEMENTAL LOG DATA;

打开了哪些日志功能，可以通过查询 v$database来获得

Table-Level Supplemental Logging

User-Defined Supplemental Log Groups

--无条件记录下面的3个字段
ALTER TABLE HR.EMPLOYEES
ADD SUPPLEMENTAL LOG GROUP emp_parttime (EMPLOYEE_ID, LAST_NAME,
DEPARTMENT_ID) ALWAYS;

--下面的只在更新3个字段之一的时候，才记录log
ALTER TABLE HR.EMPLOYEES
ADD SUPPLEMENTAL LOG GROUP emp_fulltime (EMPLOYEE_ID, LAST_NAME,
DEPARTMENT_ID);

--下面的是排除一个字段
ALTER TABLE HR.EMPLOYEES
ADD SUPPLEMENTAL LOG GROUP emp_parttime(
DEPARTMENT_ID NO LOG, EMPLOYEE_ID);

总结：

1、logminer用途不是很广，但要清楚使用logminer的功能有哪些，限制有哪些

2、把logminer的分析结果放入普通表中便于留存和后续分析