Segment Checking Using DBV….

I have seen many people talking about the Database Verify(DBV) utility that it can be only used for the physical database check for any sort of corruptions( which is true too). That’s correct to say that the use of the DBV is to check (mandatorily) the data blocks so it can be used only to check the data files.

I have just got a question which did raise a doubt that somewhere its mentioned that the DBV can be “only” used to do the “logical corruption” check as well, how? Well, first of all , the statement itself is not correct. The DBV can be certainly used to check the physical corruption of a block and that’s what I believe most of us know about DBV as well too. So what about this logical corruption? Ain’t we suppose to use Flashback technology for it? Well, to answer these questions, we first of all have to understand what does it mean by “logical corruption” ?

First , what’s a physical corruption? It means that the underlying place where the block was sitting, itself is corrupted. The error normally would arise because of a faulty media or a bad sector over the media which essentially would physically corrupt the block, making it impossible for oracle to access it.And if you do find a block physically corrupted, the very first step before you attempt to recover the block would be that you replace the underlying media first.  Once the media is replaced, the underlying block can be recovered using the Block Recover command (10g) or Recover command(11g) !

The perception that I have seen most of the times in my sessions is that when someone mentions “logical corruption”, he only means a DML corrupting data “logically” , for example all salaries getting updated to 0 by someone in one single Update command! Right? Wrong! This is not “logical corruption” but its “logical data corruption” and certainly for this, the Flashback technology is well verse to be used! So what the heck is the “logical corruption” of a block is ? Well, simply put and in the first place, the block is physically fine which means that the physical location where the block is located is perfectly fine and there are absolutely no issues for Oracle to read the block! But despite that, there is some inconsistency  in the block which is making it marked as corrupted and that’s not related to the physical allocation of the block!

For example, if there is an index entry that should point to a data block, is found to be null i.e it stores an entry pointing to nowhere is an example of logical corruption of the block! The block would be fine in terms of physical aspects but still, its termed as corrupted “logically” by oracle! If there is a row entry mismatch found in the block, oracle reports internal error, ora-600 with the argument, [KDSGRP1], which means that there was a mismatch found in the row entries of the block. This kind of corruption would normally happen on its own and there are not set rules which would make it come! When you hit logical corruption, the block from Oracle’s side is completely fine and is fully accessible, yet the block is corrupted internally! This kind of corruption can be checked with the DBV command supplying it the information about the object whose blocks you want to verify. Obviously, this detection would be done below the high water mark of the segment, covering only those block which were formatted either now or in the past some time! The new or unformatted won’t have anything to do with the lgocial corruption!

To call DBV for the segment check, you need to pass minimally the user information (login credentials) of the user with the segment information which would comprise of the Tablespace Number, Relative file Number of the tablespace containing the block and the header block of the object! All this information can be picked up using the V$tablespace and dba_segments views. Other than that, in the Sys schema, you can find the view with the name Sys_user_segs which would also show the same to you! Shown below is a small snippet which is about creating an object and thus checking its information from both the views and finally using that info in the DBV command! Please note that the segment identification information passed in the command is comprising of the tablespace id, relative file number of the object and the header block of the object!

SQL> select * from v$version; 
  
BANNER   
--------------------------------------------------------------------------    
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production    
PL/SQL Release 11.1.0.6.0 - Production    
CORE    11.1.0.6.0      Production    
TNS for 32-bit Windows: Version 11.1.0.6.0 - Production    
NLSRTL Version 11.1.0.6.0 - Production 
  
SQL> drop table e purge   
  2  ; 
  
Table dropped. 
  
SQL> create table e as select * from scott.emp; 
  
Table created. 
  
SQL> exec dbms_stats.gather_table_stats(user,'E'); 
  
PL/SQL procedure successfully completed.
  
SQL> select segment_name, tablespace_name, header_file, header_block   
  2  from dba_segments    
  3  where segment_name='E'; 
  
SEGMENT_NAME   
-------------------------------------------------------------------------    
TABLESPACE_NAME                HEADER_FILE HEADER_BLOCK    
------------------------------ ----------- ------------    
E    
USERS                                    4          395 
  
SQL> select ts#,name from v$tablespace where name='USERS'; 
  
       TS# NAME   
---------- ------------------------------    
         4 USERS 
  
SQL> exit
  
C:\>dbv userid=aman/aman segment_id=4.4.395 
  
DBVERIFY: Release 11.1.0.6.0 - Production on Thu Mar 4 00:34:59 2010 
  
Copyright (c) 1982, 2007, Oracle.  All rights reserved. 
  
DBVERIFY - Verification starting : SEGMENT_ID = 4.4.395 
  
DBVERIFY - Verification complete 
  
Total Pages Examined         : 8   
Total Pages Processed (Data) : 1    
Total Pages Failing   (Data) : 0    
Total Pages Processed (Index): 0    
Total Pages Failing   (Index): 0    
Total Pages Processed (Other): 6    
Total Pages Processed (Seg)  : 1    
Total Pages Failing   (Seg)  : 0    
Total Pages Empty            : 0    
Total Pages Marked Corrupt   : 0    
Total Pages Influx           : 0    
Total Pages Encrypted        : 0    
Highest block SCN            : 1094559 (0.1094559) 
  
C:\>

As you can see that there won’t be anything mentioned by the DBV that the corruption is due to a physical corruption or a logical corruption! That’s why, to be ensured, its better to use Rman’s validate command which can check the blocks for physical and logical corruption!

So what’s the conclusion? Well, the conclusion is that “logical data corruption” is not the same as “logical block corruption” and DBV is not meant for only checking the physical corruption of the blocks within the data file and can be used the blocks of a segment as well!

And all of this is documented( okay may be not all ) in the official documentation,

http://download.oracle.com/docs/cd/E11882_01/server.112/e10701/dbverify.htm#i1006970

Hope this helps 🙂 !