Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles
New Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 



        
 

 Oracle BBED Verify Command


Oracle Database Tips by Donald Burleson

For additional information on handling corruption, see the BC expert notes on corruption and see MOSC Note 1088018.1 - Handling Oracle Database Corruption Issues.

This is an excerpt from Advanced Oracle Utilities: The Definitive Reference.  The following is an excerpt from the book.

Use the verify command in bbed, along with several other outside-of-bbed utilities (analyze, DBMS_REPAIR, etc.) to validate the block structure. Is the restored structure valid?

 

BBED> verify dba 4,32

DBVERIFY - Verification starting

FILE = /opt/app/oracle/oradata/ORCL2/users01.dbf

BLOCK = 32

 

Block Checking: DBA = 16777248, Block Type = KTB-managed data block

data header at 0x137264

kdbchk: the amount of space used is not equal to block size

        used=613 fsc=38 avsp=7475 dtl=8088

Block 32 failed with check code 6110

 

DBVERIFY - Verification complete

 

Total Blocks Examined         : 1

Total Blocks Processed (Data) : 1

Total Blocks Failing   (Data) : 1

Total Blocks Processed (Index): 0

Total Blocks Failing   (Index): 0

Total Blocks Empty            : 0

Total Blocks Marked Corrupt   : 0

Total Blocks Influx           : 0

 

In this example, the data was restored, but the block failed to validate. Once the data is restored, take steps to check and ensure its validity. A simple way to collect the data is to perform a CTAS (Create Table As Select) using the original table as the source. The following shows there is not a difference, followed by evidence of one, and that the count is off by one.

 

SQL> select * from emp minus select * from emp2;

 

no rows selected

 

SQL> select * from emp2 minus select * from emp;

 

no rows selected

 

SQL> select empno from emp minus select empno from emp2;

 

no rows selected

 

SQL> select empno from emp2 minus select empno from emp;

 

     EMPNO

----------

      7788

 

SQL> select count(*) from emp;

 

  COUNT(*)

----------

        13

 

SQL> select count(*) from emp2;

 

  COUNT(*)

----------

        14

 

What can one do to remove this discrepancy? The best way - before anyone else starts trying other options - is to do what was just mentioned: make a copy of the data and put it elsewhere. Then, if one wants to start trying DBMS_REPAIR and other options, when one sees the following, there will not be such a desperate feeling.

 

SQL> conn / as sysdba

Connected.

SQL> declare

  2    fixed_count binary_integer;

  3  begin

  4  dbms_repair.FIX_CORRUPT_BLOCKS (

  5     schema_name       =>  'SCOTT',

  6     object_name       =>  'EMP',

  7     partition_name    =>  NULL,

  8     object_type       =>  dbms_repair.table_object,

  9     repair_table_name =>  'REPAIR_TABLE',

 10     flags             =>  NULL,

 11     fix_count         =>  fixed_count);

 12  dbms_output.put_line('Fixed '||to_char(fixed_count));

 13  end;

 14  /

Fixed 1

 

PL/SQL procedure successfully completed.

 

SQL> conn scott/tiger

Connected.

SQL> select * from emp;

select * from emp

              *

ERROR at line 1:

ORA-01578: ORACLE data block corrupted (file # 4, block # 32)

ORA-01110: data file 4: '/opt/app/oracle/oradata/ORCL2/users01.dbf'

 

This error leads into the next topic: corruption.

Summary

The BBED utility is extremely powerful, and if used incorrectly, can cause significant damage to a database. On the other hand, as a means of being able to poke around inside data blocks and files, it can be pretty handy. The best way to learn how to use this utility is to practice on a test database. Most of the commands are self-explanatory, but several are either cryptic (only Oracle knows what they do or how) or nonfunctional altogether. Additionally, how bbed may have function in Oracle version 8 or 9 is no guarantee that the same set of steps will work in version 10 or 11.

Additional information

Map command - can be used with the v flag for more verbose output. It is used with the kcbh struct to show the block header structure. Mapping against a block header and data block produces different output. Other structs are kdbh for data header, and kdbr for row information.

 

Tailcheck - consists of three elements:  the lower ordered two bytes of the SCN base, the block type (typically 06 for data blocks), and the SCN sequence number.

 

BBED> p tailchk

ub4 tailchk                                 @8188     0x75850602

 

The hex value 0x75850602 above reflects 7585 from the base, 06 for a data block, and 02 for the sequence number.

 

Block header structure, found in several public sources, consists of the type, format, spare, data block address, SCN base, SCN wrap, SCN sequence, and a flag (new, delayed logging, check value saved and temporary, using values of 01, 02, 04 and 08). Dump the beginning of a block (at offset 0) and this is the first line.

��  
 
 
Oracle Training at Sea
 
 
 
 
oracle dba poster
 

 
Follow us on Twitter 
 
Oracle performance tuning software 
 
Oracle Linux poster
 
 
 

 

 

   

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright © 1996 -  2017

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.

Remote Emergency Support provided by Conversational