• Find us:
    +1-669-900-5138   |   +44-203-372-5553
  • Free Newsletter

    Get Latest Updates

  • Make Training Enquiry


    Company

  • Categories

  • Archive

  • How to check Corruption in Database Using DBVERIFY

    Posted by "" in "database" on 2008-07-03

    Share on FacebookTweet about this on TwitterShare on Google+Share on LinkedInPin on PinterestEmail this to someone

    The primary tool for checking for corruption in an Oracle database is DBVERIFY. It can be used to perform a physical data structure integrity check on data files whether the database is online or offline. The big benefit of this is that DBVERIFY can be used to check backup data files without adding load to the database server. You invoke DBVERIFY from the operating system command line like this:
    $ dbv file=data01.dbf logfile=verify.log blocksize=8192 feedback=100
    In this example data01.dbf is the data file to check, and the tablespace this file belongs to has a block size of 8192 bytes. The feedback parameter tells DBVERIFY to draw a period on the screen after every 100 pages (blocks) of the file are verified.
    In the log file you’ll see output like this:

    DBVERIFY – Verification starting : FILE = data01.dbf

    DBVERIFY – Verification complete

    Total Pages Examined : 640
    Total Pages Processed (Data) : 631
    Total Pages Failing (Data) : 0
    Total Pages Processed (Index): 0
    Total Pages Failing (Index): 0
    Total Pages Processed (Other): 9
    Total Pages Empty : 0
    Total Pages Marked Corrupt : 0
    Total Pages Influx : 0
    The Total Pages Failing values show the number of blocks that failed either the data block or index block checking routine. The Total Pages Marked Corrupt figure shows the number of blocks for which the cache header is invalid, thereby making it impossible for DBVERIFY to identify the block type. And the Total Pages Influx is the number of blocks for which DBVERIFY could not get a consistent image. (This could happen if the database is open when DBVERIFY is run. DBVERIFY reads blocks multiple times to try to get a consistent image, but DBVERIFY cannot get a consistent image of pages that are in flux.)
    If you want to verify only a portion of a data file, you can specify a starting and ending block when running DBVERIFY. If you want to verify the entire database, you can generate a short shell script to run DBVERIFY on every data file in the database. You can do this easily using SQL*Plus:

    SQL> SPOOL dbv_on_all_files.sh
    SQL> SELECT ‘dbv file=’ || file_name ||
    2 ‘ logfile=file’ || ROWNUM ||
    3 ‘.log blocksize=8192′
    4 FROM dba_data_files;
    SQL> SPOOL OFF
    After running the shell script you can quickly scan all of the DBVERIFY log files with Unix commands like:
    $ grep Failing file*.log
    $ grep Corrupt file*.log
    $ grep Influx file*.log
    You can also use DBVERIFY to validate a single data or index segment. To do this you must be logged onto the database with SYSDBA privileges. During the verification the segment is locked; if the segment is an index then the parent table is also locked.
    There are other ways to check for database corruption besides DBVERIFY. You can take a full database export, with the dump file optionally specified as a null device. This will read every row in every user table in the database, discovering any corrupted data blocks along the way. However, this technique does not access every index entry or the entire data dictionary.

    If you want to check one table and all of its indexes, you can use the ANALYZE statement to read every row of the table, read every entry in each of the table’s indexes, and make sure the table and index data are consistent with each other:

    SQL> ANALYZE TABLE emp VALIDATE STRUCTURE CASCADE;
    This will lock the table, preventing DML on the table, unless you specify the ONLINE keyword. Online validation reduces the amount of validation performed to allow for concurrency.
    There are several ways to check for corruption in an Oracle database, but the DBVERIFY tool is the most versatile. DBVERIFY does not limit concurrency or DML while it is running, and it can be run against a database backup. Just remember that if DBVERIFY detects corruption in your database and you are planning to recover the corrupt file from a backup, you should perform a DBVERIFY validation on the backup file before beginning the recovery. This will tell you if the corruption exists in the backup also.

    16 Responses to “How to check Corruption in Database Using DBVERIFY”

    1. prajwal says:

      excellent topic..
      rajat..I appreciate your knowledge…..

    2. anonnymous says:

      Rajat,

      I have an issue of block corruption with my system tablespace and impacted table is part of sys schema i.e, argument$.

      I open a service request with Oracle support and they said they can not help on this as manipulation with sys schema is not allowed and you need backup your data using export and import. I follwed all the steps same like your post also ran the admin repair package but not resolved. As you have very good experience with block experience can you please help me.

    3. mohammed says:

      Good one,

      Thanks Rajat,

      Mohammed Yousuf
      Sr.Oracle DBA
      IBM, Bangalore.

    4. Rajat says:

      Hi ,

      I am not sure .. As per my knowledge … if system tables get coorupted we need to recover database from Hot backup/cold … But u can try this metallink Doc , that may help u .
      Doc ID: Note:68013.1

    5. anonnymous says:

      Rajat,

      I resolved the issue today. Thanks for metalink note , I already used that but no help.

      I closed my service request.

    6. Geetha M says:

      Hi Rajat

      Congratulations for an informative topic on db corruption.

      Why does db get corrupted, any specific reasons for it, if yes how could we avoid it in prior.

      Thanks in Advance.

      Rgds

    7. lakshmikanthan says:

      Hi Rajat
      Thanks for giving valuable information
      I congratulate to u
      Thanks
      lakshmikanthan

    8. Rajat says:

      Geetha M ,

      Its happen due to certain reasons some of them are System Memory ,Disk controller (Bad I/O hardware ,RAID controller),Disk Device (Problem with the device driver ).

      The best way to resolve those issue is keep the backup updated.

    9. Geetha M says:

      thank you Rajat!!

    10. Saurabh Sood says:

      Just to add, DBV only check for the physical corruptions in the database, not for logical corruptions.

      It will detect ora-1578 errors but will not be able to find ora-8103 errors which are due to logical corruption in database.

    11. Ramnik Gupta says:

      Hi All,
      My taken on this:

      Block corruption is while the data is being written to the data blocks, if the write to the block fails abruptly, which means there is a partial write in the block, may be because of power disruption or I/O problem, leaving no time for header to be updated, or row data to be populated, oracle leaves the block corrupt.In case of block corruption you can normally use the database unless you try to read that particular block, against which it shoots up the block corruption error.Generally block corruption occurs if write fails on the block, when the transaction is being committedYou can find detail information about block corruption in alert.log file

      block corruption can happens at

      1)Physical Level corruption (which means media corrupt)
      2)Logical Level corruption (which means soft corrupt)

      Physical corruption can be caused by defected memory boards, controllers or broken sectors on a hard disk;

      Logical corruption can among other reasons be caused by an attempt to recover through a NOLOGGING action.

      To Differetiate between both the corruption levels (logical and physical)

      Logical corruption is header – footer – that is one of the checks, yes (it is looking for fractured blocks and when it hits one, it’ll re-read it, that is why there is no need for “alter tablespace begin backup” with rman)

      Physical corruption is “we cannot read the block from disk, something is physically preventing us from doing so”

      The methods to detect Block corruptions are

      1) DBVerify utility
      2) Block checking parameters (DB_BLOCK_CHCEKSUM) – In Oracle 10g db_block_checksum value TYPICAL which should be TRUE
      and db_block_checking value FULL should be TRUE.
      3) ANALYZE TABLE tablename VALIDATE STRUCTURE CASCADE SQL statement
      4) RMAN BACKUP command with THE VALIDATE option

      Regards
      Ramnik Gupta

    12. Preetam says:

      Good One,Cheers

      Preetam dash
      TCS,kolkata

    13. Mohammed Saleem says:

      Good one,

      Thanks Rajat,

      Mohammed Saleem
      Sr Oracle DBA
      Sun Microsystems,Chennai

    14. pAnkAj says:

      Hi Rajat,
      Thanks for this valuable information, its really helpful. You are really good one.

      Best of Regards,
      Pankaj
      Oracle DBA,
      Emsang Technology, chennai

    15. Hi rajat sir,
      m very thank full to you b’caz of very important topic dbv, m ocp and m searching a oracle dba jobs.. M nt dba bt its very amzing topic and its gud for my knowledge.
      Thnx sir for gud topic nd experince.

    16. Salim Zaffar says:

      Thanks to Mr (Rajat and Ramnik) for sharing valuable info

    Leave a Reply



  • K21 Technologies is among the most experienced Oracle Gold Partner for Identity Access Management service providers. We work with application development companies and in-house technology division to help achieve significant returns on their IT security investment. Our clientele includes some of the globally renowned corporate, which speaks of our expertise in our field.

    We have the most talented and experienced team that can swiftly deploy security solutions even in complex IT ecosystem. Our clients highly appreciate our timely implementation, interactive training, on-demand support and community resources.
  • CONTACTS

    K21 Technologies
    8 Magnolia Place, Harrow,
    London, HA2 6DS

    UK: +44(0)7476444481
    USA: +1-888-414-1821

  • 2014, K21 Technologies. All rights reserved DMCA.com
  • TOP
    TOP