Yaping's Weblog

August 29, 2008

Get back data from truncated table without backup

Filed under: Oracle — Yaping @ 2:49 am
Tags: ,

Tables are truncated by human error, if there’s no backup, can these data be got back?

 

We know truncating table just update dictionary tables, table segment header and file header information and the actual data don’t be removed. If we can reverse these control data, it’s possible to get data back.

But there’s one condition that blocks allocated in the truncated table do not be reused.

 

I illuminate it by the following demo. I test it on Oracle 9204 under Intel platform, manual LMT tablespace.

 

@>create table test.t1(id number, a char(2000),b char(2000));

Table created.

 

@>insert into test.t1 select rownum,’A’,’B’ from dual connect by level < 1001;

1000 rows created.

 

@>commit;

Commit complete.

 

@>truncate table test.t1;

Table truncated.

 

@>select * from test.t1;

no rows selected

 

The demonstrated table just contains one segment and single free list group, if it’s a multi-segments or it has indexes, it’ll be more complex.

 

Now we dump redo log data which contains the data of truncating table.

From the redo, we can find that it update dictionary tab$, seg$, obj$ and tsq$. It changes data object id and time stamp in the first three tables. Every extent excluding the first one will be removed from segment header, from bottom to above, removing one extent every time, and update tsq$, update file header bitmap data to free these blocks.

 

If the redo log/archived log which contains the truncating table information is overwritten/lost, what should we do? We can find old data object id from the first extent of this table. Then we use this id to scan all data files in the tablespace which contains this table to find out blocks which are allocated to this table. Then we reconstruct the extents according to the space management algorithm.

 

We extract the following items from redo log, it can be used to rebuild the extent information.

 

DBA:0x00c00002

Begin: 1033, Length: 128, Instance: 0

Begin: 905, Length: 128, Instance: 0

Begin: 777, Length: 128, Instance: 0

Begin: 649, Length: 128, Instance: 0

Begin: 521, Length: 128, Instance: 0

Begin: 393, Length: 128, Instance: 0

Begin: 265, Length: 128, Instance: 0

Begin: 257, Length: 8, Instance: 0

Begin: 249, Length: 8, Instance: 0

Begin: 241, Length: 8, Instance: 0

Begin: 233, Length: 8, Instance: 0

Begin: 225, Length: 8, Instance: 0

Begin: 217, Length: 8, Instance: 0

Begin: 209, Length: 8, Instance: 0

Begin: 201, Length: 8, Instance: 0

Begin: 193, Length: 8, Instance: 0

Begin: 185, Length: 8, Instance: 0

Begin: 177, Length: 8, Instance: 0

Begin: 169, Length: 8, Instance: 0

Begin: 161, Length: 8, Instance: 0

Begin: 153, Length: 8, Instance: 0

Begin: 145, Length: 8, Instance: 0

 

DBA:0x00c00003

Begin: 128, Length: 16

Begin: 112, Length: 16

Begin: 96, Length: 16

Begin: 80, Length: 16

Begin: 64, Length: 16

Begin: 48, Length: 16

Begin: 32, Length: 16

Begin: 31, Length: 1

Begin: 30, Length: 1

Begin: 29, Length: 1

Begin: 28, Length: 1

Begin: 27, Length: 1

Begin: 26, Length: 1

Begin: 25, Length: 1

Begin: 24, Length: 1

Begin: 23, Length: 1

Begin: 22, Length: 1

Begin: 21, Length: 1

Begin: 20, Length: 1

Begin: 19, Length: 1

Begin: 18, Length: 1

Begin: 17, Length: 1

 

 

The following data indicates changing dictionary. I omit tsq$, there’re 22 $tsq records here.

 

TAB$

———————

undo

col  0: [ 3]  c2 41 1d

col 35: [ 7]  78 6c 02 19 14 28 2a

 

redo

col  0: [ 3]  c2 41 1f

col 35: [ 7]  78 6c 02 19 15 09 05

———————–

 

 

SEG$

————————

undo

