?
Solved

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

Posted on 2003-12-10
3
Medium Priority
?
2,038 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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
Suggested Courses

777 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