Oracle Consulting Oracle Training Development

Remote DBA

Remote DBA Plans  

Remote DBA Service

Remote DBA RAC

   
Remote DBA Oracle Home
Remote DBA Oracle Training
Remote DBA SQL Tuning Consulting
Remote DBA Oracle Tuning Consulting
Remote DBA Data Warehouse Consulting
Remote DBA Oracle Project Management
Remote DBA Oracle Security Assessment
Remote DBA Unix Consulting
Burleson Books
Burleson Articles
Burleson Web Courses
Burleson Qualifications
Oracle Links
Remote DBA Oracle Monitoring
Remote DBA Support Benefits
Remote DBA Plans & Prices
Our Automation Strategy
What We Monitor
Oracle Apps Support
Print Our Brochure
Contact Us (e-mail)
Oracle Job Opportunities
Oracle Consulting Prices





   

 

 

 

Remote DBA services

Remote DBA Support

Remote DBA RAC

Remote DBA Reasons

Remote Oracle Tuning

Remote DBA Links

Oracle DBA Support

Oracle DBA Forum

Oracle Disaster

Oracle Training

Oracle Tuning

Oracle Training

 Remote DBA SQL Server

Remote MSSQL Consulting

Oracle DBA Hosting

Oracle License Negotiation

 

 


 

 

 

        
 

 Oracle BBED Verify Command

Oracle Tips by Burleson Consulting

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 by Rampant TechPress is written by four of the top Oracle database experts (Bert Scalzo, Donald Burleson, and Steve Callan).  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.

Remote DBA Service
 

Oracle Tuning Book

 

Advance SQL Tuning Book 

BC Oracle support

Oracle books by Rampant

Oracle monitoring software

 

 

 

 

 

 

 

 

 

BC Remote Oracle Support

Remote DBA

Remote DBA Services

Copyright © 1996 -  2013 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.