Solved

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

Posted on 2003-12-10
3
2,013 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
  • 2
3 Comments
 
LVL 7

Accepted Solution

by:
yoren earned 250 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
select query - oracle 16 91
Export BLOB data from Oracle 10g 4 33
Bash Script to Analyze Oracle Schemas 11 80
grouping on time windows 6 41
This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

937 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now