Re: Corrupt block in DBVERIFY
Jared and Ron, As Jared mentioned, dbv can incorrectly report corrupt blocks on an open datafile, although this is likely to happen only if there is heavy write activity to the datafile and dbv scans a block as it is being written. In which case scanning the file when the db is closed or copying the datafile before scanning it should work. The pages listed by dbv are eqivalent to the blocks in the datafile, so if dbv reports page 12345 is corrupt, then that's the same as block 12345. Dbv scans *all* blocks in the datafile, not just those belonging to objects. Versions 8.1.6+ will report Found block already marked corrupted if the block doesn't belong to an existing object. These blocks will also not be counted in the stats for the file. These corruptions can be safely ignored as the corrupt block will be reformatted when it is allocated to an extent. In any case, you can identify the object to which the corrupt block belongs (if it's currently allocated to an object) using the following query: SELECT tablespace_name, segment_type, owner, segment_name FROM dba_extents WHERE file_id = file# and block# between block_id AND block_id + blocks - 1; Where file# is the absolute file # (e.g. from V$DBFILE) and block# is the page# reported corrupt. If only the DBA is given then, as someone else mentioned, used note: 113005.1 to convert the DBA to a file and block #. I always recommend running dbv and ATVSC (analyze table validate structure cascade) multiple times as flaky hardware will often manifest itself as intermittent block corruptions (i.e. if a block is truly corrupt it should be reported as corrupt every time it is checked). HTH, -- Anita --- [EMAIL PROTECTED] wrote: Ron, Was the database open when you ran dbverify? If so, run it on the file again and see if it still reports corruption. I've done this a number of times on an open database. When a corruption was found, running dbverify a second time would show that the file was ok. The only sure way to run dbverify is on a closed database. Jared Smith, Ron L. To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] [EMAIL PROTECTED] cc: om Subject: Corrupt block in DBVERIFY Sent by: [EMAIL PROTECTED] om 07/09/01 09:51 AM Please respond to ORACLE-L I have a corrupt block according to DBVERIFY. The block is on an index so I can rebuild it but I don't know how to find the object with the information displayed in DBVERIFY. Can anyone tell me how to find the object? Thanks! Ron -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Smith, Ron L. INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (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 also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051
Re: Corrupt block in DBVERIFY
The workaround that first comes to my mind is to take
all the objects of that tablespace and do an analyze
.validate structure. Take into account that the
validate structure clause locks the object. This could
be a script for that:
set pagesi 0
set feedba off
spool validate.sql
select 'analyze '||segment_type||'
'||owner||'.'||segment_name||' validate structure;'
from sys.dba_segments where
tablespace=upper('tbsp.');
spool off
set feedba on
set echo on
set pagesi 25
spool validate.out
@validate.sql
spool off
You could improve it changing the query from
dba_segments. That it is an slowly view.
Regards.-
--- Smith, Ron L. [EMAIL PROTECTED] wrote:
I have a corrupt block according to DBVERIFY. The
block is on an index so I
can rebuild it but I don't know how to find the
object with the information
displayed in DBVERIFY. Can anyone tell me how to
find the object?
Thanks!
Ron
--
Please see the official ORACLE-L FAQ:
http://www.orafaq.com
--
Author: Smith, Ron L.
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX:
(858) 538-5051
San Diego, California-- Public Internet
access / Mailing Lists
To REMOVE yourself from this mailing list, send an
E-Mail message
to: [EMAIL PROTECTED] (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
also send the HELP command for other information
(like subscribing).
=
Eng. Christian Trassens
Senior DBA
Systems Engineer
[EMAIL PROTECTED]
[EMAIL PROTECTED]
Phone : 541149816062
__
Do You Yahoo!?
Get personalized email addresses from Yahoo! Mail
http://personal.mail.yahoo.com/
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Christian Trassens
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (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
also send the HELP command for other information (like subscribing).
Re: Corrupt block in DBVERIFY
Jared and Ron, As Jared mentioned, dbv can incorrectly report corrupt blocks on an open datafile, although this is likely to happen only if there is heavy write activity to the datafile and dbv scans a block as it is being written. In which case scanning the file when the db is closed or copying the datafile before scanning it should work. The pages listed by dbv are eqivalent to the blocks in the datafile, so if dbv reports page 12345 is corrupt, then that's the same as block 12345. Dbv scans *all* blocks in the datafile, not just those belonging to objects. Versions 8.1.6+ will report Found block already marked corrupted if the block doesn't belong to an existing object. These blocks will also not be counted in the stats for the file. These corruptions can be safely ignored as the corrupt block will be reformatted when it is allocated to an extent. In any case, you can identify the object to which the corrupt block belongs (if it's currently allocated to an object) using the following query: SELECT tablespace_name, segment_type, owner, segment_name FROM dba_extents WHERE file_id = file# and block# between block_id AND block_id + blocks - 1; Where file# is the absolute file # (e.g. from V$DBFILE) and block# is the page# reported corrupt. If only the DBA is given then, as someone else mentioned, used note: 113005.1 to convert the DBA to a file and block #. I always recommend running dbv and ATVSC (analyze table validate structure cascade) multiple times as flaky hardware will often manifest itself as intermittent block corruptions (i.e. if a block is truly corrupt it should be reported as corrupt every time it is checked). HTH, -- Anita --- [EMAIL PROTECTED] wrote: Ron, Was the database open when you ran dbverify? If so, run it on the file again and see if it still reports corruption. I've done this a number of times on an open database. When a corruption was found, running dbverify a second time would show that the file was ok. The only sure way to run dbverify is on a closed database. Jared Smith, Ron L. To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] [EMAIL PROTECTED] cc: om Subject: Corrupt block in DBVERIFY Sent by: [EMAIL PROTECTED] om 07/09/01 09:51 AM Please respond to ORACLE-L I have a corrupt block according to DBVERIFY. The block is on an index so I can rebuild it but I don't know how to find the object with the information displayed in DBVERIFY. Can anyone tell me how to find the object? Thanks! Ron -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Smith, Ron L. INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (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 also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051
Corrupt block in DBVERIFY
I have a corrupt block according to DBVERIFY. The block is on an index so I can rebuild it but I don't know how to find the object with the information displayed in DBVERIFY. Can anyone tell me how to find the object? Thanks! Ron -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Smith, Ron L. INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (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 also send the HELP command for other information (like subscribing).
RE: Corrupt block in DBVERIFY
Ron, I have not done this myself yet...but had searched for it in the past... If DBVERIFY is reporting Database Addresses (DBA) as corrupt, then you will have to convert those to file# and block# and from there you can query the DBA_EXTENTS view to get the object name. There is a Note #113005.1 on Metalink that lists a script to convert DBA to file# and block#. Give it a try and let me know if that works. Hope this helps. Regards, - Kirti Deshpande Verizon Information Services http://www.superpages.com -Original Message- From: Smith, Ron L. [SMTP:[EMAIL PROTECTED]] Sent: Monday, July 09, 2001 11:51 AM To: Multiple recipients of list ORACLE-L Subject: Corrupt block in DBVERIFY I have a corrupt block according to DBVERIFY. The block is on an index so I can rebuild it but I don't know how to find the object with the information displayed in DBVERIFY. Can anyone tell me how to find the object? Thanks! Ron -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Smith, Ron L. INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (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 also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Deshpande, Kirti INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (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 also send the HELP command for other information (like subscribing).
Re: Corrupt block in DBVERIFY
I think you can run an analyze index validate structure on all the indexes in the tablespace associated with that datafile. Once you identify the corrupted index(es), you can drop/recreate them. Jim I have a corrupt block according to DBVERIFY. The block is on an index so I can rebuild it but I don't know how to find the object with the information displayed in DBVERIFY. Can anyone tell me how to find the object? Thanks! Ron -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Smith, Ron L. INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (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 also send the HELP command for other information (like subscribing). -- Jim Hawkins Lead SAPR/3 Oracle Database Administrator MEMC Electronic Materials, Inc. 600 Pearl Drive St. Louis, MO 633376 9636) 474-7832 [EMAIL PROTECTED] (work) [EMAIL PROTECTED] (home) -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jim Hawkins INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (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 also send the HELP command for other information (like subscribing).
Re: Corrupt block in DBVERIFY
I think you can run an analyze index validate structure on all the indexes in the tablespace associated with that datafile. Once you identify the corrupted index(es), you can drop/recreate them. Jim I have a corrupt block according to DBVERIFY. The block is on an index so I can rebuild it but I don't know how to find the object with the information displayed in DBVERIFY. Can anyone tell me how to find the object? Thanks! Ron -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Smith, Ron L. INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (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 also send the HELP command for other information (like subscribing). -- Jim Hawkins Lead SAPR/3 Oracle Database Administrator MEMC Electronic Materials, Inc. 600 Pearl Drive St. Louis, MO 633376 9636) 474-7832 [EMAIL PROTECTED] (work) [EMAIL PROTECTED] (home) -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jim Hawkins INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (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 also send the HELP command for other information (like subscribing).
Re: Corrupt block in DBVERIFY
Ron, Was the database open when you ran dbverify? If so, run it on the file again and see if it still reports corruption. I've done this a number of times on an open database. When a corruption was found, running dbverify a second time would show that the file was ok. The only sure way to run dbverify is on a closed database. Jared Smith, Ron L. To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] [EMAIL PROTECTED] cc: om Subject: Corrupt block in DBVERIFY Sent by: [EMAIL PROTECTED] om 07/09/01 09:51 AM Please respond to ORACLE-L I have a corrupt block according to DBVERIFY. The block is on an index so I can rebuild it but I don't know how to find the object with the information displayed in DBVERIFY. Can anyone tell me how to find the object? Thanks! Ron -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Smith, Ron L. INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (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 also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (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 also send the HELP command for other information (like subscribing).
Re: Corrupt block in DBVERIFY
[EMAIL PROTECTED] wrote: Ron, Was the database open when you ran dbverify? If so, run it on the file again and see if it still reports corruption. I've done this a number of times on an open database. When a corruption was found, running dbverify a second time would show that the file was ok. The only sure way to run dbverify is on a closed database. Jared or on a file from a backup set ;) -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Paul Drake INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (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 also send the HELP command for other information (like subscribing).
