Solved

Finding when Rollbacks occur in Oracle

Posted on 2013-11-04
6
412 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!
0
Comment
Question by:gantone1
6 Comments
 
LVL 76

Expert Comment

by:arnold
ID: 39623382
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.
0
 
LVL 36

Expert Comment

by:Geert Gruwez
ID: 39623538
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

0
 
LVL 76

Expert Comment

by:arnold
ID: 39624024
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.
0
 
LVL 15

Accepted Solution

by:
Franck Pachot earned 500 total points
ID: 39624171
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.
0
 

Author Closing Comment

by:gantone1
ID: 39624365
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?
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now