col 14: [ 3]  c2 41 1d

 

redo

col 14: [ 3]  c2 41 1f

————————

 

 

OBJ$

————————

undo

col  1: [ 3]  c2 41 1d

col  8: [ 7]  78 6c 02 19 14 28 2a

 

 

redo

col  1: [ 3]  c2 41 1f

col  8: [ 7]  78 6c 02 19 15 09 05

———————–

 

 

After we get this information, now we can rebuild the table segment header. The highlighted data with –> symbol is reverted data.

 

 

Start dump data blocks tsn: 3 file#: 3 minblk 137 maxblk 137

buffer tsn: 3 rdba: 0x00c00089 (3/137)

scn: 0x0000.000bba9a seq: 0x01 flg: 0x04 tail: 0xba9a1001

frmt: 0x02 chkval: 0x5f6a type: 0x10=DATA SEGMENT HEADER – UNLIMITED

  Extent Control Header

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

  Extent Header:: spare1: 0      spare2: 0      #extents: 1 –> 23    #blocks: 7 –> 1023

                  last map  0x00000000  #maps: 0      offset: 4128

      Highwater::  0x00c0008a –> 0x00c00472 ext#: 0 –> 22     blk#: 0  –> 105    ext size: 7 –> 128

  #blocks in seg. hdr’s freelists: 0  –> 1

  #blocks below: 0  –> 1000

  mapblk  0x00000000  offset: 0 –> 22

                   Unlocked

     Map Header:: next  0x00000000  #extents: 1 –> 23   obj#: 6430 –> 6428  flag: 0x40000000

  Extent Map

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

   0x00c0008a  length: 7

–> add the following items

   0x00c00091  length: 8

   0x00c00099  length: 8

   0x00c000a1  length: 8

   0x00c000a9  length: 8

   0x00c000b1  length: 8

   0x00c000b9  length: 8

   0x00c000c1  length: 8

   0x00c000c9  length: 8

   0x00c000d1  length: 8

   0x00c000d9  length: 8

   0x00c000e1  length: 8

   0x00c000e9  length: 8

   0x00c000f1  length: 8

   0x00c000f9  length: 8

   0x00c00101  length: 8

   0x00c00109  length: 128

   0x00c00189  length: 128

   0x00c00209  length: 128

   0x00c00289  length: 128

   0x00c00309  length: 128

   0x00c00389  length: 128

   0x00c00409  length: 128

 

  nfl = 1, nfb = 1 typ = 1 nxf = 0 ccnt = 0  –> 213

  SEG LST:: flg: UNUSED –> USED lhd: 0x00000000 –> 0x00c00471 ltl: 0x00000000 –> 0x00c00471

End dump data blocks tsn: 3 file#: 3 minblk 137 maxblk 137

 

 

#blocks: 1023 (15*8 + 7*128 + 7)

Highwater: 0x00c00472 (0x00c00471 + 1)

blk#: 105 (1137 – 1033 + 1)

#blocks below: 1000 (1137 – 137)

ccnt: 213

1. Ext0 7 blocks –> 5 + ceil(2/5) –> 6

2. Ext1 — Ext15 8 blocks –> ceil(8/5) * 15 –> 30

3. Ext16 — Ext21 128 blocks –> ceil(128/5) * 6 –> 156

4. Ext22 105 (1137 – 1033 + 1) blocks –> ceil(105/5) –> 21

Total: 6 + 30 + 156 + 21 –> 213

 

We can use the following method to dump block to decide which/how many blocks are in the free list.

 

1033 + 128/2 –> 1097 –> 1097 + 64/2 –> 1129 –> 1129 + 32/2 –> 1145 (<) –> 1145 – 16/2 –> 1137 –> 1137 + 8/2 –> 1141 (<) –> 1141 – 4/2 –> 1139 (<) –> 1138 (<) –> 1137 –> only 1137 in free list

 

We have rebuilt the previous table segment header data, now we use bbed to change it.

 

BBED> p ktech

