ORACLE BLOCK DUMP

Block Dump Analysis:
===================

Run the blocklab.sql script in your schema to create a table.
-- Starting of blocklab.sql script.
-- blocklab.sql
drop table block_lab;

create table block_lab (
id number,
name varchar2(20),
hire_dt date);

insert into block_lab values (1,'Richard',to_date('1990-May-12','YYYY-Mon-dd'));
insert into block_lab values (2,'Andy', to_date('1990-Jun-01','YYYY-Mon-dd'));
insert into block_lab values (3,'Sunitha',to_date('1995-Nov-10','YYYY-Mon-dd'));
insert into block_lab values (4,'VB', to_date('1991-Feb-14','YYYY-Mon-dd'));
insert into block_lab values (5,'Ali', to_date('1986-Sep-23','YYYY-Mon-dd'));

1. Perform a UNIX binary dump on the BLOCK_LAB table.

SQL> select file_id, block_id from dba_extents
2 where segment_name = 'BLOCK_LAB';

FILE_ID BLOCK_ID
------- ----------
1 5060

SQL> select file#, name from v$datafile;

FILE# NAME
----- ---------------------------
1 /u01/oradata/816/system.dbf
2 /u01/oradata/816/temp.dbf
3 /u01/oradata/816/rbs.dbf
6 /u01/oradata/816/rbs2.dbf
8 /u01/oradata/816/big.dbf

SQL> exit

This note is made on reference on kcbh, ktbbh,ktbbit, kxid, ksqn, kuba,kdbh kdrh

In the above query, we found that the starting block for the table 'BLOCK_LAB' is 5060.
Remember, that first starting block is the object header block, and the immediate next block is the starting block for data.
So our case data should be in block number 5060+1 = 5061St. Block.

While taking a dd dump you should take care poper skip parameter.
In a datafile , 1St Block , which is in same size of oracle block is known as oracle header block. 2Nd. Block is the Oracle 1St. Data Block.
So to reached to block number 5061 , we should skip 5060 number of oracle data blocks and 1 header block. So we should skip
5061 blocks.

Or You can choose the alternate method

SQL> select rowid, id, name, hire_dt from block_lab;

ROWID ID NAME HIRE_DT
------------------------------- ---- ------------ -----------
AAAAwtAABAAABPFAAA 1 Richard 12-MAY-1990
AAAAwtAABAAABPFAAB 2 Andy 01-JUN-1990
AAAAwtAABAAABPFAAC 3 Sunitha 10-NOV-1995
AAAAwtAABAAABPFAAD 4 VB 14-FEB-1991
AAAAwtAABAAABPFAAE 5 Ali 23-SEP-1986

SQL> select dbms_rowid.rowid_block_number('AAAAwtAABAAABPFAAA') from dual;

DBMS_ROWID
----------
5061

SQL> select dbms_rowid.rowid_relative_nfno('AAAAwtAABAAABPFAAA') from dual;

DBMS_ROWID
----------
1

So we can take the dump of filenumber 1 and block number 5061 from above query.

UNIX dd Utility

bs: Block size (For our purposes, this is the Oracle block size; 2 KB , 4 KB, 1024, 4096, and so on)

if: Input file

skip: Number of blocks to skip

count: Number of blocks to dump

od: UNIX octal dump utility

-x: Option to the od command to convert output to hex
-v Show all input data (verbose). Without the -v option, all groups of output lines that would be identical to the preceding line are replaced with a line containing only an asterisk (*).

% dd if=/u01/oradata/816/system.dbf bs=4k skip=5061 count=1 | od -x > ddbd3.txt

1+0 records in
1+0 records out
ccd-orsun3% more ddbd3.txt
0000000 0602 0000 0040 13c5 0000 af23 0000 0102
0000020 0000 0000 0100 0000 0000 0c2d 0000 af22
0000040 0000 eb50 0001 0300 0000 0000 0004 002a
0000060 0000 0038 00c0 134b 0023 1e14 2005 0000
0000100 0000 af23 0001 0005 ffff 001c 0f56 0f3a
0000120 0f3a 0000 0005 0fa2 0f8f 0f79 0f68 0f56
0000140 0000 0000 0000 0000 0000 0000 0000 0000
*
0007620 0000 0000 0000 0000 0000 2c01 0302 c106
0007640 0341 6c69 0777 ba09 1701 0101 2c01 0302
0007660 c105 0256 4207 77bf 020e 0101 012c 0103
0007700 02c1 0407 5375 6e69 7468 6107 77c3 0b0a
0007720 0101 012c 0103 02c1 0304 416e 6479 0777
0007740 be06 0101 0101 2c01 0302 c102 0752 6963
0007760 6861 7264 0777 be05 0c01 0101 af23 0601

