topic Re: Oracle trace in Operating System - HP-UX

Oracle trace

KapilRaj — Fri, 13 Oct 2006 13:12:07 GMT

Guys,

I have two database processes [ LOCAL=NO ] on the production database server. lsof shows they are from the apps server. But the apps does not know what that is :( same reply from the DBAs. These processes are consuming 20 % of CPU each. They are running for last 3 days !!.
There is also a file in "bdump" directory called , ${ORACLE_SID}_ora_.trc Does it mean something ? It looks like a coredump file though the information inside is plain text !.

Can I kill this processes ?.

Regards,

Kaps

Re: Oracle trace

spex — Fri, 13 Oct 2006 14:10:15 GMT

Hi Kaps,

To see the underlying SQL statements for these processes, substitute your PIDs in the following statement:

SELECT sql_text
FROM v$sqlarea sa, v$session s, v$process p
WHERE sa.address = s.sql_address
AND s.paddr = p.addr
AND p.spid = '';

"[LOCAL=NO]" means a process is not one of Oracle's background processes. Therefore, it should be safe to kill them. Just realize that this may cause unexpected results on your application server.

The preferred order for killing an Oracle process:

1) Close the session from the application server.

2) SQL> alter system kill session 'sid,serial#';

3) SQL> alter system kill session 'sid,serial#' immediate;

4) # kill

5) Usual kill switch progression (-1, -2, -3, -11, -9).

Trace files in bdump are commonplace, and may not be related to the issue at hand. These files are ASCII data.

PCS

Re: Oracle trace

KapilRaj — Fri, 13 Oct 2006 14:29:24 GMT

Having a .trc file in the bdump directory does not mean an *error ? Is that what you mean ?

Regards,

Kaps

Re: Oracle trace

Jonathan Fife — Fri, 13 Oct 2006 14:35:07 GMT

If you run:

select s.process from v$session s, v$process p
where s.paddr = p.addr and p.spid =

you should get something like 1180:1023.

The first number in that pair should be the process ID on the app server. Go to that server and see what that process is. If it is a windows box use taskman or the following command:

tasklist -FI "PID eq 1180" (or whatever your pid is)

HTH

Re: Oracle trace

spex — Fri, 13 Oct 2006 14:48:52 GMT

Of course a trace file means there was an error. However, the file you see may represent a different error--unrelated to the errant application server processes. Look at the timestamp of the file to determine when it was created. Better yet, look at the contents.

In general, trace files just happen. As long as their number is small and you review them regularly, you should be fine.

Re: Oracle trace

KapilRaj — Fri, 13 Oct 2006 14:51:38 GMT

I did lsof to find the remote pid. It is a java process on the app server.

Re: Oracle trace

Yogeeraj_1 — Sat, 14 Oct 2006 04:08:46 GMT

hi Kaps,

Allow me to add that in case, this happens too often you may wish to log a Service Request with Oracle.

The trace file will also allow you to identify the culprit user. If you query your alert.log, you can also get the exact Oralce error message.

hope this helps too!

kind regards
yogeeraj