Possible file corruption

[Peter Day_3]

Guys,
I have a bit of a problem with an Oracle .dbf file. Basically we can't access this file, compress, fbackup & cp all hang when you try to use it. The database is also having a few problems.

I've checked out the filesystem using fsck -o full, and I've checked the disks, which all checked ok.

Is there anything else that I can check, or a utility that I can use on the file.

As always thanks for your help

Pete

[BONNAFOUS Jean Marc]

Hi Pete,

I have same problem with .dbf file and cp command, some months ago and it's due to hardware problem.
Try cpio to save file. If it doesn't work, it give errors unlike cp which sometime say nothing.

See also this thread: http://forums1.itrc.hp.com/service/forums/questionanswer.do?threadId=503114

Rgds
JMB

[Eric Antunes]

Peter,

If you are doing an online backup, you can see some informations on V$BACKUP, V$BACKUP_CORRUPTION and V$BACKUP_DATAFILE

Antunes

[Brian Markus]

Check to see if there is another process in memory using fuser. That process could be causing your issues.

Recovery like this is generally the function that makes a DBA earn his/her keep. There is an oracle tool called dbv or dbverify, however, it can be very dangerous to use. I highly recommend reading up on it prior to using it. It can cause corruption. Even then don't use it unless you have full backups.

I found this on metalink.oracle.com. You have to be a registered user to obtain it, so I copied it for you.

Hope this helps.

-Brian.


<>
<>>


<>=DBVERIFY%20-%20Database%20file%20Verification%20Utility%20(7.3.2%20-%208.1)
&p_database_id=NOT&p_document_id=35512.1&p_username=HPTHORN> Bookmark
<>1&p_font=> Fixed font
<>1&p_showHeader=1&p_showHelp=1#bottom> Go to End


Doc <> ID:
Note:35512.1
Subject: DBVERIFY - Database file Verification Utility (7.3.2 - 8.1)

Type: REFERENCE
Status: PUBLISHED
Content Type: TEXT/PLAIN
Creation Date: 14-AUG-1997
Last Revision Date: 27-APR-2004
Introduction


~~~~~~~~~~~~


This article describes the basic details of the DBVERIFY (or DBV)


utility which can be used to check Oracle datafiles for signs of


corruption. The article gives summary details of how to use


DBV and gives an indication of what output to expect, along with


notes on how to interpret the output. There is also an example at the


end of the article.





Availability


~~~~~~~~~~~~


The DBV utility is supplied with Oracle7 release 7.3.2 onwards and


with all Oracle8 / 8i releases.





DBV can be used against data files from earlier Oracle releases


but it must be executed from the ORACLE_HOME environment in which it


is installed - you CANNOT just copy the executable about.


Eg: DBVERIFY 7.3 can check Oracle 7.1 data files provided it is run


from the 7.3 $ORACLE_HOME


Purpose


~~~~~~~


DBV checks Oracle datafiles to ensure that:


- The datafile has a valid header


- Each datablock in the file has a special "wrapper" which
identifies


the block - this "wrapper" is checked for correctness


- DATA (TABLE) and INDEX blocks are internally consistent


- From 8.1.6 onwards: That various other block types are internally



consistent (such as rollback segment blocks)





The tool can be used to give some degree of confidence that a


datafile is free from corruption. It opens files in a read only mode


and so cannot change the contents of the file being checked.





Usage


~~~~~





DBV can be run against datafiles which are currently opened by a


database instance - there is no need to shutdown the database.


Datafiles are opened read-only by DBV so it cannot corrupt


the contents. There was a bug on 8.0.4 where DBV could not be used


on opened datafiles on Windows NT but that bug was fixed on 8.1.6.


Bug:727547
<>27547&p_database_id=BUG>





Unix:


Any release: dbv FILE=filename [options]





Windows NT:





7.3: DBVERF73 FILE=filename [options]


8.0: DBVERF80 FILE=filename [options]