I am taking small part of the block dump at a time for detail analysis
Structure kcbh Starting Point.
----------- 0 1 2 3 4 5 6 7 1011 1213 1415 1617 This line added to explain dump
0000000 0602 0000 0040 13c5 0000 af23 0000 0102
0000020 0000 0000 0100 0000 0000 0c2d 0000 af22

Address Location 0000000 -> (type_kcbh)06 means the block type is data.

Address Location 0000001 -> (frmt_kcbh)02 means the block version is 8+.

Address Location 0000002-3 -> (spare1_kcbh+spare2_kcbh)filler, not used.

Address Location 0000004-7 -> (rdba_kcbh)RDBA, 4 bytes means 32 bits.Where first 10 bits for rfno and 22s bit is for block count. So here is the limitation of rfno/ts is 1111111111 which is 1023 in decimal, So maximum number of datafile per tablespace will be 1023. And no of block in a file is 11 1111 1111 1111 1111 1111 which is 4194303 in decimal. So if you use 2k block size, theoretically your oracle supported filesize will be 8Gb.

Address Location 0000010-13 -> (bas_kcbh)SCN Base.

Address Location 0000014-15 -> (wrp_kcbh)SCN Wrap.

Address Location 0000016 -> (seq_kcbh)Sequence Number.

#define KCBH_NLCSEQ 0 /* non-logged changes - do not advance seq# */
#define KCBH_MAXSEQ (UB1MAXVAL-1) /* maximum possible sequence number */
#define KCBH_CRPTSEQ (UB1MAXVAL)/* sequence # to indicate a block is corrupt */

Address Location 00000017 -> (flg_kcbh) Flag, which is like below

#define KCBHFNEW 0x01 /* new block - zeroed data area */
#define KCBHFDLC 0x02 /* Delayed Logging Change advanced SCN/seq */
#define KCBHFCKV 0x04 /* ChecK Value saved - block xor's to zero */
#define KCBHFTMP 0x08 /* Temporary block */

Address Location 0000020-21 -> (chkval_kcbh)Checksum value if db_checksum enabled.

Address Location 0000022-23 -> (spare3_kcb)Another filler , is not used.

Structure ktbbh Starting Point.
----------- 0 1 2 3 4 5 6 7 1011 1213 1415 1617 This line added to explain dump
0000020 0000 0000 0100 0000 0000 0c2d 0000 af22
0000040 0000 eb50 0001 0300 0000 0000 0004 002a

Address Location 0000027 -> (ktbbhtyp)

Address Location 0000030-33 -> (ktbbhsg1/ktbbhod1)Object id . So here is another limitation of maximum number of object, that oracle can support i.e. 0xffffffff , which is 4GB in decimal count.

Address Location 0000034-41 -> (ktbbhcsc)CSC at Last Block Clean Out.

Address Location 0000042-43 -> ()Some Magic Number. ??????

Address Location 0000044-45 -> (ktbbhict)No. of Itl.

Address Location 0000046 -> (ktbbhflg)Current txn layer version.

Address Location 0000047 -> (ktbbhfsl)Free Space lock.

Address Location 0000050-53 -> (ktbbhfnx)krdba.

Structure ktbit Starting Point.

---------- 0 1 2 3 4 5 6 7 1011 1213 1415 1617 This line added to explain dump
0000040 0000 eb50 0001 0300 0000 0000 0004 002a
0000060 0000 0038 00c0 134b 0023 1e14 2005 0000
0000100 0000 af23 0001 0005 ffff 001c 0f56 0f3a
0000120 0f3a 0000 0005 0fa2 0f8f 0f79 0f68 0f56

• Address Location 0000054-55 -> (kxidusn)Undo segment No.
• Address Location 0000056-57 -> (kxidslt)Undo Slot Number.
• Address Location 0000060-63 -> (kxidsqn)Undo wrap Number.
• Address Location 0000064-72 -> (kuba)undo address for last change.
• Address Location 0000073 -> ()Not Used.
• Address Location 0000074-75 -> (ktbitflg)Commit Status and Row count by itl.

