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.
