How to use embedded BBED in ODU to manually fix missing offset in row directory

Posted: April 12, 2014 | Author: Cui Hua | Filed under: Oracle | 2 Comments »

Note: The latest ODU version (ODU 4.3.3) supports recover accidentally deleted data automatically even if all the deleted rows’ offsets in corresponding row directory are completely cleared by Oracle. Due to Jun’s amazing work, you don’t need to manually fix missing offset if you use ODU 4.3.3, ODU 4.3.3 can identify all the missing offsets in row directory automatically! Thank God!

In this article, I demonstrate how to use embedded BBED in ODU 4.3.2 to manually fix missing offset in row directory.

First of all, let’s construct an example of missing offset in row directory.

Create table T1 in user scott and insert more than 580,000 test rows:

SQL> conn scott/tiger@cuihua112;

Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0

Connected as scott

SQL> create table t1 as select * from dba_objects;

Table created

SQL> insert into t1 select * from t1;

72532 rows inserted

SQL> insert into t1 select * from t1;

145064 rows inserted

SQL> insert into t1 select * from t1;

290128 rows inserted

SQL> commit;

Commit complete

SQL> select count(*) from t1;

COUNT(*)

——————-

580256

SQL> create sequence seq_t1 minvalue 1 maxvalue 999999999999999999999 start with 1 increment by 1 cache 200;

Sequence created

SQL> alter table t1 add (id number);

Table altered

SQL> update t1 set id = seq_t1.nextval;

580256 rows updated

SQL> commit;

Commit complete

Execute the following SQL, you can see that the 580,256 records of table T1 are distributed among 8231 data blocks:

SQL> select min(id) min_id,max(id) max_id,dbms_rowid.rowid_relative_fno(rowid)||’_’||dbms_rowid.rowid_block_number(rowid) location from t1 group by dbms_rowid.rowid_relative_fno(rowid)||’_’||dbms_rowid.rowid_block_number(rowid) order by max_id;

MIN_ID MAX_ID LOCATION

———- ———- ——————————————————————————–

1 88 4_619

89 171 4_620

172 251 4_621

……omit some display contents

4845 4924 4_684

MIN_ID MAX_ID LOCATION

———- ———- ——————————————————————————–

4925 5000 4_685

5001 5080 4_686

……omit some display contents

574626 574694 4_9483

574695 574763 4_9484

574764 574833 4_9485

……omit some display contents

580111 580181 4_9599

580182 580256 4_9626

8231 rows selected

Create a backup table T1_BACKUP to record the columns ID of table T1:

SQL> create table t1_backup as select id from t1;

Table created

SQL> select count(*) from t1_backup;

COUNT(*)

———-

580256

Perform a delete operation on table T1, but do not commit:

SQL> delete from t1;

580256 rows deleted

In another session execute flush buffer_cache in order to construct a delay block cleanout:

E:\Recovery_Test\ODU\data>sqlplus /nolog

SQL*Plus: Release 11.2.0.1.0 Production on Thu Apr 3 16:17:07 2014

SQL> conn / as sysdba;

Connected.

SQL> alter system flush buffer_cache;

System altered.

Go back to the session where execute delete opration towards table T1, execute commit, followed by a select and checkpoint operations:

SQL> commit;

Commit complete

SQL> select * from t1;

OWNER OBJECT_NAME SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE CREATED LAST_DDL_TIME TIMESTAMP STATUS TEMPORARY GENERATED SECONDARY NAMESPACE EDITION_NAME ID

—————————— ——————————————————————————– —————————— ———- ————– ——————- ———– ————- ——————- ——- ——— ——— ——— ———- —————————— ———-

SQL> select count(*) from t1;

COUNT(*)

——————

SQL> alter system checkpoint;

System altered

Now we use ODU 4.3.2 to recover all the deleted rows of table T1:

E:\Recovery_Test\ODU>odu

Oracle Data Unloader:Release 4.3.2

Web: http://www.oracleodu.com

Email: magic007cn@gmail.com

loading default config…….

byte_order little

block_size 8192

data_path data

lob_path lob

db_timezone -7

Invalid db timezone:-7

client_timezone 8

Invalid client timezone:8

asmfile_extract_path asmfile

charset_name ZHS16GBK

ncharset_name AL16UTF16

output_format dmp

lob_storage infile

clob_byte_order big

trace_level 2

delimiter |

unload_deleted yes

file_header_offset 0

is_tru64 no

record_row_addr no

convert_clob_charset yes

use_scanned_lob yes

trim_scanned_blob yes

lob_switch_dir_rows 1000

db_block_checksum yes

db_block_checking yes

rdba_file_bits 10

compatible 10

load config file ‘config.txt’ successful

loading default asm disk file ……

can not open file ‘asmdisk.txt’, error message:No such file or directory.

loading default control file ……

ts# fn rfn bsize blocks bf offset filename

—- —- —- —– ——– — —— ——————————————–

0 1 1 8192 101120 N 0 D:\app\cuihua\oradata\cuihua112\SYSTEM01.DBF

1 2 2 8192 69120 N 0 D:\app\cuihua\oradata\cuihua112\SYSAUX01.DBF

2 3 3 8192 72960 N 0 D:\app\cuihua\oradata\cuihua112\UNDOTBS01.DBF

6 5 5 8192 12800 N 0 D:\app\cuihua\oradata\cuihua112\EXAMPLE01.DBF

4 4 4 8192 157760 N 0 D:\app\cuihua\oradata\cuihua112\USERS01.DBF

load control file ‘oductl.dat’ successful

loading dictionary data……done

loading scanned data……done

ODU> unload dict

CLUSTER C_USER# file_no: 1 block_no: 208

TABLE OBJ$ obj_no: 18 file_no: 1 block_no: 240

CLUSTER C_OBJ# file_no: 1 block_no: 144

found IND$’s obj# 19

found IND$’s dataobj#:2,ts#:0,file#:1,block#:144,tab#:3

found TABPART$’s obj# 576

found TABPART$’s dataobj#:576,ts#:0,file#:1,block#:3824,tab#:0

found INDPART$’s obj# 581

found INDPART$’s dataobj#:581,ts#:0,file#:1,block#:3872,tab#:0

found TABSUBPART$’s obj# 588

found TABSUBPART$’s dataobj#:588,ts#:0,file#:1,block#:3928,tab#:0

found INDSUBPART$’s obj# 593

found INDSUBPART$’s dataobj#:593,ts#:0,file#:1,block#:3968,tab#:0

found IND$’s obj# 19

found IND$’s dataobj#:2,ts#:0,file#:1,block#:144,tab#:3

found LOB$’s obj# 80

found LOB$’s dataobj#:2,ts#:0,file#:1,block#:144,tab#:6

found LOBFRAG$’s obj# 609

found LOBFRAG$’s dataobj#:609,ts#:0,file#:1,block#:4096,tab#:0

ODU> unload table scott.t1

Unloading table: T1,object ID: 74955 at 2014-04-03 16:22:08

Unloading segment,storage(Obj#=74955 DataObj#=74955 TS#=4 File#=4 Block#=618 Cluster=0)

Table T1 575558 rows unloaded

At 2014-04-03 16:22:23

You can see that ODU only recovered 575,558 records (number of records that has been deleted in table T1 is 580,256).

Import the above 575,558 records into the user SYS:

E:\Recovery_Test\ODU\data>imp \”sys/oracle@cuihua112 as sysdba\” file=SCOTT_T1.dmp tables=t1 ignore=true

Import: Release 11.2.0.1.0 – Production on Thu Apr 3 16:26:42 2014

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Export file created by EXPORT:V08.01.07 via conventional path

Warning: the objects were exported by SCOTT, not by you

import done in ZHS16GBK character set and AL16UTF16 NCHAR character set

. importing SCOTT’s objects into SYS

. . importing table “T1” 575558 rows imported

Import terminated successfully without warnings.

From the following query results, we can see that there are 4698 deleted records of the table T1 that ODU did not successfully recover:

SQL> conn scott/tiger@cuihua112;

Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0

Connected as scott

SQL> select id from (select id from t1_backup minus select id from sys.t1) order by id;

———-

4925

4926

4927

……omit some display contents

574692

574693

574694

4698 rows selected

Now we choose two blocks (where id is 4925 and 574694) of the table T1 to dump:

SQL> select min(id) from (select id from t1_backup minus select id from sys.t1);

MIN(ID)

——————

4925

SQL> select max(id) from (select id from t1_backup minus select id from sys.t1);

MAX(ID)

——————

574694

The record where ID equals to 4925 stored in datafile 4, block 685:

MIN_ID MAX_ID LOCATION

—————— —————- ——————————————————————————–

4925 5000 4_685

The record where ID equals to 574694 stored in datafile 4, block 9483:

MIN_ID MAX_ID LOCATION

—————— —————- ——————————————————————————–

574626 574694 4_9483

Now we use ODU to dump the above two blocks:

ODU> dump datafile 4 block 685

Block Header:

block type=0x06 (table/index/cluster segment data block)

block format=0xa2 (oracle 10+)

block rdba=0x010002ad (file#=4, block#=685)

scn=0x0000.001ae559, seq=1, tail=0xe5590601

block checksum value=0xa9a2=43426, flag=4

Data Block Header Dump:

Object id on Block? Y

seg/obj: 0x124cb=74955 csc: 0x00.1ae559 itc: 3 flg: E typ: 1 (data)

brn: 0 bdba: 0x10002a8 ver: 0x01

Itl Xid Uba Flag Lck Scn/Fsc

0x01 0xffff.000.00000000 0x00000000.0000.00 C— 0 scn 0x0000.001a30aa

0x02 0x0003.004.00000478 0x00c1168c.0180.51 C— 0 scn 0x0000.001a7899

0x03 0x0004.00f.00000398 0x00c11452.00bb.20 C— 0 scn 0x0000.001ae557

Data Block Dump:

================

flag=0x0 ——–

ntab=1

nrow=76

frre=0

fsbo=0xaa

ffeo=0x170

avsp=0x1ed6

tosp=0x1ed6

0xe:pti[0] nrow=76 offs=0

0x12:pri[0] sfll=1

0x14:pri[1] sfll=2

0x16:pri[2] sfll=3

0x18:pri[3] sfll=4

0x1a:pri[4] sfll=5

0x1c:pri[5] sfll=6

0x1e:pri[6] sfll=7

0x20:pri[7] sfll=8

0x22:pri[8] sfll=9

0x24:pri[9] sfll=10

0x26:pri[10] sfll=11

0x28:pri[11] sfll=12

0x2a:pri[12] sfll=13

0x2c:pri[13] sfll=14

0x2e:pri[14] sfll=15

0x30:pri[15] sfll=16

0x32:pri[16] sfll=17

0x34:pri[17] sfll=18

0x36:pri[18] sfll=19

0x38:pri[19] sfll=20

0x3a:pri[20] sfll=21

0x3c:pri[21] sfll=22

0x3e:pri[22] sfll=23

0x40:pri[23] sfll=24

0x42:pri[24] sfll=25

0x44:pri[25] sfll=26

0x46:pri[26] sfll=27

0x48:pri[27] sfll=28

0x4a:pri[28] sfll=29

0x4c:pri[29] sfll=30

0x4e:pri[30] sfll=31

0x50:pri[31] sfll=32

0x52:pri[32] sfll=33

0x54:pri[33] sfll=34

0x56:pri[34] sfll=35

0x58:pri[35] sfll=36

0x5a:pri[36] sfll=37

0x5c:pri[37] sfll=38

0x5e:pri[38] sfll=39

0x60:pri[39] sfll=40

0x62:pri[40] sfll=41

0x64:pri[41] sfll=42

0x66:pri[42] sfll=43

0x68:pri[43] sfll=44

0x6a:pri[44] sfll=45

0x6c:pri[45] sfll=46

0x6e:pri[46] sfll=47

0x70:pri[47] sfll=48

0x72:pri[48] sfll=49

0x74:pri[49] sfll=50

0x76:pri[50] sfll=51

0x78:pri[51] sfll=52

0x7a:pri[52] sfll=53

0x7c:pri[53] sfll=54

0x7e:pri[54] sfll=55

0x80:pri[55] sfll=56

0x82:pri[56] sfll=57

0x84:pri[57] sfll=58

0x86:pri[58] sfll=59

0x88:pri[59] sfll=60

0x8a:pri[60] sfll=61

0x8c:pri[61] sfll=62

0x8e:pri[62] sfll=63

0x90:pri[63] sfll=64

0x92:pri[64] sfll=65

0x94:pri[65] sfll=66

0x96:pri[66] sfll=67

0x98:pri[67] sfll=68

0x9a:pri[68] sfll=69

0x9c:pri[69] sfll=70

0x9e:pri[70] sfll=71

0xa0:pri[71] sfll=72

0xa2:pri[72] sfll=73

0xa4:pri[73] sfll=74

0xa6:pri[74] sfll=75

0xa8:pri[75] sfll=-1

Block Rows Dump:

ODU> dump datafile 4 block 9483

Block Header:

block type=0x06 (table/index/cluster segment data block)

block format=0xa2 (oracle 10+)

block rdba=0x0100250b (file#=4, block#=9483)

scn=0x0000.001ae559, seq=1, tail=0xe5590601

block checksum value=0xa625=42533, flag=4

Data Block Header Dump:

Object id on Block? Y

seg/obj: 0x124cb=74955 csc: 0x00.1ae559 itc: 2 flg: E typ: 1 (data)

brn: 0 bdba: 0x1002282 ver: 0x01

Itl Xid Uba Flag Lck Scn/Fsc

0x01 0x0004.00f.00000398 0x00c0cf15.0104.16 C— 0 scn 0x0000.001ae557

0x02 0x0003.004.00000478 0x00c08e1b.01b5.3f C-U- 0 scn 0x0000.001a7af8

Data Block Dump:

================

flag=0x0 ——–

ntab=1

nrow=69

frre=0

fsbo=0x9c

ffeo=0x204

avsp=0x1efc

tosp=0x1efc

0xe:pti[0] nrow=69 offs=0

0x12:pri[0] sfll=1

0x14:pri[1] sfll=2

0x16:pri[2] sfll=3

0x18:pri[3] sfll=4

0x1a:pri[4] sfll=5

0x1c:pri[5] sfll=6

0x1e:pri[6] sfll=7

0x20:pri[7] sfll=8

0x22:pri[8] sfll=9

0x24:pri[9] sfll=10

0x26:pri[10] sfll=11

0x28:pri[11] sfll=12

0x2a:pri[12] sfll=13

0x2c:pri[13] sfll=14

0x2e:pri[14] sfll=15

0x30:pri[15] sfll=16

0x32:pri[16] sfll=17

0x34:pri[17] sfll=18

0x36:pri[18] sfll=19

0x38:pri[19] sfll=20

0x3a:pri[20] sfll=21

0x3c:pri[21] sfll=22

0x3e:pri[22] sfll=23

0x40:pri[23] sfll=24

0x42:pri[24] sfll=25

0x44:pri[25] sfll=26

0x46:pri[26] sfll=27

0x48:pri[27] sfll=28

0x4a:pri[28] sfll=29

0x4c:pri[29] sfll=30

0x4e:pri[30] sfll=31

0x50:pri[31] sfll=32

0x52:pri[32] sfll=33

0x54:pri[33] sfll=34

0x56:pri[34] sfll=35

0x58:pri[35] sfll=36

0x5a:pri[36] sfll=37

0x5c:pri[37] sfll=38

0x5e:pri[38] sfll=39

0x60:pri[39] sfll=40

0x62:pri[40] sfll=41

0x64:pri[41] sfll=42

0x66:pri[42] sfll=43

0x68:pri[43] sfll=44

0x6a:pri[44] sfll=45

0x6c:pri[45] sfll=46

0x6e:pri[46] sfll=47

0x70:pri[47] sfll=48

0x72:pri[48] sfll=49

0x74:pri[49] sfll=50

0x76:pri[50] sfll=51

0x78:pri[51] sfll=52

0x7a:pri[52] sfll=53

0x7c:pri[53] sfll=54

0x7e:pri[54] sfll=55

0x80:pri[55] sfll=56

0x82:pri[56] sfll=57

0x84:pri[57] sfll=58

0x86:pri[58] sfll=59

0x88:pri[59] sfll=60

0x8a:pri[60] sfll=61

0x8c:pri[61] sfll=62

0x8e:pri[62] sfll=63

0x90:pri[63] sfll=64

0x92:pri[64] sfll=65

0x94:pri[65] sfll=66

0x96:pri[66] sfll=67

0x98:pri[67] sfll=68

0x9a:pri[68] sfll=-1

Block Rows Dump:

From the above dump results, we can see that all the offsets in above two blocks’ row directory are totally cleared by Oracle.

That means we successfully construct an example of missing offset in row directory.

ODU integrates BBED, you can directly modify Oracle data block in ODU.

Input ‘blockedit’ or ‘exit’ to enter into or quit from the embedded BBED in ODU.

ODU> blockedit

Entering block edit module.

BBED> help

help —- get command list

spool —- spool information to file

host —- enter os terminal

rowid —- decode rowid components

rdba —- decode RDBA to rfile# and block#

time —- convert number to timestamp

exit —- exit from blockedit module

set —- set file,block,offset for edit

show —- show enviroment and variables

dump —- dump block

modify —- modify block data

sum —- calculate checksum and write block to file

rollback —- rollback the change

find —- find hex string

verify —- verify the block

mark —- mark undo segments corruption

clone —- copy block from another block

clean —- clean free block pool or transaction

BBED> exit

Exiting block edit module.

ODU>

From the following results, we can see that the deleted rows in the above two blocks are still in fact, Oracle just cleared out all the offset in these two blocks’ row directory:

ODU> blockedit

Entering block edit module.

BBED> set datafile 4

BBED> set block 685

BBED> dump /x

-0–1–2–3–4–5–6–7–8–9–a–b–c–d–e–f-

0000 06 a2 00 00 ad 02 00 01 59 e5 1a 00 00 00 01 04 ……..Y…….

0010 a2 a9 00 00 01 00 00 00 cb 24 01 00 59 e5 1a 00 ⅸ…….$..Y…

0020 00 00 00 00 03 00 32 00 a8 02 00 01 ff ff 00 00 ……2………

0030 00 00 00 00 00 00 00 00 00 00 00 00 00 80 00 00 …………….

0040 aa 30 1a 00 03 00 04 00 78 04 00 00 8c 16 c1 00 .0……x…….

0050 80 01 51 00 00 80 00 00 99 78 1a 00 04 00 0f 00 ..Q……x……

0060 98 03 00 00 52 14 c1 00 bb 00 20 00 00 80 00 00 ….R….. …..

0070 57 e5 1a 00 00 00 00 00 00 00 00 00 00 01 4c 00 W………….L.

0080 00 00 aa 00 70 01 d6 1e d6 1e 00 00 4c 00 01 00 ….p…….L…

0090 02 00 03 00 04 00 05 00 06 00 07 00 08 00 09 00 …………….

BBED> set offset 512

BBED> dump /x

-0–1–2–3–4–5–6–7–8–9–a–b–c–d–e–f-

0200 4e 54 45 58 54 ff 03 c2 33 48 ff 07 53 59 4e 4f NTEXT…3H..SYNO

0210 4e 59 4d 07 78 6e 03 1e 0b 0a 2f 07 78 6e 03 1e NYM.xn…./.xn..

0220 0b 0a 2f 13 32 30 31 30 2d 30 33 2d 33 30 3a 31 ../.2010-03-30:1

0230 30 3a 30 39 3a 34 36 05 56 41 4c 49 44 01 4e 01 0:09:46.VALID.N.

0240 4e 01 4e 02 c1 02 ff 02 c2 33 3c 03 10 03 53 59 N.N……3<…SY

0250 53 0e 47 4c 4f 42 41 4c 5f 43 4f 4e 54 45 58 54 S.GLOBAL_CONTEXT

0260 ff 03 c2 33 47 ff 04 56 49 45 57 07 78 6e 03 1e …3G..VIEW.xn..

0270 0b 0a 2f 07 78 6e 03 1e 0b 0a 2f 13 32 30 31 30 ../.xn…./.2010

0280 2d 30 33 2d 33 30 3a 31 30 3a 30 39 3a 34 36 05 -03-30:10:09:46.

0290 56 41 4c 49 44 01 4e 01 4e 01 4e 02 c1 02 ff 03 VALID.N.N.N…..

BBED> set offset 1024

BBED> dump /x

-0–1–2–3–4–5–6–7–8–9–a–b–c–d–e–f-

0400 56 41 4c 49 44 01 4e 01 4e 01 4e 02 c1 02 2c 00 VALID.N.N.N…,.

0410 0e 06 50 55 42 4c 49 43 10 47 56 24 47 4c 4f 42 ..PUBLIC.GV$GLOB

0420 41 4c 43 4f 4e 54 45 58 54 ff 03 c2 33 46 ff 07 ALCONTEXT…3F..

0430 53 59 4e 4f 4e 59 4d 07 78 6e 03 1e 0b 0a 2f 07 SYNONYM.xn…./.

0440 78 6e 03 1e 0b 0a 2f 13 32 30 31 30 2d 30 33 2d xn…./.2010-03-

0450 33 30 3a 31 30 3a 30 39 3a 34 36 05 56 41 4c 49 30:10:09:46.VALI

0460 44 01 4e 01 4e 01 4e 02 c1 02 3c 03 10 06 50 55 D.N.N.N…<…PU

0470 42 4c 49 43 0f 56 24 47 4c 4f 42 41 4c 43 4f 4e BLIC.V$GLOBALCON

0480 54 45 58 54 ff 03 c2 33 44 ff 07 53 59 4e 4f 4e TEXT…3D..SYNON

0490 59 4d 07 78 6e 03 1e 0b 0a 2f 07 78 6e 03 1e 0b YM.xn…./.xn…

BBED> set offset 2048

BBED> dump /x

-0–1–2–3–4–5–6–7–8–9–a–b–c–d–e–f-

0800 24 43 4f 4e 54 45 58 54 ff 03 c2 33 3a ff 07 53 $CONTEXT…3:..S

0810 59 4e 4f 4e 59 4d 07 78 6e 03 1e 0b 0a 2f 07 78 YNONYM.xn…./.x

0820 6e 03 1e 0b 0a 2f 13 32 30 31 30 2d 30 33 2d 33 n…./.2010-03-3

0830 30 3a 31 30 3a 30 39 3a 34 36 05 56 41 4c 49 44 0:10:09:46.VALID

0840 01 4e 01 4e 01 4e 02 c1 02 ff 03 c2 32 57 3c 03 .N.N.N……2W<.

0850 10 03 53 59 53 0a 56 5f 24 43 4f 4e 54 45 58 54 ..SYS.V_$CONTEXT

0860 ff 03 c2 33 39 ff 04 56 49 45 57 07 78 6e 03 1e …39..VIEW.xn..

0870 0b 0a 2f 07 78 6e 03 1e 0b 0a 2f 13 32 30 31 30 ../.xn…./.2010

0880 2d 30 33 2d 33 30 3a 31 30 3a 30 39 3a 34 36 05 -03-30:10:09:46.

0890 56 41 4c 49 44 01 4e 01 4e 01 4e 02 c1 02 ff 03 VALID.N.N.N…..

BBED> set offset 4096

BBED> dump /x

-0–1–2–3–4–5–6–7–8–9–a–b–c–d–e–f-

1000 49 44 01 4e 01 4e 01 4e 02 c1 02 ff 03 c2 32 43 ID.N.N.N……2C

1010 3c 03 10 03 53 59 53 17 54 58 4e 5f 42 41 43 4b <…SYS.TXN_BACK

1020 4f 55 54 5f 53 54 41 54 45 5f 49 44 58 33 24 ff OUT_STATE_IDX3$.

1030 03 c2 33 24 03 c2 33 24 05 49 4e 44 45 58 07 78 ..3$..3$.INDEX.x

1040 6e 03 1e 0b 0a 2e 07 78 6e 03 1e 0b 0a 2e 13 32 n……xn……2

1050 30 31 30 2d 30 33 2d 33 30 3a 31 30 3a 30 39 3a 010-03-30:10:09:

1060 34 35 05 56 41 4c 49 44 01 4e 01 4e 01 4e 02 c1 45.VALID.N.N.N..

1070 05 ff 03 c2 32 42 3c 03 10 03 53 59 53 17 54 58 ….2B<…SYS.TX

1080 4e 5f 42 41 43 4b 4f 55 54 5f 53 54 41 54 45 5f N_BACKOUT_STATE_

1090 49 44 58 32 24 ff 03 c2 33 23 03 c2 33 23 05 49 IDX2$…3#..3#.I

BBED> set datafile 4

BBED> set block 9483

BBED> dump /x

-0–1–2–3–4–5–6–7–8–9–a–b–c–d–e–f-

0000 06 a2 00 00 0b 25 00 01 59 e5 1a 00 00 00 01 04 …..%..Y…….

0010 25 a6 00 00 01 00 00 00 cb 24 01 00 59 e5 1a 00 %……..$..Y…

0020 00 00 00 00 02 00 32 00 82 22 00 01 04 00 0f 00 ……2..”……

0030 98 03 00 00 15 cf c0 00 04 01 16 00 00 80 00 00 …..侠………

0040 57 e5 1a 00 03 00 04 00 78 04 00 00 1b 8e c0 00 W…….x…….

0050 b5 01 3f 00 00 a0 00 00 f8 7a 1a 00 00 00 00 00 ..?……z……

0060 00 00 00 00 00 01 45 00 00 00 9c 00 04 02 fc 1e ……E………

0070 fc 1e 00 00 45 00 01 00 02 00 03 00 04 00 05 00 ….E………..

0080 06 00 07 00 08 00 09 00 0a 00 0b 00 0c 00 0d 00 …………….

0090 0e 00 0f 00 10 00 11 00 12 00 13 00 14 00 15 00 …………….

BBED> set offset 1024

BBED> dump /x

-0–1–2–3–4–5–6–7–8–9–a–b–c–d–e–f-

0400 01 4e 01 59 01 4e 02 c1 02 ff 04 c3 3a 2f 5d 3c .N.Y.N……:/]<

0410 01 10 06 53 59 53 4d 41 4e 12 50 4b 5f 4d 47 4d …SYSMAN.PK_MGM

0420 54 5f 48 4f 53 54 5f 43 52 45 44 53 ff 04 c3 07 T_HOST_CREDS….

0430 4a 02 04 c3 07 4a 02 05 49 4e 44 45 58 07 78 6e J….J..INDEX.xn

0440 03 1e 0b 2b 19 07 78 6e 03 1e 0b 2b 19 13 32 30 …+..xn…+..20

0450 31 30 2d 30 33 2d 33 30 3a 31 30 3a 34 32 3a 32 10-03-30:10:42:2

0460 34 05 56 41 4c 49 44 01 4e 01 4e 01 4e 02 c1 05 4.VALID.N.N.N…

0470 ff 04 c3 3a 2f 5c 3c 01 10 06 53 59 53 4d 41 4e …:/\<…SYSMAN

0480 15 4d 47 4d 54 5f 48 4f 53 54 5f 43 52 45 44 45 .MGMT_HOST_CREDE

0490 4e 54 49 41 4c 53 ff 04 c3 07 49 64 ff 05 54 41 NTIALS….Id..TA

BBED> set offset 2048

BBED> dump /x

-0–1–2–3–4–5–6–7–8–9–a–b–c–d–e–f-

0800 33 30 3a 31 30 3a 34 31 3a 34 32 05 56 41 4c 49 30:10:41:42.VALI

0810 44 01 4e 01 4e 01 4e 02 c1 02 ff 04 c3 3a 2f 53 D.N.N.N……:/S

0820 3c 01 10 06 50 55 42 4c 49 43 1a 55 53 45 52 5f <…PUBLIC.USER_

0830 53 44 4f 5f 4e 45 54 57 4f 52 4b 5f 55 53 45 52 SDO_NETWORK_USER

0840 5f 44 41 54 41 ff 04 c3 07 3d 28 ff 07 53 59 4e _DATA….=(..SYN

0850 4f 4e 59 4d 07 78 6e 03 1e 0b 28 14 07 78 6e 03 ONYM.xn…(..xn.

0860 1e 0b 2a 2e 13 32 30 31 30 2d 30 33 2d 33 30 3a ..*..2010-03-30:

0870 31 30 3a 34 31 3a 34 35 05 56 41 4c 49 44 01 4e 10:41:45.VALID.N

0880 01 4e 01 4e 02 c1 02 ff 04 c3 3a 2f 52 3c 01 10 .N.N……:/R<..

0890 05 4d 44 53 59 53 19 41 4c 4c 5f 53 44 4f 5f 4e .MDSYS.ALL_SDO_N

BBED> set offset 4096

BBED> dump /x

-0–1–2–3–4–5–6–7–8–9–a–b–c–d–e–f-

1000 2f 38 38 33 35 61 33 63 61 5f 44 69 63 6f 6d 4f /8835a3ca_DicomO

1010 62 6a 32 ff 04 c3 07 0d 3b ff 0a 4a 41 56 41 20 bj2…..;..JAVA

1020 43 4c 41 53 53 07 78 6e 03 1e 0b 20 2c 07 78 6e CLASS.xn… ,.xn

1030 03 1e 0b 20 36 13 32 30 31 30 2d 30 33 2d 33 30 … 6.2010-03-30

1040 3a 31 30 3a 33 31 3a 34 33 05 56 41 4c 49 44 01 :10:31:43.VALID.

1050 4e 01 4e 01 4e 02 c1 02 ff 04 c3 3a 2f 3f 3c 01 N.N.N……:/?<.

1060 10 06 4f 52 44 53 59 53 13 2f 37 39 62 35 34 66 ..ORDSYS./79b54f

1070 65 34 5f 44 69 63 6f 6d 4f 62 6a 31 ff 04 c3 07 e4_DicomObj1….

1080 0d 3a ff 0a 4a 41 56 41 20 43 4c 41 53 53 07 78 .:..JAVA CLASS.x

1090 6e 03 1e 0b 20 2c 07 78 6e 03 1e 0b 20 36 13 32 n… ,.xn… 6.2

Now we choose a normal (the associated row directory offset is not cleared by Oracle) data block (datafile 4, block 619) from table T1 to dump:

MIN_ID MAX_ID LOCATION

——————– ————– ——————————————————————————–

1 88 4_619

ODU> dump datafile 4 block 619

Block Header:

block type=0x06 (table/index/cluster segment data block)

block format=0xa2 (oracle 10+)

block rdba=0x0100026b (file#=4, block#=619)

scn=0x0000.001a8061, seq=30, tail=0x8061061e

block checksum value=0xef07=61191, flag=4

Data Block Header Dump:

Object id on Block? Y

seg/obj: 0x124cb=74955 csc: 0x00.1a805f itc: 3 flg: E typ: 1 (data)

brn: 0 bdba: 0x1000268 ver: 0x01

Itl Xid Uba Flag Lck Scn/Fsc

0x01 0xffff.000.00000000 0x00000000.0000.00 C— 0 scn 0x0000.001a30aa

0x02 0x0003.004.00000478 0x00c003dd.017f.39 C— 0 scn 0x0000.001a7899

0x03 0x0004.00f.00000398 0x00c016c7.00ba.1e —- 88 fsc 0x1bfe.00000000

Data Block Dump:

================

flag=0x0 ——–

ntab=1

nrow=88

frre=-1

fsbo=0xc2

ffeo=0x13c

avsp=0x210

tosp=0x1ebe

0xe:pti[0] nrow=88 offs=0

0x12:pri[0] offs=0x1f2f

0x14:pri[1] offs=0x1edc

0x16:pri[2] offs=0x1e8c

0x18:pri[3] offs=0x1e3b

0x1a:pri[4] offs=0x1de6

0x1c:pri[5] offs=0x1d94

0x1e:pri[6] offs=0x1d38

0x20:pri[7] offs=0x1ce6

0x22:pri[8] offs=0x1c93

0x24:pri[9] offs=0x1c41

0x26:pri[10] offs=0x1be1

0x28:pri[11] offs=0x1b90

0x2a:pri[12] offs=0x1b40

0x2c:pri[13] offs=0x1ae6

0x2e:pri[14] offs=0x1a93

0x30:pri[15] offs=0x1a41

0x32:pri[16] offs=0x19ef

0x34:pri[17] offs=0x199e

0x36:pri[18] offs=0x194b

0x38:pri[19] offs=0x18fb

0x3a:pri[20] offs=0x18ab

0x3c:pri[21] offs=0x1858

0x3e:pri[22] offs=0x1805

0x40:pri[23] offs=0x17b5

0x42:pri[24] offs=0x1764

0x44:pri[25] offs=0x1711

0x46:pri[26] offs=0x16c1

0x48:pri[27] offs=0x166a

0x4a:pri[28] offs=0x1617

0x4c:pri[29] offs=0x15be

0x4e:pri[30] offs=0x156c

0x50:pri[31] offs=0x151a

0x52:pri[32] offs=0x14c8

0x54:pri[33] offs=0x1476

0x56:pri[34] offs=0x1423

0x58:pri[35] offs=0x13d4

0x5a:pri[36] offs=0x1378

0x5c:pri[37] offs=0x1323

0x5e:pri[38] offs=0x12d0

0x60:pri[39] offs=0x127a

0x62:pri[40] offs=0x122a

0x64:pri[41] offs=0x11d8

0x66:pri[42] offs=0x1187

0x68:pri[43] offs=0x1136

0x6a:pri[44] offs=0x10e4

0x6c:pri[45] offs=0x1091

0x6e:pri[46] offs=0x1041

0x70:pri[47] offs=0xfef

0x72:pri[48] offs=0xf9b

0x74:pri[49] offs=0xf4b

0x76:pri[50] offs=0xefa

0x78:pri[51] offs=0xea7

0x7a:pri[52] offs=0xe54

0x7c:pri[53] offs=0xe01

0x7e:pri[54] offs=0xdae

0x80:pri[55] offs=0xd5c

0x82:pri[56] offs=0xd09

0x84:pri[57] offs=0xca9

0x86:pri[58] offs=0xc54

0x88:pri[59] offs=0xc00

0x8a:pri[60] offs=0xbaa

0x8c:pri[61] offs=0xb54

0x8e:pri[62] offs=0xb01

0x90:pri[63] offs=0xaac

0x92:pri[64] offs=0xa57

0x94:pri[65] offs=0xa07

0x96:pri[66] offs=0x9b7

0x98:pri[67] offs=0x966

0x9a:pri[68] offs=0x90f

0x9c:pri[69] offs=0x8ba

0x9e:pri[70] offs=0x863

0xa0:pri[71] offs=0x80c

0xa2:pri[72] offs=0x7bc

0xa4:pri[73] offs=0x769

0xa6:pri[74] offs=0x710

0xa8:pri[75] offs=0x6be

0xaa:pri[76] offs=0x66c

0xac:pri[77] offs=0x61a

0xae:pri[78] offs=0x5ca

0xb0:pri[79] offs=0x578

0xb2:pri[80] offs=0x526

0xb4:pri[81] offs=0x4d2

0xb6:pri[82] offs=0x2e6

0xb8:pri[83] offs=0x290

0xba:pri[84] offs=0x239

0xbc:pri[85] offs=0x1e0

0xbe:pri[86] offs=0x18f

0xc0:pri[87] offs=0x13c

Block Rows Dump:

tab 0, row 0, @0x1f2f

fb: –HDFL– lb: 0x3 cc: 16

col 0: [ 3] 53 59 53

col 1: [ 5] 49 43 4f 4c 24

col 2: *NULL*

col 3: [ 2] c1 15

col 4: [ 2] c1 03

col 5: [ 5] 54 41 42 4c 45

col 6: [ 7] 78 6e 03 1e 0b 08 31

col 7: [ 7] 78 6e 03 1e 0b 1c 16

col 8: [ 19] 32 30 31 30 2d 30 33 2d 33 30 3a 31 30 3a 30 37 3a 34 38

col 9: [ 5] 56 41 4c 49 44

col 10: [ 1] 4e

col 11: [ 1] 4e

col 12: [ 1] 4e

col 13: [ 2] c1 02

col 14: *NULL*

col 15: [ 2] c1 02

tab 0, row 1, @0x1edc

fb: –HDFL– lb: 0x3 cc: 16

col 0: [ 3] 53 59 53

col 1: [ 7] 49 5f 55 53 45 52 31

col 2: *NULL*

col 3: [ 2] c1 2f

col 4: [ 2] c1 2f

col 5: [ 5] 49 4e 44 45 58

col 6: [ 7] 78 6e 03 1e 0b 08 31

col 7: [ 7] 78 6e 03 1e 0b 08 31

col 8: [ 19] 32 30 31 30 2d 30 33 2d 33 30 3a 31 30 3a 30 37 3a 34 38

col 9: [ 5] 56 41 4c 49 44

col 10: [ 1] 4e

col 11: [ 1] 4e

col 12: [ 1] 4e

col 13: [ 2] c1 05

col 14: *NULL*

col 15: [ 2] c1 03

……omit some display contents

tab 0, row 86, @0x18f

fb: –HDFL– lb: 0x3 cc: 16

col 0: [ 3] 53 59 53

col 1: [ 5] 4e 54 41 42 24

col 2: *NULL*

col 3: [ 2] c1 59

col 4: [ 2] c1 03

col 5: [ 5] 54 41 42 4c 45

col 6: [ 7] 78 6e 03 1e 0b 08 33

col 7: [ 7] 78 6e 03 1e 0b 08 33

col 8: [ 19] 32 30 31 30 2d 30 33 2d 33 30 3a 31 30 3a 30 37 3a 35 30

col 9: [ 5] 56 41 4c 49 44

col 10: [ 1] 4e

col 11: [ 1] 4e

col 12: [ 1] 4e

col 13: [ 2] c1 02

col 14: *NULL*

col 15: [ 2] c1 58

tab 0, row 87, @0x13c

fb: –HDFL– lb: 0x3 cc: 16

col 0: [ 3] 53 59 53

col 1: [ 7] 49 5f 4e 54 41 42 31

col 2: *NULL*

col 3: [ 2] c1 5a

col 4: [ 2] c1 5a

col 5: [ 5] 49 4e 44 45 58

col 6: [ 7] 78 6e 03 1e 0b 08 33

col 7: [ 7] 78 6e 03 1e 0b 08 33

col 8: [ 19] 32 30 31 30 2d 30 33 2d 33 30 3a 31 30 3a 30 37 3a 35 30

col 9: [ 5] 56 41 4c 49 44

col 10: [ 1] 4e

col 11: [ 1] 4e

col 12: [ 1] 4e

col 13: [ 2] c1 05

col 14: *NULL*

col 15: [ 2] c1 59

Let’s see the last two rows’ relative offset in the above block (datafile 4 block 619)’s row directory.

tab 0, row 86, @0x18f

tab 0, row 87, @0x13c

Now you can see that row 86’s relative offset is 0x18f, row 87’s relative offset is 0x13c.

We must add some base to relative offset in order to get the above two rows’ actual offset.

The algorithm of base is as follows:

For ASSM：base = 76+（itc-1)*24

For MSSM：base = 68+（itc-1)*24

From the following contents, we can see that the value of the above block’s itc is 3:

seg/obj: 0x124cb=74955 csc: 0x00.1a805f itc: 3 flg: E typ: 1 (data)

The tablespace where the table T1 resides use ASSM, so the actual offset of row 86 is 523:

SQL> select to_number(’18f’,’XXXX’) + 76 + (3-1)*24 from dual;

TO_NUMBER(’18F’,’XXXX’)+76+(3-

——————————

523

The actual offset of row 87 is 440:

SQL> select to_number(’13c’,’XXXX’) + 76 + (3-1)*24 from dual;

TO_NUMBER(’13C’,’XXXX’)+76+(3-

——————————

440

Let’s locate the row 86:

BBED> set datafile 4

BBED> set block 619

BBED> set offset 523

BBED> dump /x

-0–1–2–3–4–5–6–7–8–9–a–b–c–d–e–f-

020b 3c 03 10 03 53 59 53 05 4e 54 41 42 24 ff 02 c1 <…SYS.NTAB$…

021b 59 02 c1 03 05 54 41 42 4c 45 07 78 6e 03 1e 0b Y….TABLE.xn…

022b 08 33 07 78 6e 03 1e 0b 08 33 13 32 30 31 30 2d .3.xn….3.2010-

023b 30 33 2d 33 30 3a 31 30 3a 30 37 3a 35 30 05 56 03-30:10:07:50.V

024b 41 4c 49 44 01 4e 01 4e 01 4e 02 c1 02 ff 02 c1 ALID.N.N.N……

025b 58 3c 03 10 03 53 59 53 0d 49 5f 53 55 42 43 4f X<…SYS.I_SUBCO

026b 4c 54 59 50 45 31 ff 02 c1 58 02 c1 58 05 49 4e LTYPE1…X..X.IN

027b 44 45 58 07 78 6e 03 1e 0b 08 33 07 78 6e 03 1e DEX.xn….3.xn..

028b 0b 08 33 13 32 30 31 30 2d 30 33 2d 33 30 3a 31 ..3.2010-03-30:1

029b 30 3a 30 37 3a 35 30 05 56 41 4c 49 44 01 4e 01 0:07:50.VALID.N.

Let’s locate the row 87:

BBED> set offset 440

BBED> dump /x

-0–1–2–3–4–5–6–7–8–9–a–b–c–d–e–f-

01b8 3c 03 10 03 53 59 53 07 49 5f 4e 54 41 42 31 ff <…SYS.I_NTAB1.

01c8 02 c1 5a 02 c1 5a 05 49 4e 44 45 58 07 78 6e 03 ..Z..Z.INDEX.xn.

01d8 1e 0b 08 33 07 78 6e 03 1e 0b 08 33 13 32 30 31 …3.xn….3.201

01e8 30 2d 30 33 2d 33 30 3a 31 30 3a 30 37 3a 35 30 0-03-30:10:07:50

01f8 05 56 41 4c 49 44 01 4e 01 4e 01 4e 02 c1 05 ff .VALID.N.N.N….

0208 02 c1 59 3c 03 10 03 53 59 53 05 4e 54 41 42 24 ..Y<…SYS.NTAB$

0218 ff 02 c1 59 02 c1 03 05 54 41 42 4c 45 07 78 6e …Y….TABLE.xn

0228 03 1e 0b 08 33 07 78 6e 03 1e 0b 08 33 13 32 30 ….3.xn….3.20

0238 31 30 2d 30 33 2d 33 30 3a 31 30 3a 30 37 3a 35 10-03-30:10:07:5

0248 30 05 56 41 4c 49 44 01 4e 01 4e 01 4e 02 c1 02 0.VALID.N.N.N…

We can see that the value of these two rows’ row header are both 0x3c, that means Oracle actually label a delete flag on these two rows’ row header.

Before we manually fix missing offset in datafile 4 block 9483’s row directory, even we change the value of ODU config parameter unload_deleted from ‘no’ to ‘yes’, we still can’t unload all the deleted rows from that block:

ODU> unload table scott.t1 datafile 4 block 9483 blocks 1

unload specific block mode.

Unloading table: T1,object ID: 74955 at 2014-04-05 11:33:51

Unloading segment,storage(Obj#=74955 DataObj#=74955 TS#=4 File#=4 Block#=618 Cluster=0)

Table T1 0 rows unloaded

At 2014-04-05 11:33:51

Now we start to use embedded BBED in ODU to manually fix missing offset in datafile 4 block 9483’s row directory.

For a normal table (not cluster), its’ data block must have these five structures before row directory structure kdbr:

SQL> select * from v$type_size where type in (‘KCBH’,’KTBBH’,’KTBIT’,’KDBH’,’KDBT’);

COMPONENT TYPE DESCRIPTION TYPE_SIZE

—————– ——– ————————————————————— —————

KCB KCBH BLOCK COMMON HEADER 20

KTB KTBIT TRANSACTION VARIABLE HEADER 24

KTB KTBBH TRANSACTION FIXED HEADER 48

KDB KDBH DATA HEADER 14

KDB KDBT TABLE DIRECTORY ENTRY 4

For a normal table (not cluster) in ASSM, when you compute the actual offset start from KDBH of a data block, you must add extra 8 bytes.

So for a normal table (not cluster) in ASSM, the algorithm of row directory structure kdbr’s actual offset is as follows:

actual offset of kdbr = 20 + 48 + 24*(itc-1) + 14 + 4 + 8 = 94 + 24*(itc-1)

From the following query result, we know that datafile 4 block 9483 has 69 rows:

MIN_ID MAX_ID LOCATION

——————- ————– ——————————————————————————–

574626 574694 4_9483 /* 574694 – 574626 + 1 = 69 */

From the following dump result, we see that the value of datafile 4 block 9483’s itc is 2 and it has 69 rows in its’ row directory:

ODU> dump datafile 4 block 9483

Block Header:

block type=0x06 (table/index/cluster segment data block)

block format=0xa2 (oracle 10+)

block rdba=0x0100250b (file#=4, block#=9483)

scn=0x0000.001ae559, seq=1, tail=0xe5590601

block checksum value=0xa625=42533, flag=4

Data Block Header Dump:

Object id on Block? Y

seg/obj: 0x124cb=74955 csc: 0x00.1ae559 itc: 2 flg: E typ: 1 (data)

brn: 0 bdba: 0x1002282 ver: 0x01

Itl Xid Uba Flag Lck Scn/Fsc

0x01 0x0004.00f.00000398 0x00c0cf15.0104.16 C— 0 scn 0x0000.001ae557

0x02 0x0003.004.00000478 0x00c08e1b.01b5.3f C-U- 0 scn 0x0000.001a7af8

Data Block Dump:

================

flag=0x0 ——–

ntab=1

nrow=69

frre=0

fsbo=0x9c

ffeo=0x204

avsp=0x1efc

tosp=0x1efc

0xe:pti[0] nrow=69 offs=0

0x12:pri[0] sfll=1

0x14:pri[1] sfll=2

0x16:pri[2] sfll=3

0x18:pri[3] sfll=4

0x1a:pri[4] sfll=5

0x1c:pri[5] sfll=6

0x1e:pri[6] sfll=7

0x20:pri[7] sfll=8

0x22:pri[8] sfll=9

0x24:pri[9] sfll=10

0x26:pri[10] sfll=11

0x28:pri[11] sfll=12

0x2a:pri[12] sfll=13

0x2c:pri[13] sfll=14

0x2e:pri[14] sfll=15

0x30:pri[15] sfll=16

0x32:pri[16] sfll=17

0x34:pri[17] sfll=18

0x36:pri[18] sfll=19

0x38:pri[19] sfll=20

0x3a:pri[20] sfll=21

0x3c:pri[21] sfll=22

0x3e:pri[22] sfll=23

0x40:pri[23] sfll=24

0x42:pri[24] sfll=25

0x44:pri[25] sfll=26

0x46:pri[26] sfll=27

0x48:pri[27] sfll=28

0x4a:pri[28] sfll=29

0x4c:pri[29] sfll=30

0x4e:pri[30] sfll=31

0x50:pri[31] sfll=32

0x52:pri[32] sfll=33

0x54:pri[33] sfll=34

0x56:pri[34] sfll=35

0x58:pri[35] sfll=36

0x5a:pri[36] sfll=37

0x5c:pri[37] sfll=38

0x5e:pri[38] sfll=39

0x60:pri[39] sfll=40

0x62:pri[40] sfll=41

0x64:pri[41] sfll=42

0x66:pri[42] sfll=43

0x68:pri[43] sfll=44

0x6a:pri[44] sfll=45

0x6c:pri[45] sfll=46

0x6e:pri[46] sfll=47

0x70:pri[47] sfll=48

0x72:pri[48] sfll=49

0x74:pri[49] sfll=50

0x76:pri[50] sfll=51

0x78:pri[51] sfll=52

0x7a:pri[52] sfll=53

0x7c:pri[53] sfll=54

0x7e:pri[54] sfll=55

0x80:pri[55] sfll=56

0x82:pri[56] sfll=57

0x84:pri[57] sfll=58

0x86:pri[58] sfll=59

0x88:pri[59] sfll=60

0x8a:pri[60] sfll=61

0x8c:pri[61] sfll=62

0x8e:pri[62] sfll=63

0x90:pri[63] sfll=64

0x92:pri[64] sfll=65

0x94:pri[65] sfll=66

0x96:pri[66] sfll=67

0x98:pri[67] sfll=68

0x9a:pri[68] sfll=-1

Block Rows Dump:

So the actual kdbr offset of datafile 4 block 9483 is 94 + 24*(2-1) = 118

From the following desc result, we know the number of columns of table T1 is 16, which equals 0x10:

SQL> desc scott.t1

Name Type Nullable Default Comments

————– ————- ——– ——- ——–

OWNER VARCHAR2(30) Y

OBJECT_NAME VARCHAR2(128) Y

SUBOBJECT_NAME VARCHAR2(30) Y

OBJECT_ID NUMBER Y

DATA_OBJECT_ID NUMBER Y

OBJECT_TYPE VARCHAR2(19) Y

CREATED DATE Y

LAST_DDL_TIME DATE Y

TIMESTAMP VARCHAR2(19) Y

STATUS VARCHAR2(7) Y

TEMPORARY VARCHAR2(1) Y

GENERATED VARCHAR2(1) Y

SECONDARY VARCHAR2(1) Y

NAMESPACE NUMBER Y

EDITION_NAME VARCHAR2(30) Y

ID NUMBER Y

The row header structure of Oracle’s row in a table (not cluster) has three parts.

The first part is row flag, normally (not consider row chain and row migration), the deleted row’s row flag is 0x3c.

The second part is lock byte, the value of a row’s lock byte is between 0x00 to itc of the block where this row belongs to.

The third part is the number of columns, consider NULL value storage optimization of the last column in Oracle, the value of a row’s number of columns is between 0x00 to the number of columns of the table where this row belongs to.

According to the above background knowledge, let’s use ODU’s find command to find the actual offset of every deleted rows in datafile 4 block 9483.

The find rules are as follows:

You just need to define a search string, the length of the search string is 3 bytes.

The first byte is 0x3c

The second byte is between 0x00 to 0x02 (the itc of datafile 4 block 9483 is 2)

The third part is between 0x00 to 0x10 (because the number of columns of table T1 is 16, which equals 0x10)

Note: the value of third part in that situation is usually 0x10 if the row in datafile 4 block 9483 do not contain null value in last column.

So let’s do it:

ODU> blockedit

Entering block edit module.

BBED> set datafile 4

BBED> set block 9483

BBED> set offset 118

BBED> dump /x

-0–1–2–3–4–5–6–7–8–9–a–b–c–d–e–f-

0076 01 00 02 00 03 00 04 00 05 00 06 00 07 00 08 00 …………….

0086 09 00 0a 00 0b 00 0c 00 0d 00 0e 00 0f 00 10 00 …………….

0096 11 00 12 00 13 00 14 00 15 00 16 00 17 00 18 00 …………….

00a6 19 00 1a 00 1b 00 1c 00 1d 00 1e 00 1f 00 20 00 ………….. .

00b6 21 00 22 00 23 00 24 00 25 00 26 00 27 00 28 00 !.”.#.$.%.&.’.(.

00c6 29 00 2a 00 2b 00 2c 00 2d 00 2e 00 2f 00 30 00 ).*.+.,.-…/.0.

00d6 31 00 32 00 33 00 34 00 35 00 36 00 37 00 38 00 1.2.3.4.5.6.7.8.

00e6 39 00 3a 00 3b 00 3c 00 3d 00 3e 00 3f 00 40 00 9.:.;.<.=.>.?.@.

00f6 41 00 42 00 43 00 44 00 ff ff 00 00 00 00 00 00 A.B.C.D………

0106 00 00 00 00 00 00 00 2c 02 10 06 53 59 53 4d 41 …….,…SYSMA

Start search:

BBED> find 3c

-0–1–2–3–4–5–6–7–8–9–a–b–c–d–e–f-

00ec 3c 00 3d 00 3e 00 3f 00 40 00 41 00 42 00 43 00 <.=.>.?.@.A.B.C.

00fc 44 00 ff ff 00 00 00 00 00 00 00 00 00 00 00 00 D……………

010c 00 2c 02 10 06 53 59 53 4d 41 4e 17 4d 47 4d 54 .,…SYSMAN.MGMT

011c 5f 54 41 52 47 45 54 5f 43 52 45 44 45 4e 54 49 _TARGET_CREDENTI

012c 41 4c 53 ff 04 c3 07 49 61 ff 05 54 41 42 4c 45 ALS….Ia..TABLE

013c 07 78 6e 03 1e 0b 2b 18 07 78 6e 03 1e 0b 2d 21 .xn…+..xn…-!

014c 13 32 30 31 30 2d 30 33 2d 33 30 3a 31 30 2c 02 .2010-03-30:10,.

015c 10 06 53 59 53 4d 41 4e 12 53 59 53 5f 49 4f 54 ..SYSMAN.SYS_IOT

016c 5f 4f 56 45 52 5f 36 37 33 30 32 ff 04 c3 07 4a _OVER_67302….J

017c 04 04 c3 07 4a 04 05 54 41 42 4c 45 07 78 6e 03 ….J..TABLE.xn.

From the above search result, 0x3c003d don’t match our search string, so ignore it.

Continue search:

BBED> f

-0–1–2–3–4–5–6–7–8–9–a–b–c–d–e–f-

0268 3c 01 10 06 53 59 53 4d 41 4e 18 50 4b 5f 4d 47 <…SYSMAN.PK_MG

0278 4d 54 5f 45 4e 54 45 52 50 52 49 53 45 5f 43 52 MT_ENTERPRISE_CR

0288 45 44 53 ff 04 c3 07 4a 05 04 c3 07 4a 05 05 49 EDS….J….J..I

0298 4e 44 45 58 07 78 6e 03 1e 0b 2b 19 07 78 6e 03 NDEX.xn…+..xn.

02a8 1e 0b 2b 19 13 32 30 31 30 2d 30 33 2d 33 30 3a ..+..2010-03-30:

02b8 31 30 3a 34 32 3a 32 34 05 56 41 4c 49 44 01 4e 10:42:24.VALID.N

02c8 01 4e 01 4e 02 c1 05 ff 04 c3 3a 2f 5f 3c 01 10 .N.N……:/_<..

02d8 06 53 59 53 4d 41 4e 1b 4d 47 4d 54 5f 45 4e 54 .SYSMAN.MGMT_ENT

02e8 45 52 50 52 49 53 45 5f 43 52 45 44 45 4e 54 49 ERPRISE_CREDENTI

02f8 41 4c 53 ff 04 c3 07 4a 03 ff 05 54 41 42 4c 45 ALS….J…TABLE

‘f’ stands for go on search from the current position, from top to down.

From the above search result, 0x3c0110 match our search string, so the sixty-ninth record’s actual offset is 0x0268.

Continue search:

BBED> f

-0–1–2–3–4–5–6–7–8–9–a–b–c–d–e–f-

02d5 3c 01 10 06 53 59 53 4d 41 4e 1b 4d 47 4d 54 5f <…SYSMAN.MGMT_

02e5 45 4e 54 45 52 50 52 49 53 45 5f 43 52 45 44 45 ENTERPRISE_CREDE

02f5 4e 54 49 41 4c 53 ff 04 c3 07 4a 03 ff 05 54 41 NTIALS….J…TA

0305 42 4c 45 07 78 6e 03 1e 0b 2b 19 07 78 6e 03 1e BLE.xn…+..xn..

0315 0b 2d 21 13 32 30 31 30 2d 30 33 2d 33 30 3a 31 .-!.2010-03-30:1

0325 30 3a 34 32 3a 32 34 05 56 41 4c 49 44 01 4e 01 0:42:24.VALID.N.

0335 4e 01 4e 02 c1 02 ff 04 c3 3a 2f 5e 2c 00 0e 06 N.N……:/^,…

0345 53 59 53 4d 41 4e 18 50 4b 5f 4d 47 4d 54 5f 45 SYSMAN.PK_MGMT_E

0355 4e 54 45 52 50 52 49 53 45 5f 43 52 45 44 53 ff NTERPRISE_CREDS.

0365 04 c3 07 4a 05 04 c3 07 4a 05 05 49 4e 44 45 58 …J….J..INDEX

From the above search result, 0x3c0110 match our search string, so the sixty-eighth record’s actual offset is 0x02d5.

Continue the above search process, we can get all the 69 records’ actual offset.

Here I only search the last ten records’ actual offset:

69: 0x0268

68: 0x02d5

67: 0x03a8

66: 0x040f

65: 0x0476

64: 0x04dc

63: 0x0541

62: 0x05aa

61: 0x0612

60: 0x0679

I just mentioned, Oracle stores the relative offset in row directory.

We must add some base to relative offset to get the actual offset.

The algorithm of base is as follows:

For ASSM：base = 76+（itc-1)*24

For MSSM：base = 68+（itc-1)*24

Now, here we get last ten records’ actual offset, so we have to substract base to get these ten records’ relative offset.

The calculation results of these ten records’ relative offset are as follows:

69 (0x45) : 0x0268 – (76+（2-1)*24) = 0x0204

68 (0x44) : 0x02d5 – (76+（2-1)*24) = 0x0271

67 (0x43) : 0x03a8 – (76+（2-1)*24) = 0x0344

66 (0x42) : 0x040f – (76+（2-1)*24) = 0x03ab

65 (0x41) : 0x0476 – (76+（2-1)*24) = 0x0412

64 (0x40) : 0x04dc – (76+（2-1)*24) = 0x0478

63 (0x3f): 0x0541 – (76+（2-1)*24) = 0x04dd

62 (0x3e): 0x05aa – (76+（2-1)*24) = 0x0546

61 (0x3d): 0x0612 – (76+（2-1)*24) = 0x05ae

60 (0x3c): 0x0679 – (76+（2-1)*24) = 0x0615

Base on the above calculation results, plus we already know the actual kdbr offset of datafile 4 block 9483 is 94 + 24*(2-1) = 118,

so we can modify the datafile 4 block 9483’s last ten records’ relative offset in row directory:

BBED> set offset 118

BBED> dump /x

-0–1–2–3–4–5–6–7–8–9–a–b–c–d–e–f-

0076 01 00 02 00 03 00 04 00 05 00 06 00 07 00 08 00 …………….

0086 09 00 0a 00 0b 00 0c 00 0d 00 0e 00 0f 00 10 00 …………….

0096 11 00 12 00 13 00 14 00 15 00 16 00 17 00 18 00 …………….

00a6 19 00 1a 00 1b 00 1c 00 1d 00 1e 00 1f 00 20 00 ………….. .

00b6 21 00 22 00 23 00 24 00 25 00 26 00 27 00 28 00 !.”.#.$.%.&.’.(.

00c6 29 00 2a 00 2b 00 2c 00 2d 00 2e 00 2f 00 30 00 ).*.+.,.-…/.0.

00d6 31 00 32 00 33 00 34 00 35 00 36 00 37 00 38 00 1.2.3.4.5.6.7.8.

00e6 39 00 3a 00 3b 00 3c 00 3d 00 3e 00 3f 00 40 00 9.:.;.<.=.>.?.@.

00f6 41 00 42 00 43 00 44 00 ff ff 00 00 00 00 00 00 A.B.C.D………

0106 00 00 00 00 00 00 00 2c 02 10 06 53 59 53 4d 41 …….,…SYSMA

Let’s locate to the row 60 (0x3c):

BBED> set offset 0xec

BBED> dump /x

-0–1–2–3–4–5–6–7–8–9–a–b–c–d–e–f-

00ec 3c 00 3d 00 3e 00 3f 00 40 00 41 00 42 00 43 00 <.=.>.?.@.A.B.C.