Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Oracle 9i , how do I find the deadlock object in the trace file?

Posted on 2003-12-10
3
Medium Priority
?
2,046 Views
Last Modified: 2013-12-11
I have many UNIX servers with many Oracle instances. When a certain application is run on the database server, it creates a deadlock, which in turn hits a nasty Oracle 9.2.3 bug which corrupts Oracle Home and takes out all the Oracle instances on that server. I have lost 20 Oracle instances on 4 UNIX (HP-UX 9000 11.0 & 11.11) ( AIX 5.1) database servers. I need to find out what ojbect is causing the deadlock, so that I can have the developers fix the issue. How do I read the trace file, to find the deadlock table or object?  Following is a portion of one of the trace files.

 With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.3.0 - Production
ORACLE_HOME = /oracle/app/oracle/product/920
System name:    HP-UX
Node name:      dbsrv2
Release:        B.11.00
Version:        U
Machine:        9000/800
Instance name: lsu9
Redo thread mounted by this instance: 1
Oracle process number: 12
Unix process pid: 16414, image: oracle@dbsrv2 (TNS V1-V3)
*** 2003-11-08 14:57:33.744
*** SESSION ID:(18.437) 2003-11-08 14:57:33.700
DEADLOCK DETECTED
Current SQL statement for this session:
delete from dependency$ where d_obj#=:1
The following deadlock is not an ORACLE error. It is a
deadlock due to user error in the design of an application
or from issuing incorrect ad-hoc SQL. The following
information may aid in determining the deadlock:
Deadlock graph:
                       ---------Blocker(s)--------  ---------Waiter(s)---------
Resource Name          process session holds waits  process session holds waits
TX-000a001a-000075a0        12      18     X             12      18           X
session 18: DID 0001-000C-00000002      session 18: DID 0001-000C-00000002
Rows waited on:
Session 18: no row
Information on the OTHER waiting sessions:
End of information on OTHER waiting sessions.
===================================================
PROCESS STATE
-------------
Process global information:
     process: c0000000094581f0, call: c0000000095986a8, xact: c00000000a3c7850,
curses: c0000000094bfb50, usrses: c0000000094c4540
  ----------------------------------------
  SO: c0000000094581f0, type: 2, owner: 0000000000000000, flag: INIT/-/-/0x00
  (process) Oracle pid=12, calls cur/top: c0000000095986a8/c000000009598468, fla
g: (0) -
            int error: 0, call error: 0, sess error: 0, txn error 0
  (post info) last post received: 0 0 0
              last post received-location: No post
              last process to post me: none
Standard input
0
Comment
Question by:bonniekittelson
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 7

Accepted Solution

by:
yoren earned 750 total points
ID: 9982799
Since there's no particular row that's getting locked, you may be getting deadlocks due to insufficient transaction slots. Try increasing the INITRANS storage parameter on your tables. As far as determining what table is missing, try adding the following line to your init.ora and see if you get anything more.

event='60 trace name errorstack level 10'

(You can find more on this at http://www.orafaq.com/faqdbain.htm)
0
 
LVL 7

Expert Comment

by:yoren
ID: 9982802
Correction - I meant "determining what table is deadlocking" instead of "determining what table is missing"
0
 

Author Comment

by:bonniekittelson
ID: 10012825
I will put the trace in and see if I can get any more information on this error. I will let you know.
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines

636 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question