#define KTBFCOM 0x8000 /* transaction is committed */
#define KTBFIBI 0x4000 /* rollback of this uba gives a BI of the itl */
/*
* For a cleaned-out committed itl, 0x2000 indicates the commit time is
* an upperbound time.
* For a uncleaned-out active itl, 0x2000 indicates that the itl has
* been delayed-logged cleaned out with an scn base set, row locks are not
* cleared
*/
#define KTBFUPB 0x2000 /* commit time is upper bound */
#define KTBFDLG KTBFUPB /* cleanout of this ITL has been delayed-logged */
#define KTBFTAC 0x1000 /* this xac is active as of ktbbhcsc */
#define KTBFLKC 0x0FFF /* lock count mask for flag */

• Address Location 0000076-77 -> (_ktbitfsc/ _ktbitwrp/)Scn wrap/fsc
• Address Location 0000100-03 -> (ktbitbas)Scn Base/0x0000 0000.

Structure kdbh Starting Point.

---------- 0 1 2 3 4 5 6 7 1011 1213 1415 1617 This line added to explain dump
0000100 0000 af23 0001 0005 ffff 001c 0f56 0f3a
0000120 0f3a 0000 0005 0fa2 0f8f 0f79 0f68 0f56

Address Location 0000104 -> (kdbhflag)Flag. See kdbh.

Address Location 0000105 -> (kdbhntab)No of Tables involve.

Address Location 0000106-07 -> (kdbhnrow)No of rowpiece in the block.

Address Location 0000110-11 -> (kdbhfrre)first FRee Row index Entry.

Address Location 0000112-13 -> (kdbhfsbo)fsbo – Free Space Beginning Offset.

Address Location 0000114-15 -> (kdbhfseo)fseo - Free Space Ending Offset.

Address Location 0000116-17 -> (kdbhavsp)AVailable SPace in the block.

Address Location 0000120-21 -> (kdbhtosp)TOtal Space that will be available.

The detail calculation for fsbo address location:
Data area start from octal address 104, in decimal it is 68 bytes.
Rememember for one ITL entry header is 68 Bytes. For n ITL entry header will be 44+n*24 bytes.
Because 24 bytes allocated for each ITL entry.
Fsbo value is 0x001c=28 in decimal. So We have to skip 68+28 byte=96 bytes.
So octal address will be 140 i.e. 0000140, from where all value is 0x0000.

The detail calculation for fseo address location:
Fseo value is 0x0f56 means 3926 in dec. So add 68 byte of header. It will be
3926+68=3994 bytes. So you have to skip 3994 bytes from the beginning.
So Octal address will be 7632 i.e. 0007632.

Structure kdbt Starting Point.

--------- 0 1 2 3 4 5 6 7 1011 1213 1415 1617 This line added to explain dump
0000120 0f3a 0000 0005 0fa2 0f8f 0f79 0f68 0f56

Address Location 0000122-23 -> (kdbtoffs) OFFSet in the block from kdbpri.

Address Location 0000124-25 -> (kdbtnrow) Number of Rows in the table

Detail of rowindex header
As kdbtnrow is 5 , So after kdbt first 5 consecutive entry (each of 2 bytes) is for rowindex.
----------- 0 1 2 3 4 5 6 7 1011 1213 1415 1617 This line added to explain dump
0000120 0f3a 0000 0005 0fa2 0f8f 0f79 0f68 0f56
0000140 0000 0000 0000 0000 0000 0000 0000 0000
*
0007620 0000 0000 0000 0000 0000 2c01 0302 c106

Address Location 0000126-27 -> First row idx.

Address Location 0000130-31 -> Second tow idx.

Address Location 0000132-33 -> Third row idx.

Address Location 0000134-35 -> Fourth row idx.

Address Location 0000136-37 -> Fifth row idx.

Structure kdrh Starting Point.

----------- 0 1 2 3 4 5 6 7 1011 1213 1415 1617 This line added to explain dump
0007740 be06 0101 0101 2c01 0302 c102 0752 6963
0007760 6861 7264 0777 be05 0c01 0101 af23 0601
Rememember for one ITL entry header is 68 Bytes. For n ITL entry header will be 44+n*24 bytes.
Because 24 bytes allocated for each ITL entry.
Lets take First row idx, value is 0x0fa2 which is 4002 in decimal.
So starting point of this row should be 4002+68(header bytes) = 4070Th byte. In octal if you convert the address is 0007746. Where you find a value “2c”, which is a row header byte. So

