Re: Oracle 9.2 Log Miner Scripts?

"Howard J. Rogers" <hjr_at_dizwell.com> wrote in message news:3fb53dcc$0$13968$afc38c87_at_news.optusnet.com.au...
>
> "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
>

Thank you, Mr Rogers, I appreciate your constructive feedback.

I have tried this syntax you suggest, which I've culled from the docs and from Metalink examples.

However, I believe this example you gave will extract the logs into the SYSTEM tablespace, which I do not want. I can not seem to get the tablespace spec to work with all the other syntax and so far I can't find examples that pull it all together. I may open a TAR on Metalink and ask the experts.

Also, I am eager to get the tablespace spec working because I want to run the Log Miner against archived log files for a given time period, and the volume of logged info I cope with is huge.

The SAP R/3 database I am most interested in using LogMiner on has 8 ReDo groups and each is 1GB is size so, you see, I don't want to flood the SYSTEM tablespace. The database itself generates a 1GB archive log about every 15 minutes now, so you see I need a low-impact LogMiner scripted process that can scale up very well into a VLDB such as an SAP R/3 implementation. Occasionally I am asked things like "what was going on 8 hours ago?"

If you have actually run your syntax example can you also work in how the the tablespace spec can be used to extract the redo logs into a user tablespace? I can not devine that info from the docs.

Many thanks for your time so far and I appreciate anything additional you may have the time to say about this.

Charlie

>
>
>
Received on Sun Nov 16 2003 - 11:17:38 CST