Solved

The Oracle  dead lock in Near real time.

Posted on 2014-03-02
21
494 Views
Last Modified: 2014-03-12
dear all,

How to find out the deadlock (as detail as possible!) happened in Oracle 10g and 11g?

tools can be suggest ..
'
the oracle will automatically kill the victim? what is the victim choice condition ?

what is the process to email the result out as the alert so that all deadlock information can be send to developers and DBA?

information should contain:
1) Time of the deadlock
2) Application the own that process.
3) Host the process comes from.
4) the login the process use.
5) which one is the Victim get killed.

pleaes briefing explain the deadlock process oracle will handle.
0
Comment
Question by:marrowyung
  • 11
  • 8
21 Comments
 
LVL 36

Accepted Solution

by:
Geert Gruwez earned 500 total points
ID: 39899721
i just use the alert.log of the oracle instance
search for DEADLOCK DETECTED
it will indicate what trace file the deadlock info is in

from that trace file, look for the corresponding start time
and look forward till you DEADLOCK DETECTED
then add everything to the mail until line PROCESS STATE

everything between those 2 points is all the info oracle has about the deadlock
it should contain the deadlock graph, the 2 blockers and the statements they were running

> for very detailed info, check below the process state line

off course, if the application ignores/doesn't catch the error and doesn't report this
you'll need to automate this by periodically checking the alert log
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39899752
"i just use the alert.log of the oracle instance"

where is the alert.log ?
any example ?

check the v$spparameter view?
what is the full statement( shouldn't be select * from v$spparameter) for checking out all infromatoin you talk about.

"from that trace file, look for the corresponding start time
and look forward till you DEADLOCK DETECTED "

should it be find the deadlock detected first, then see the start time or we will have a lot of thing to scan through.

"and look forward till you DEADLOCK DETECTED
then add everything to the mail until line PROCESS STATE

everything between those 2 points is all the info oracle has about the deadlock
it should contain the deadlock graph, the 2 blockers and the statements they were running"

you mean everything in between DEADLOCK DETECTED  and PROCESS STATE ?

" periodically checking the alert log "

so we can only check the alert log and no where else?

and how to checking it reqularily, what kind of tools you will use ?
0
 
LVL 36

Expert Comment

by:Geert Gruwez
ID: 39899770
you aren't an oracle  dba ?
your oracle dba will probably have things setup to monitor the alert log like enterprise manager ... or his own tools
the alert.log file is used by oracle to log all actions happening on the oracle like checpoints, switch logfiles, deadlocks, shutdown, startup, etc ...

the app will get ORA-0600 error when deadlock occurs
0
 
LVL 36

Expert Comment

by:Geert Gruwez
ID: 39899779
in oracle you could setup a procedure to read the alert.log as an external table
, analyze it for the deadlocks
then if found open that specific trace file indicated by the deadlock as another external table and fetch all the info about the deadlock

see this blog:
http://www.adp-gmbh.ch/ora/concepts/alert_log.html
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39899817
"you aren't an oracle  dba ?"

I just start to learn Oracle from MS SQL background,

"your oracle dba will probably have things setup to monitor the alert log like enterprise manager ... or his own tools"

and what is the tools ? Toad for Oracle ? I use Toad a lot !

"the alert.log file is used by oracle to log all actions happening on the oracle like checpoints, switch logfiles, deadlocks, shutdown, startup, etc ..."

Thanks and what is the path of that? can't see it from UI ? like OEM ?

So right now, only alert log can help ? MSSQL can offer other thing like deadlock graphic when we do the SQL profiler!

so when we try to simulate the deadlock again in real time, keep refreshing the alert log until we see that ?
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39899822
"then if found open that specific trace file indicated by the deadlock as another external table and fetch all the info about the deadlock"

say this again please, dion't understand.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39899842
What is the procedure to read alert.log ?
0
 
LVL 36

Expert Comment

by:Geert Gruwez
ID: 39900132
this should give the directory/location of the alert log on the oracle host:
select value from v$parameter where name = 'background_dump_dest';

this query will give the name of the alert log file:
select 'alert_'||instance_name||'.log' from v$instance;

if you have a single instance oracle

btw, about that oracle monitoring tool of ours ...
it also mails all the deadlock info when it happens, as described above :)
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39900687
"btw, about that oracle monitoring tool of ours ...
it also mails all the deadlock info when it happens, as described above :)

I am sorry, what is the tools ?
0
 
LVL 23

Expert Comment

by:David
ID: 39917962
Hi marrowyung, sorry about your rough introduction to Oracle.  You're needing to learn concepts and tools and you're not getting the training first.

