4

I have noticed something in Oracle which never happened before to me. Though I frequently do this kind of thing for many years now. In this case it's an Oracle11 instance (Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit running on Linux to be precise).

I am importing a dumpfile and at the same time I am increasing the tablespace using statements such as this:

alter tablespace example add datafile '/path/to/oradata/instance/exampleXX.dbf' size 31000m;

I sometimes get the following error when running the above statement:

ERROR at line 1: ORA-00060: deadlock detected while waiting for resource

I can restart the alter tablespace statement and it mostly will go all right the second attempt.

I am asking why this happens because it is unusual to me. Does it indicate a bug or impeding filesystem failure? I understand running an import and adding datafiles to a tablespace at the same time may be a lot to ask of a server, however this is server has 4 CPUs, 64 GB and a few TB of SAS raid10 at its disposal.

This is not a production system by the way.

Relevant trace file output:

Deadlock graph: ---------Blocker(s)-------- ---------Waiter(s)--------- Resource Name process session holds waits process session holds waits TT-00000006-00000010 36 11 SX 38 142 S TT-00000006-00000010 38 142 S 36 11 SX session 11: DID 0001-0024-0000005D session 142: DID 0001-0026-00000068 session 142: DID 0001-0026-00000068 session 11: DID 0001-0024-0000005D Rows waited on: Session 11: no row Session 142: no row ----- Information for the OTHER waiting sessions ----- Session 142: sid: 142 ser: 30639 audsid: 0 user: 5/SYSTEM flags: 0x8000051 pid: 38 O/S info: user: example_user, term: UNKNOWN, ospid: 14589 image: example@example (DW00) current SQL: CREATE TABLE EXAMPLE ... ----- Error Stack Dump ----- ORA-00060: deadlock detected while waiting for resource ----- Current SQL Statement for this session (sql_id=6bvzdgdn8vqqt8) ----- alter tablespace ...

The stacktrace and memory dump could provide more information however I am not a specialist in troubleshooting those. What I am after is an idea what it might be, someone who had this happen before who could make an informed guess. If it is a bug, a sign the filesystem may be having issues, or that the raid controller couldn't handle the load. Why were the two statements waiting for the resource and why was this resource unavailable.

Improve this question
1
  • Really interesting question, but wondering if you'll get more DBA love on the DBA site. We can flag to have it moved if you like. Commented Aug 20, 2012 at 19:29

4 Answers 4

Reset to default
2

It might be helpful to see the actual resource(s) in contention, but I'm guessing that both operations are trying to touch the tablespace metadata and so locking each other out. A good test of that would be to re-arrange your tasks so the alter tablespace command didn't have such a huge run-out (creating the datafile):

imp dumpfile control=blahblah.ctl create datafile .... alter tablespace add datafile...

However, on a machine running 11g and with the resources you describe, I would recommend taking a look at using ASM rather than managing files manually. ASM makes a DBA's life so much easier (Well, mine anyway).

Improve this answer
5
  • That sounds like a sensible guess. I am curious though why it never happened before but only on this particular Oracle11 instance. Although that's not easy to pinpoint of course. By the way the adding of datafiles is to prevent the tablespace from filling up during import and I don't want to over commit tablespace which then is hard to retrieve back from the database. Yes I have thought about ASM and I might as well start using it at some point. Luckily tablespace size is rather stable after import, during normal usage. Commented Aug 20, 2012 at 19:32
  • Understood about manual control of disk space, and the unknowns of a random issue. One of the fun things about Oracle is the depth of arcane detail one can wallow around in. GL. Commented Aug 20, 2012 at 19:36
  • Yeah I see your point :-D I added some more detail from the trace file, I don't know how to actually find the resources the "Resource Name" is pointing to. A quick search didn't help much either: dbaforums.org/oracle/index.php?showtopic=2631 Commented Aug 20, 2012 at 19:57
  • The "no row" looks familiar and might be a clue. Any chance your dumpfile includes tables with unindexed foreign keys? Just a thought. Commented Aug 20, 2012 at 20:22
  • I found this asktom.oracle.com/pls/asktom/… which provides a script to query for unindexed foerign keys. Indeed there are tables with unindexed foreign keys. However older schemas on Oracle10 have also have this and those never showed any problems. Commented Aug 28, 2012 at 23:44
2

According to this website (that I found with a very simple web search) it's because your session conflicted with another:

ORA-00060 error indicates that a dead lock occurred due to a resource contention with another session and Oracle rolled back your current statement to resolve the dead lock. The other session can proceed further as usual . Your current sessions rolled backed statements needs to be resubmitted for the execution after the required resources are available.

Read more from that article for more information about deadlocks.

Improve this answer
5
  • Much more concise answer than my plan to regurgitate CPSC2xx-3xx courses to explain what a deadlock was (dining philosphers, anyone?). Commented Aug 18, 2012 at 3:23
  • Thanks, yes I read about deadlocks and did pretty much the same search you did. However it isn't clear to me why adding a datafile would cause a deadlock whilst importing a dumpfile. As far as I can see the datafile does NOT become available to the database until it's finished creating. This does not appear like a situation that would normally cause a deadlock. Commented Aug 18, 2012 at 4:29
  • If you could please elaborate on your answer, I must just overlook something totally obvious. Commented Aug 18, 2012 at 4:36
  • 1
    Look in the Oracle alert log. Right after the "ORA-00060: Deadlock detected" it should give a path to a trace file... As the link bahamat provided says: "Action: Look at the trace file to see the transactions and resources involved." Commented Aug 18, 2012 at 21:00
  • I understand what you're trying to say, but unfortunately the trace file does not provide the information I am after. It does show the exact statements causing the deadlock (create table and add datafile, which I already figured) and a whole lot of detailed information such as memory dumps and stack traces. However the exact cause is not clear, which makes sense, that's not oracle's business. I am hoping that someone is able to give an informed guess about what would cause this, or had this happening before and found out the underlying reasons. Commented Aug 20, 2012 at 18:32
2

Well, it's a very old post, but I get to that page while trying to solve same problem with my Oracle 12c server. Finally it was because I reached the default limit of 200 datafiles configured in control files:

SQL> select max(FILE_ID) from DBA_DATA_FILES; MAX(FILE_ID) ------------ 200

I had to increase that value, and then I could create a new tablespace o add a datafile to a existing one:

alter system set db_files = 2000 scope = spfile; dbshut / dbstart
Improve this answer
0

A deadlock occurs when 2 sessions block each other by attempting to update a row, which is already updated by another session but has not been yet committed or rolled back. There can be more than 2 sessions involved, but the main idea is the same.

http://dbpilot.net/2018/01/15/ora-00060-deadlock-detected-while-waiting-for-resource/

...

Improve this answer

You must log in to answer this question.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.