Address Location 0007746 -> (kdrhflag)(value 0x2c) the flag byte for the piece being inserted.

#define KDRHFK 0x80 /* cluster Key */
#define KDRHFC 0x40 /* Clustered table member */
#define KDRHFH 0x20 /* Head piece of row */
#define KDRHFD 0x10 /* Deleted row */
#define KDRHFF 0x08 /* First data piece */
#define KDRHFL 0x04 /* Last data piece */
#define KDRHFP 0x02 /* first column continues from Previous piece */
#define KDRHFN 0x01 /* last column continues in Next piece */
#define KDRHFCK (KDRHFK|KDRHFH|KDRHFF|KDRHFL) /* Cluster Key flag settings */
#define KDRHFSB(c) (KDRHFH|((c)&KDRHFC)) /* StuB flags from regular */
#define KDRHFSP (KDRHFH|KDRHFF|KDRHFL) /* Single Piece flags */
#define KDRHFCS (KDRHFSP|KDRHFC) /* Clustered Single piece */
#define KDRHCK (KDRHFCK|KDRHFN|KDRHFP) /* hash cluster key */

Address Location 0007747 -> (kdrhlock)(value 0x01) locking itl index.

Address Location 0007750 -> (kdrhccnt)(value 0x03) the column count for the row piece.

Address Location 0007751 -> (value 0x02) 1St Column width.

Address Location 0007752-53 -> (value 0xc102) 1St Column value.

Oracle converted value for 1St column is “1” See Article-ID:<<>

Address Location 0007754 -> (value 0x07) 2Nd Column width.

Address Location 0007755-63 -> (value 0x52 69 63 68 61 72 64) 2Nd Column value.

Oracle converted value for 2Nd column is “Richard” See Article-ID:<<>

Address Location 0007764 -> (value 0x07) 3rd Column width.

Address Location 0007765-73 -> (value 0x77 be 05 0c 01 01 01) 3Rd Column value.

Oracle converted value for 3Rd column is “1990-MAY-12-00:00:00” See Article-ID:<<>

Block Tail
Consistency data used to verify that the beginning and the end of the block are of the same version
(Consists of lower order two bytes of SCNBase plus block Type plus SCN Seq number.)
As we know this oracle blocksize is 4k. So, to represent 4k bytes , octal address required 7777. Reason is, if you
convert Octal 7777, which is 4095 in decimal+ Oth. address. So it is 4096 in count.

----------- 0 1 2 3 4 5 6 7 1011 1213 1415 1617 This line added to explain dump
0007740 be06 0101 0101 2c01 0302 c102 0752 6963
0007 be05 0c01 0101 af23 0601

As we know last 4 bytes are tail. So

Address Location 0007774-75 -> (value 0xaf23)Consists of lower order two bytes of SCNBase.

Address Location 0007776-77 -> (value 0x0601) Block Type plus SCN Seq number .

Logical Block Dump
SQL> alter system dump datafile 1 block 5061;

Dump file /u01/app/oracle/admin/816/udump/816_ora_21094.trc
Oracle8 Enterprise Edition Release 8.0.3.0.0 - Production
With the Partitioning and Objects options
PL/SQL Release 8.0.3.0.0 - Production
ORACLE_HOME = /u01/app/oracle/product/8.0.3
System name: SunOS
Node name: ccd-orsun3-le0
Release: 5.5.1
Version: Generic
Machine: sun4m
Instance name: 816
Redo thread mounted by this instance: 1
Oracle process number: 10
Unix process pid: 21094, image: oracle816

Mon Sep 22 23:45:46 1997
*** SESSION ID:(9.707) 1997.09.22.23.45.46.000
Start dump data blocks tsn: 0 file#: 1 minblk 5061 maxblk 5061
buffer tsn: 0 rdba: 0x004013c5 (1/5061)
scn:0x0000.0000af23 seq:0x01 flg:0x02 tail:0xaf230601
frmt:0x02 chkval:0x0000 type:0x06=trans data

Block header dump: rdba: 0x004013c5
Object id on Block? Y
seg/obj: 0xc2d csc: 0x00.af22 itc: 1 flg: O typ: 1 - DATA
fsl: 0 fnx: 0x0 ver: 0x01

Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0004.02a.00000038 0x00c0134b.0023.1e --U- 5 fsc 0x0000.0000af23

