Re: Analyzing a system state dump for an ORA-01410 error

1. I think, it is the time systemstate was started and second time is when the first line was written to the trace file. There is a difference of 15ms which can be accounted for file open and initialization etc. Not sure, but probable that two different oracle modules wrote those timestamps.
2. Last wait is really not an accurate indicator to start( in this case) . Block with invalid rowid could have been in the buffer cache and starting the analysis with last wait might lead to confusion.
3. Yes, you should be reviewing the processstate for that process. If you don't mind can you please send me the part of the trace file for that offending process.

-- 
Cheers

Riyaj Shamsudeen
Principal DBA,
Ora!nternals -  http://www.orainternals.com
Specialists in Performance, Recovery and EBS11i
Blog: http://orainternals.wordpress.com

Original message:
==============
On Tue, Mar 17, 2009 at 8:53 AM, Schauss, R. Peter (IT Solutions) <
peter.schauss_at_ngc.com> wrote:
Oracle 8.1.7.4/Solaris-SunOS 5.9

I am still trying to produce definitive proof to my management that the
ORA-01410 errors we get on our data warehouse reports are caused by
running them at the same time that that the ETL runs (tables being
truncated, indexes being dropped and rebuilt...).

 I added a login trigger to the user that runs the reports to force asystem
state dump whenever an ORA-01410 occurs.
 Now I need to interpret the dump and I have a few questions:



>

> 1.  Lines 16 and 17 look like this:

>

> *** 2009-03-17 09:50:55.272

> *** SESSION ID:(85.16274) 2009-03-17 09:50:55.257

>

> Does this mean that the dump was taken at 09:50:55.272 and that the

> process initiating the dump started at 2009-03-17 09:50:55.257?

>

> 2.  When I go down to the section for the process which caused the dump

> there is a line which starts "last wait for 'db file sequential read'"

> followed by "file#=6, block#=aeb73, blocks=1".  Is this supposed to be

> the location of the invalid rowid?  When I check this location several

> hours later using the query:

>

> select owner,segment_name,segment_type,file_id,

> to_char(block_id,'XXXXXX')first_block,

> to_char(block_id+blocks-1,'XXXXXX') last_block,

> blocks

> from

> dba_extents

> where

> file_id=6

> and to_number ('AEB73','XXXXX') between block_id and block_id+blocks-1

> order by block_id;

>

> I get a valid and plausible table name.  (Or am I missing something

> here?).

>

> 3.  Did the "last wait" information pertain to the last successful call

> in which case I should be looking further down in the section for this

> process?

>

> Thanks,

> Peter Schauss

> --

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

>

>

>


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