8.1: DBV FILE=filename [options]





VMS:


On VMS systems DBV cannot be used against files which are currently


opened by an instance.





Any release: DBV FILE=filename [options]








MVS:


7.3: Does not exist


8.0/8.1: DBV FILE=/DSN/filename








Options:


Keyword Description Meaning


--------- ------------------ -----------------


FILE File to Verify This is the name of the file to verify.



See "Limitations" below if your
datafile


name has no suffix.


START Start Block This is the first datablock to check in



the file. This defaults to the first


block in the file and need only be


specified if you want to check just


a portion of a given file.


END End Block This is the last datablock to check in
the


file. This defaults to the last block
of


the file but may need specifying for
RAW


devices (See "Limitations" below)


BLOCKSIZE Logical Block Size This is the database block size of the


datafile you wish to scan. The value


defaults to "2048".


This parameter must be set to the


DB_BLOCK_SIZE of the datafile to be


scanned.


LOGFILE Output Log This is the name of file to output the


results to. The default is "NONE" and


output is sent to terminal.


FEEDBACK Display Progress If set to a value above 0 (the default)



then DBV outputs a "." for every N
pages


of the datafile checked. This is useful



to see that DBV is working through the


file.


PARFILE Parameter file Parameters can be specified in a


parameter file and PARFILE used to
cause


the file contents to be used as input


parameters. The PARFILE can contain any



of the above options.





For help on command line parameters in a given version type


"dbv help=y" at the command line.








Limitations and Special Notes


~~~~~~~~~~~~~~~~~~~~~~~~~~~~~


- As DBV performs checks at a block level it cannot detect problems


such as INDEX versus TABLE mismatches which can be detected by the


'ANALYZE TABLE .. VALIDATE STRUCTURE CASCADE' command.





- This utility can ONLY be used against DATA files.


It CANNOT be used to verify redo log files nor control files.





- On most releases on Unix DBV expects a filename extension.


This means that DBV cannot be used against datafiles with no


filename suffix, or against RAW devices.


The workaround is to create a symbolic link to the raw device where


the link name MUST have an extension.


Eg: ln -s /dev/rdsk/mydevice /tmp/mydevice.dbf


Now use DBV against /tmp/mydevice.dbf





- For RAW devices you should use the END parameter to avoid running


off the end of the Oracle file space.


eg: "dbv FILE=/dev/rdsk/r1.dbf END="


If you get the END value too high DBV can report the last page/s of the



file as corrupt as these are beyond the end of the Oracle portion of


the raw device.





You can find value for END from the V$DATAFILE view by dividing the


BYTES value by the database block size.





Eg: To find out the END value to use for file#=5:



SVRMGRL> show parameter db_block_size


NAME TYPE VALUE


----------------------------------- ------- ------


db_block_size integer 2048





SVRMGRL> select BYTES/2048 from v$datafile where FILE#=5;


BYTES/2048


----------


5120





So the command would be:





dbv file=/dev/rdsk/r1.dbf blocksize=2048 END=5120





- DBV may not be able to scan datafiles larger than 2Gb and


may report "DBV-100". This is reported in [BUG:710888]
<>10888&p_database_id=BUG> for Unix and


[BUG:1372172]
<>372172&p_database_id=BUG> for 8.1.6 on NT. This problem is platform and
release


specific so if you get DBV-100 errors check the filesize first.





- DBV from 8.1.6 onwards may report spurious errors for rollback segment


blocks if the database has been migrated from Oracle7. See [BUG:1359160]
<>359160&p_database_id=BUG>


and [NOTE:118008.1]
<>18008.1&p_database_id=NOT> .





- DBV only checks a block in isolation - it does not know if the block


is part of an existing object or not.





- DBV is broken on SCO Unix - see [BUG:814249]
<>14249&p_database_id=BUG>








Example Output


~~~~~~~~~~~~~~


$ dbv file=users01.dbf blocksize=2048





