Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Help investigating block corruption

Re: Help investigating block corruption

From: Mike Heden <mheden_at_bigfoot.com>
Date: 8 Jul 2004 13:14:42 -0700
Message-ID: <53b3de4.0407081214.3f5f9f27@posting.google.com>


mheden_at_bigfoot.com (Mike Heden) wrote in message news:<53b3de4.0407080545.5f0e034c_at_posting.google.com>...
> mheden_at_bigfoot.com (Mike Heden) wrote in message news:<53b3de4.0407080255.4b7a5035_at_posting.google.com>...
> > I'm investigating a recent case of block corruption and have a couple
> > of questions:-
> >
> > First, can I use dbv against an online database? The documentation
> > says offline, but I've seen several posts that suggest that it can
> > sometimes be used against an online database, depending on OS and
> > Oracle version - I'm running Solaris 8 and Oracle 8.1.7.
> > Incidentally, the results I get from dbv are the same whether the
> > database is online or offline.
> >
> > Second, the corruptions are reported in an almost contiguous set of
> > blocks in one of the user datafiles:-
> >
> > Page 6599 is marked corrupt
> > ***
> > Corrupt block relative dba: 0x024019c7 (file 0, block 6599)
> > Completely zero block found during dbv:
> >
> > Page 6600 is marked corrupt
> > ***
> > Corrupt block relative dba: 0x024019c8 (file 0, block 6600)
> > Completely zero block found during dbv:
> >
> > Page 6601 is marked corrupt
> > ***
> > Corrupt block relative dba: 0x024019c9 (file 0, block 6601)
> > Completely zero block found during dbv:
> >
> > ... etc...
> >
> > All the corrupt block reports state that a 'completely zero block' has
> > been found.
> >
> > My understanding is that I need to examine dba_extents in order to
> > find out
> > which objects utilise(d) the affected blocks.
> >
> > Running the query...
> >
> > SELECT segment_name, segment_type, owner, tablespace_name
> > FROM sys.dba_extents
> > WHERE file_id=0
> > AND block_id BETWEEN 6598 and 6793;
> >
> > ... returns zero rows.
> >
> > I also tried...
> >
> > SELECT * FROM sys.dba_free_space
> > WHERE file_id=0
> > AND block_id BETWEEN 6598 and 6793;
> >
> > but that returns zero rows as well.
> >
> > Can anyone point out what I'm doing wrong.
> >
> > Thanks,
> >
> > Mike
>
> Ah - looks like I've been able to answer my own questions...
>
> I *can* use dbv on a running database and my query against dba_extents
> doesn't work because I hadn't grasped the fact that the block_id is
> that of the first block in the segment...
>
> I'll modify it and try again...
>
> Mike

I must stop talking to myself like this... :-)

I ran the following query:-

SELECT segment_name, segment_type, owner, tablespace_name, block_id, blocks
FROM sys.dba_extents
WHERE file_id=0
AND 6599 BETWEEN block_id and block_id + blocks - 1

It returned no rows. I think the reason for this is that dbv has claimed that the corruption is in a non-existent file. There is no file_id of zero on the system. Jus to check, I ran the query...

	SELECT tablespace_name, file_id "AFN", relative_fno "RFN"
	FROM dba_data_files

... to get the file numbers and, as expected, they start with the system file at 1.

Any suggestions as to what's going on here? Why should dbv, when run against a user file that actually has a file_id of 9, report corrupt blocks and claim that the file_id is zero.

Any ideas, anyone?

Thanks,

Mike Received on Thu Jul 08 2004 - 15:14:42 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US