| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: quckways to find block corruption
Royce,
Just small correction
It is
Analyze table table_name validate structure cascade;
which checks for corruption of table and indexes based on that table and error is written in alertSID log as well as producing trace file. In my experience to check table/index level corruption this is is the best way but a very resource intensive and time consuming if table is large and put exclusive lock on table (prior to 9i) as it has no ONLINE option.......
Regards
Rafiq
Reply-To: ORACLE-L_at_fatcity.com
To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
Date: Mon, 23 Sep 2002 08:58:42 -0800
Agreed ..... though I have had disappointing results with 'ANALYZE ... VALIDATE STRUCTURE ...' not detecting corruption .... only later to have an application (i.e. user) encounter data block (or index block) corruption. My last incidence ... I simply did a count(*) .. full table scan ... which narrowed down the specific table involved. See Alert.log and sql dialog
below ....................... As a quick check for corruption ... I don'tsee an easy way to get around exercising block-by-block reads. I also would be interested in alternative methods. My databases are smaller (than the 500 Gbytes) than that of Srinivas' ..... but I am supporting a LINUX --> NettApps environment that will occasionally 'hang' and upon restart (reboot of OS) I have been 'honored' with 'Fractured block found during media/instance recovery' ...... Thanks for further input and comments ........
Recovery of Online Redo Log: Thread 1 Group 1 Seq 3974 Reading mem 0
Mem# 0 errs 0: /u001/oradata/rhyp01/redo01.log
***
Corrupt block relative dba: 0x0200f242 (file 8, block 62018)
Fractured block found during media/instance recovery
Data in bad block -
type: 6 format: 2 rdba: 0x0200f242
last change scn: 0x04e3.814c2697 seq: 0x1 flg: 0x02
consistency value in tail: 0x1ec20603
check value in block header: 0x0, block checksum disabled
spare1: 0x0, spare2: 0x0, spare3: 0x0
***
Reread of rdba: 0x0200f242 (file 8, block 62018) found same corrupted data
Sun Sep 15 07:06:11 2002
Thread recovery: finish rolling forward thread 1
Thread recovery: 5409 data blocks read, 3710 data blocks written, 50221 redo
blocks read
Crash recovery completed successfully
Sun Sep 15 07:06:11 2002
On RESTART:
Sun Sep 15 10:24:30 2002
Database mounted in Exclusive Mode.
Completed: alter database mount
Sun Sep 15 10:24:30 2002
alter database open
Sun Sep 15 10:24:31 2002
Thread 1 opened at log sequence 3975
Current log# 2 seq# 3975 mem# 0: /u001/oradata/rhyp01/redo02.log
Successful open of redo thread 1.
Sun Sep 15 10:24:31 2002
SMON: enabling cache recovery
SMON: enabling tx recovery
Sun Sep 15 10:24:33 2002
Completed: alter database open
I then checked all tables in 'file 8' until corruption was detected
(below is excerpt of results)
Sql+ rhyp01>
select count(*) from RSRYP.MDB_LOTS
*
Thnx & OK Bye
To me, it's a good idea to always carry
two sacks of something when you walk
around. That way, if anybody says,
"Hey, can you give me a hand?" ... ..
You can say, "Sorry, got these sacks"
- Jack Handey
Chris Royce
Oracle DBA
(602) 244 3735
chris_at_royce.net
ffm9np_at_Onsemi.com <mailto:ffm9np_at_Onsemi.com>
-----Original Message-----
WILLIAMS
Sent: Monday, September 23, 2002 9:08 AM
To: Multiple recipients of list ORACLE-L
Srinivas
I think that as a minimum, to detect corruption you must read all data blocks. There are several methods, most involve an Oracle process that will error if a corrupt block is encountered. Export to /dev/null was mentioned already. I have used ANALYZE TABLE VALIDATE STRUCTURE CASCADE, which will also check out the indexes and verify they point to good blocks, but is quite resource intensive. If you use RMAN to back up your database, then as a bonus it always verifies your data blocks and will error out when it encounters a bad block.
I haven't used dbv personally, but assume that dbv was one of the most efficient methods of checking your datafiles since it runs outside Oracle and therefore doesn't tie up your Oracle resources. Perhaps you can spell out the problems you are encountering with dbv and what you are trying to accomplish in more detail.
Dennis Williams
DBA
Lifetouch, Inc.
dwilliams_at_lifetouch.com <mailto:dwilliams_at_lifetouch.com>
-----Original Message-----
Sent: Monday, September 23, 2002 5:03 AM
To: Multiple recipients of list ORACLE-L
Hello all,
DB: 8i
OS: solaris 2.7
can somebody post me reply for this.
is there any quick way to find which datablocks are corrupted in my oracle database .
( other than dbverify and rman backup. )
b'coz we have BCV backup already implemented and we cannot do a dbv every week for 500 gig production database .
thanks in advance,
srinivas
-- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: kommareddy sreenivasa INET: kommacnu_at_yahoo.com Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-LReceived on Mon Sep 23 2002 - 12:58:32 CDT
(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: DENNIS WILLIAMS INET: DWILLIAMS_at_LIFETOUCH.COM Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (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). << winmail.dat >> _________________________________________________________________ MSN Photos is the easiest way to share and print your photos: http://photos.msn.com/support/worldwide.aspx -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mohammad Rafiq INET: rafiq9857_at_hotmail.com Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (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).
 |
 |