username and session_info blank in v$logmnr_contents

Using logminer and these fields are not populated, any ideas....thanks

steve

testcase.......................

SQL> EXEC DBMS_LOGMNR_D.BUILD
('dictionary.dbf','C:\oracle\oradata\DALLAS\logminer',options
=>dbms_logmnr_d.store_in_flat_file);
PL/SQL procedure successfully completed.
SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;
System altered.
/* From a seperate system issued
SQL> UPDATE emp SET location = 'Dallas' WHERE emp#=3;
1 row updated.
SQL> COMMIT;
Commit complete.
SQL> show user
USER is "SYSADM"
SQL>
*/
SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;
System altered.
SQL> SELECT name FROM v$archived_log
2 WHERE sequence# = (SELECT max(sequence#) FROM v$archived_log);
NAME
--------------------------------------------------------------------------------
----------------------------------------
C:\ORACLE\ORADATA\DALLAS\ARCHIVE_LOGS\ARC00047.001
SQL> EXEC
DBMS_LOGMNR.ADD_LOGFILE('C:\oracle\oradata\DALLAS\archive_logs\arc00047.001',DBM
S_LOGMNR.NEW);
PL/SQL procedure successfully completed.
SQL> select low_scn,next_scn from v$logmnr_logs;
LOW_SCN NEXT_SCN
---------- ----------
2617061 2617073
SQL> EXECUTE DBMS_LOGMNR.START_LOGMNR( -
> DICTFILENAME => 'c:\oracle\oradata\dallas\logminer\dictionary.dbf', startSCN
=> 2617061, endSCN => 2617073);
PL/SQL procedure successfully completed.
SQL> select username, session_info,sql_redo from v$logmnr_contents where
seg_name ='EMP';
USERNAME
------------------------------
SESSION_INFO
--------------------------------------------------------------------------------
----------------------------------------
SQL_REDO
--------------------------------------------------------------------------------
----------------------------------------
update "SYSADM"."EMP" set "LOCATION" = 'Dallas' where "LOCATION" = 'Dallas' and
ROWID = 'AAAHg5AABAAABTCAAC';
SQL> column session_info format a10
SQL> column session_info format a15
SQL> column username format a10
SQL> column sql_redo format a40
SQL> select username, session_info,sql_redo from v$logmnr_contents where
Text continued in next action...


23-APR-04 15:24:57 GMT

Text continued from previous action...

seg_name ='EMP';
USERNAME SESSION_INFO SQL_REDO
---------- --------------- ----------------------------------------
update "SYSADM"."EMP" set "LOCATION" = '
Dallas' where "LOCATION" = 'Dallas' and
ROWID = 'AAAHg5AABAAABTCAAC';
SQL> show parameter transaction
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
transaction_auditing boolean TRUE
transactions integer 187
transactions_per_rollback_segment integer 5
SQL
SQL> show parameter compat
NAME TYPE VALUE
------------------------------------ ----------- ----------------------------
compatible string 9.2.0.0.0
plsql_v2_compatibility boolean FALSE
SQL>

Yeah, this is weird. I seem to be able to pull up username,session_info when I add an online redolog file to logmnr. But once I use an archivelog file, the information (username,session_info) appears as null. I'm testing on a 9.2.0.1.

Quote:

---

Originally posted by Axr2
Yeah, this is weird. I seem to be able to pull up username,session_info when I add an online redolog file to logmnr. But once I use an archivelog file, the information (username,session_info) appears as null. I'm testing on a 9.2.0.1.

---

I believe this was *gasp* user error. Once I recreated the DD objects with catalog/catproc the view worked as expected.

Thanks

Steve

Ok, I'm confused now. I don't understand.

User error? Can you explain? Are you saying there was a problem with the v$logmnr_contents view..and once you reran the catalog and catproc scripts, everything was back to normal?

Can't be coincidence that I'm running into an identical error! I tried the below using both OEM and running the procedures manually..and ended up with similar results. I was able to pullup session_info and username when using the redologs, but not with archivelogs..

Quote:

---

Originally posted by Axr2
Ok, I'm confused now. I don't understand.

User error? Can you explain? Are you saying there was a problem with the v$logmnr_contents view..and once you reran the catalog and catproc scripts, everything was back to normal?

Can't be coincidence that I'm running into an identical error! I tried the below using both OEM and running the procedures manually..and ended up with similar results. I was able to pullup session_info and username when using the redologs, but not with archivelogs..

---

Yeah I often test on my crash and burn db so sometimes that enviornment is not the stable-ist of creatures. Anyway, could be that my db was messed up or it could be that we also upgraded to 92050
and I do see bug 2909726 is related to v$logmnr_contents view being empty. I'd be happy to try your test case on a 92050 system if you like

