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 Restoring Data in Corrupted Blocks

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.

Corrupted Blocks

A value of 0xff reported in bbed and elsewhere is an indicator of the block that has been marked corrupt. Printing the block header information (kcbh) should confirm this (see the seq_kcbh line).


BBED> set dba 4,32

        DBA             0x01000020 (16777248 4,32)


BBED> p kcbh

struct kcbh, 20 bytes                       @0

   ub1 type_kcbh                            @0        0x06

   ub1 frmt_kcbh                            @1        0xa2

   ub1 spare1_kcbh                          @2        0x00

   ub1 spare2_kcbh                          @3        0x00

   ub4 rdba_kcbh                            @4        0x01000020

   ub4 bas_kcbh                             @8        0x000aebab

   ub2 wrp_kcbh                             @12       0x0000

   ub1 seq_kcbh                             @14       0xff

   ub1 flg_kcbh                             @15       0x04 (KCBHFCKV)

   ub2 chkval_kcbh                          @16       0x98e9

   ub2 spare3_kcbh                          @18       0x0000


What is the uncorrupted value for a block? This needs to be known before changing 0xff to something else. The good value is 0x01 and the offset  is at 14 as indicated by the “@14” in the kcbh output. Set the dba to a known good block and confirm that 0x01 is indeed valid. In addition to the block header being set straight, one also has to fix the tail, and the bbed command tailchk can be used to check the tail. Once the tail has been modified, perform a sum with apply to effect the change.


Experimenting with trying to corrupt and uncorrupt a block, or even dealing with a block found to be corrupt after recovering data given that the data has already been rescued in a copy table is interesting but not practical. If normal tools cannot repair a corrupt block, the best option is to leave it corrupted and move on to other things. If the block becomes corrupted or fractured and one “really”   needs it, try using Oracle Support. Again, if the DBA was using bbed on her own and of her own accord, she may have to plead her case to get support.

Restoring Data

This scenario of copying blocks from one file to another is based on having deleted rows in a table, and the goal is to recover the data. There is a backup of the datafile where the table data is good. The current file is not good due to a table having missing rows. Why would one use bbed to copy data, and blocks specifically, from one file to another? In other words, why would one not be using any number of other valid and more supported recovery means?


The motivation could be because it is a time issue; but remember, if using bbed on one’s own, it is like playing with fire. One reason may be due to expediency and another may be related to what is on hand at the time. One only needs the relevant files: the older one and the current one. There is no need to create a clone, apply archived redo logs, wait for tapes to be mounted, and so on.

Two tricks to this recovery technique are accounting for counting and what goes in a parameter file. By accounting for counting, that means one has to take into account how Oracle starts counting (from which position) version how bbed starts. It is clearly zero or one, but it makes a difference when taking information from one source and applying it to the other.


In the current instance, the 14 rows in the EMP table are deleted. So retrieve some before and after information.


SQL> select segment_name, header_file, header_block, blocks

  2  from dba_segments

  3  where owner = 'SCOTT' and segment_name = 'EMP';



------------ ----------- ------------ ----------

EMP                    4           27          8


SQL> conn scott/tiger


SQL> delete from emp;


14 rows deleted.


SQL> commit;


Commit complete.


SQL> conn / as sysdba


SQL> select segment_name, header_file, header_block, blocks

  2  from dba_segments

  3  where owner = 'SCOTT' and segment_name = 'EMP';



------------ ----------- ------------ ----------

EMP                    4           27          8


What this shows is that hopefully the file and block usage does not change after the deletion. Since the author knows from beforehand that the location information is going to be the same from the old file to the new one, there is no need to inspect the old file.


Start from block 27 and use 8 blocks. Or is that correct? In bbed, add one to the header_block value. The data dictionary view’s start position for counting is 0, but bbed’s is 1. In this example, the dba will start at 1,28 for the old file and 2,28 for the current file. The blocks to be copied are 28, 29, 30, 31, 32, 33, 34, and 35. The copy command syntax is copy dba file#,block# to dba file#,block#. It should be apparent that it is possible to copy blocks in a file to a location within the same file. Experiments with this are left for the curious reader.


Create a listfile for use inside the parameter file. The listfile will contain two files: the old one and the new one. Number them 1 and 2 (they both can not be 4, for example; the file number is their relative position bbed reads from).


1 /opt/app/oracle/oradata/ORCL2/backup/users01.dbf 5242880

2 /opt/app/oracle/oradata/ORCL2/users01.dbf 5242880


The following commands need to be applied to a closed database:


copy dba 1,28 to dba 2,28

copy dba 1,29 to dba 2,29

copy dba 1,30 to dba 2,30

copy dba 1,31 to dba 2,31

copy dba 1,32 to dba 2,32

copy dba 1,33 to dba 2,33

copy dba 1,34 to dba 2,34

copy dba 1,35 to dba 2,35


After copying the blocks, the data can be viewed in bbed.


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.