Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Mining Logs in Oracle for Missing Data

Mining Logs in Oracle for Missing Data

From: MacGregor, Ian A. <>
Date: Tue, 8 Jun 2004 07:25:39 -0700
Message-ID: <>

On one of our Peoplesoft databases some data was found to be missing after Peoplesoft reported a successful run. The Peoplesoft user was able to report the start time and end time of the process they had run and also the process id. I used this information from logminer to extract the session# and serial# from the logs. I then mined the logs based on those two columns.

I found there were 12 transactions started, but only eleven commit statements. The transaction lacking the commit was the fifth of twelve. There was no evidence of rollback. The rollback request value was always 0. Besides even statements rolled back show a commit to end the transaction in log miner.

The transaction with the missing commit involved an update statement. Logminer recorded information on the update for every row involved. The table being updated has triggers to populate a materialized view log. The inserts were also recorded and these information were interspersed with Internal ones.

There weren't a large number of rows to be updated, less than 100, and the update was of a single column. The log miner details looked like

Start --> Update for rowid --> Internal ---Insert for mv log -- Internal --- Update for next rowid ....

The number of internal operations may not be exact in the above. I expected this transaction to end with a commit But it did not.

Instead another transaction started. This transaction only had "Internal" operations. It was followed by another of the same nature, and then by transactions which included "user DML"

What does it mean when one sees Start ---> Update --->Internal --> Insert---> Internal --->Start Instead if the expected Start--> Update ---> Internal ---> Insert -Internal ----> Commit ---> Start.

The session# and serial# remained constant throughout the 12 transactions, as did the Peoplesoft Process id, the session_info was also consistent. There was nothing strange about the timestamps in the log. This was one session. No autonomous transactions were involved. One can have only one transaction active at one time in a session. That transaction may involve multiple DML statements, but one cannot begin a second non-autonomous transaction in that session, do they even run in the same session, without committing or rolling back the first. So why does a second transaction appear to be happening in logminer without the first completing.

I admit I'm not a logminer expert. I've used it successfully to reverse "accidental" transactions, and to correct a problem where the middleware inserted a bad timestamp (It's a Peoplesoft thing corrected in later versions). The usual stuff. This may be as simple as not knowing where to find the rollback statements in a redo log. Perhaps what I am seeing is not unusual, just unexpected by me with only limited log mining experience. After all one only mines logs when there is a problem.

There were no errors issued by Peoplesoft or Oracle during the process.

Ian MacGregor
Stanford Linear Accelerator Center     

Please see the official ORACLE-L FAQ:

To unsubscribe send email to: put 'unsubscribe' in the subject line.

Archives are at FAQ is at
Received on Tue Jun 08 2004 - 09:24:27 CDT

Original text of this message