Not answering your question, but a side note when you write “all data files.” I
have recently run into an exasperating situation with freespace.
IF you experience an ora-600 abort during a parallel direct insert, space that
contains internally corrupt blocks may be returned to your freespace (at least
for bitmap [called auto] freespace, I have never seen it for freelists [called
manual].)
None of the tools of which I am aware do a scan of freespace. The symptom of
the issue is when that space is attempted to be used you get a new ora-600
reporting a corrupt block. Creating a dummy table and allocating extents to
cover the corrupt space is effective as a bridge until you can repair the
damaged blocks (anyone have a good tool for repairing freespace blocks?) or
relocate data out of the damaged file. That was the interim work-around I
thought of since allocate extent does not check the blocks (apparently).
I’ll happily add to my kitbag any tools anyone can identify (especially if I
missed any and Oracle support missed any that are authorized while dealing with
this SR.)
One by one any ora-600 aborts that causes returning “bad blocks” to freespace
can be handled normally via SR. But so far fixing the point problem of failing
the insert as ora-600 instead of trapping it as a specific error has been
ignored as each “don’t let it happen in the first place” fix has appeared. Once
the customer’s problem is solved the SR is closed, so the general issue of
cleaning up the blocks before returning them to the free list on any internally
aborted direct insert has not been addressed.
I have not been able to reproduce the issue by manually terminating a direct
insert, so the normal case is apparently already handled just fine. Indeed it
may be only a few corner cases that fail to do the cleanup.
No data loss is associated with the problem, but you can get into a situation
where inserts fail when new freespace with a declaration of corrupt blocks.
Since any insert after that point can potentially trigger the issue and a scan
of the objects associated with the insert report a clean slate, the issue is
not obvious unless you happen to think of it.
Anyway, freespace scanners and cleaners would be useful, so if you know of any
I’m all ears.
I also look forward in case Tim has an opportunity to write a comprehensive
answer to your actual question.
mwf
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On ;
Behalf Of Ashoke Mandal
Sent: Monday, June 05, 2017 3:50 PM
To: tim.evdbt@xxxxxxxxx
Cc: ORACLE-L
Subject: 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@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
