Re: Long time for v$database_block_corruption to return

On Nov 28, 10:55 am, Richard Nairn <saturn..._at_gmail.com> wrote:
> On Nov 27, 3:40 pm, hpuxrac <johnbhur..._at_sbcglobal.net> wrote:
>
>
>
>
>
> > On Nov 27, 12:33 pm, saturn..._at_gmail.com wrote:
>
> > > Hi,
>
> > > I've been noticing on Enterprise Manager that I am getting spikes in
> > > activity every 20 minutes or so. It is running the query:
>
> > >  "SELECT nvl(sum(blocks), 0) FROM v$database_block_corruption".
>
> > > This can take up to two minutes to return, even though there are no
> > > corrupt blocks. I have a duplicate machine, which returns almost
> > > immediately from this query. This machine had a bad backup a while
> > > ago, but it had been resolved.
>
> > > Does anyone have any idea why this may take so long to to, and what I
> > > can do to reset it or something.
>
> > My guess is that taking another "good" rman backup should fix the
> > situation.
>
> > It might be interesting to put in a 10046 trace while it still has the
> > problem and see what it is waiting on exactly ...
>
> I have done backups since that have all been good. It always returns a
> 0.
>
> I ran the trace and it comes up with a whole bunch of wait on control
> file sequential read.

What is the distribution of your controlfiles and data files? You can get this if you are doing some heavy updating of lots of datafiles and that is conflicting with where the controlfiles are. Are you seeing any errors in the alert log of things not complete? It's also possible for something to set off the block cleanout that had previously been delayed, so you say "no, I'm not doing any heavy updating now," when you really are.

Anyways, EM is still pretty much an Oracle viewpoint, you might use OS tools to figure out what processes are pounding on what.

Also let us know the results of:
select count(*), file#, corruption_type from v $database_block_corruption group by file#, corruption_type; and figure out what files those are from dba_data_files and if they are on a dying device. Also, any non-default init.ora settings that have "checksum" in them.

See the manuals for v$database_block_corruption, is it possible someone did a user-mode backup/restore without putting the tablespaces in backup mode?

You might just need to do an rman validate backup to clean up the control file - if the above query shows corruption just was in one data file, you can do it just for that file.

jg

--
@home.com is bogus.
Free web for all!  http://online.wsj.com/article/SB122809560499668087.html