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@xxxxxxxxx> 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
