Return-Path: Delivered-To: 2-oracle-l@orafaq.com Received: (qmail 21746 invoked from network); 9 Dec 2007 14:01:51 -0600 Received: from freelists-180.iquest.net (HELO turing.freelists.org) (206.53.239.180) by 69.64.49.119 with SMTP; 9 Dec 2007 14:01:50 -0600 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 5A8667D5E75; Sun, 9 Dec 2007 15:01:51 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing.freelists.org [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 10574-03; Sun, 9 Dec 2007 15:01:51 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id A3FCE7D5E50; Sun, 9 Dec 2007 15:01:50 -0500 (EST) Received: with ECARTIS (v1.0.0; list oracle-l); Sun, 09 Dec 2007 14:59:42 -0500 (EST) Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id B35097D5DF4 for ; Sun, 9 Dec 2007 14:59:42 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing.freelists.org [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 09945-05 for ; Sun, 9 Dec 2007 14:59:42 -0500 (EST) Received: from mtiwmhc11.worldnet.att.net (mtiwmhc11.worldnet.att.net [204.127.131.115]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 5BFDC7D5DC5 for ; Sun, 9 Dec 2007 14:59:41 -0500 (EST) Received: from [199.1.1.2] (c-76-102-154-188.hsd1.ca.comcast.net[76.102.154.188]) by worldnet.att.net (mtiwmhc11) with ESMTP id <2007120919593511100hbgj6e>; Sun, 9 Dec 2007 19:59:41 +0000 Message-ID: <475C4928.7050705@att.net> Date: Sun, 09 Dec 2007 11:59:36 -0800 From: Ahbaid Gaffoor User-Agent: Thunderbird 2.0.0.9 (Windows/20071031) MIME-Version: 1.0 To: nigel_cl_thomas@yahoo.com CC: oracle-l Subject: Re: Logminer Question References: <859709.58920.qm@web58803.mail.re1.yahoo.com> In-Reply-To: <859709.58920.qm@web58803.mail.re1.yahoo.com> Content-Type: multipart/alternative; boundary="------------090305000808020004090906" X-archive-position: 3807 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-to: oracle-l-bounce@freelists.org X-original-sender: ahbaid@att.net Precedence: normal Reply-to: ahbaid@att.net List-help: List-unsubscribe: List-software: Ecartis version 1.0.0 List-Id: oracle-l X-List-ID: oracle-l List-subscribe: List-owner: List-post: List-archive: X-list: oracle-l X-Virus-Scanned: Debian amavisd-new at localhost.localdomain --------------090305000808020004090906 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Hello Nigel, there are no deferred constraints, actually the design is so bad that there are very few constraints. I'm beginning to want to take the app from the developer and start going through it with a fine tooth comb. This is perhaps the best approach to see what's going wrong. The problem I am faced with is the developer is claiming "Oracle is doing something" :) I think I need to tear the app apart... but, back to my original question, am I correct that "ROLLBACK=1" indicates that this transaction was applied to rollback a row? Also, how do you order entries in V$LOGMNR_CONTENTS if they have the same SCN and TIMESTAMP? many thanks Ahbaid Nigel Thomas wrote: > Ahbaid > > Are there any deferred constraints in your schema - ie something that > might not be rejected until the end of the transaction? > > Again, you should see an exception - but it might be raised somewhere > unexpected (unexpected by the developer, that is). See eg > http://www.oracle-base.com/articles/8i/ConstraintCheckingUpdates.php. > > Other possibilities: > - any autonomous transactions that could fail? > - any WHEN OTHERS THEN NULL? > > Regards Nigel > > > > > ----- Original Message ---- > From: Ahbaid Gaffoor > To: Finn Jorgensen > Cc: oracle-l@freelists.org > Sent: Sunday, December 9, 2007 6:18:48 AM > Subject: Re: Logminer Question > > What's puzzling is the developer is claiming that no ORA- errors were > seen, so even with a PK or unique key violation that should throw an > error. > > Does Oracle actually apply the transaction then roll it back in cases > of PK/Unique viloations? > > Interesting, I'll test for that > > thanks! > > Ahbaid > > Finn Jorgensen wrote: >> Barring a user rollback operation, perhaps an insert into a table >> with a PK or unique index which was violated? >> >> Finn >> >> On Dec 8, 2007 2:59 PM, Ahbaid Gaffoor > > wrote: >> >> We're trying to figure out exactly what an application has done >> in the >> db. I restored an archive log from tape and mined it. >> >> When querying the logminer view I'm seeing two rows for a >> transaction in >> V$LOGMNR_CONTENTS >> >> The first row has sql entries in SQL_REDO and SQL_UNDO, and the >> ROLLBACK >> field is 0 >> >> The second row has sql only in SQL_REDO and the ROLLBACK field is 1 >> >> Both rows have the same SCN and TIMESTAMP, does this mean that >> the first >> row was applied and the second was indeed a rollback entry? >> >> What's especially puzzling is we are not seeing any lost >> connections or >> abnormal program terminations, so what's causing this rollback entry? >> >> all ideas and comments welcome. >> >> thanks >> >> Ahbaid >> -- >> http://www.freelists.org/webpage/oracle-l >> >> >> > > --------------090305000808020004090906 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Hello Nigel,

there are no deferred constraints, actually the design is so bad that there are very few constraints.

I'm beginning to want to take the app from the developer and start going through it with a fine tooth comb. This is perhaps the best approach to see what's going wrong.

The problem I am faced with is the developer is claiming "Oracle is doing something" :)

I think I need to tear the app apart...

but, back to my original question, am I correct that "ROLLBACK=1" indicates that this transaction was applied to rollback a row?


Also, how do you order entries in V$LOGMNR_CONTENTS if they have the same SCN and TIMESTAMP?

many thanks

Ahbaid


Nigel Thomas wrote:
Ahbaid
 
Are there any deferred constraints in your schema - ie something that might not be rejected until the end of the transaction?
 
Again, you should see an exception - but it might be raised somewhere unexpected (unexpected by the developer, that is). See eg http://www.oracle-base.com/articles/8i/ConstraintCheckingUpdates.php.
 
Other possibilities:
- any autonomous transactions that could fail?
- any WHEN OTHERS THEN NULL?
 
Regards Nigel
 


 
----- Original Message ----
From: Ahbaid Gaffoor <ahbaid@att.net>
To: Finn Jorgensen <finn.oracledba@gmail.com>
Cc: oracle-l@freelists.org
Sent: Sunday, December 9, 2007 6:18:48 AM
Subject: Re: Logminer Question

What's puzzling is the developer is claiming that no ORA- errors were seen, so even with a PK or unique key violation that should throw an error.

Does Oracle actually apply the transaction then roll it back in cases of PK/Unique viloations?

Interesting, I'll test for that

thanks!

Ahbaid

Finn Jorgensen wrote:
Barring a user rollback operation, perhaps an insert into a table with a PK or unique index which was violated?
 
Finn

On Dec 8, 2007 2:59 PM, Ahbaid Gaffoor <ahbaid@att.net> wrote:
We're trying to figure out exactly what an application has done in the
db. I restored an archive log from tape and mined it.

When querying the logminer view I'm seeing two rows for a transaction in
V$LOGMNR_CONTENTS

The first row has sql entries in SQL_REDO and SQL_UNDO, and the ROLLBACK
field is 0

The second row has sql only in SQL_REDO and the ROLLBACK field is 1

Both rows have the same SCN and TIMESTAMP, does this mean that the first
row was applied and the second was indeed a rollback entry?

What's especially puzzling is we are not seeing any lost connections or
abnormal program terminations, so what's causing this rollback entry?

all ideas and comments welcome.

thanks

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






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