- 论坛徽章:
- 0
|
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. |
|
免费注册
发表于 2008-08-28 11:01