Nevermind, it seems to be working this morning! AFAIK I wasn't doing anything differently yesterday. I'll give this a shot using OEM later in the day to see if it behaves any different.

Got a few logmnr questions of my own : 1) Are "drop tables" any easier to track/decipher through logminer in 9i? Drop tables (DDL) in 8i were a pain to track down. I know I ought to RTFM..but thought I'll ask anyway :).

2) Is there any way to estimate the size of the dictfile?

3) How often does one typically build the logminer dictionary in a production environment? Once a week or s'thing?


============

SQL> conn sys as sysdba
Enter password:
Connected.
SQL> alter system switch logfile;

System altered.
SQL> conn anand
Enter password:
Connected.
SQL> desc sys.anand_junk
Name Null? Type
----------------------------------------- -------- ------------------------

A VARCHAR2(50)

SQL> select count(*) from sys.anand_junk;

COUNT(*)
----------
2
SQL> insert into sys.anand_junk values ('BLAH BLAH');

1 row created.

SQL> insert into sys.anand_junk values ('BLAH2 BLAH2');

1 row created.

SQL> commit;

Commit complete.

SQL>
SQL> conn sys as sysdba
Enter password:
Connected.

SQL> select group#,thread#,sequence# from v$log where status='CURRENT';

GROUP# THREAD# SEQUENCE#
---------- ---------- ----------
1 1 189
SQL> select member,type from v$logfile where group#=1;

MEMBER
-------------------------------------------------------------------------------
TYPE
-------
D:\ORACLE\ORADATA\ARTEST\REDO01.LOG
ONLINE

SQL> exec dbms_logmnr.start_logmnr(DICTFILENAME=>'D:\oracle\admin\ARTEST\logmine
r\LogMinerDictionary.ora');

PL/SQL procedure successfully completed.

SQL> select username, session_info,sql_redo from v$logmnr_contents where
2 seg_name ='ANAND_JUNK';

USERNAME
------------------------------
SESSION_INFO
--------------------------------------------------------------------------------

SQL_REDO
--------------------------------------------------------------------------------

ANAND
login_username=ANAND client_info= OS_username=Administrator Machine_name=US001\APOLLO
insert into "SYS"."ANAND_JUNK"("A") values ('BLAH BLAH');

ANAND
login_username=ANAND client_info= OS_username=Administrator Machine_name=US001\APOLLO
insert into "SYS"."ANAND_JUNK"("A") values ('BLAH2 BLAH2');


SQL> alter system archive log current;

System altered.

SQL> exit
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 – Production


SQL> SELECT name FROM v$archived_log
2 WHERE sequence# = (SELECT max(sequence#) FROM v$archived_log);

NAME
--------------------------------------------------------------------------------

D:\ORACLE\ORA9201\RDBMS\ARTEST\ARCHIVE\ARC00189.001

SQL> exec dbms_logmnr.add_logfile('D:\ORACLE\ORA9201\RDBMS\ARTEST\ARCHIVE\ARC00189.001',DBMS_LOGMNR.NEW);

PL/SQL procedure successfully completed.

SQL> exec dbms_logmnr.start_logmnr(DICTFILENAME=>'D:\oracle\admin\ARTEST\logminer\LogMinerDictionary.ora');

PL/SQL procedure successfully completed.

SQL> select username, session_info,sql_redo from v$logmnr_contents where seg_name ='ANAND_JUNK';

USERNAME
------------------------------
SESSION_INFO
--------------------------------------------------------------------------------

SQL_REDO
--------------------------------------------------------------------------------

ANAND
login_username=ANAND client_info= OS_username=Administrator Machine_name=US001\APOLLO
insert into "SYS"."ANAND_JUNK"("A") values ('BLAH BLAH');

ANAND
login_username=ANAND client_info= OS_username=Administrator Machine_name=US001\APOLLO
insert into "SYS"."ANAND_JUNK"("A") values ('BLAH2 BLAH2');

Quote:

---

Originally posted by Axr2
Nevermind, it seems to be working this morning! AFAIK I wasn't doing anything differently yesterday. I'll give this a shot using OEM later in the day to see if it behaves any different.

Got a few logmnr questions of my own : 1) Are "drop tables" any easier to track/decipher through logminer in 9i? Drop tables (DDL) in 8i were a pain to track down. I know I ought to RTFM..but thought I'll ask anyway :).

2) Is there any way to estimate the size of the dictfile?

3) How often does one typically build the logminer dictionary in a production environment? Once a week or s'thing?

---

1. I think DDL is supposed to be a feature of 9i so yeah it should be easier to track


2 & 3 I have no idea, I'm interested in what other would to say as well. I was just trying to get a valid test case together