这里只记录下有条件的情况如何使用
BEGIN
dbms_logmnr.start_logmnr(
dictfilename => '/u01/arch/logminer_dict.ora',
starttime => to_date('20090808 22:00:00','yyyymmdd hh24:mi:ss'),
endtime => to_date('20090808 23:40:00','yyyymmdd hh24:mi:ss')
);
END;
/
logminer中v$logmnr_contents实际也是根据v$session等视图对出来的，跟自己做的方法一样，所以想要在session_info字段中有client_info这个里有IP，就必须使用登录触发器，
登录触发器：
CREATE OR REPLACE TRIGGER on_logon_trigger AFTER logon ON DATABASEBEGIN dbms_application_info.set_client_info(sys_context('userenv', 'ip_address'));END;
在那里添加IP，这样v$session中就有IP了，项目没问题是因为使用了登录触发器，也这么做了，所以有IP地址，但是session_info有时会有unknown的情况，原因查找可能如下：
Problem:
========
You have just built the LogMiner dictionary and started LogMiner. You query
V$LOGMNR_CONTENTS expecting to see the USERNAME and SESSION_INFO for some
particular redo or undo operation. However, the USERNAME field is NULL.
This is NOT a recursive operation, and the USERNAME column is expected to
contain a non-null value.
Solution:
=========
This can result from your database parameter settings and also from the method
you are using to mine redo logs using LogMiner.
1. Ensure that database was in minimum supplemental logging at the time that
the redo information was created:
SQL> SELECT name, supplemental_log_data_min FROM v$database;
NAME SUPPLEME
------------------------------ --------
M10202WA YES
2. Ensure that all archive redo logs containing the necessary redo
information have been added to the LogMiner session.
3. Ensure that the COMPATIBLE initialization parameter is set to 8.1.0
or higher.
SQL> show parameter compatible
NAME TYPE VALUE
------------------------------------ ----------- ----------
compatible string 10.2.0.2.0
4. For Oracle8i and Oracle9i only: ensure that the TRANSACTION_AUDITING
initialization parameter is set to TRUE (default).
SQL> show parameter transaction_autiting
NAME TYPE VALUE
------------------------------------ ----------- ----------
transaction_auditing boolean TRUE
Explanation:
============
1. If minimum supplemental logging was not active at the time when the
redo records were created, then LogMiner won't be able to obtain all
the required information. The Oracle10g Database Utilities manual mentions:
"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."
So, we have to Enable supplemental logging by using a command like this then
the redolog that will be generated will contain the information:
SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA
2. LogMiner does not store USERNAME and SESSION_INFO for all redo records.
In order to have all necessary information available, LogMiner must have
the appropriate logs added to the session. If the transaction being
tracked spans multiple logs and you have not specified all archived redo
logs, then LogMiner won't be able to obtain the value for the USERNAME.
Also, if the log containing the initial user connection was not added,
the SESSSION_INFO won't be available to LogMiner.
3. LogMiner was first available in Oracle8i (8.1.x). If COMPATIBLE is set
below 8.1.0 you will not have access to its full functionality.
4. In Oracle9i and below, TRANSACTION_AUDITING is set to TRUE by default.
It generates a redo record containing the user logon name, user name,
session id, and some operating system (including client) information.
When transaction_auditing is set to FALSE, this redo record is not written
and the user information is not available to LogMiner.
References:
===========
B14215-01 - Oracle Database Utilities 10g Release 2 (10.2)
Bug:1959969 "LOGMNR SHOWING NULL VALUES FOR USERNAME COLUMN IN
V$LOGMNR_CONTENTS
Bug:2998128 "NO INFORMATION ON V$LOGMNR_CONTENTS.SESSION_INFO FOR SOME ENT"
posted on