struct ktech, 72 bytes                      @20

   ub4 spare1_ktech                         @20       0x00000000

   word tsn_ktech                           @24       0

   ub4 lastmap_ktech                        @28       0x00000000

   ub4 mapcount_ktech                       @32       0x00000000

   ub4 extents_ktech                        @36       0x00000001 –> 0x00000017  modify /x 17000000 dba 3,137 offset 36

   ub4 blocks_ktech                         @40       0x00000007 –> 0x000003ff  modify /x ff030000 dba 3,137 offset 40

   ub2 mapend_ktech                         @44       0x1020

   struct hwmark_ktech, 32 bytes            @48

      ub4 extno_ktehw                       @48       0x00000000 –> 0x00000016  modify /x 16000000 dba 3,137 offset 48

      ub4 blkno_ktehw                       @52       0x00000000 –> 0x00000069  modify /x 69000000 dba 3,137 offset 52

      ub4 extsize_ktehw                     @56       0x00000007 –> 0x00000080  modify /x 80000000 dba 3,137 offset 56

      ub4 blkaddr_ktehw                     @60       0x00c0008a –> 0x00c00472  modify /x 7204c000 dba 3,137 offset 60

      ub4 mapblk_ktehw                      @64       0x00000000

      ub4 offset_ktehw                      @68       0x00000000 –> 0x00000016  modify /x 16000000 dba 3,137 offset 68

      ub4 flblks_ktehw                      @72       0x00000000 –> 0x00000001  modify /x 01000000 dba 3,137 offset 72

      ub4 blkcnt_ktehw                      @76       0x00000000 –> 0x000003e8  modify /x e8030000 dba 3,137 offset 76

   struct locker_ktech, 8 bytes             @80

      ub2 kxidusn                           @80       0x0000

      ub2 kxidslt                           @82       0x0000

      ub4 kxidsqn                           @84       0x00000000

   ub4 flag_ktech                           @88       0x00000000 (NONE)

 

 

BBED> p ktemh

struct ktemh, 16 bytes                      @92

   ub4 count_ktemh                          @92       0x00000001 –> 0x00000017  modify /x 17000000 dba 3,137 offset 92

   ub4 next_ktemh                           @96       0x00000000

   ub4 obj_ktemh                            @100      0x0000191e –> 0x0000191c  modify /x 1c190000 dba 3,137 offset 100

   ub4 flag_ktemh                           @104      0x40000000

 

 

BBED> p ktetb

struct ktetb[0], 8 bytes                    @108

   ub4 ktetbdba                             @108      0x00c0008a

   ub4 ktetbnbk                             @112      0x00000007

 

–> add the following items

struct ktetb[1], 8 bytes                    @116

   ub4 ktetbdba                             @116      –> 0x00c00091  modify /x 9100c000 dba 3,137 offset 116

   ub4 ktetbnbk                             @120      –> 0x00000008  modify /x 08000000 dba 3,137 offset 120

struct ktetb[2], 8 bytes                    @124

   ub4 ktetbdba                             @124      –> 0x00c00099  modify /x 9900c000 dba 3,137 offset 124

   ub4 ktetbnbk                             @128      –> 0x00000008  modify /x 08000000 dba 3,137 offset 128

struct ktetb[3], 8 bytes                    @132

   ub4 ktetbdba                             @132      –> 0x00c000a1  modify /x a100c000 dba 3,137 offset 132

   ub4 ktetbnbk                             @136      –> 0x00000008  modify /x 08000000 dba 3,137 offset 136

struct ktetb[4], 8 bytes                    @140

   ub4 ktetbdba                             @140      –> 0x00c000a9  modify /x a900c000 dba 3,137 offset 140

   ub4 ktetbnbk                             @144      –> 0x00000008  modify /x 08000000 dba 3,137 offset 144

struct ktetb[5], 8 bytes                    @148

   ub4 ktetbdba                             @148      –> 0x00c000b1  modify /x b100c000 dba 3,137 offset 148

   ub4 ktetbnbk                             @152      –> 0x00000008  modify /x 08000000 dba 3,137 offset 152

