Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Oracle won't release locks - ever!

Re: Oracle won't release locks - ever!

From: Leonard F Clark <lfc_at_zoom.co.uk>
Date: Sat, 03 Jul 2004 16:02:38 GMT
Message-ID: <40e6c9f3.24580515@usenet.plus.net>


On Sat, 03 Jul 2004 15:25:05 +0200, Sybrand Bakker <sybrandb_at_hccnet.nl> wrote:

>On Sat, 03 Jul 2004 11:18:27 GMT, lfc_at_zoom.co.uk (Leonard F Clark)
>wrote:
>
>>I'm running Oracle 9R2 on several machines (2 Windows 2003 Server and
>>one Windows XP Pro). I've had to kill a batch process running on each
>>and, inevitably, it's left locks for quite some time as the jobs roll
>>back.
>>
>>However, on all machines these locks are not being released. (It
>>looks as if the rollback is just not ... er ... rolling back.)
>>
>>Most of the locks are on the data files. There's also the rollback
>>and a lock for creating a new block. None of them are showing as
>>blockers. I have tried shutting down the instance and even re-booting
>>the server but the locks remain (although it starts CTIME from 0
>>again.
>>
>>Query results:
>>
>>from select * from v$lock;
>>
>>ADDR KADDR SID TY ID1 ID2 LMODE
>>REQUEST CTIME BLOCK
>>-------- -------- ---------- -- ---------- ---------- ----------
>>---------- ---------- ----------
>>2F2FD590 2F2FD5A0 2 MR 201 0 4
>>0 56535 0
>>2F2FD544 2F2FD554 2 MR 18 0 4
>>0 56535 0
>>2F2FD4F8 2F2FD508 2 MR 17 0 4
>>0 56535 0
>>2F2FD4AC 2F2FD4BC 2 MR 16 0 4
>>0 56535 0
>>2F2FD460 2F2FD470 2 MR 15 0 4
>>0 56535 0
>>2F2FD414 2F2FD424 2 MR 14 0 4
>>0 56535 0
>>2F2FD3C8 2F2FD3D8 2 MR 12 0 4
>>0 56535 0
>>2F2FD37C 2F2FD38C 2 MR 11 0 4
>>0 56535 0
>>2F2FD330 2F2FD340 2 MR 10 0 4
>>0 56535 0
>>2F2FD2E4 2F2FD2F4 2 MR 9 0 4
>>0 56535 0
>>2F2FD298 2F2FD2A8 2 MR 8 0 4
>>0 56535 0
>>2F2FD24C 2F2FD25C 2 MR 7 0 4
>>0 56535 0
>>2F2FD200 2F2FD210 2 MR 6 0 4
>>0 56535 0
>>2F2FD1B4 2F2FD1C4 2 MR 5 0 4
>>0 56535 0
>>2F2FD168 2F2FD178 2 MR 4 0 4
>>0 56535 0
>>2F2FD11C 2F2FD12C 2 MR 3 0 4
>>0 56535 0
>>2F2FD0D0 2F2FD0E0 2 MR 2 0 4
>>0 56535 0
>>2F2FD084 2F2FD094 2 MR 1 0 4
>>0 56535 0
>>2F2FCFA0 2F2FCFB0 3 RT 1 0 6
>>0 56538 0
>>2F2FCE70 2F2FCE80 4 XR 4 0 1
>>0 56540 0
>>2F2FCFEC 2F2FCFFC 5 TS 2 1 3
>>0 56534 0
>>
>>21 rows selected.
>>
>>Other queries:
>>
>>SQL> select * from v$locked_object;
>>
>>no rows selected
>>
>>SQL> select * from v$transaction;
>>
>>no rows selected
>>
>>I'm really stymied by this one. Inevitably, when I try re-running the
>>batch, I get ORA-00600 errors.
>>
>>Any suggestions greatly recieved
>>
>>
>
>Your assertion that bouncing an instance doesn't release locks, is
>ehhmm.. to ridiculous to believe.
>Also there are not TX locks or TM locks to be seen so no transactions
>are involved.
>I think you need to find out what the various locktypes are (they are
>described in the various locking scripts located in
>$ORACLE_HOME/rdbms/admin, which you also didn't use so far)
>
>
>--
>Sybrand Bakker, Senior Oracle DBA

Sybrand,

Okay, let's start again.

I seem to remember saying that "Most of the locks are on the data files. There's also the rollback and a lock for creating a new block" I didn't say anything about TX or TM locks. In fact, I thought I'd let the query speak for itself. (I guess the TX/TM comment is because I showed a query on v$transactions, but I was just trying to forestall comments and showing I had nothing up my sleeve.)

In fact I *din't* expect anything by either boumcing the instance or the box: I'm perfectly aware that, something does have to rollback, then all we can do is give it time. But when you a certain point, you try anything.

In fact it's not correct to say that bouncing the box achieves nothing: because windows is thread based rather than process based, you can find that killed sessions are still showing locks - hence oracle has kindly given us the ORAKILL application as well.

I actually bounced because most of the locks are file locks and the problem was caused by a rogue anti-virus application locking up the machine: I was worried that OS file locks were preventing oracle from preventing the indicated tasks (e.g. acquiring another extent)

So, okay, let me re-phrase my question:

Does anyone know why Oracle should hold a redo thread for over a week when there are no other clients (no transactions) on the instance?

Does anyone know why Oracle would hold a Temporary Segment lock on the same basis?

Can anyone suggest a plausible explanation why all data files should be held in Media Recovery for over a week (the offending process lasted about 8 hours), and there be no aparant activity on the instance?

I have run some other queries but they told me nothing I couldn't get from the info I presented and, on the law of deminishing returns, just didn't see the need to pile on unnecessary information.

Now, I hasten to add I do not claim to have an extensive knowledge in this area. In fact, I thought the point of this group was for porr, benighted working DBAs like me and that it wasn't just restricted to gurus. (Perhaps I'd better add that I am a Certified DBA and, while I'm not sure that proves an awful lot, I'd like to think that it does mean that I have put some effort into understanding performance monitoring, as other areas). The fact is that I'm lost here and i could do with some help (and - if your basic premise were right - wouldn't a gentle mention of catblock.sql and how i might use it be more help than the really unhelpful sarcasm that I got? If you're trying to keep all but the most informaed out of the group, you're going the right way.)

I presume that my other respondent was equally sarcastically suggesting Metalink. In fact my other problem is that our "Oracle Partner" seems reluctant to translate our license into a CSI number. And I can't resolve any of that until Monday. I *do* have access to Metalink and I have researched the ORA-600 messages there (which point straight back to these locks), but that access doesn't allow me to raise a TAR.

So, unless i can get a civilised response, I guess I will just have to wait till Monday.

Len Received on Sat Jul 03 2004 - 11:02:38 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US