detect corrupted block

[stressman]

Hello everybody,
Do you a know if there is a way to find out if Oracle database has corrupted block by the way of a query?
not reading each morning the log files ?

Thanks,

 

[SantaMufasa]

Stressman,

Here is a method for checking the physical integrity of a database file. Since you didn't mention the platform upon which you are running, I'll presume Unix.

First, confirm the filesystem's blocksize; from a Unix command prompt, issue these commands:

df -g <file system containing the db file to check>
(for example)
[b]$ df -g /dbsrv/oracle/data1[/b]
/dbsrv/oracle/data1(/dev/vx/dsk/datadg/oradata1):     [b]8192 block size[/b]          1024 frag size
24772216 total blocks    8415860 free blocks  8415860 available        1542144 total files
 1540979 free files     60395124 filesys id
     ufs fstype       0x00000004 flag             255 filename length

Then, check a specific file's physical integrity (using the blocksize information from above) with the database-verify ("dbv") command. ("yada.txt" is the file to which I piped the results.):

$ dbv file=system01.dbf blocksize=8192 > yada.txt

DBVERIFY: Release 9.2.0.4.0 - Production on Wed Aug 24 11:08:01 2005

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

DBVERIFY - Verification starting : FILE = ./system01.dbf


DBVERIFY - Verification complete

Total Pages Examined         : 23040
Total Pages Processed (Data) : 14986
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 5031
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 1173
Total Pages Processed (Seg)  : 0
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 1850
Total Pages Marked Corrupt   : 0
Total Pages Influx           : 0

Let us know if this meets your need.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[stressman]

I sorry for the lack of information i gave.
The database is running under Win2K Server.

Thanks Mufasa have you got a same tips under windows

 

[SantaMufasa]

Stressman,

Sure, you can run Oracle's "dbv" program on Windows, as well. Since I don't know (off the top of my head) how to confirm the blocksize of a Windows machine, you can just run "dbv" without the blocksize (which defaults to 2048). If your actual machine blocksize differs from 2048, then dbv throws an error telling you what the blocksize actually is:

D:\oracle\ora92\bin>dbv file=D:\DHUNT\ORACLEDATA\DBDATA\DHUNT\DHUNTDATA201.DBF

DBVERIFY: Release 9.2.0.4.0 - Production on Thu Aug 25 09:37:03 2005

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


DBV-00103: Specified BLOCKSIZE (2048) differs from actual (8192)

Then, you can re-run the command using the designated blocksize:

*********************************************************************************************
D:\oracle\ora92\bin>dbv file=D:\DHUNT\ORACLEDATA\DBDATA\DHUNT\DHUNTDATA201.DBF blocksize=8192

DBVERIFY: Release 9.2.0.4.0 - Production on Thu Aug 25 09:37:57 2005

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

DBVERIFY - Verification starting : FILE = D:\DHUNT\ORACLEDATA\DBDATA\DHUNT\DHUNTDATA201.DBF


DBVERIFY - Verification complete

Total Pages Examined         : 29440
Total Pages Processed (Data) : 9891
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 6399
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 114
Total Pages Processed (Seg)  : 0
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 13036
Total Pages Marked Corrupt   : 0
Total Pages Influx           : 0

Let us know if this resolves your need on Windows.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.