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

Home -> Community -> Mailing Lists -> Oracle-L -> DBA_2PC Problem

DBA_2PC Problem

From: Jeff Herrick <jherrick_at_host.ott.igs.net>
Date: Sun, 13 Aug 2000 15:30:04 -0400 (EDT)
Message-Id: <10588.114466@fatcity.com>


Folks.....

I've created myself a bit of a problem and of course it's in 'production'.

Background:

a developer calls saying one of his updates has bombed on rollback segs. No problem extending the RBS
tablespace but the problem is that the update was DISTRIBUTED. Upon re-attempting the update he
gets 'Transaction xx.xxx.xxx has item locked...yadda yadda yadda.

I only know enough about 2PC to look in DBA_2PC_PENDING and DBA_2PC_NEIGHBORS but of course
I don't know about 'COMMIT FORCE xx.xx.xx' and 'ROLLBACK FORCE xx.xx.xx' at the time so what
do I decide to do?.....you got it! I blow away the DBA_2PC_PENDING entry. When I try to delete
the DBA_2PC_NEIGHBORS rows (two of them) I get an error trying to do DML on the view and this
makes sense because its a UNION ALL of ps_1$ and pss_1$ (I think!). At this point I RTFM (DOH!) and
find out about COMMIT FORCE and ROLLBACK FORCE and now neither command works because I
dropped the parent. So I bravely dump the view definitions for 2PC and 2PC_NEIGHBORS and delete
all rows from the SYS.base tables. Now there are no rows in 2PC_PENDING or 2PC_NEIGHBORS
_BUT_ I still get the 'transaction locked' error. Fine....I bounce the database figuring the lock entry
is in memory and cross my fingers on restart.....but I still get 'transaction xx.xxx.xx has entry locked' even though there are no signs of the transaction.

I know I f***ed up...but is there any other base table that somebpdy knows about that is holding
this lock ID that I could _try_ and delete...or is my only option an imcomplete recovery?

signed Received on Sun Aug 13 2000 - 14:30:04 CDT

Original text of this message

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