Re: Question on Corrupted blocks

Hi Tim, I have been busy in doing production upgrade and didn't follow-up on your email. Luckily the block corruption I identified in one of my test databases and didn't find any corruption two of the production databases, which I upgraded recently.

As I mentioned earlier that dbv utility identified that some blocks on some files were corrupted and I understand that dbv is not the right way to check block corruption since the datbase stays down when I run the dbv command.

So please suggest how to check block/data corruption on the entire database (including all data files).

Thanks,
Ashoke

On Wed, May 24, 2017 at 4:30 PM, Tim Gorman <tim.evdbt_at_gmail.com> wrote:

> Ashoke,
>
> Please consider "dbv" as more of a *screening* for block corruption than a
> definitive diagnosis. For one thing, "dbv" does not connect to the
> database instance, so it cannot determine whether the buffer in the Buffer
> Cache in the SGA is also corrupted.
>
> To determine if a datafile block is truly corrupted, please connect to
> SQL*Plus as SYSDBA and run the following...
>
> ALTER SYSTEM DUMP DATAFILE [ *file#* | *'filename'* ] BLOCK [ *block#* |
> MIN *block#* BLOCK MAX *block#* ]
>
>
> ...which in your situation might mean running the following commands,
> based on "dbv" output...
>
> alter system dump datafile 40 block 35586;
> alter system dump datafile 40 block 35598;
> alter system dump datafile 40 block 35599;
> alter system dump datafile 40 block 35600;
> alter system dump datafile 40 block 35601;
> alter system dump datafile 40 block 35603;
> alter system dump datafile 40 block 35605;
> alter system dump datafile 40 block 35607;
> alter system dump datafile 40 block 35834;
> alter system dump datafile 40 block 36579;
>
>
> ...or maybe something like this instead...
>
> alter system dump datafile 40 block min 35586 block max 36579;
>
>
> ...but for that you'd have to be careful only to pay attention to the
> blocks listed above as corrupted within that range from 35586..36579, of
> course.
>
> The dumped block information will go to a trace file in your
> USER_DUMP_DEST, so it might be wise to first run a command like "alter
> session set tracefile_identifier = dump" so that the trace file(s)
> generated from your ALTER SYSTEM DUMP will have the file-name suffix of
> "_DUMP.trc", making it easier to identify them inside the USER_DUMP_DEST
> directory on the database server.
>
> If the indicated database blocks are indeed corrupted, then the output in
> the trace file will state so. If instead the ALTER SYSTEM DUMP command
> does not list the block as corrupted, then it was a "false positive" from
> the "dbv" program.
>
> Hope this helps...
>
> -Tim
>
>
>
>
>
> On 5/24/17 15:10, Ashoke Mandal wrote:
>
> Hello,
>
> I shutdown the database, ran dbverify($ORACLE_HOME/bin/dbv) and it
> reported the following block corruption in the respective logfile (as
> listed below - 1 through 4).
>
> But "select * from v$database_block_corruption;" doesn't return any rows.
> Q1. Could you provide me any idea what are the possible cause for these
> corruption?
> Q2. What is best way to check for such block corruption before I upgrade a
> production database?
> Q3. Is it possible there might be some corrupt blocks but we don't receive
> any complain from anywhere (user or alert log etc.)?
>
>
> 1. $ORACLE_HOME/bin/dbv file=/u03/oradata/file_1_ind_idx02.dbf
> feedback=100 logfile=/<logfile_location>/file_1_ind_idx02.log
> blocksize=8192
>
> Corrupt block relative dba: 0x0a80271a (file 42, block 10010)
> Total Pages Marked Corrupt : 1
>
> 2. $ORACLE_HOME/bin/dbv file=/u03/oradata/file_2_data.dbf feedback=100
> logfile=/<logfile_location>/file_2_data.log blocksize=8192
>
> Corrupt block relative dba: 0x0a008b02 (file 40, block 35586)
> Corrupt block relative dba: 0x0a008b0e (file 40, block 35598)
> Corrupt block relative dba: 0x0a008b0f (file 40, block 35599)
> Corrupt block relative dba: 0x0a008b10 (file 40, block 35600)
> Corrupt block relative dba: 0x0a008b11 (file 40, block 35601)
> Corrupt block relative dba: 0x0a008b13 (file 40, block 35603)
> Corrupt block relative dba: 0x0a008b15 (file 40, block 35605)
> Corrupt block relative dba: 0x0a008b17 (file 40, block 35607)
> Corrupt block relative dba: 0x0a008bfa (file 40, block 35834)
> Corrupt block relative dba: 0x0a008ee3 (file 40, block 36579)
> Total Pages Marked Corrupt : 10
>
> 3. $ORACLE_HOME/bin/dbv file=/u03/oradata/file_3_idx01.dbf feedback=100
> logfile=/<logfile_location>/file_3_idx01.log blocksize=8192
>
> mdl_ind_medium_idx01.log:Corrupt block relative dba: 0x090075a5 (file 36,
> block 30117)
> mdl_ind_medium_idx01.log:Total Pages Marked Corrupt : 1
>
> 4. $ORACLE_HOME/bin/dbv file=/u03/oradata/file_3_ind_large_idx01.dbf
> feedback=100 logfile=/<logfile_location>/file_3_ind_large_idx01.log
> blocksize=8192
>
> wip_ind_large_idx01.log:Corrupt block relative dba: 0x02c0f581 (file 11,
> block 62849)
> wip_ind_large_idx01.log:Corrupt block relative dba: 0x02c0f583 (file 11,
> block 62851)
> wip_ind_large_idx01.log:Corrupt block relative dba: 0x02c0f586 (file 11,
> block 62854)
> wip_ind_large_idx01.log:Total Pages Marked Corrupt : 3
>
> Thanks,
> Ashoke
>
>
>

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