Re: DBMS_REPAIR Package

Hi!

> If dbv reads a block in transition, it will appear corrupt.

In 9i, I believe dbv reads a block multiple times, if it doesn't get a consistent (non-corrupted) image first time. If it gets the block ok during next attempts, block isn't reported corrupt. But if several subsequent attempts fail, a dbverify output statistic Total Pages In Flux is incremented (might be that dbv is comparing the block image just read to image of same block read in previous attempt). So that means, 9i can give correct results on open datafiles as well.

(I haven't tested or verified it, its based on 9.2 docs: http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96652/ch13.htm #1006796)

> Personally, I think it a colossal waste of time to run dbverify.

No, it can be useful in several cases: validating backups, verifying restored files etc..
If at all, it can be ore like waste of IO bandwith.

Also, in 9i you can do segment level dbverify, that can be quite useful in some cases.

>
> In 9 years of DBA'ing, I've experienced maybe 3 or 4 instances
> of a corrupt block, and it was always an index. The solution
> was to drop/rebuild the index.

Ok, you've been lucky. I've seen them in data, index, rollback, even temp segements.
And in few cases the index rebuild was definitely not a solution, because the index was a global index on one billion row table, thus it was definitely easier to restore & recover in our case, than allocate huge amounts of temp space and waste a lot of IO and CPU resources for rebuilding.

Cheers,
Tanel.

>
> Jared
>
>
>
>
>
>
> Stefick Ronald S Contr ESC/HRIDD <Ronald.Stefick_at_RANDOLPH.AF.MIL>
> Sent by: ml-errors_at_fatcity.com
> 07/17/2003 07:39 AM
> Please respond to ORACLE-L
>
>
> To: Multiple recipients of list ORACLE-L
<ORACLE-L_at_fatcity.com>
> cc:
> Subject: RE: DBMS_REPAIR Package
>
>
> I dug a little more and found my answer. I found that Oracle uses
> DBMS_REPAIR just to put a band-aid on the corrupt blocks. It basically
> identifies the corrupt block and marks it so oracle skips over it like
> it's not even there.
>
>
> -----Original Message-----
> Sent: Thursday, July 17, 2003 8:55 AM
> To: Multiple recipients of list ORACLE-L
>
> Has anyone ever used DBMS_REPAIR? If so, what do you think of it, does the
> DB have to be shutdown to run it. Does it really fix any corrupt blocks in
> the datafiles? We ran DBVerify and found several corrupt DB files in one
> of our DB's.
>
> TIA,
> Scott Stefick
> MILPDS OCP Oracle DBA
> scott.stefick_at_randolph.af.mil
> 210-565-2540
>
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author:
> INET: Jared.Still_at_radisys.com
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
Received on Thu Jul 17 2003 - 14:19:34 CDT