Re: Need help locating correct trace file.

From: Richard Sutherland <rvsutherland_at_yahoo.com>
Date: Sun, 21 Jan 2001 16:25:26 -0500
Message-ID: <t6mkjcccndid19@corp.supernews.com>

The following is from Oracle's web site:

To derive the tracefile name on Unix:

----------------- Cut below this line -------------------------
REM NAME: tracename.sql
REM       Generates trace file name on Unix corresponding to
REM       user session.

declare
spid1 varchar2(9);
tracefile varchar2(40);
begin
SELECT spid INTO spid1
FROM v$process a, v$session b
WHERE a.addr=b.paddr
AND b.audsid = userenv('SESSIONID') ;
tracefile:='ora_' || spid1 ||'.trc';
dbms_output.put_line(tracefile);
end;
/

----------------- Cut above this line -------------------------

.
HTH
Richard Sutherland
rvsutherland_at_yahoo.com

<afairch_at_my-deja.com> wrote in message news:94a7tu$2i6$1_at_nnrp1.deja.com...
> Thanks for the response, unfortunately your one of your presumptions
> does not match my situation. This is not a one-shot occurrence, it is
> being scripted to handle a recurring process, and as such I don't feel
> 100% comfortable with the solution you offer. (No offense, it is
> similar to but an improvement on what I am currently doing) There are a
> couple of problems: 1)As you stated you cannot absolutely count on the
> pid value always being higher than the last time this was done as the
> pid numbers are recycled - the machine currently stays up for very long
> periods of time between reboots and the hope is that this will remain
> true, and 2) since this will be a recurring process more than 1 file
> will match the search criteria when grep'd (I know, the reasons are
> very interdependent). Failing the ability to track down the process
> that actually writes the file and using the pid of that process, do you
> have any other suggestions?
>
>
> In article <94a5v8$o9$1_at_nnrp1.deja.com>,
> as part of the ongoing saga, David Fitzjarrell <oratune_at_aol.com> wrote:
> > In our last gripping episode afairch_at_phaedo.com wrote:
> > > I need help with a script that I am writing to move and rename a
> > > database. The script currently uses sqlplus to do a "backup control
> > > file to trace" and then uses the trace file that was created.
> > >
> > > The trace file that is created is named something lide ora_PID.trc,
> > > where PID is the process id of the process that created the file. I
> > > originally thought that would be the sqlplus pid, but it appears
 that
 a
> > > new process is spawned to write the trace file. Does anyone know a
> > > simple and more importantly reliable way to determine what process
 that
> > > is. My fallback plan is to make some educated guesses based on the
 date
> > > and time stamp on the file, but that does leave more room for error
> > > than I would like.
> > >
> > > some (hopefully) helpful information:
> > > Oracle version: 8.1.6
> > > OS: Solaris 2.6 (SunOS 5.6)
> > >
> > > Any help would be greatly appreciated.
> > >
> > > Thanks,
> > >
> > > Anthony
> > > afairch_at_phaedo.com
> > >
> > > Sent via Deja.com
> > > http://www.deja.com/
> > >
> >
> > I shall make some presumptions and base my response upon them:
> >
> > 1) This is a 'one-shot' occurrence
> > 2) There is only one trace file of importance
> >
> > From these presumptions I would suggest you utilise 'grep' to
 determine
> > which trace file you need. You can 'grep' the trace files for text
> > unique to the 'backup controlfile to trace' output and presuming you
> > have but one trace file that meets this criteria 'grep' will return,
> > with a bit of output massaging, the file name of the requisite trace
> > file. You will need to utilise 'awk' as well, to process the output
> > from 'grep' and isolate the file name. An example follows:
> >
> > TRCFILE=`grep 'CONTROLFILE' *.trc | awk -F":" '{print $1}'`
> >
> > The variable TRCFILE is now set with the filename of the trace file
> > generated from the 'alter database backup controlfile to trace;'
> > command. Again I state that I presume only one trace file will match
> > the 'grep' criteria. Should this not be the case the above can be
> > modified to, hopefully, achieve the desired result:
> >
> > TRCFILE=`grep 'CONTROLFILE' *.trc | awk -F":" '{print $1}' | sort -u |
> > head -1`
> >
> > This makes another presumption that the embedded PID value will
> > increase for each run of the script -- a presumption that cannot truly
> > be relied upon should the UNIX server be up for a considerable period
> > of time (the process id's cycle and can therefore be smaller for later
> > runs). A third option is to utilise your second thought of date/time
> > stamps on the files:
> >
> > FLIST=`ls -ltr *.trc | awk '{print $9}' | head -10`
> > TRCFILE=`grep 'CONTROLFILE' $FLIST | awk -F":" '{print $1}'`
> >
> > Limiting the available file list to the 10 newest files should isolate
> > the trace file in question. Passing this list to 'grep' should
 provide
> > only one file name, the currently created trace file.
> >
> > There may be other solutions, such as Perl scripts and such, but I
> > wanted to give you a starting place. I hope I have done that to your
> > satisfaction.
> >
> > --
> > David Fitzjarrell
> > Oracle Certified DBA
> >
> > Sent via Deja.com
> > http://www.deja.com/
> >
>
>
> Sent via Deja.com
> http://www.deja.com/
Received on Sun Jan 21 2001 - 15:25:26 CST