struct ktetb[6], 8 bytes                    @156

   ub4 ktetbdba                             @156      –> 0x00c000b9  modify /x b900c000 dba 3,137 offset 156

   ub4 ktetbnbk                             @160      –> 0x00000008  modify /x 08000000 dba 3,137 offset 160

struct ktetb[7], 8 bytes                    @164

   ub4 ktetbdba                             @164      –> 0x00c000c1  modify /x c100c000 dba 3,137 offset 164

   ub4 ktetbnbk                             @168      –> 0x00000008  modify /x 08000000 dba 3,137 offset 168

struct ktetb[8], 8 bytes                    @172

   ub4 ktetbdba                             @172      –> 0x00c000c9  modify /x c900c000 dba 3,137 offset 172

   ub4 ktetbnbk                             @176      –> 0x00000008  modify /x 08000000 dba 3,137 offset 176

struct ktetb[9], 8 bytes                    @180

   ub4 ktetbdba                             @180      –> 0x00c000d1  modify /x d100c000 dba 3,137 offset 180

   ub4 ktetbnbk                             @184      –> 0x00000008  modify /x 08000000 dba 3,137 offset 184

struct ktetb[10], 8 bytes                   @188

   ub4 ktetbdba                             @188      –> 0x00c000d9  modify /x d900c000 dba 3,137 offset 188

   ub4 ktetbnbk                             @192      –> 0x00000008  modify /x 08000000 dba 3,137 offset 192

struct ktetb[11], 8 bytes                   @196

   ub4 ktetbdba                             @196      –> 0x00c000e1  modify /x e100c000 dba 3,137 offset 196

   ub4 ktetbnbk                             @200      –> 0x00000008  modify /x 08000000 dba 3,137 offset 200

struct ktetb[12], 8 bytes                   @204

   ub4 ktetbdba                             @204      –> 0x00c000e9  modify /x e900c000 dba 3,137 offset 204

   ub4 ktetbnbk                             @208      –> 0x00000008  modify /x 08000000 dba 3,137 offset 208

struct ktetb[13], 8 bytes                   @212

   ub4 ktetbdba                             @212      –> 0x00c000f1  modify /x f100c000 dba 3,137 offset 212

   ub4 ktetbnbk                             @216      –> 0x00000008  modify /x 08000000 dba 3,137 offset 216

struct ktetb[14], 8 bytes                   @220

   ub4 ktetbdba                             @220      –> 0x00c000f9  modify /x f900c000 dba 3,137 offset 220

   ub4 ktetbnbk                             @224      –> 0x00000008  modify /x 08000000 dba 3,137 offset 224

struct ktetb[15], 8 bytes                   @228

   ub4 ktetbdba                             @228      –> 0x00c00101  modify /x 0101c000 dba 3,137 offset 228

   ub4 ktetbnbk                             @232      –> 0x00000008  modify /x 08000000 dba 3,137 offset 232

struct ktetb[16], 8 bytes                   @236

   ub4 ktetbdba                             @236      –> 0x00c00109  modify /x 0901c000 dba 3,137 offset 236

   ub4 ktetbnbk                             @240      –> 0x00000080  modify /x 80000000 dba 3,137 offset 240

struct ktetb[17], 8 bytes                   @244

   ub4 ktetbdba                             @244      –> 0x00c00189  modify /x 8901c000 dba 3,137 offset 244

   ub4 ktetbnbk                             @248      –> 0x00000080  modify /x 80000000 dba 3,137 offset 248

struct ktetb[18], 8 bytes                   @252

   ub4 ktetbdba                             @252      –> 0x00c00209  modify /x 0902c000 dba 3,137 offset 252

   ub4 ktetbnbk                             @256      –> 0x00000080  modify /x 80000000 dba 3,137 offset 256

struct ktetb[19], 8 bytes                   @260

   ub4 ktetbdba                             @260      –> 0x00c00289  modify /x 8902c000 dba 3,137 offset 260

   ub4 ktetbnbk                             @264      –> 0x00000080  modify /x 80000000 dba 3,137 offset 264