There are gobs of sources, but at some point you need to get familiar with Oracle's free online material, such as a Two-day Introduction to DBA.

More to the point, I would have you turn to your site's paid, professional support team at Oracle Support Services.  This way, one of their staff can help diagnose your specific issue quickly.  Your IT manager should be able to provide you with the account and privileges.
0
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.

 
LVL 1

Author Comment

by:marrowyung
ID: 39919471
very sales feel.
0
 
LVL 1

Author Closing Comment

by:marrowyung
ID: 39919474
thansk anyway.
0
 
LVL 36

Expert Comment

by:Geert Gruwez
ID: 39919809
my bad, i thought you already new the oracle environment from the developer point of view

if you are new to oracle and you don't have access to the oracle host/server then my help will be of not much use

you need the person/team/vendor who setup the oracle database
or an oracle dba
without them it will be harder to find the deadlocks
> it's possible to figure out via the code in the apps but it's way easier if someone gives the actual statements giving the deadlock

btw, for oracle support contact you also need the oracle dba/vendor ...
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39919851
"my bad, i thought you already new the oracle environment from the developer point of view"

what makes you say this ? I gave you score already!

I am new but I use the script to check the oracle and a lot of thing need to learn.

"you need the person/team/vendor who setup the oracle database
or an oracle dba"

this will not setup by us, in other country and we manage from overseas.

"it's possible to figure out via the code in the apps but it's way easier if someone gives the actual statements giving the deadlock"

this is what i mean, what is hte procedure to read the alert.log and trace file if I use SQL developer 4.0.x ?
0
 
LVL 36

Expert Comment

by:Geert Gruwez
ID: 39919867
points ? the idea is to help you, the points are a nice thing, but only secondary

you can't do it with sql developer. that's the problem
you need access to the server hosting/running the oracle database

this query gives the filename of the alert file (assuming windows server)

select '\\'||replace(p.value, ':', '$')||'\alert_'||i.instance_name||'.log' filename
from v$parameter p, v$instance i
where p.name = 'background_dump_dest';

this will not setup by us ...
if they setup for you, then ask them to also setup a mailing system whenever a deadlock is raised in the alert log.

we manage from overseas.
> do you have dba access ? and admin access to the server ?
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39920105
"points ? the idea is to help you, the points are a nice thing, but only secondary

I think you are worrying and think I don't give you score .


"you can't do it with sql developer. that's the problem
you need access to the server hosting/running the oracle database"

oh, so I have to login to that oracle box locally ?

"this will not setup by us ... "

who is us you are referring to ?

"if they setup for you, then ask them to also setup a mailing system whenever a deadlock is raised in the alert log."

What if they ask what they have to do in order to archive that?

"do you have dba access ? and admin access to the server ? "

At this moment no !
0
 
LVL 36

Expert Comment

by:Geert Gruwez
ID: 39920126
> oh, so I have to login to that oracle box locally ?
yes, or have remote access to the alert log file

> who is us you are referring to ?
the ones you were referring to in this sentence
> this will not setup by us, in other country and we manage from overseas.

> What if they ask what they have to do in order to achieve that?
if they don't have their own method, refer them to this link :
http://www.adp-gmbh.ch/ora/concepts/alert_log.html
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39922465
"yes, or have remote access to the alert log file"

MS SQL can do this from the console however !! but I agree that the RAC is real good while MS SQL is quickly getting to this direction !


thanks anyway.
0
 
LVL 36

Expert Comment

by:Geert Gruwez
ID: 39922920
lol,
>> MS SQL can do this from the console however
MS SQL still isn't capable of getting me a coffee with a simple query
but ... neither is oracle ... or any other database for that matter

there is a lot of difference between both
I stopped working with mssql in 2003 and had to adapt to oracle and all the better stuff it could do ... :)
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39922986
"MS SQL still isn't capable of getting me a coffee with a simple query "

what you don't like about MS SQL, tell me

"
there is a lot of difference between both"

yeah, I belive, but the way to find out problem is not much different in this case.

"I stopped working with mssql in 2003 and had to adapt to oracle and all the better stuff it could do ... :) "

which one is the latest vesrion fo SQL you use ? SQL 2000 ?

starting from SLQ 2012, it is acting/learning from Oracle's RAC but not there yet ! RAC and ASM is quite unique for Oracle.

But MS SQL 2012 has other method to handle it, like SQL route.

SQL 2014 has some kind of secret weapon to kill oracle on performance.
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.

Join & Write a Comment

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…
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
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
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

708 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

12 Experts available now in Live!

Get 1:1 Help Now