Process state dump needed to trouble shoot an ORA-600

Per Oracle Support, we need to generate a process state dump when an ORA-600 [17074] error occurs. I put together the commands in a small script called pdump.sql to do this task. This error has already crashed our production database several times.

The plan is once the ORA-600 hits, find the first trace file that causes it. At the top of the file , we should see the 'LOCK OWNERS' section, looks like:

LOCK OWNERS:
lock user session count mode flags
-------- -------- -------- ----- ---- ------------------------
5311a3900 510a1c370 510a1c370 1 N PNS/[08]
531372200 512a23348 512a23348 1 N [00]
5233801c0 513a18980 513a18980 1 N PNS/[08]
5311c3440 51b148020 51b148020 1 N PNS/[08]
531280c00 50fa23be8 50fa23be8 1 N PNS/[08]
:
53135ef00 50ea46a08 50ea46a08 1 N PNS/[08]

We note down the session address column(third one).

Then we login as sysdba, to do:

SQL> @pdump 510a1c370
SQL> @pdump 512a23348

so on and so forth for the first few.

Here is a test output for the pdump script:

sys@TESTDB> @pdump 3b4333248
sys@TESTDB> select p.pid
2 from v$session s, v$process p
3 where s.paddr = p.addr
4 and s.saddr in (upper(lpad('&1',16,'0')))
5 ;
old 4: and s.saddr in (upper(lpad('&1',16,'0')))
new 4: and s.saddr in (upper(lpad('3b4333248',16,'0')))

PID
----------
70

sys@TESTDB>
sys@TESTDB> oradebug setorapid &OPID
Unix process pid: 13818, image: oracle@testserv2(TNS V1-V3)
sys@TESTDB> oradebug unlimit
Statement processed.
sys@TESTDB> oradebug dump processstate 266
Statement processed.
sys@TESTDB> oradebug tracefile_name
/apps/opt/oracle/admin/testdb/udump/testdb_ora_13818.trc
sys@TESTDB> set echo off