struct ktetb[20], 8 bytes                   @268

   ub4 ktetbdba                             @268      –> 0x00c00309  modify /x 0903c000 dba 3,137 offset 268

   ub4 ktetbnbk                             @272      –> 0x00000080  modify /x 80000000 dba 3,137 offset 272

struct ktetb[21], 8 bytes                   @276

   ub4 ktetbdba                             @276      –> 0x00c00389  modify /x 8903c000 dba 3,137 offset 276

   ub4 ktetbnbk                             @280      –> 0x00000080  modify /x 80000000 dba 3,137 offset 280

struct ktetb[22], 8 bytes                   @284

   ub4 ktetbdba                             @284      –> 0x00c00409  modify /x 0904c000 dba 3,137 offset 284

   ub4 ktetbnbk                             @288      –> 0x00000080  modify /x 80000000 dba 3,137 offset 288

 

 

 

BBED> p ktsfs_seg

struct ktsfs_seg[0], 20 bytes               @4156

   ub2 ktsfsflg                             @4156     0x0000 (NONE) –> 0x0001  modify /x 0100 dba 3,137 offset 4156

   struct ktsfsxid, 8 bytes                 @4160

      ub2 kxidusn                           @4160     0x0000

      ub2 kxidslt                           @4162     0x0000

      ub4 kxidsqn                           @4164     0x00000000  –> 0x000000d5  modify /x d5000000 dba 3,137 offset 4164

   ub4 ktsfslhd                             @4168     0x00000000  –> 0x00c00471  modify /x 7104c000 dba 3,137 offset 4168

   ub4 ktsfsltl                             @4172     0x00000000  –> 0x00c00471  modify /x 7104c000 dba 3,137 offset 4172

 

 

After rebuild the table segment header, we should check whether the blocks allocated in this table have been reused by other objects.

 

We use sql statement to change dictionary tables, here I just update object data id.

 

@>select DATAOBJ# from tab$ where OBJ#=6428;

  DATAOBJ#

———-

      6430

 

@>update tab$ set DATAOBJ#=6428 where OBJ#=6428;

1 row updated.

 

@>select DATAOBJ# from obj$ where OBJ#=6428;

  DATAOBJ#

———-

      6430

 

@>update obj$ set DATAOBJ#=6428 where OBJ#=6428;

1 row updated.

 

@>select “HWMINCR” from “SYS”.”SEG$” where “HWMINCR”=6430;

   HWMINCR

———-

      6430

 

@>update “SYS”.”SEG$” set “HWMINCR” =6428 where “HWMINCR”=6430;

1 row updated.

 

@>commit;

Commit complete.

 

@>select count(*) from test.t1;

  COUNT(*)

———-

      1000

 

[oracle@chen ~]$ exp test/test tables=t1 file=t1.dmp log=t1.log

Export: Release 9.2.0.4.0 – Production on Thu Feb 28 06:40:14 2008

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

Connected to: Oracle9i Enterprise Edition Release 9.2.0.4.0 – Production

With the Partitioning, OLAP and Oracle Data Mining options

JServer Release 9.2.0.4.0 – Production

Export done in US7ASCII character set and AL16UTF16 NCHAR character set

About to export specified tables via Conventional Path …

. . exporting table                             T1       1000 rows exported

Export terminated successfully without warnings.

 

 

 

Because bbed can’t modify file allocated bitmap data, so the file usage status doesn’t be refreshed. But we can use package dbms_space_admin to rebuild the bitmap data. To avoid the unexpected things, we should export data immediately. Now I take a cold full backup, it will be used late.

 

@>@dumpf 3 2 3

PL/SQL procedure successfully completed.

 

@>@trace

/opt/app/oracle/admin/chen/udump/chen_ora_3728.trc

Start dump data blocks tsn: 3 file#: 3 minblk 2 maxblk 3

buffer tsn: 3 rdba: 0x00c00002 (3/2)

