Message-Id: <10589.114525@fatcity.com> From: Alex Hillman Date: Mon, 14 Aug 2000 15:02:32 -0400 Subject: RE: DBA_2PC Problem This message is in MIME format. Since your mail reader does not understand this format, some or all of this message may not be legible. ------_=_NextPart_001_01C00622.36BFF570 Content-Type: text/plain; charset="iso-8859-1" You know enough to be dangerous. Next time if there is something you are not sure call support or ask this list or somebody else who know what he or she is doing - especially if you are going to do DML on data dictionary. Also it is advisable to first test on nonproduction database. I think you made your database nonsupported by Oracle even if you have support contract. Alex Hillman -----Original Message----- From: Jeff Herrick [mailto:jherrick@host.ott.igs.net] Sent: Sunday, August 13, 2000 4:35 PM To: Multiple recipients of list ORACLE-L Subject: DBA_2PC Problem 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 worried_in_Canada a.k.a Jeff Herrick -- Author: Jeff Herrick INET: jherrick@host.ott.igs.net Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru@fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). ------_=_NextPart_001_01C00622.36BFF570 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable RE: DBA_2PC Problem

You  know enough to be dangerous. Next time if = there is something you are not sure call support or ask this list or = somebody else who know what he or she is doing - especially if you are = going to do DML on data dictionary. Also it is advisable to first test = on nonproduction database. I think you made your database nonsupported = by Oracle even if you have support contract.

Alex Hillman

-----Original Message-----
From: Jeff Herrick [mailto:jherrick@host.ott.igs.n= et]
Sent: Sunday, August 13, 2000 4:35 PM
To: Multiple recipients of list ORACLE-L
Subject: DBA_2PC Problem


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

worried_in_Canada
a.k.a Jeff Herrick

--
Author: Jeff Herrick
  INET: jherrick@host.ott.igs.net

Fat City Network Services    -- (858) = 538-5051  FAX: (858) 538-5051
San Diego, = California        -- Public Internet = access / Mailing Lists
---------------------------------------------------------------= -----
To REMOVE yourself from this mailing list, send an = E-Mail message
to: ListGuru@fatcity.com (note EXACT spelling of = 'ListGuru') and in
the message BODY, include a line containing: UNSUB = ORACLE-L
(or the name of mailing list you want to be removed =