tags:

views:

108

answers:

1
+1  Q: 

Logminer help

Hi all,

I need help on executing archived log files in the database, instead of inserting each of the logs in the execute statement below one by one is there a way i can include all the logs at once in one single statement in a script?

EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => '/u02/arch/1_50662_581101112.arc' ,OPTIONS => DBMS_LOGMNR.NEW); EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => '/u02/arch/1_50706_581101112.arc'

I need to mine over a thousand logs for a report and including them one by one is not feasible.

Kindly help out.

Regards.

A: 

Use SQL and sqlplus to generate the command to a spool file

spool c:\temp\log.sql

    select 'EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME =>'''||name||'''||,OPTIONS =>   DBMS_LOGMNR.NEW);' 
from  v$archived_log;

spool off

then execute the spool file in sqlplus

start c:\temp\log.sql

EDIT

Sometimes it's easier to use an anonymous PL/SQL block:

declare
begin
    for c in (select name from v$archived_log where name is not null)
    loop    
      EXECUTE IMMEDIATE 'begin DBMS_LOGMNR.ADD_LOGFILE(:1); end;' 
        USING c.name;    
    end loop;
end;
/
Oliver Michels
i need to spool over 1000 logs how do i then start including logfilename for each? i mean i need a single statement to automate an pick all 1000 archivelogs at once
You can select the 1000 log filenames from a datadictionary view. e.g. v$archived_log. Then select the column name from that view and generate all commands to a spool file. That's what the above code does....
Oliver Michels
Hi,Thanks a lot for the hint, how do i then populate v$logmnr_contents viewwith the 1000 log filenames i have selected in my script.what statement will insert into the view and where?Kindly help
#!/bin/kshecho " Type the STARTTIME(DD-MM-YYYY HH24:MI:SS): "read STARTTIMEecho "Type User ENDTIME(DD-MM-YYYY HH24:MI:SS): "read ENDTIMEecho $STARTTIMEsqlplus "/as sysdba" << EOFselect 'EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME =>'''||name||'''||,OPTIONS => DBMS_LOGMNR.NEW);EXECUTE DBMS_LOGMNR.START_LOGMNR();set lines 100set pages 10col USERNAME format a10col SQL_REDO format a10col SQL_UNDO format a10select username,sql_redo,sql_undo FROM v\$logmnr_contents;EXITEOF
help out on the last two post below how do i modify it to run succesfeully , i actually want to automate logminer process using this shell script and i need to extract the archived logs and populate the v$logmnr_contents view to give me a text output.kindly help out
i am getting this error when i run your script above:select 'EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME =>'''||name||'''||,OPTIONS => DBMS_LOGMNR.NEW);' from v$archived_log;ORA-00923: FROM keyword not found where expected
Please try the statement in sqlplus (from MS-Windows) first, to be sure that the literal escaping is correct.
Oliver Michels
Hi Oliver,Thanks oliver it worked, how do i now populate the v$logmnr_contents view with the 1000 archived logs i have picked with this statement?.Kindly help out
try to use an anonymous block, as in the edit of my question
Oliver Michels
Thanks oliver i am very grateful to you.Best regards
Thanks for your help, i want to find out why if i issue the following statement select * from v$logmnr_contents where username = 'AYO' and seg_owner not in ('SYS','SYSTEM'), the output i get is the transaction the user did and other transactions which do not include the username but are refrenced to the user.how do i exempt the others with no username, and get only the record for the transaction i did?
Please take a look at http://www.oracle.com/technology/oramag/oracle/05-jul/o45dba.html
Oliver Michels
Thanks oliver,do you have any idea on sysdate -1 error even though the logs exist and the sysdate is correctEXECUTE DBMS_LOGMNR.START_LOGMNR( STARTTIME => SYSDATE - 1, ENDTIME => SYSDATE, OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG + DBMS_LOGMNR.CONTINUOUS_MINE + DBMS_LOGMNR.COMMITTED_DATA_ONLY + DBMS_LOGMNR.PRINT_PRETTY_SQL);Error:* ERROR at line 1: ORA-01291: missing logfile ORA-06512: at "SYS.DBMS_LOGMNR", line 58 ORA-06512: at line 1
ORA-01291 states that the logfile for your timeperiod you are trying to query is not present. Also take in mind, that SYSDATE refers to 'now' down to exactly 'this second'. Exactly now!! Logminer uses archived logfiles, not logfiles which are still open and being written to!
Oliver Michels
Thanks for the reply,But SYSDATE - 1 meams the previous day and archivelogs for the previous day have already been wriitten to file,
Please see here 'EXECUTE DBMS_LOGMNR.START_LOGMNR( STARTTIME => SYSDATE - 1, ENDTIME => SYSDATE'endtime is set to SYSDATE!!
Oliver Michels
even if i set the endtime to SYSDATE - 1 , i still get this error:*ERROR at line 1:ORA-01292: no log file has been specified for the current LogMiner sessionORA-06512: at "SYS.DBMS_LOGMNR", line 58ORA-06512: at line 1
and all logs file have been specified
maybe it's an internal bug in logminer, consider to open a TAR in metalink
Oliver Michels