Need help locating correct trace file.

[afa...@phaedo.com]

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
afa...@phaedo.com

Sent via Deja.com
http://www.deja.com/

[David Fitzjarrell]

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

[afa...@my-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...@nnrp1.deja.com>,

[Kevin Brand]

Try getting your oracle<SID> process pid from v$process based on your
current session. The PID you get here is the pid that the trace file name
is generated with.

-Kevin

<afa...@phaedo.com> wrote in message news:949ut9$pkq$1...@nnrp1.deja.com...

[Kevin Brand]

I'm not aware of a way to get sid and serial# from a session, but you can
get username and match that with something in v$session like so...

Assume you log in as a special user given privileges to backup the control
file to trace and that username is CTRLBKUP...

something like this should do the trick...

alter database backup controlfile to trace;

<skip var defs >
BEGIN
dbms_output.enable(100000);

select name into p_name from v$database;

select spid into p_pid
from v$process
where addr = ( select paddr from v$session where username = 'CTRLBKUP' );

dbms_output.put_line('ora_'||p_pid||'_'||p_name||'.trc' );

END
/

Assuming that you would never have more than one CTRLBKUP session running OR
that you find a way to get the sid and serial# from v$session for your
current session more reliably, this code snippett should output the file
name your're looking for.

-Kevin

"Kevin Brand" <kevin....@tel.gte.com> wrote in message
news:94aagm$771$1...@news.gte.com...

[Kevin Brand]

You can get your current sid from v$mystat, then use that instead of
username.

[tgphe...@my-deja.com]

In article <949ut9$pkq$1...@nnrp1.deja.com>,

afa...@phaedo.com wrote:
> 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.

I've been using the following method for a couple of years now, and
have never had a failure. It isn't foolproof, but it has worked
perfectly so far. I search for a trace file that (1) was written in the
past 24 hours (maybe you can do better than this with a little effort),
and (2) contains the string "CONTROLFILE". My script runs the following
two lines:

cd $ADMIN/prod/udump
grep CONTROLFILE `find . -mtime -1 -print` |cut -f 1 -d : >/tmp/$$

The "find" locates all fairly newly-written files, the "grep" locates
all occurrences of CONTROLFILE in those files, and the "cut" extracts
the filename and writes it to a temporary file. Now I have the name of
the file, and can do whatever I want with it. (If the grep finds more
than one match, you'll have problems.)

For now, I simply email the trace file to myself, by doing the
following:

TRACE=`cat /tmp/$$`
mailx -s "Oracle trace file" mys...@domain.net <$TRACE

If you get a foolproof way, let us know.

[David Fitzjarrell]

The final 'solution' presented did not rely upon the previously listed
presumptions and should, if implemented, provide the requisite file.
The restriction to the 10 most recently generated trace files should
isolate the correct file and return only one result from 'grep'. The
listing can be restricted even further by altering the parameter to
the 'head' command.

[Martin Haltmayer]

At Solaris 2.6 8.1.6 I use the following script reliably. You have to run this
as SYS or internal. You get the trace file name by "select name from my_trace;":

create or replace view my_trace as
select distinct p.spid
, f.value || '/' || lower (i.instance_name) || '_ora_' || to_char (p.spid,
'fm999999') || '.trc' as name
from v$process p
, v$session s
, v$instance i
, v$parameter f
where 1 = 1
and p.addr = s.paddr
and s.audsid = userenv ('sessionid')
and 'user_dump_dest' = f.name
/

create public synonym my_trace for my_trace;

grant select on my_trace to public;

[Richard Sutherland]

You have missed the obvious. Try

BACKUP CONTROL FILE TO '/my_dir/my_control_file.sql' ;

Richard Sutherland

<afa...@phaedo.com> wrote in message news:949ut9$pkq$1...@nnrp1.deja.com...

[Howard J. Rogers]

"Richard Sutherland" <rvsuth...@yahoo.com> wrote in message
news:t6ke0f6...@corp.supernews.com...

> You have missed the obvious. Try
>
> BACKUP CONTROL FILE TO '/my_dir/my_control_file.sql' ;
>
> Richard Sutherland

I just tested this. It took all of 15 seconds, and my first hunch was
right: it doesn't work. the command to backup to a fully-qualified path and
filename results in a *binary* copy of the Control File, not a trace
script -regardless of what you attempt to call the thing.

Sticking a ".sql" at the end does not a sql script make.

Did you not test this before posting?

HJR

[Richard Sutherland]

Howard is correct.

Richard Sutherland

Howard J. Rogers <howa...@www.com> wrote in message
news:3a6a...@news.iprimus.com.au...

[Richard Sutherland]

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
rvsuth...@yahoo.com

<afa...@my-deja.com> wrote in message news:94a7tu$2i6$1...@nnrp1.deja.com...