DBVERIFY - Verification starting : FILE = users01.dbf


kdbchk: a row ends in the middle of another


tab=0 slot=1 begin=0x7a0 len=0x14


Page 3 failed with check code 5


Page 10 is marked software corrupt


Page 12 is marked software corrupt





DBVERIFY - Verification complete





Total Pages Examined : 512


Total Pages Processed (Data) : 1


Total Pages Failing (Data) : 1


Total Pages Processed (Index): 0


Total Pages Failing (Index): 0


Total Pages Empty : 507


Total Pages Marked Corrupt : 2


Total Pages Influx : 0





If the utility reports any pages to be 'Marked Corrupt' or 'Failing' then


re-run the command to see if the problem is transient or not. If there


are still corruptions reported then contact your local support centre


for advice.








Interpreting the Output


~~~~~~~~~~~~~~~~~~~~~~~


If any pages report an error then contact Oracle support with the output.


This section gives a brief overview of the meaning of the main output


lines from the above output.





~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~


"Page N failed with check code Y" errors


~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~


This implies the block wrapper is correct but the content of the block



failed one of the many internal consistency checks.


The "Page" number in the output is the database block number within
the


file.


eg: kdbchk: a row ends in the middle of another


tab=0 slot=1 begin=0x7a0 len=0x14


Page 3 failed with check code 5





This sort of corruption on a block is most likely to cause one of the



following problems when accessed:


- ORA-600 errors


- Core dump (ORA-7445)


- Corrupt data to be returned


If block checking is enabled on the database then an update to the


block may mark the block as corrupt (ORA-1578) or may just crash


the session.





~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~


"Page N is marked software corrupt" or


"Page N is marked media corrupt" or


"Page N is marked corrupt"


~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~


This means the block wrapper is incorrect.


Oracle7 does not dump any additional information so the form of the


corruption is difficult to tell just from DBV. Oracle8 dumps details


of the cache wrapper.


The "Page" number in the output is the database block number within
the


file.





eg: Page 10 is marked corrupt


***


Corrupt block relative dba: 0x04c0000a file=0. blocknum=10.


Bad header found during dbv:


Data in bad block - type:6. format:2. rdba:0x04c000ff


last change scn:0x056c.ce87bf25 seq:0x1 flg:0x00


consistency value in tail 0xbf250601


check value in block header: 0x0, check value not calculated


spare1:0x0, spare2:0x0, spare2:0x0





This sort of corruption on a block will show up as an ORA-1578


when the block is accessed.








~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~


"Block Checking: DBA = 67108867, Block Type = Undo data block"


~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~


This form of error can be reported by DBV from 8.1.6 onwards if it


sees corrupt rollback segment blocks. If the database has been


migrated from Oracle7 then these errors may be spurious if DBV


sees an Oracle7 format rollback segment block.


See [BUG:1359160]
<>359160&p_database_id=BUG> and [NOTE:118008.1]
<>18008.1&p_database_id=NOT> for more details.





If this is a real problem (and not due to an Oracle7 to 8 migration


as described above) then this sort of corruption is most likely to


cause one of the following problems when accessed:


- ORA-600 errors


- Core dump (ORA-7445)


- Corrupt data





~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~


Summary lines


~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~


When DBV completes it outputs a summary of the form below:





Output text Meaning


~~~~~~~~~~~ ~~~~~~~


Total Pages Examined : 512 Number of blocks looked at


Total Pages Processed (Data) : 1 Number of TABLE blocks seen


Total Pages Failing (Data) : 1 Number of TABLE blocks with


internal inconsistencies


Total Pages Processed (Index): 0 Number of INDEX blocks seen


Total Pages Failing (Index): 0 Number of INDEX block with


internal inconsistencies


Total Pages Empty : 507 Number of unused blocks seen



Total Pages Marked Corrupt : 2 Number of blocks with
corrupt


cache wrappers


Total Pages Influx : 0 Number of pages we re-read


