Re: Oracle 9.2 Log Miner Scripts?

"Charles Davis" <cdavis10717_at_comcast.net> wrote in message news:WfidnaSymrxepSiiRVn-sA_at_comcast.com...
>
> "Howard J. Rogers" <hjr_at_dizwell.com> wrote in message
> news:3fb43db2$0$13680$afc38c87_at_news.optusnet.com.au...
> >
> > "Charles Davis" <cdavis10717_at_comcast.net> wrote in message
> > news:pf6dnUSjWvr_qymiRVn-vw_at_comcast.com...
> > > So, as the poster of the original question......let me repeat the
> original
> > > question.....
> > >
> > > ...do either of you have a Log Miner script to post or not?
> > > > Senior Oracle DBA
> > >
> >
> > Right, Charles.
> >
> > I don't have a script. Sorry.
> >
> > I only got involved in this thread to correct an egregious mis-statement
> of
> > facts by another poster. Sorry your question got hijacked in the
process.
> > Sometimes it happens.
> >
> > The individual steps are not difficult, but I think you said that when
you
> > first posted. It's putting them altogether that's going to be difficult,
> and
> > particularly depending on what you have in mind for this process. For a
> > start, will you be capturing the logs regularly (at, say, every log
> switch)
> > or is it just an ad hoc thing when a problem arises?
> >
> > Why not post back with a little more detail about you envisage using
this
> > process? And now that the question has been re-asked, maybe someone who
> > actually knows something about Log Miner will reply this time.
> >
> > Regards
> > HJR
> >
> >
> >
> Thank you, HJR.
>
> I want to run Log Miner on an ad joc basis. I support a large SAP R/3
> application and occasionally is 'misbehaves'. We don't know what the
source
> code is doing, nor what any of thousands of end-users might be doing.
>
> I would like to be able to see what's going on during the event, and I
> thought if I could get all ReDo records into a tablespace via LogMiner I
> could then use any of several canned queries on it to help identify the
> offending user/process.
>
> I don't think anyone who reads this news group uses LogMiner, but I'm
> willing to give this a try.
>
> I welcome and appreciate any constructive help from anyone.
>
> Thank you.
>
> Charles

Well, a script would have to do the following:

exec dbms_logmnr.add_logfile ( -
logfilename => '/path/filename', -
options => dbms_logmnr.new)

then

exec dbms_logmnr.add_logfile ( -
logfilename => '/path/filename', -
options => dbms_logmnr.addfile)

and so on until all online logs are listed for analysis.

Next, you'd have to initiate the analysis, using the online dictionary, and probably restricting the analysis time too (I don't know how big your logs are, and there's a limit to how big v$logmnr_contents can get).

exec dbms_logmnr.start_logmnr ( -
starttime => 'sometimestamp', endtime => 'sometimestamp' options => dbms_logmnr.dict_from_online_catalog)

Now that then populates the v$logmnr_contents view, and to create a table from that would be just

create table XXXX tablespace zzz as select * from v$logmnr_contents;

How you'd put all that into a script, I haven't really thought about. One thing you can definitely do is to replace some of the options with the following:

exec dbms_logmnr.add_logfile ( -
logfilename => '&full_log_name', options=> &option)

When run in SQL Plus, at least, those '&' cause you to be prompted to supply the necessary information.

Oh, and you'll need exec dbms_logmnr.end_logmnr() to clear v$logmnr_contents ready for your next analysis session.

Regards
HJR Received on Fri Nov 14 2003 - 14:40:36 CST