data_block_dump
===============
tsiz: 0xfb8
hsiz: 0x1c
pbl: 0x00ff8f44
bdba: 0x004013c5
flag=---------
ntab=1
nrow=5
frre=-1
fsbo=0x1c
fseo=0xf56
avsp=0xf3a
tosp=0xf3a
0xe:pti[0] nrow=5 offs=0
0x12:pri[0] offs=0xfa2
0x14:pri[1] offs=0xf8f
0x16:pri[2] offs=0xf79
0x18:pri[3] offs=0xf68
0x1a:pri[4] offs=0xf56
block_row_dump:
tab 0, row 0, @0xfa2
tl: 22 fb: --H-FL-- lb: 0x1 cc: 3
col 0: [ 2] c1 02
col 1: [ 7] 52 69 63 68 61 72 64
col 2: [ 7] 77 be 05 0c 01 01 01
tab 0, row 1, @0xf8f
tl: 19 fb: --H-FL-- lb: 0x1 cc: 3
col 0: [ 2] c1 03
col 1: [ 4] 41 6e 64 79
col 2: [ 7] 77 be 06 01 01 01 01
tab 0, row 2, @0xf79
tl: 22 fb: --H-FL-- lb: 0x1 cc: 3
col 0: [ 2] c1 04
col 1: [ 7] 53 75 6e 69 74 68 61
col 2: [ 7] 77 c3 0b 0a 01 01 01
tab 0, row 3, @0xf68
tl: 17 fb: --H-FL-- lb: 0x1 cc: 3
col 0: [ 2] c1 05
col 1: [ 2] 56 42
col 2: [ 7] 77 bf 02 0e 01 01 01
tab 0, row 4, @0xf56
tl: 18 fb: --H-FL-- lb: 0x1 cc: 3
col 0: [ 2] c1 06
col 1: [ 3] 41 6c 69
col 2: [ 7] 77 ba 09 17 01 01 01
end_of_block_dump
End dump data blocks tsn: 0 file#: 1 minblk 5061 maxblk 5061

3. Dump the Oracle8 formatted block of the BLOCK_LAB segment header.

SQL> select header_file, header_block from dba_segments
2 where segment_name = 'BLOCK_LAB';

HEADER_FILE HEADER_BLOCK
----------- ------------
1 5060

SQL> alter system dump datafile 1 block 5060;

Dump file /u01/app/oracle/admin/816/udump/816_ora_21149.trc
Oracle8 Enterprise Edition Release 8.0.3.0.0 - Production
With the Partitioning and Objects options
PL/SQL Release 8.0.3.0.0 - Production
ORACLE_HOME = /u01/app/oracle/product/8.0.3
System name: SunOS
Node name: ccd-orsun3-le0
Release: 5.5.1
Version: Generic
Machine: sun4m
Instance name: 816
Redo thread mounted by this instance: 1
Oracle process number: 10
Unix process pid: 21149, image: oracle816

Mon Sep 22 23:49:28 1997
*** SESSION ID:(9.709) 1997.09.22.23.49.28.000
Start dump data blocks tsn: 0 file#: 1 minblk 5060 maxblk 5060
buffer tsn: 0 rdba: 0x004013c4 (1/5060)
scn:0x0000.0000af22 seq:0x01 flg:0x00 tail:0xaf221001
frmt:0x02 chkval:0x0000 type:0x10=DATA SEGMENT HEADER - UNLIMITED

Extent Control Header
--------------------------------------------------------------------
Extent Header:: spare1: 0 tsn: 0 #extents: 1 #blocks: 2
last map rdba: 0x00000000 #maps: 0 offset: 2080
Highwater:: rdba: 0x004013c6 ext#: 0 blk#: 1 ext size: 2
#blocks in seg. hdr's freelists: 1
#blocks below: 1
mapblk rdba: 0x00000000 offset: 0
Unlocked
Map Header:: next rdba: 0x00000000 #extents: 1 obj#: 3117 flag: 0x40000000

Extent Map
-----------------------------------------------------------------
rdba: 0x004013c5 length: 2

nfl = 1, nfb = 1 typ = 1 nxf = 0
SEG LST:: flg: USED lhd: 0x004013c5 ltl: 0x004013c5

End dump data blocks tsn: 0 file#: 1 minblk 5060 maxblk 5060