This discussion is archived
4 Replies Latest reply: May 7, 2012 3:01 PM by Mark Malakanov (user11181920) RSS

Querying V$LOGMNR_CONTENTS for Audit user activities

hitgon Expert
Currently Being Moderated
Hi Oracle Gurus,

Database : Oracle10g R2 10.2.0.4
RAC: 2 node cluster database
Operating system is Sun Solaris SPARC

I have the following queries related to V$LOGMNR_CONTENTS.

result of V$LOGMNR_CONTENTS provide the audit for current redo logs contents and active query of any user

We want to keep audit all the user activities in one table which is generated by redo logs by using Log Miner in already setup the Oracle Streaming Environment.

Is there any table or dictionary where oracle stored the history contents of V$LOGMNR_CONTENTS so we can refer that data later and provide the report to client

Regards
Hitgon
  • 1. Re: Querying V$LOGMNR_CONTENTS for Audit user activities
    Mark Malakanov (user11181920) Expert
    Currently Being Moderated
    result of V$LOGMNR_CONTENTS provide the audit for current redo logs contents and active query of any user
    Athough Logminer can be used for investigations it is not for audit. Oracle has Audit facility embedded into RDBMS.

    V$LOGMNR_CONTENTS does not provide audit.
    V$LOGMNR_CONTENTS does not show active query of any user.

    It reconstructs Redo and Undo SQLs from changes done by user. For each and every changed row.
    For example, user deleted 10000000000 rows by one DELETE statement. LM will show 10000000000 separate delete statements, for each deleted row.
    We want to keep audit all the user activities in one table which is generated by redo logs by using Log Miner in already setup the Oracle Streaming Environment.
    Then be prepared to triplicate Logs volume.
    N bytes will be generated by all user changes.
    Plus N bytes will be generated by inserting these changes into your table.
    Plus N bytes will be generated by deleting obsolete changes from your table, if you not going to keep them forever.
    If you will use Streams for it, add there some more bytes for queue tables.

    Also be prepared to performance degradation caused by Streams.
    Is there any table or dictionary where oracle stored the history contents of V$LOGMNR_CONTENTS so we can refer that data later and provide the report to client.
    No.

    Again. you better consider Audit facility for your purposes.

    Edited by: user11181920 on May 4, 2012 10:54 AM
  • 2. Re: Querying V$LOGMNR_CONTENTS for Audit user activities
    hitgon Expert
    Currently Being Moderated
    please help for how to used the v$logmnr_contents for audit purpose

    can we able to store the result of v$logmnr_contents inside the other table for audit purpose?

    v$logmnr_contents refer the current redo log and return the result so I want to do something that we constantly check the v$logmnr_contents and store it some other table for later audit report


    Can we do like this ?
    1     create table my_logmnr_contents as
    2     
    3     select * from v$logmnr_contents where sql_redo like '%CDEENTCDE%'
    4     
    5     and timestamp between to_date('04/02/2011 17:58', 'dd/mm/yyyy hh24:mi')
    6     and to_date('04/02/2011 17:59', 'dd/mm/yyyy hh24:mi');
    7     
    8     select timestamp, sql_redo, sql_undo from my_logmnr_contents;

    Please share your suggestions

    Edited by: hitgon on May 7, 2012 3:35 PM
  • 3. Re: Querying V$LOGMNR_CONTENTS for Audit user activities
    EdStevens Guru
    Currently Being Moderated
    hitgon wrote:
    please help for how to used the v$logmnr_contents for audit purpose

    can we able to store the result of v$logmnr_contents inside the other table for audit purpose?

    v$logmnr_contents refer the current redo log and return the result so I want to do something that we constantly check the v$logmnr_contents and store it some other table for later audit report


    Can we do like this ?
    1     create table my_logmnr_contents as
    2     
    3     select * from v$logmnr_contents where sql_redo like '%CDEENTCDE%'
    4     
    5     and timestamp between to_date('04/02/2011 17:58', 'dd/mm/yyyy hh24:mi')
    6     and to_date('04/02/2011 17:59', 'dd/mm/yyyy hh24:mi');
    7     
    8     select timestamp, sql_redo, sql_undo from my_logmnr_contents;

    Please share your suggestions

    Edited by: hitgon on May 7, 2012 3:35 PM
    Did you read the previous reply?

    Logminer is NOT an audit tool. It is simply a utility to allow one to read the redo (and archived redo) logs. Do you understand what is in a redo log? Do you understand what is NOT in a redo log?

    Please explain why you cannot use oracle's built in AUDIT capability to achive your business goals?
  • 4. Re: Querying V$LOGMNR_CONTENTS for Audit user activities
    Mark Malakanov (user11181920) Expert
    Currently Being Moderated
    Can we do like this ?
    create table my_logmnr_contents as
    select * from v$logmnr_contents where sql_redo like '%CDEENTCDE%'
    and timestamp between to_date('04/02/2011 17:58', 'dd/mm/yyyy hh24:mi')
    and to_date('04/02/2011 17:59', 'dd/mm/yyyy hh24:mi');
    select timestamp, sql_redo, sql_undo from my_logmnr_contents;
    Sure you can!

    you can create a job that calls stor proc

    create procedure PERSIST_LM as
    last_SCN number;
    begin
    select max(SCN) into last_SCN from my_logmnr_contents;
    delete from my_logmnr_contents where SCN=last_SCN; -- delete partially persisted scn
    dbms_logmnr.start_logmnr(startscn=>last_SCN, options=dbms_logmnr.CONTINUOUS_MINE+dbms_logmnr.DICT_FROM_ONLINE_CATALOG);
    for LM in (select * from v$logmnr_contents where segment_owner in ('THIS','THAT') and whatever)
    loop
    insert into my_logmnr_contents values (LM.SCN,...columns you need);
    if ( mod(LM.SCN,10)=0) then commit; end if;
    end loop;
    commit;
    end;


    But, again, you better check AUDIT option.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points