Solved

Finding when Rollbacks occur in Oracle

Posted on 2013-11-04

Oracle Database

1 solution

Medium Priority

448 Views

Last Modified: 2013-11-05

We have an application we suspect is not properly hadnling rollbacks. The symptom is occasionally (maybe 1 out of 1000 times) a scenario occurs one of the tables gets out of sync such that it appears a transaction is partially committed.

When this occurs our dba cannot find any errors in the Oracle log, but I am not sure we have logging levels at the highest setting.

When the problem occurs next, we can idenify the approximate time that it occured. Is there any Oracle tool that can show us if any rollbacks occurred at that time?

Thanks!

Comment

Question by:gantone1

[X]

Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

Help others & share knowledge
Earn cash & points
Learn & ask questions

Join The Community

6 Comments

LVL 79

Expert Comment

by:arnold

ID: 396233822013-11-04

You've defined the problem, but since I am not an oracle person, not sure whether a non-committed transaction is recorded and even if it is, not sure whether you can track it back given that you may have many transactions started and some committed while other rolled back.

You have to review your code that deals with this table combination and if you don't have it already look at the transactions/process.

You may have depended transactions that are setup independently.

LVL 38

Expert Comment

by:Geert Gruwez

ID: 396235382013-11-04

have you tried adding a
begin exception >>log the exeception end
block around the rollback to catch any possible error ?

i came across a nesting of savepoints once which wasn't correct
something like this:

savepoint a;
if x > 1 then 
  savepoint b;
  if Y > 1 then 
    if Z > 1 then 
      rollback to a;
    end if;
    commit b;
  end if;
end if;
commit a;

Open in new window

LVL 79

Expert Comment

by:arnold

ID: 396240242013-11-05

I think the asker is looking for an oracle DB level tool to discover this.
I suspect at this time, the code where this is an issue is yet to be located/identified.

LVL 15

Accepted Solution

by:Franck Pachot

Franck Pachot earned 1500 total points

ID: 396241712013-11-05

Hi,
You can have statistics such as the number of rommits/rollbacks in a session.
But if you need more information, only logminer can help. It is an Oracle tool to read the redo logs and they have information about each updates and each commit/rollback.
Regards,
Franck.

Author Closing Comment

by:gantone1

ID: 396243652013-11-05

I am trying to collect evidence of production problem that can be given to development group to help them isolate cause of problem. If I can prove a rollback occured at the time that problem occurred, it will help them isolate the problem

Is Logminer the best tool for examining the redo logs? Is there any place other than the Redo logs that Rollbacks are logged?

Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

UNDO AND REDO IN ORACLE

Article by: Anjugarg66

This post first appeared at Oracleinaction (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…

Oracle Database

Downloading Oracle Log Files in AWS RDS

Article by: Doug

Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface. I made a script to download the Alert Log, look for errors, and email me the trace files. In this article I'll describe what I did and share my script.

Duplicating a Database Using Oracle RMAN

Video by: Steve

This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines