Column USERNAME and SESSION_INFO are UNKNOWN or NULL in V$LOGMNR_CONTENTS [ID 110301.1]
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"
Some Columns Values Are Changing to UNKNOWN or NULL When Querying V$LOGMNR_CONTENTS
Applies to:
Oracle Server – Enterprise Edition – Version: 10.2.0.4 to 11.2.0.2 – Release: 10.2 to 11.2
Information in this document applies to any platform.
Symptoms
If executing DBMS_LOGMNR.START_LOGMNR several times in same session (i.e. without calling DBMS_LOGMNR.END_LOGMNR in between) and with same parameters, the session or transaction audit columns such as TX_NAME, USERNAME, OS_USERNAME, MACHINE_NAME, AUDIT_SESSIONID, SESSION#, SERIAL# or SESSION_INFO from V$LOGMNR_CONTENTS will have NULL or UNKNOWN values on second and subsequent queries but not on first query.
For example:
Result from FIRST call to DBMS_LOGMNR.START_LOGMNR (correct):
SQL> select username, count(*)
2 from v$logmnr_contents c
3 group by username
4 order by username;
USERNAME COUNT(*)
—————————— ———-
TC1 23911
Result from SECOND call to DBMS_LOGMNR.START_LOGMNR (wrong):
USERNAME COUNT(*)
—————————— ———-
UNKNOWN 23911
DBMS_LOGMNR.START_LOGMNR has to use STARTSCN and DDL_DICT_TRACKING
options for this behavior.
Cause
Issue was investigated in Bug 10129774 – USERNAME VALUE IN V$LOGMNR_CONTENTS CHANGES TO UNKNOWN
fixed in 12.1 database release.
Solution
Apply one-off patch available under Patch 10129774. If a such patch does not exist for your database version and platform please open a backport request with Support team.
References
BUG:10129774 – USERNAME VALUE IN V$LOGMNR_CONTENTS CHANGES TO UNKNOWN
Hdr: 10129774 10.2.0.4 RDBMS 10.2.0.4 LOGMINER PRODID-5 PORTID-912 Abstract: USERNAME VALUE IN V$LOGMNR_CONTENTS CHANGES TO UNKNOWN PROBLEM: -------- When using DBMS_LOGMNR.START_LOGMNR twice in the same Logminer session this gives inconsistent results for username when querying v$logmnr_contents. The records belonging to an username (GEADBA) are placed under UNKNOWN username after second execution of DBMS_LOGMNR.START_LOGMNR: begin sys.DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => 'E:\TESTCASE\LOGMINER-TEST\ARC00849_0710874523.001', OPTIONS => DBMS_LOGMNR.NEW); sys.DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => 'E:\TESTCASE\LOGMINER-TEST\ARC00850_0710874523.001'); sys.DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => 'E:\TESTCASE\LOGMINER-TEST\ARC00851_0710874523.001'); sys.DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => 'E:\TESTCASE\LOGMINER-TEST\ARC00852_0710874523.001'); sys.DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => 'E:\TESTCASE\LOGMINER-TEST\ARC00853_0710874523.001'); sys.DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => 'E:\TESTCASE\LOGMINER-TEST\ARC00854_0710874523.001'); end; / begin sys.DBMS_LOGMNR.START_LOGMNR( STARTscn => 1398442097, ENDscn => 1398487738, OPTIONS =>sys.dbms_logmnr.dict_from_redo_logs+sys.dbms_logmnr.ddl_dict_tracking+ dbms_logmnr.committed_data_only); end; / SQL> select username, count(*) 2 from v$logmnr_contents c 3 group by username 4 order by username; USERNAME COUNT(*) ------------------------------ ---------- GEADBA 40847 SYS-5288 SYSMAN 2106 UNKNOWN 1286 SQL> begin 2 sys.DBMS_LOGMNR.START_LOGMNR( 3 STARTscn => 1398442097, 4 ENDscn => 1398487738, 5 OPTIONS =>sys.dbms_logmnr.dict_from_redo_logs+sys.dbms_logmnr.ddl_ dict_tracking+ dbms_logmnr.committed_data_only); 6 end; 7 / PL/SQL procedure successfully completed. ==================================================== The count of all of the items that belonged to GEADBA have now been placed under UNKNOWN ==================================================== SQL> select username, count(*) 2 from v$logmnr_contents c 3 group by username 4 order by username; USERNAME COUNT(*) ------------------------------ ---------- SYS-5288 SYSMAN 2106 UNKNOWN 42133 Somewhere along the line of re-running the START_LOGMNR, it changed the username from GEADBA and moved them all to the UNKNOWN user. DIAGNOSTIC ANALYSIS: -------------------- Minimal supplemental logging was enabled in database since February 2010: Mon Feb 15 16:46:19 2010 alter database add supplemental log data Mon Feb 15 16:46:19 2010 SUPLOG: Updated supplemental logging attributes at scn = 1253350773 SUPLOG: minimal = ON, primary key = OFF SUPLOG: unique = OFF, foreign key = OFF, all column = OFF Completed: alter database add supplemental log data Mon Feb 15 16:47:09 2010 The archived redo logs were generated on September 06, 2010. WORKAROUND: ----------- RELATED BUGS: ------------- REPRODUCIBILITY: ---------------- At customer site using the archived logs above. The customer did not noticed same behavior for other archives. The issue reproduces too in our systems in: 10.2.0.4, 10.2.0.5, 11.1.0.7, 11.2.0.1 databases on Windows 32-bit plaform. TEST CASE: ---------- Copy the archived redo logs in a directory. In testcase.sql replace the value: "d:\gea\" with the directory name where archived redo logs were placed. From sqlplus connected as sys as sysdba execute: STACK TRACE: ------------ SUPPORTING INFORMATION: ----------------------- 24 HOUR CONTACT INFORMATION FOR P1 BUGS: ---------------------------------------- DIAL-IN INFORMATION: -------------------- IMPACT DATE: ------------ RELEASE NOTES: ]]V$LOGMNR_CONTENTS could show extraneous information in the session and ]]transaction audit columns on first execution of a query after starting the ]]session. REDISCOVERY INFORMATION: If a client queries v$logmnr_contents using a StartScn and DDL_DICT_TRACKING options and they select session or transaction audit columns such as TX_NAME, USERNAME, OS_USERNAME, MACHINE_NAME, AUDIT_SESSIONID, SESSION#, SERIAL# or SESSION_INFO and the values are seen to be NULL or UNKNOWN on second and subsequent queries but not on first query then this bug is being hit. WORKAROUND: None
© 2008 – 2011, www.oracledatabase12g.com. 版权所有.文章允许转载,但必须以链接方式注明源地址,否则追究法律责任.
相关文章 | Related posts:
最新评论