RE: DBA_2PC Problem

From: Alex Hillman <alex_hillman_at_physia.com>
Date: Mon, 14 Aug 2000 15:02:32 -0400
Message-Id: <10589.114525@fatcity.com>

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_at_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_at_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_at_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

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
<HTML>
<HEAD>
<META HTTP-EQUIV=3D"Content-Type" CONTENT=3D"text/html; =
charset=3Diso-8859-1">
<META NAME=3D"Generator" CONTENT=3D"MS Exchange Server version =
5.5.2448.0">
<TITLE>RE: DBA_2PC Problem</TITLE>
</HEAD>
<BODY>

<P><FONT SIZE=3D2>You&nbsp; 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.</FONT></P>

<P><FONT SIZE=3D2>Alex Hillman</FONT>
</P>

<P><FONT SIZE=3D2>-----Original Message-----</FONT>
<BR><FONT SIZE=3D2>From: Jeff Herrick [<A =
HREF=3D"mailto:jherrick_at_host.ott.igs.net">mailto:jherrick_at_host.ott.igs.n=
et</A>]</FONT>
<BR><FONT SIZE=3D2>Sent: Sunday, August 13, 2000 4:35 PM</FONT>
<BR><FONT SIZE=3D2>To: Multiple recipients of list ORACLE-L</FONT>
<BR><FONT SIZE=3D2>Subject: DBA_2PC Problem</FONT>
</P>
<BR>

<P><FONT SIZE=3D2>Folks.....</FONT>
</P>

<P><FONT SIZE=3D2>I've created myself a bit of a problem and of course =
it's in 'production'.</FONT>
</P>

<P><FONT SIZE=3D2>Background:</FONT>
</P>

<P><FONT SIZE=3D2>a developer calls saying one of his updates has =
bombed on rollback segs.</FONT>
<BR><FONT SIZE=3D2>No problem extending the RBS</FONT>
<BR><FONT SIZE=3D2>tablespace but the problem is that the update was =
DISTRIBUTED. Upon</FONT>
<BR><FONT SIZE=3D2>re-attempting the update he</FONT>
<BR><FONT SIZE=3D2>gets 'Transaction xx.xxx.xxx has item locked...yadda =
yadda yadda. </FONT>
</P>

<P><FONT SIZE=3D2>I only know enough about 2PC to look in =
DBA_2PC_PENDING and</FONT>
<BR><FONT SIZE=3D2>DBA_2PC_NEIGHBORS but of course</FONT>
<BR><FONT SIZE=3D2>I don't know about 'COMMIT FORCE xx.xx.xx' and =


'ROLLBACK FORCE xx.xx.xx'</FONT>

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

<P><FONT SIZE=3D2>I know I f***ed up...but is there any other base =
table that somebpdy knows</FONT>
<BR><FONT SIZE=3D2>about that is holding</FONT>
<BR><FONT SIZE=3D2>this lock ID that I could _try_ and delete...or is =
my only option an</FONT>
<BR><FONT SIZE=3D2>imcomplete recovery?</FONT>
</P>
<BR>

<P><FONT SIZE=3D2>signed</FONT>
</P>

<P><FONT SIZE=3D2>worried_in_Canada</FONT>
<BR><FONT SIZE=3D2>a.k.a Jeff Herrick</FONT>
</P>

<P><FONT SIZE=3D2>-- </FONT>
<BR><FONT SIZE=3D2>Author: Jeff Herrick</FONT>
<BR><FONT SIZE=3D2>&nbsp; INET: jherrick_at_host.ott.igs.net</FONT>
</P>

<P><FONT SIZE=3D2>Fat City Network Services&nbsp;&nbsp;&nbsp; -- (858) =
538-5051&nbsp; FAX: (858) 538-5051</FONT>
<BR><FONT SIZE=3D2>San Diego, =
California&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -- Public Internet =
access / Mailing Lists</FONT>
<BR><FONT =
SIZE=3D2>---------------------------------------------------------------=
-----</FONT>
<BR><FONT SIZE=3D2>To REMOVE yourself from this mailing list, send an =
E-Mail message</FONT>
<BR><FONT SIZE=3D2>to: ListGuru_at_fatcity.com (note EXACT spelling of =


'ListGuru') and in</FONT>

<BR><FONT SIZE=3D2>the message BODY, include a line containing: UNSUB =
ORACLE-L</FONT>
<BR><FONT SIZE=3D2>(or the name of mailing list you want to be removed =

Received on Mon Aug 14 2000 - 14:02:32 CDT