username and session_info blank in v$logmnr_contents
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: username and session_info blank in v$logmnr_contents

  1. #1
    Join Date
    Aug 2002
    Location
    Atlanta
    Posts
    1,187

    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

  2. #2
    Join Date
    Oct 2002
    Posts
    807
    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 09:05 PM.

  3. #3
    Join Date
    Aug 2002
    Location
    Atlanta
    Posts
    1,187
    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

  4. #4
    Join Date
    Oct 2002
    Posts
    807
    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..

  5. #5
    Join Date
    Aug 2002
    Location
    Atlanta
    Posts
    1,187
    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

  6. #6
    Join Date
    Oct 2002
    Posts
    807
    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 02:21 PM.

  7. #7
    Join Date
    Aug 2002
    Location
    Atlanta
    Posts
    1,187
    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
  •  


Click Here to Expand Forum to Full Width