RE: redo stream

Well, I started mining through our dev data to track down the unique constraint error and found some strange stuff. There is one scenario I = can
construct which fits the data, but our developers claim it is = impossible.
Just wondering if anyone can think of any other models. This is a = simplified
version of what I found:

--At ~11am there is a txn with rollbacks applied to tables a, b, and c.
DELETE from table a (rollback of insert. Rollback flag=3D1), UPDATE of = table b
(rollback flag=3D1), DELETE from table c (rb flag=3D1).=20
--I extended my search on this txn forwards another 10 hours and =
backwards
another 30 hours (logs no longer available prior to this). There were no other entries for this txn. No start txn (set transaction read write), = no
end txn (commit, rollback), no statements with the initial INSERTs, = UPDATE
(with rb flag =3D 0) which I saw rolled back.

Here is my model:

--There must have been an INSERT in this txn since there was a rollback. =
It
must have happened more than 30 hours ago.
--Since there was no explicit ROLLBACK statement in the logs, the =
rollback
was not issued by the application, but by Oracle, and it must be within = an
already open txn.
--Therefore, for my txn to insert 30 hours ago, but not get the error =
until
now, some other txn must have done an INSERT prior to my txn, but not committed until now.
--After the other txn commits, my txn rolls back, but the txn remains =
open
(no commit or rollback).=20

The developers claim that the code run in my txn opens and closes the = txn in
an order of seconds (minutes is pushing it). There is no way it would = have
been open for more than 30 hours (really 40 as I never saw it closed). Moreover, having another txn open this long without commiting for >30 = hrs
makes the impossible even more improbable.

I agree that the scenario doesn't sound very likely, but what else fits = my
observations? Unfortunately I can't get any more logminer info at this point, so a certain amount of speculation is necessary.

Thanks.

Henry

-----Original Message-----

From: oracle-l-bounce_at_freelists.org =
[mailto:oracle-l-bounce_at_freelists.org]
On Behalf Of Henry Poras
Sent: Tuesday, June 07, 2005 2:29 PM
To: oracle-l_at_freelists.org
Subject: RE: redo stream

OK, here is my simple(minded) test. There are four parts. After creating = =3D a
test table (with a PK), I:

1. Ran some Inserts, Updates, Deletes followed by a COMMIT
2. Ran some Inserts, Updates, Deletes followed by a ROLLBACK
3. Ran an Insert which would fail (non-unique value) as a standalone statement=20
4. Ran an Insert which would fail within an existing transaction

What I found was that when a statement causing a Unique Constraint error = =3D
is the initial statement in a txn, Oracle rolls it back and then =3D explicitely adds a ROLLBACK statement to end the txn. If the error = statement
is =3D within an existing txn, the offending statement is rolled back (rollback flag =3D =3D3D 1 in v$logmnr_contents) but there is no = explicit
rollback statement =3D generated. (this makes sense as an explicit = rollback
would terminate the txn).

Henry

-----Original Message-----

From: oracle-l-bounce_at_freelists.org =3D = [mailto:oracle-l-bounce_at_freelists.org]
On Behalf Of Henry Poras
Sent: Monday, June 06, 2005 1:40 AM
To: oracle-l_at_freelists.org
Subject: redo stream

We got a Unique Constraint error in our application. I wanted to = find=3D20 the
sql which caused this. Since I found out about the error a bit after =3D

the fact, my thoughts turned to using logminer. When thinking about this = =3D

I wondered wether the SQL which caused the error would make it to = the=3D20
redo stream. If it did, the error would then need to be rolled back. But = =3D

this would need to be a special kind of rollback, one which = wouldn't=3D20
terminate the transaction. So maybe the kernal would notice the = error=3D20 on
checking and cancel the statement before it got to the redo stream.=3D20 However, even if that happened in this case, what about ROLLBACK TO=3D20 SAVEPOINT. These statements defiinitely make the redo stream.

I'm putting together a test case, so I'll post what I find as soon as I = =3D

can. (probably with other questions)

Henry

--

http://www.freelists.org/webpage/oracle-l

--

http://www.freelists.org/webpage/oracle-l

--

http://www.freelists.org/webpage/oracle-l Received on Wed Jun 08 2005 - 14:49:03 CDT