as the page looked like it


was being modified when it



was first read.








What to do if DBV shows problems on a file


~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~


If DBV reports any pages as 'Marked Corrupt' or 'Failing' then it is


advisable to re-run the command to see if the problem is transient or not.



Continual transient problems are often caused by a faulty disk controller


and to the first step should be the check the disk subsystem.





If DBV consistently reports errors in the same location then the file


contains corrupt block/s:





If there are very many errors it usually best to assume the


file is bad and look for backups of the file which could be
recovered.





If there are only a few bad blocks then you need to note down:





- The filename





- The absolute file number of this file


( Use "SELECT file#, name FROM V$DATAFILE;" to find this )






- The block number of the bad block/s


( This is the same as the "Page" number in the DBV output
)





- The type of error on the block


If the block would signal an ORA-1578 error in Oracle


there are more options to get around the corruption but


the content of the block is lost. If the block is


internally inconsistent there are less options to get


around the corruption but some rows in the block may


be accessible.





Once you know the above details see [NOTE:28814.1]
<>8814.1&p_database_id=NOT> which describes


how to determine which object is corrupt and what you can do about


it. Use the absolute FILE# for and the page number for in


that article. Note that it is quite possible that the corrupt block


does not belong to any current object in which case the corruption


can be ignored.


*** IMPORTANT *** If the file you have DBVed is from a backup


then you cannot use the current data dictionary


to determine which object/s have a problem as


the block may have been reused by a different


object. (eg: Original table dropped)








Example


~~~~~~~


1) Log into svrmgrl to find out block size for your databae and datafile


locations and names:





$ svrmgrl





Oracle Server Manager Release 3.1.5.0.0 - Production


(c) Copyright 1997, Oracle Corporation. All Rights Reserved.





Oracle8i Enterprise Edition Release 8.1.5.0.0 - Production


With the Partitioning and Java options


PL/SQL Release 8.1.5.0.0 - Production





SVRMGR> connect internal


Connected.


SVRMGR> show parameter db_block_size


NAME TYPE VALUE


----------------------------------- ------- -----------------------



db_block_size integer 2048





SVRMGR> select file#,name,bytes/2048 from v$datafile;


FILE# NAME BYTES/2048


----- --------------------------------------- ----------


1 /u02/oradata/R815/oradata/R815/system01.dbf 40960


2 /u02/oradata/R815/oradata/R815/rbs01.dbf 7680


3 /u02/oradata/R815/oradata/R815/temp01.dbf 5120


4 /u02/oradata/R815/oradata/R815/users01.dbf 67454


5 /u02/oradata/R815/oradata/R815/indx01.dbf 5120


...





2) Run dbv against any files you want to check:





$ dbv file=/u02/oradata/R815/oradata/R815/users01.dbf blocksize=2048
logfile=users01_dbv.log feedback=100





DBVERIFY: Release 8.1.5.0.0 - Production on Tue Mar 21 15:05:35 2000



(c) Copyright 1999 Oracle Corporation. All rights reserved.



............................................................................
.......





$ cat users01_dbv.log





DBVERIFY: Release 8.1.5.0.0 - Production on Tue Mar 21 15:05:35 2000



(c) Copyright 1999 Oracle Corporation. All rights reserved.





DBVERIFY - Verification starting : FILE =
/u02/oradata/R815/oradata/R815/users01.dbf





DBVERIFY - Verification complete


Total Pages Examined : 67454


Total Pages Processed (Data) : 29310


Total Pages Failing (Data) : 0


Total Pages Processed (Index): 3425


Total Pages Failing (Other): 294


Total Pages Empty : 34425


Total Pages Marked Corrupt : 0


Total Pages Influx : 0








References:


~~~~~~~~~~~


Oracle Server Utilities Guide


.

_____


<>if> Copyright (c) <> 1995,2000
Oracle Corporation. All Rights Reserved.
<> Legal
Notices and Terms of Use.
<>gif>