My system admin. accidentally wrote over a production database file with a backup of the production database file. Will an export to /dev/null verify the file?
Thanks, Paula
Alessandro Vercelli <alever@(protected)> wrote: Sorry for the wrong trace file: here is the correct one:
*** SESSION ID:(29.650) 2006.05.10.03.58.18.000 A deadlock among DDL and parse locks is detected. This deadlock is usually due to user errors in the design of an application or from issuing a set of concurrent statements which can cause a deadlock. This should not be reported to Oracle Support. The following information may aid in finding the errors which cause the deadlock: ORA-04020 (See ORA-04020.ora-code.com): deadlock detected while trying to lock object SYSUTIL_OWNER .ADFDATASETDESCRIPTOR -- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- --- object waiting waiting blocking blocking handle session lock mode session lock mode -- ----- -- ----- -- ----- ---- -- ----- -- ----- ---- 9bd59b80 9c774d98 9e0422bc X 9c774d98 9e042e74 S -- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- --- -- ---- -- DUMP OF WAITING AND BLOCKING LOCKS -- ---- -- -- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- --- -- ---- ----- WAITING LOCK -- ---- ----- -- ---- ---- ---- ---- ---- ---- ---- -- SO: 9e0422bc, type: 23, owner: 9dfbe6b8, flag: INIT/-/-/0x00 LIBRARY OBJECT LOCK: lock=9e0422bc handle=9bd59b80 request=X call pin=0 session pin=0 user=9c774d98 session=9c774d98 count=0 flags=[00] savepoint=17051 LIBRARY OBJECT HANDLE: handle=9bd59b80 name=SYSUTIL_OWNER.ADFDATASETDESCRIPTOR hash=c41c8eb9 timestamp=06-16-2003 08:48:33 namespace=TABL/PRCD/TYPE flags=TIM/SML/[02000000] kkkk-dddd-llll=0000-050d-050d lock=S pin=S latch=3 lwt=9bd59b98[9e0422cc,9e0422cc] ltm=9bd59ba0[9bd59ba0,9bd59ba0] pwt=9bd59bb0[9bd59bb0,9bd59bb0] ptm=9bd59c08[9bd59c08,9bd59c08] ref=9bd59b88[9a25b544,9bd5b670] LIBRARY OBJECT: object=9bd22008 type=TABL flags=EXS/LOC[0005] pflags= [00] status=VALD load=0 DATA BLOCKS: data# heap pointer status pins change -- -- -- ----- -- ----- -- --- ---- -- --- 0 9ba8e9b4 9bd2214c I/P/A 0 NONE 2 9bd22198 9a251648 I/P/A 1 NONE 3 9a2553e8 9a254fc8 I/-/A 0 NONE 8 9bd2209c 9bb43f78 I/-/A 0 NONE 10 9bd220fc 9bb3ebec I/-/A 0 NONE -- ---- ----- BLOCKING LOCK -- ---- ---- -- ---- ---- ---- ---- ---- ---- ---- -- SO: 9e042e74, type: 23, owner: 9c7da4d0, flag: INIT/-/-/0x00 LIBRARY OBJECT LOCK: lock=9e042e74 handle=9bd59b80 mode=S call pin=9e036e90 session pin=0 user=9c774d98 session=9c774d98 count=2 flags=PNC/[04] savepoint=248 LIBRARY OBJECT HANDLE: handle=9bd59b80 name=SYSUTIL_OWNER.ADFDATASETDESCRIPTOR hash=c41c8eb9 timestamp=06-16-2003 08:48:33 namespace=TABL/PRCD/TYPE flags=TIM/SML/[02000000] kkkk-dddd-llll=0000-050d-050d lock=S pin=S latch=3 lwt=9bd59b98[9e0422cc,9e0422cc] ltm=9bd59ba0[9bd59ba0,9bd59ba0] pwt=9bd59bb0[9bd59bb0,9bd59bb0] ptm=9bd59c08[9bd59c08,9bd59c08] ref=9bd59b88[9a25b544,9bd5b670] LIBRARY OBJECT: object=9bd22008 type=TABL flags=EXS/LOC[0005] pflags= [00] status=VALD load=0 DATA BLOCKS: data# heap pointer status pins change -- -- -- ----- -- ----- -- --- ---- -- --- 0 9ba8e9b4 9bd2214c I/P/A 0 NONE 2 9bd22198 9a251648 I/P/A 1 NONE 3 9a2553e8 9a254fc8 I/-/A 0 NONE 8 9bd2209c 9bb43f78 I/-/A 0 NONE 10 9bd220fc 9bb3ebec I/-/A 0 NONE -- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- --- This lock request was aborted.
The indexes are periodically rebuilded.
Thanks for your help,
Alessandro
> Hi Alessandro, > > First off, this is an ORA-0060 (See ORA-0060.ora-code.com), not an ORA-4020 (See ORA-4020.ora-code.com). (ORA-60 (See ORA-60.ora-code.com) is an enqueue > deadlock, ORA-4020 (See ORA-4020.ora-code.com) is a library cache deadlock.) > > Second, it's a TX (transaction enqueue) deadlock. > > Third, the mode held by the blocker and the mode held by the waiter are > 'X' (exclusive). > > Fourth, the statement encountering the deadlock is an UPDATE. > > So, the combination of the above info tells me that this is a row-level > application deadlock. You've got two concurrent sessions, one session > updates row X and does not commit. Another session updates row Y and > does not commit. Then, the first session tries updating row Y and > starts waiting on the second session, and finally, the second session > tries to update row X and starts waiting on the first session. In this > state, the sessions would wait forever, so, Oracle detects a deadlock, > raises ORA-0060 (See ORA-0060.ora-code.com), and statement level rollback occurs. > > I don't know anything about your application, however, the cleanest > solution would be to examine the application logic, and alter as > necessary to ensure that two concurrent sessions do not attempt to > update the same set of rows. Also, another possiblility, which would > still suffer from slowdowns due to TX enqueue waits, but not deadlocks, > would be to ensure that the order that rows are updated is the same in > all the concurrent sessions. > > Hope that helps, > > -Mark > > > > > -- > Mark J. Bobak > Senior Oracle Architect > ProQuest Information & Learning > > For a successful technology, reality must take precedence over public > relations, for Nature cannot be fooled. --Richard P. Feynman, 1918-1988 > > > -- --Original Message-- -- > From: oracle-l-bounce@(protected) > [mailto:oracle-l-bounce@(protected)] On Behalf Of Alessandro Vercelli > Sent: Monday, May 08, 2006 1:34 PM > To: Oracle Freelists.org > Subject: [SPAM] Deadlock problem > Importance: Low > > Hi all, > I'm trying to solve an ora-4020 (See ora-4020.ora-code.com) (deadlock) issue; the trace file (sorry > if messed) shows: > > ksqded1: deadlock detected via did > DEADLOCK DETECTED > Current SQL statement for this session: > Update set ='' where like '%' > The following deadlock is not an ORACLE error. It is a deadlock due to
-- http://www.freelists.org/webpage/oracle-l
-- ---- ---- ---- ---- ---- ----- Talk is cheap. Use Yahoo! Messenger to make PC-to-Phone calls. Great rates starting at 1¢/min. <div>My system admin. accidentally wrote over a production database file with a backup of the production database file. Will an export to /dev/null verify the file?</div> <div> </div> <div>Thanks,</div> <div>Paula<BR> <BR><B><I>Alessandro Vercelli <alever@(protected)></I></B> wrote:</div> <BLOCKQUOTE class=replbq style="PADDING-LEFT: 5px; MARGIN-LEFT: 5px; BORDER-LEFT : #1010ff 2px solid">Sorry for the wrong trace file: here is the correct one:<BR ><BR>*** SESSION ID:(29.650) 2006.05.10.03.58.18.000<BR>A deadlock among DDL and parse locks is detected.<BR>This deadlock is usually due to user errors in<BR >the design of an application or from issuing a set<BR>of concurrent statements which can cause a deadlock.<BR>This should not be reported to Oracle Support. <BR>The following information may aid in finding<BR>the errors which cause the deadlock:<BR>ORA-04020 (See ORA-04020.ora-code.com): deadlock detected while trying to lock object SYSUTIL_OWNER.ADFDATASETDESCRIPTOR<BR>-- ---- ---- ---- ---- ---- ---- ---- -- -- ---- ---- ---<BR>object waiting waiting blocking blocking<BR>handle session lock mode session lock mode<BR>-- ----- -- ----- -- ----- ---- -- ----- -- ---- - ----<BR>9bd59b80 9c774d98 9e0422bc X 9c774d98 9e042e74 S<BR>-- ---- ---- ----- -- ---- ---- ---- ---- ---- ---- -----<BR>-- ---- -- DUMP OF WAITING AND BLOCKING LOCKS -- ---- --<BR>-- ---- ---- ---- ---- ---- ---- ---- ---- ---- -- -- ---<BR>-- ---- ----- WAITING LOCK -- ---- -----<BR>-- ---- ---- ---- ---- --- -- ---- ------<BR>SO: 9e0422bc, type: 23, owner: 9dfbe6b8, flag: INIT/-/-/0x00 <BR>LIBRARY OBJECT LOCK: lock=9e0422bc handle=9bd59b80 request=X<BR>call pin=0 session pin=0<BR>user=9c774d98 session=9c774d98 count=0 flags=[00] savepoint =17051<BR>LIBRARY OBJECT HANDLE: handle=9bd59b80<BR>name=SYSUTIL_OWNER .ADFDATASETDESCRIPTOR <BR>hash=c41c8eb9 timestamp=06-16-2003 08:48:33<BR >namespace=TABL/PRCD/TYPE flags=TIM/SML/[02000000]<BR>kkkk-dddd-llll=0000-050d-050d lock=S pin=S latch=3 <BR>lwt=9bd59b98[9e0422cc,9e0422cc] ltm=9bd59ba0[9bd59ba0,9bd59ba0]<BR>pwt =9bd59bb0[9bd59bb0,9bd59bb0] ptm=9bd59c08[9bd59c08,9bd59c08]<BR>ref=9bd59b88 [9a25b544,9bd5b670]<BR>LIBRARY OBJECT: object=9bd22008<BR>type=TABL flags=EXS /LOC[0005] pflags= [00] status=VALD load=0<BR>DATA BLOCKS:<BR>data# heap pointer status pins change<BR>-- -- -- ----- -- ----- -- --- ---- -- ---<BR>0 9ba8e9b4 9bd2214c I/P/A 0 NONE <BR>2 9bd22198 9a251648 I/P/A 1 NONE <BR>3 9a2553e8 9a254fc8 I/-/A 0 NONE <BR>8 9bd2209c 9bb43f78 I/-/A 0 NONE <BR>10 9bd220fc 9bb3ebec I/-/A 0 NONE <BR>-- ---- ----- BLOCKING LOCK -- ---- ----<BR>-- ------ -- ---- ---- ---- ---- ---- ---<BR>SO: 9e042e74, type: 23, owner: 9c7da4d0, flag : INIT/-/-/0x00<BR>LIBRARY OBJECT LOCK: lock=9e042e74 handle=9bd59b80 mode=S<BR >call pin=9e036e90 session pin=0<BR>user=9c774d98 session=9c774d98 count=2 flags =PNC/[04] savepoint=248<BR>LIBRARY OBJECT HANDLE: handle=9bd59b80<BR>name=SYSUTIL_OWNER.ADFDATASETDESCRIPTOR <BR>hash=c41c8eb9 timestamp=06-16-2003 08:48:33<BR>namespace=TABL/PRCD/TYPE flags=TIM/SML/ [02000000]<BR>kkkk-dddd-llll=0000-050d-050d lock=S pin=S latch=3<BR>lwt=9bd59b98 [9e0422cc,9e0422cc] ltm=9bd59ba0[9bd59ba0,9bd59ba0]<BR>pwt=9bd59bb0[9bd59bb0 ,9bd59bb0] ptm=9bd59c08[9bd59c08,9bd59c08]<BR>ref=9bd59b88[9a25b544,9bd5b670]<BR >LIBRARY OBJECT: object=9bd22008<BR>type=TABL flags=EXS/LOC[0005] pflags= [00] status=VALD load=0<BR>DATA BLOCKS:<BR>data# heap pointer status pins change<BR> -- -- -- ----- -- ----- -- --- ---- -- ---<BR>0 9ba8e9b4 9bd2214c I/P/A 0 NONE <BR>2 9bd22198 9a251648 I/P/A 1 NONE <BR>3 9a2553e8 9a254fc8 I/-/A 0 NONE <BR>8 9bd2209c 9bb43f78 I/-/A 0 NONE <BR>10 9bd220fc 9bb3ebec I/-/A 0 NONE <BR>-- --- -- ---- ---- ---- ---- ---- ---- ---- ---- ---- --<BR>This lock request was aborted.<BR><BR>The indexes are periodically rebuilded.<BR><BR>Thanks for your help,<BR><BR>Alessandro<BR><BR><BR>> Hi Alessandro,<BR>> <BR>> First off, this is an ORA-0060 (See ORA-0060.ora-code.com), not an ORA-4020 (See ORA-4020.ora-code.com). (ORA-60 (See ORA-60.ora-code.com) is an enqueue<BR>> deadlock, ORA-4020 (See ORA-4020.ora-code.com) is a library cache deadlock.) <BR>> <BR>> Second, it's a TX (transaction enqueue) deadlock.<BR>> <BR> > Third, the mode held by the blocker and the mode held by the waiter are<BR> > 'X' (exclusive).<BR>> <BR>> Fourth, the statement encountering the deadlock is an UPDATE.<BR>> <BR>> So, the combination of the above info tells me that this is a row-level<BR>> application deadlock. You've got two concurrent sessions, one session<BR>> updates row X and does not commit. Another session updates row Y and<BR>> does not commit. Then, the first session tries updating row Y and<BR>> starts waiting on the second session, and finally, the second session<BR>> tries to update row X and starts waiting on the first session. In this<BR>> state, the sessions would wait forever, so, Oracle detects a deadlock,<BR>> raises ORA-0060 (See ORA-0060.ora-code.com), and statement level rollback occurs.<BR>> <BR>> I don't know anything about your application, however, the cleanest<BR>> solution would be to examine the application logic, and alter as<BR>> necessary to ensure that two concurrent sessions do not attempt to<BR>> update the same set of rows. Also, another possiblility, which would<BR>> still suffer from slowdowns due to TX enqueue waits, but not deadlocks,<BR>> would be to ensure that the order that rows are updated is the same in<BR>> all the concurrent sessions.<BR>> <BR>> Hope that helps,<BR>> <BR>> -Mark <BR>> <BR>> <BR>> <BR>> <BR>> --<BR>> Mark J. Bobak<BR>> Senior Oracle Architect<BR>> ProQuest Information & Learning<BR>> <BR >> For a successful technology, reality must take precedence over public<BR> > relations, for Nature cannot be fooled. --Richard P. Feynman, 1918-1988<BR> > <BR>> <BR>> -- --Original Message-- --<BR>> From: oracle-l-bounce@(protected)<BR>> [mailto:oracle-l-bounce@(protected) .org] On Behalf Of Alessandro Vercelli<BR>> Sent: Monday, May 08, 2006 1:34 PM<BR>> To: Oracle Freelists.org<BR>> Subject: [SPAM] Deadlock problem<BR >> Importance: Low<BR>> <BR>> Hi all,<BR>> I'm trying to solve an ora-4020 (See ora-4020.ora-code.com) (deadlock) issue; the trace file (sorry<BR>> if messed) shows:<BR> > <BR>> ksqded1: deadlock detected via did<BR>> DEADLOCK DETECTED<BR> > Current SQL statement for this session:<BR>> Update <TABLE>set <FIELD1 >='<VALUE>' where <FIELD2>like '<STRING>%'<BR>> The following deadlock is not an ORACLE error. It is a deadlock due to<BR><BR>--<BR>http://www.freelists.org /webpage/oracle-l<BR><BR><BR> <BLOCKQUOTE></BLOCKQUOTE><BR> <TBODY></TBODY>< /BLOCKQUOTE><p> <hr size=1>Talk is cheap. Use Yahoo! Messenger to make PC-to-Phone calls. <a href="http://us.rd.yahoo.com/mail_us/taglines/postman7/*http://us.rd.yahoo.com /evt=39666/*http://messenger.yahoo.com"> Great rates starting at 1¢/min.
