-
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>
I'm stmontgo and I approve of this message
-
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.
Last edited by Axr2; 05-03-2004 at 08:05 PM.
-
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
I'm stmontgo and I approve of this message
-
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..
-
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
I'm stmontgo and I approve of this message
-
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');
Last edited by Axr2; 05-04-2004 at 01:21 PM.
-
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
I'm stmontgo and I approve of this message
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|