scn: 0x0000.000bba87 seq: 0x01 flg: 0x04 tail: 0xba871d01

frmt: 0x02 chkval: 0x1b2e type: 0x1d=KTFB Bitmapped File Space Header

File Space Header Block:

Header Control:

RelFno: 3, Unit: 8, Size: 1280, Flag: 1

AutoExtend: NO, Increment: 0, MaxSize: 0

Initial Area: 7, Tail: 1280, First: 17, Free: 127

Header Opcode:

Save: No Pending Op

buffer tsn: 3 rdba: 0x00c00003 (3/3)

scn: 0x0000.000bba87 seq: 0x01 flg: 0x04 tail: 0xba871e01

frmt: 0x02 chkval: 0x6f2c type: 0x1e=KTFB Bitmapped File Space Bitmap

File Space Bitmap Block:

BitMap Control:

RelFno: 3, BeginBlock: 9, Flag: 0, First: 17, Free: 63456

FFFF010000000000 0000000000000000 0000FF7F00000000 0000000000000000

 

 

@>exec dbms_space_admin.TABLESPACE_REBUILD_BITMAPS(‘TEST’);

PL/SQL procedure successfully completed.

 

@>@dumpf 3 2 3

PL/SQL procedure successfully completed.

 

@>@trace

/opt/app/oracle/admin/chen/udump/chen_ora_3834.trc

Start dump data blocks tsn: 3 file#: 3 minblk 2 maxblk 3

buffer tsn: 3 rdba: 0x00c00002 (3/2)

scn: 0x0000.000c0b6e seq: 0x01 flg: 0x00 tail: 0x0b6e1d01

frmt: 0x02 chkval: 0x0000 type: 0x1d=KTFB Bitmapped File Space Header

File Space Header Block:

Header Control:

RelFno: 3, Unit: 8, Size: 1280, Flag: 1

AutoExtend: NO, Increment: 0, MaxSize: 0

Initial Area: 7, Tail: 1280, First: 159, Free: 0

Header Opcode:

Save: No Pending Op

buffer tsn: 3 rdba: 0x00c00003 (3/3)

scn: 0x0000.000c0b6e seq: 0x01 flg: 0x00 tail: 0x0b6e1e01

frmt: 0x02 chkval: 0x0000 type: 0x1e=KTFB Bitmapped File Space Bitmap

File Space Bitmap Block:

BitMap Control:

RelFno: 3, BeginBlock: 9, Flag: 0, First: 159, Free: 63329

FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF FFFFFF7F00000000 0000000000000000

 

 

I illuminate how to use bbed and package to rebuild block structure above. Until now, this table can be operated as common tables, such as DML, allocate new extents.

 

 

Now I introduce OS commands to achieve the same aim. These OS commands are echo, dd and od, they’re all ordinary, but they’re very powerful.

I restore the cold backup files did just now. Here I just demonstrate how to modify file header information.

 

Start dump data blocks tsn: 3 file#: 3 minblk 2 maxblk 3

buffer tsn: 3 rdba: 0x00c00002 (3/2)

scn: 0x0000.000bba87 seq: 0x01 flg: 0x04 tail: 0xba871d01

frmt: 0x02 chkval: 0x1b2e type: 0x1d=KTFB Bitmapped File Space Header

File Space Header Block:

Header Control:

RelFno: 3, Unit: 8, Size: 1280, Flag: 1

AutoExtend: NO, Increment: 0, MaxSize: 0

Initial Area: 7, Tail: 1280, First: 17 –> 159, Free: 127 –> 0

Header Opcode:

Save: No Pending Op

buffer tsn: 3 rdba: 0x00c00003 (3/3)

scn: 0x0000.000bba87 seq: 0x01 flg: 0x04 tail: 0xba871e01

frmt: 0x02 chkval: 0x6f2c type: 0x1e=KTFB Bitmapped File Space Bitmap

File Space Bitmap Block:

BitMap Control:

RelFno: 3, BeginBlock: 9, Flag: 0, First: 17 –> 159, Free: 63456 –> 63329

FFFF010000000000 0000000000000000 0000FF7F00000000 0000000000000000 –> FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF FFFFFF7F00000000 0000000000000000

 

dba 3,2:

chkval: see below

First: 128 + 16 + 15 –> 159 –> x9f

— according to FFFF01, the last bitmap is 17th, and the truncated table space allocated start from 17th and all space (16*7 + 1*15) is conterminous. The truncated table’s last bitmap is 144th, the first bitmap is 145th within FF7F.

 

Free: 127 – (16*7 + 1*15) –> 0

 

dba 3,3

Free: 63456 – 127 –> 63329 –> f761

 

 

[oracle@chen 9204]$ dd if=test01.dbf bs=8192 skip=2 count=2|od -x

0000000 021d 0000 0002 00c0 ba87 000b 0000 0401 — dba 3,2

0000020 1b2e 0000 0003 0000 0008 0000 0500 0000 — 1b2e –> ?

0000040 0001 0000 0000 0000 0000 0000 0007 0000

0000060 0500 0000 0011 0000 007f 0000 0000 0000 — 11 –> 9f   7f –> 00

0000100 0000 0000 0000 0000 0000 0000 0000 0000

0000120 0091 0000 0008 0000 0000 0000 0000 0000

0000140 0000 0000 0000 0000 0000 0000 0000 0000

*

0017760 0000 0000 0000 0000 0000 0000 1d01 ba87

2+0 records in

2+0 records out

0020000 021e 0000 0003 00c0 ba87 000b 0000 0401 — dba 3,3

0020020 6f2c 0000 0003 0000 0009 0000 0000 0000 — 6f2c –> ?

0020040 0011 0000 f7e0 0000 0000 0000 0000 0000 — 11 –> 9f   f7e0 –> f761

0020060 0000 0000 0000 0000 ffff 0001 0000 0000  — 0001 0000 0000 –> ffff ffff ffff

0020100 0000 0000 0000 0000 0000 7fff 0000 0000 — 0000 0000 0000 0000 0000 –> ffff ffff ffff ffff ffff

0020120 0000 0000 0000 0000 0000 0000 0000 0000

*

0037760 0000 0000 0000 0000 0000 0000 1e01 ba87

0040000

 

 

According to the above value, we run the following OS commands to change it, but these commands don’t contain the one of changing checksum, it will be explained at last.

 

11 –> 9f

echo -ne “\x9f\x00″|dd of=test01.dbf bs=1 seek=16436 count=2 conv=notrunc

 

7f –> 00

echo -ne “\x00\x00″|dd of=test01.dbf bs=1 seek=16440 count=2 conv=notrunc

 

11 –> 9f

echo -ne “\x9f\x00″|dd of=test01.dbf bs=1 seek=24608 count=2 conv=notrunc

 

f7e0 –> f761

echo -ne “\x61\xf7″|dd of=test01.dbf bs=1 seek=24612 count=2 conv=notrunc

 

0001 0000 0000 0000 0000 0000 0000 0000 –> ffff ffff ffff ffff ffff ffff ffff ffff

echo -ne “\xff\xff\xff\xff\xff\xff\xff\xff\xff\xff\xff\xff\xff\xff\xff\xff”|dd of=test01.dbf bs=1 seek=24634 count=16 conv=notrunc

 

The following is the new content of the two blocks.

 

[oracle@chen 9204]$ dd if=test01.dbf bs=8192 skip=2 count=2|od -x

0000000 021d 0000 0002 00c0 ba87 000b 0000 0401

0000020 1b2e 0000 0003 0000 0008 0000 0500 0000

0000040 0001 0000 0000 0000 0000 0000 0007 0000

0000060 0500 0000 009f 0000 0000 0000 0000 0000

0000100 0000 0000 0000 0000 0000 0000 0000 0000

0000120 0091 0000 0008 0000 0000 0000 0000 0000

0000140 0000 0000 0000 0000 0000 0000 0000 0000

*

0017760 0000 0000 0000 0000 0000 0000 1d01 ba87

2+0 records in

2+0 records out

0020000 021e 0000 0003 00c0 ba87 000b 0000 0401

0020020 6f2c 0000 0003 0000 0009 0000 0000 0000

0020040 009f 0000 f761 0000 0000 0000 0000 0000

0020060 0000 0000 0000 0000 ffff ffff ffff ffff

0020100 ffff ffff ffff ffff ffff 7fff 0000 0000

0020120 0000 0000 0000 0000 0000 0000 0000 0000

*

0037760 0000 0000 0000 0000 0000 0000 1e01 ba87

0040000

 

We now consider computing the checksum. The checksum is the XOR of all the other 2-byte pairs in the block. Here create one function to compute the checksum.

 

@>create or replace function checksum(p_str varchar2)

  2    return varchar2 as

  3    v_str varchar2(2000);

  4    v_raw1 varchar2(4);

  5    v_raw2 varchar2(4);

  6  begin

  7

  8    v_str:=rtrim(ltrim(p_str));

  9    v_raw1:=substr(v_str,1,4);

 10    v_str:=rtrim(ltrim(substr(v_str,5)));

 11

 12    while length(v_str)>0 loop

 13          v_raw2:=substr(v_str,1,4);

 14          v_raw1:=utl_raw.bit_xor(v_raw1,v_raw2);

 15          v_str:=rtrim(ltrim(substr(v_str,5)));

 16    end loop;

 17

 18    return ‘check sum :’||lower(v_raw1);

 19  end;

 20  /

Function created.

 

First, compute the checksum of original blocks to validate the function, then compute the new checksum.

 

@>select checksum(‘021d 0000 0002 00c0 ba87 000b 0000 0401 0000 0000 0003 0000 0008 0000 0500 0000 0001 0000 0000 0000 0000 0000 0007 0000 0500 0000 0011 0000 007f 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0091 0000 0008 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 1d01 ba87′) check_sum from dual;

CHECK_SUM

—————————————————————————————

check sum :1b2e

 

@>select checksum(‘021e 0000 0003 00c0 ba87 000b 0000 0401 0000 0000 0003 0000 0009 0000 0000 0000 0011 0000 f7e0 0000 0000 0000 0000 0000 0000 0000 0000 0000 ffff 0001 0000 0000 0000 0000 0000 0000 0000 7fff 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 1e01 ba87′) check_sum from dual;

CHECK_SUM

—————————————————————————————

check sum :6f2c

 

@>select checksum(‘021d 0000 0002 00c0 ba87 000b 0000 0401 0000 0000 0003 0000 0008 0000 0500 0000 0001 0000 0000 0000 0000 0000 0007 0000 0500 0000 009f 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0091 0000 0008 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 1d01 ba87′) check_sum from dual;

CHECK_SUM

—————————————————————————————

check sum :1bdf

 

@>select checksum(‘021e 0000 0003 00c0 ba87 000b 0000 0401 0000 0000 0003 0000 0009 0000 0000 0000 009f 0000 f761 0000 0000 0000 0000 0000 0000 0000 0000 0000 ffff ffff ffff ffff ffff ffff ffff ffff ffff 7fff 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 1e01 ba87′) check_sum from dual;

CHECK_SUM

—————————————————————————————

check sum :6f22

 

 

1b2e –> 1bdf

echo -ne “\xdf\x1b”|dd of=test01.dbf bs=1 seek=16400 count=2 conv=notrunc

 

6f2c –> 6f22

echo -ne “\x22\x6f”|dd of=test01.dbf bs=1 seek=24592 count=2 conv=notrunc

 

 

Appendix

 

kcbh.flg

value

Comment

0x00

 

0x01

New block – zero data area

0x02

last change to the block was for a cleanout operation

0x04

checksum value is set

0x05

 

0x06

 

0x08

Temporary block

 

 

 

 

 

 

References

Disassembling the Oracle Data Block

Advanced Backup, Restore, and Recover Techniques

Recovery architecture Components

Advertisements

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: