How to detect a corrupt table
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: How to detect a corrupt table

  1. #1
    Join Date
    Dec 2005
    Posts
    3

    How to detect a corrupt table

    I ran into a problem this week that I'm trying to understand and learn how to detect in the future.

    I'm a newbie to sql databases in general, So I will apologize up front.

    Earlier this week, a 9i box that is used to control a portion of my manufacturing floor began to respond slowly to several servers. The hardware stats look normal (low CPU and mem usage), but our application vendor thought it was response time issue.

    After 3 days of them troubleshooting, I was told that they found a corrupt table, and they had dropped and rebuilt it.

    Some of my questions are:

    • How would could I be pro-active and detect corrupt tables in the future?
    • Should my inside DBA been able to detect this?
    • Is it possible to leverage RMAN's VALIDATE procedure for this purpose?


    Thanks in advance

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    errrr whats a corrupt table? tables dont get corrupted

  3. #3
    Join Date
    Dec 2005
    Posts
    3
    >I'm a newbie to sql databases in general, So I will apologize up front.

    Thats what my application vendor blamed it on, and my oracle DBA seems to nod his head to.

    I've been researching the subject via google, and only find referances to corrupt databases such as block corruption. Which leads me to believe this is more related to hardware caused corruption.

    What I'm trying to understand is how I (or someone else) could have properly diagnose this problem.

    The problem was that the DB did not respond to some servers fast enough. CPU and memory were at their normal production run levels (so no increase to the load of the box). However it was deteremined that when certain process was running, oracle did not respond to the servers fast enough. When the process was shut down, the system sped up.

    The vendor said they found a table that the this process used that was corrupt. Hence when the process ran, and it queried the table it supposedly cause the system to slow down. They said they fixed it by dropping the table and rebuilding it.

    If its not possible for this to occur, then what would the possible explanations be?

  4. #4
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    well if its block corruption, an rman backup would of found it as it checks the datafiles.

    Or the dbv executable checks for it but most people dont run that as a matter of course

    only other way is to use the data in the corrupt blocks. If it tries to access it the users wil get an error and something will appear in the database alert log which the dba should check.

    So it depends on what really happened, if you had block corruption I think you'd know about it quickly as you users would complain.

    What were the symptons - just slow queries, if thats all i doubt it is block corruption

  5. #5
    Join Date
    Dec 2005
    Posts
    3
    Really that was it, just slow queries.

    The systems controls a conveyor belt with. The queries normally take 1-2 seconds, but during the problem it took 7 - 15 seconds. The delay cause the system to lose track of items on the converyor, and the conveyor has a fail-safe that shuts it down after X number of missing items.

    What concerns me is that what ever caused the slowness seemed to be hard to detect.

    I understand that several factors can effect the speed at which queries are executed (how efficent the querie is, hardware). But if its normal recorded exection is just 1-2 seconds, then it's not the querie itself. The only other thing that I know of would be hardware issues, which there were none.

    What else could cause a query to be slow?

  6. #6
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    Wrong execution plan?

  7. #7
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    table corruption is rubbish then, as pando said - you need to compare execution plans

  8. #8
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    The vendor said they found a table that the this process used that was corrupt. Hence when the process ran, and it queried the table it supposedly cause the system to slow down. They said they fixed it by dropping the table and rebuilding it.
    There could be several reasons for slowness of the system.
    It seems to be that the table had bad data.

    There are 3 levels of corruption.

    1. Logical corruption on data/index segment or data dictionary corruption. This can be easily identified by export of the table.

    2. Disk sector corruption - This will happen when the server has faulty disk. You need to replace the disk. You can use dbverify to cross check the data files integrity.

    3. Data corruption - The application loaded wrong data in the table.

    Tamil

  9. #9
    Join Date
    Oct 2005
    Location
    Indianapolis
    Posts
    100
    It's easy for vendors to blame problems on things like "corrupt data" or indexes that "needed rebuilding". It's usually from people who don't know what really happened, and such claims are hard to dispute.

    Could have been something as simple as missing statistics.

    I would trace the transactions involved, and save the (good) explain plans for future reference. And if it really was one process that was dragging the whole database down, it should have showed up quick in top sessions.
    "False data can act only as a distraction. Therefore. I shall refuse to perceive you." - Bomb #20

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width