[20181212]关于truncate reuse storage.txt

来源：这里教程网时间：2026-03-03 12:44:11 作者：

[20181212]关于truncate reuse storage.txt --//前一阵子,尝试了truncate的恢复,链接如下: http://blog.itpub.net/267265/viewspace-2156936/ http://blog.itpub.net/267265/viewspace-2157144/ --//一种方式是尝试修改相关数据段的数据库的段号实现的.另外是修改数据字典,来恢复truncate表. --//注意truncate还是使用reuse storage选项,存储空间不会回收. --//理论将如果能修改高水位标识,就不用执行执行里面扫描代码完成数据的恢复,而且那种方法很慢. --//今天探究一下truncate reuse storage主要改动什么? 1.环境: SCOTT@book> @ ver1 PORT_STRING VERSION BANNER ------------------------------ -------------- -------------------------------------------------------------------------------- x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production 2.建立测试环境: SCOTT@book> create table t as select * from all_objects ; Table created. SCOTT@book> select object_id,data_object_id from dba_objects where owner=user and object_name='T'; OBJECT_ID DATA_OBJECT_ID ---------- -------------- 90551 90551 SCOTT@book> select count(*) from t; COUNT(*) ---------- 84769 SCOTT@book> column PARTITION_NAME noprint SCOTT@book> select * from dba_extents where owner=user and segment_name='T'; OWNER SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME EXTENT_ID FILE_ID BLOCK_ID BYTES BLOCKS RELATIVE_FNO ----- ------------ ------------ --------------- --------- ------- -------- ------- ------ ------------ SCOTT T TABLE USERS 0 4 520 65536 8 4 SCOTT T TABLE USERS 1 4 528 65536 8 4 SCOTT T TABLE USERS 2 4 536 65536 8 4 SCOTT T TABLE USERS 3 4 544 65536 8 4 SCOTT T TABLE USERS 4 4 552 65536 8 4 SCOTT T TABLE USERS 5 4 560 65536 8 4 SCOTT T TABLE USERS 6 4 672 65536 8 4 SCOTT T TABLE USERS 7 4 680 65536 8 4 SCOTT T TABLE USERS 8 4 688 65536 8 4 SCOTT T TABLE USERS 9 4 696 65536 8 4 SCOTT T TABLE USERS 10 4 704 65536 8 4 SCOTT T TABLE USERS 11 4 712 65536 8 4 SCOTT T TABLE USERS 12 4 720 65536 8 4 SCOTT T TABLE USERS 13 4 728 65536 8 4 SCOTT T TABLE USERS 14 4 736 65536 8 4 SCOTT T TABLE USERS 15 4 744 65536 8 4 SCOTT T TABLE USERS 16 4 768 1048576 128 4 SCOTT T TABLE USERS 17 4 896 1048576 128 4 SCOTT T TABLE USERS 18 4 1024 1048576 128 4 SCOTT T TABLE USERS 19 4 1152 1048576 128 4 SCOTT T TABLE USERS 20 4 1280 1048576 128 4 SCOTT T TABLE USERS 21 4 1408 1048576 128 4 SCOTT T TABLE USERS 22 4 1536 1048576 128 4 SCOTT T TABLE USERS 23 4 1664 1048576 128 4 SCOTT T TABLE USERS 24 4 1792 1048576 128 4 25 rows selected. SCOTT@book> select SEGMENT_NAME,HEADER_FILE,HEADER_BLOCK from dba_segments where owner=user and segment_name='T'; SEGMENT_NAME HEADER_FILE HEADER_BLOCK -------------------- ----------- ------------ T 4 522 SCOTT@book> alter system checkpoint ; System altered. SCOTT@book> alter system dump datafile 4 block 522; System altered. 3.看看段头信息: Extent Header:: spare1: 0 spare2: 0 #extents: 25 #blocks: 1280 last map 0x00000000 #maps: 0 offset: 2716 Highwater:: 0x01000757 ext#: 24 blk#: 87 ext size: 128 #blocks in seg. hdr's freelists: 0 #blocks below: 1239 mapblk 0x00000000 offset: 24 Unlocked -------------------------------------------------------- Low HighWater Mark : Highwater:: 0x01000757 ext#: 24 blk#: 87 ext size: 128 #blocks in seg. hdr's freelists: 0 #blocks below: 1239 mapblk 0x00000000 offset: 24 Level 1 BMB for High HWM block: 0x01000701 Level 1 BMB for Low HWM block: 0x01000701 -------------------------------------------------------- Segment Type: 1 nl2: 1 blksz: 8192 fbsz: 0 L2 Array start offset: 0x00001434 First Level 3 BMB: 0x00000000 L2 Hint for inserts: 0x01000209 Last Level 1 BMB: 0x01000701 Last Level II BMB: 0x01000209 Last Level III BMB: 0x00000000 Map Header:: next 0x00000000 #extents: 25 obj#: 90551 flag: 0x10000000 Inc # 0 Extent Map ----------------------------------------------------------------- 0x01000208 length: 8 0x01000210 length: 8 0x01000218 length: 8 0x01000220 length: 8 0x01000228 length: 8 0x01000230 length: 8 0x010002a0 length: 8 0x010002a8 length: 8 0x010002b0 length: 8 0x010002b8 length: 8 0x010002c0 length: 8 0x010002c8 length: 8 0x010002d0 length: 8 0x010002d8 length: 8 0x010002e0 length: 8 0x010002e8 length: 8 0x01000300 length: 128 0x01000380 length: 128 0x01000400 length: 128 0x01000480 length: 128 0x01000500 length: 128 0x01000580 length: 128 0x01000600 length: 128 0x01000680 length: 128 0x01000700 length: 128 Auxillary Map -------------------------------------------------------- Extent 0 : L1 dba: 0x01000208 Data dba: 0x0100020b Extent 1 : L1 dba: 0x01000208 Data dba: 0x01000210 Extent 2 : L1 dba: 0x01000218 Data dba: 0x01000219 Extent 3 : L1 dba: 0x01000218 Data dba: 0x01000220 Extent 4 : L1 dba: 0x01000228 Data dba: 0x01000229 Extent 5 : L1 dba: 0x01000228 Data dba: 0x01000230 Extent 6 : L1 dba: 0x010002a0 Data dba: 0x010002a1 Extent 7 : L1 dba: 0x010002a0 Data dba: 0x010002a8 Extent 8 : L1 dba: 0x010002b0 Data dba: 0x010002b1 Extent 9 : L1 dba: 0x010002b0 Data dba: 0x010002b8 Extent 10 : L1 dba: 0x010002c0 Data dba: 0x010002c1 Extent 11 : L1 dba: 0x010002c0 Data dba: 0x010002c8 Extent 12 : L1 dba: 0x010002d0 Data dba: 0x010002d1 Extent 13 : L1 dba: 0x010002d0 Data dba: 0x010002d8 Extent 14 : L1 dba: 0x010002e0 Data dba: 0x010002e1 Extent 15 : L1 dba: 0x010002e0 Data dba: 0x010002e8 Extent 16 : L1 dba: 0x01000300 Data dba: 0x01000302 Extent 17 : L1 dba: 0x01000380 Data dba: 0x01000382 Extent 18 : L1 dba: 0x01000400 Data dba: 0x01000402 Extent 19 : L1 dba: 0x01000480 Data dba: 0x01000482 Extent 20 : L1 dba: 0x01000500 Data dba: 0x01000502 Extent 21 : L1 dba: 0x01000580 Data dba: 0x01000582 Extent 22 : L1 dba: 0x01000600 Data dba: 0x01000602 Extent 23 : L1 dba: 0x01000680 Data dba: 0x01000682 Extent 24 : L1 dba: 0x01000700 Data dba: 0x01000702 -------------------------------------------------------- Second Level Bitmap block DBAs -------------------------------------------------------- DBA 1: 0x01000209 End dump data blocks tsn: 4 file#: 4 minblk 522 maxblk 522 4.做truncate分析: $ cat tr.txt column member new_value v_member column member noprint set numw 12 pause run alter system archive log current or alter system switch logfile; --//12c不允许在pluggable database执行这条命令 alter system archive log current; SELECT member FROM v$log a, v$logfile b WHERE a.group#(+) = b.group# and a.STATUS='CURRENT' and rownum=1; column curr1 new_value v_curr1 select current_scn curr1 from v$database; --//以下操作内容: truncate table t reuse storage; column curr2 new_value v_curr2 select current_scn curr2 from v$database; prompt exec DBMS_LOGMNR.START_LOGMNR(STARTSCN => &&v_curr1 ,ENDSCN => &&v_curr2 ,OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG + DBMS_LOGMNR.CONTINUOUS_MINE); prompt alter system dump logfile '&&v_member' scn min &&v_curr1 scn max &&v_curr2; alter system dump logfile '&&v_member' scn min &&v_curr1 scn max &&v_curr2; SCOTT@book> @ tr.txt run alter system archive log current or alter system switch logfile System altered. CURR1 ------------ 13815784998 Table truncated. CURR2 ------------ 13815785068 exec DBMS_LOGMNR.START_LOGMNR(STARTSCN => 13815784998 ,ENDSCN => 13815785068 ,OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG + DBMS_LOGMNR.CONTINUOUS_MINE) alter system dump logfile '/mnt/ramdisk/book/redo01.log' scn min 13815784998 scn max 13815785068 System altered. SCOTT@book> alter system checkpoint ; System altered. SCOTT@book> alter system dump datafile 4 block 522; System altered. --//再次查看转储: Extent Control Header ----------------------------------------------------------------- Extent Header:: spare1: 0 spare2: 0 #extents: 25 #blocks: 1280 last map 0x00000000 #maps: 0 offset: 2716 Highwater:: 0x0100020b ext#: 0 blk#: 3 ext size: 8 #blocks in seg. hdr's freelists: 0 #blocks below: 0 mapblk 0x00000000 offset: 0 Disk Lock:: Locked by xid: 0x000a.01f.00005369 -------------------------------------------------------- Low HighWater Mark : Highwater:: 0x0100020b ext#: 0 blk#: 3 ext size: 8 #blocks in seg. hdr's freelists: 0 #blocks below: 0 mapblk 0x00000000 offset: 0 Level 1 BMB for High HWM block: 0x01000208 Level 1 BMB for Low HWM block: 0x01000208 -------------------------------------------------------- Segment Type: 1 nl2: 1 blksz: 8192 fbsz: 0 L2 Array start offset: 0x00001434 First Level 3 BMB: 0x00000000 L2 Hint for inserts: 0x01000209 Last Level 1 BMB: 0x01000701 Last Level II BMB: 0x01000209 Last Level III BMB: 0x00000000 Map Header:: next 0x00000000 #extents: 25 obj#: 90552 flag: 0x10000000 Inc # 0 Extent Map ----------------------------------------------------------------- 0x01000208 length: 8 0x01000210 length: 8 0x01000218 length: 8 0x01000220 length: 8 0x01000228 length: 8 0x01000230 length: 8 0x010002a0 length: 8 0x010002a8 length: 8 0x010002b0 length: 8 0x010002b8 length: 8 0x010002c0 length: 8 0x010002c8 length: 8 0x010002d0 length: 8 0x010002d8 length: 8 0x010002e0 length: 8 0x010002e8 length: 8 0x01000300 length: 128 0x01000380 length: 128 0x01000400 length: 128 0x01000480 length: 128 0x01000500 length: 128 0x01000580 length: 128 0x01000600 length: 128 0x01000680 length: 128 0x01000700 length: 128 Auxillary Map -------------------------------------------------------- Extent 0 : L1 dba: 0x01000208 Data dba: 0x0100020b Extent 1 : L1 dba: 0x01000208 Data dba: 0x01000210 Extent 2 : L1 dba: 0x01000218 Data dba: 0x01000219 Extent 3 : L1 dba: 0x01000218 Data dba: 0x01000220 Extent 4 : L1 dba: 0x01000228 Data dba: 0x01000229 Extent 5 : L1 dba: 0x01000228 Data dba: 0x01000230 Extent 6 : L1 dba: 0x010002a0 Data dba: 0x010002a1 Extent 7 : L1 dba: 0x010002a0 Data dba: 0x010002a8 Extent 8 : L1 dba: 0x010002b0 Data dba: 0x010002b1 Extent 9 : L1 dba: 0x010002b0 Data dba: 0x010002b8 Extent 10 : L1 dba: 0x010002c0 Data dba: 0x010002c1 Extent 11 : L1 dba: 0x010002c0 Data dba: 0x010002c8 Extent 12 : L1 dba: 0x010002d0 Data dba: 0x010002d1 Extent 13 : L1 dba: 0x010002d0 Data dba: 0x010002d8 Extent 14 : L1 dba: 0x010002e0 Data dba: 0x010002e1 Extent 15 : L1 dba: 0x010002e0 Data dba: 0x010002e8 Extent 16 : L1 dba: 0x01000300 Data dba: 0x01000302 Extent 17 : L1 dba: 0x01000380 Data dba: 0x01000382 Extent 18 : L1 dba: 0x01000400 Data dba: 0x01000402 Extent 19 : L1 dba: 0x01000480 Data dba: 0x01000482 Extent 20 : L1 dba: 0x01000500 Data dba: 0x01000502 Extent 21 : L1 dba: 0x01000580 Data dba: 0x01000582 Extent 22 : L1 dba: 0x01000600 Data dba: 0x01000602 Extent 23 : L1 dba: 0x01000680 Data dba: 0x01000682 Extent 24 : L1 dba: 0x01000700 Data dba: 0x01000702 -------------------------------------------------------- Second Level Bitmap block DBAs -------------------------------------------------------- DBA 1: 0x01000209 End dump data blocks tsn: 4 file#: 4 minblk 522 maxblk 522 5.对比分析: $ diff -Nur a1.txt a2.txt --- a1.txt 2018-12-12 09:22:47.000000000 +0800 +++ a2.txt 2018-12-12 09:26:16.000000000 +0800 @@ -1,18 +1,20 @@ + Extent Control Header + ----------------------------------------------------------------- Extent Header:: spare1: 0 spare2: 0 #extents: 25 #blocks: 1280 last map 0x00000000 #maps: 0 offset: 2716 - Highwater:: 0x01000757 ext#: 24 blk#: 87 ext size: 128 + Highwater:: 0x0100020b ext#: 0 blk#: 3 ext size: 8 #blocks in seg. hdr's freelists: 0 - #blocks below: 1239 - mapblk 0x00000000 offset: 24 - Unlocked + #blocks below: 0 + mapblk 0x00000000 offset: 0 + Disk Lock:: Locked by xid: 0x000a.01f.00005369 -------------------------------------------------------- Low HighWater Mark : - Highwater:: 0x01000757 ext#: 24 blk#: 87 ext size: 128 + Highwater:: 0x0100020b ext#: 0 blk#: 3 ext size: 8 #blocks in seg. hdr's freelists: 0 - #blocks below: 1239 - mapblk 0x00000000 offset: 24 - Level 1 BMB for High HWM block: 0x01000701 - Level 1 BMB for Low HWM block: 0x01000701 + #blocks below: 0 + mapblk 0x00000000 offset: 0 + Level 1 BMB for High HWM block: 0x01000208 + Level 1 BMB for Low HWM block: 0x01000208 -------------------------------------------------------- Segment Type: 1 nl2: 1 blksz: 8192 fbsz: 0 L2 Array start offset: 0x00001434 @@ -21,7 +23,7 @@ Last Level 1 BMB: 0x01000701 Last Level II BMB: 0x01000209 Last Level III BMB: 0x00000000 - Map Header:: next 0x00000000 #extents: 25 obj#: 90551 flag: 0x10000000 + Map Header:: next 0x00000000 #extents: 25 obj#: 90552 flag: 0x10000000 Inc # 0 Extent Map ----------------------------------------------------------------- --//你可以发现仅仅改动了高低水位标识相关信息,以及obj#. - Highwater:: 0x01000757 ext#: 24 blk#: 87 ext size: 128 + Highwater:: 0x0100020b ext#: 0 blk#: 3 ext size: 8 Low HighWater Mark : - Highwater:: 0x01000757 ext#: 24 blk#: 87 ext size: 128 + Highwater:: 0x0100020b ext#: 0 blk#: 3 ext size: 8 - Map Header:: next 0x00000000 #extents: 25 obj#: 90551 flag: 0x10000000 + Map Header:: next 0x00000000 #extents: 25 obj#: 90552 flag: 0x10000000 --//Extent Map,Auxillary Map信息没有变化. --//这样通过修改数据字典,然后修复高低水位相关信心以及段号,这样就能恢复里面的数据,注意前提条件是对应块没有被覆盖. --//我估计如果覆盖,通过修改对应数据块的段号应该也可以正常读取. 6.确定高低水位信息在块中位置: SCOTT@book> @ bbvi 4 522 BVI_COMMAND ---------------------------------------------------------------------------------------------------- bvi -b 4276224 -s 8192 /mnt/ramdisk/book/users01.dbf xxd -c16 -g 2 -s 4276224 -l 8192 /mnt/ramdisk/book/users01.dbf dd if=/mnt/ramdisk/book/users01.dbf bs=8192 skip=522 count=1 of=4_522.dd conv=notrunc 2>/dev/null od -j 4276224 -N 8192 -t x1 -v /mnt/ramdisk/book/users01.dbf hexdump -s 4276224 -n 8192 -C -v /mnt/ramdisk/book/users01.dbf alter system dump datafile '/mnt/ramdisk/book/users01.dbf' block 522; alter session set events 'immediate trace name set_tsn_p1 level 5'; alter session set events 'immediate trace name buffer level 16777738'; 9 rows selected. $ dd if=/mnt/ramdisk/book/users01.dbf bs=8192 skip=522 count=1 of=4_522.dd conv=notrunc 2>/dev/nul --//0100020b 大小头对调 0b 02 00 01 --//90552=0x161b8 大小头对调 b8 61 01 00 $ xxd -c 32 -g 1 4_522.dd | grep "b8 61 01 00" --// 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 0000100: 00 00 00 00 00 00 00 00 19 00 00 00 00 00 00 00 b8 61 01 00 00 00 00 10 08 02 00 01 08 00 00 00 .................a.............. ~~~~~~~~~~~ --//obj# 位于 0x110=272位置.注前面的标尺为了查看方便我加入的. + Highwater:: 0x0100020b ext#: 0 blk#: 3 ext size: 8 $ xxd -c 32 -g 1 4_522.dd | head -20 --// 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 0000000: 23 a2 00 00 0a 02 00 01 5e 26 7c 37 03 00 01 04 b2 ab 00 00 00 00 00 00 00 00 00 00 00 00 00 00 #.......^&|7.................... 0000020: 00 00 00 00 19 00 00 00 00 05 00 00 9c 0a 00 00 00 00 00 00 03 00 00 00 08 00 00 00 0b 02 00 01 ................................ ~~~~~~~~~~~ ~~~~~~~~~~~ ~~~~~~~~~~~ ~~~~~~~~~~~ 0000040: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 0a 00 1f 00 69 53 00 00 01 00 00 00 00 00 00 00 ....................iS.......... ~~~~~~~~~~~ 0000060: 03 00 00 00 08 00 00 00 0b 02 00 01 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 08 02 00 01 ................................ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 0000080: 08 02 00 01 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 ................................ 00000a0: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 ................................ 00000c0: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 01 00 00 00 00 20 00 00 00 00 00 00 34 14 00 00 ..................... ......4... 00000e0: 00 00 00 00 09 02 00 01 01 00 00 00 01 07 00 01 09 02 00 01 00 00 00 00 00 00 00 00 00 00 00 00 ................................ 0000100: 00 00 00 00 00 00 00 00 19 00 00 00 00 00 00 00 b8 61 01 00 00 00 00 10 08 02 00 01 08 00 00 00 .................a.............. 0000120: 10 02 00 01 08 00 00 00 18 02 00 01 08 00 00 00 20 02 00 01 08 00 00 00 28 02 00 01 08 00 00 00 ................ .......(.......) 0000140: 30 02 00 01 08 00 00 00 a0 02 00 01 08 00 00 00 a8 02 00 01 08 00 00 00 b0 02 00 01 08 00 00 00 0............................... 0000160: b8 02 00 01 08 00 00 00 c0 02 00 01 08 00 00 00 c8 02 00 01 08 00 00 00 d0 02 00 01 08 00 00 00 ........?......?......?...... 0000180: d8 02 00 01 08 00 00 00 e0 02 00 01 08 00 00 00 e8 02 00 01 08 00 00 00 00 03 00 01 80 00 00 00 ?......?......?.............. 00001a0: 80 03 00 01 80 00 00 00 00 04 00 01 80 00 00 00 80 04 00 01 80 00 00 00 00 05 00 01 80 00 00 00 ................................ 00001c0: 80 05 00 01 80 00 00 00 00 06 00 01 80 00 00 00 80 06 00 01 80 00 00 00 00 07 00 01 80 00 00 00 ................................ 00001e0: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 ................................ 0000200: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 ................................ 0000220: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 ................................ 0000240: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 ................................ 0000260: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 ................................ --//注意看下划线部分应该对应高低水位信息.你可以改动后转储看看,可以确定前面是高水位信息. --//32+16=48 开始每4各字节分别高HWM的ext#,高HWM的blk#,高HWM的ext size. 高HWM的dba地址. --//64+28=92 开始每4各字节分别低HWM的ext#,低HWM的blk#,低HWM的ext size. 低HWM的dba地址. segment header 每个 offset 对应的含义，如下： ---------------------------------------------- offset desc ---------------------------------------------- 36 total extents 40 total blocks 48 高HWM的ext# 52 高HWM的blk#(从0开始) 56 高HWM的ext size# 60 高HWM的dba地址 76 高HWM下有多少个block 92 低HWM的ext# 96 低HWM的blk#(从0开始) 100 低HWM的ext size 104 低HWM的dba地址 120 高LHWM下有多少个block 124 Level 1 BMB for High HWM block 128 Level 1 BMB for Low HWM block 213 block size 220 L2 Array start offset 224 First Level 3 BMB 228 L2 Hint for inserts 236 Last Level 1 BMB 240 Last Level II BMB 244 Last Level III BMB 264 extents 272 obj# 280 ext#为0的block_id 284 ext#为0的extent blocks 288 ext#为1的block_id 292 ext#为1的extent blocks ...... 以此类推循环 2736 aux map信息，ext#为0的L1 dba 2740 aux map信息，ext#为0的data dba 2744 aux map信息，ext#为1的L1 dba 2748 aux map信息，ext#为1的data dba ...... 以此类推循环 5192 Second Level Bitmap block DBAs --//对于truncate table t reuse storage;操作修改信息就相对较少. 7.修改数据字典: --//A.先修改数据字典。 UPDATE tab$ set dataobj#=90551 where obj#=90551; UPDATE seg$ set hwmincr=90551 where hwmincr=90552; update obj$ set dataobj#=90551 where obj#=90551; commit ; SYS@book> alter system flush buffer_cache; System altered. SYS@book> alter system flush shared_pool ; System altered. SCOTT@book> select * from t where rowid=dbms_rowid.ROWID_CREATE(1,90551,4, 523 ,0); OWNER OBJECT_NAME SUBOBJECT_ OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE CREATED LAST_DDL_TIME TIMESTAMP STATUS T G S NAMESPACE EDITION_NAME ------ ----------- ---------- ---------- -------------- ----------- ------------------- ------------------- ------------------- ------- - - - ---------- ------------ SYS ICOL$ 20 2 TABLE 2013-08-24 11:37:35 2013-08-24 11:47:37 2013-08-24:11:37:35 VALID N N N 1 --//能查询到，说明以上修改没有问题。 --//B.修复高低水位相关信息: --//我这里高低修改一样 --//修改前 Highwater:: 0x0100020b ext#: 0 blk#: 3 ext size: 8 --//修改后 Highwater:: 0x01000757 ext#: 24 blk#: 87 ext size: 128 --// 25=0x19 24=0x18 87=0x57 128=0x80 --// 0x01000757 大小头对调 57 07 00 01 m /x 19 offset 36 --//total extents m /x 19 offset 264 --//extents --//注：这步不需要修改，因为空间没有回收。 --//32+16=48 开始每4各字节分别高HWM的ext#,高HWM的blk#,高HWM的ext size. 高HWM的dba地址. --//64+28=92 开始每4各字节分别低HWM的ext#,低HWM的blk#,低HWM的ext size. 低HWM的dba地址. m /x 18 offset 48 --//高HWM的ext# m /x 57 offset 52 --//高HWM的blk#(从0开始) m /x 80 offset 56 --//高HWM的ext size# m /x 5707 offset 60 --//HWM的dba地址(低位) m /x 0001 offset 62 --//HWM的dba地址(高位) m /x 18 offset 92 --//低HWM的ext# m /x 57 offset 96 --//低HWM的blk#(从0开始) m /x 80 offset 100 --//低HWM的ext size m /x 5707 offset 104 --//低HWM的dba地址(低位) m /x 0001 offset 106 --//低HWM的dba地址(高位) --//注意大小头问题。 --//90551=0x161b7 大小头对调 b7 61 01 00 m /x b761 offset 272 --//修改obj# m /x 0100 offset 274 --//修改obj# --//整理如下,别写错了.^_^: $ cat tr_bbed.txt set dba 4,522 m /x 18 offset 48 y m /x 57 offset 52 m /x 80 offset 56 m /x 5707 offset 60 m /x 0001 offset 62 m /x 18 offset 92 m /x 57 offset 96 m /x 80 offset 100 m /x 5707 offset 104 m /x 0001 offset 106 m /x b761 offset 272 m /x 0100 offset 274 sum apply --//注:中间有1行y不是多余的,这样可以通过管道执行.因为是修改,中间有提示如下 Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) --//时就报错.如果害怕错误,使用copy &paste 执行吧. $ cat tr_bbed.txt | bbed parfile=bbed.par cmdfile=cmd.par ... --//检查: BBED> set dba 4,522 DBA 0x0100020a (16777738 4,522) BBED> dump /v offset 48 count 16 File: /mnt/ramdisk/book/users01.dbf (4) Block: 522 Offsets: 48 to 63 Dba:0x0100020a ----------------------------------------------------------------------------------------------------------- 18000000 57000000 80000000 57070001 l ....W.......W... <32 bytes per line> BBED> dump /v offset 92 count 16 File: /mnt/ramdisk/book/users01.dbf (4) Block: 522 Offsets: 92 to 107 Dba:0x0100020a ----------------------------------------------------------------------------------------------------------- 18000000 57000000 80000000 57070001 l ....W.......W... <32 bytes per line> BBED> dump /v offset 272 count 8 File: /mnt/ramdisk/book/users01.dbf (4) Block: 522 Offsets: 272 to 279 Dba:0x0100020a ----------------------------------------------------------------------------------------------------------- b7610100 00000010 l .a...... <32 bytes per line> --//ok,现在修改完成了. --//C.验证修改是否有效. SCOTT@book> alter system flush shared_pool; System altered. SCOTT@book> alter system flush buffer_cache; System altered. SCOTT@book> alter session set optimizer_dynamic_sampling=0; Session altered. SCOTT@book> select count(*) from t ; COUNT(*) ---------- 84769 --//ok!!现在修复了.你可能问原来的到底水位信息如何获得,自己上可以通过转储日志获得. 8.检查转储日志: $ egrep -A1 "^Low HWM|High HWM" /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_8998.trc Low HWM Highwater:: 0x01000757 ext#: 24 blk#: 87 ext size: 128 -- High HWM Highwater:: 0x01000757 ext#: 24 blk#: 87 ext size: 128 -- Low HWM Highwater:: 0x0100020b ext#: 0 blk#: 3 ext size: 8 -- High HWM Highwater:: 0x0100020b ext#: 0 blk#: 3 ext size: 8 --//可以发现可以与上面的信息对上. REDO RECORD - Thread:1 RBA: 0x0002ff.00000016.011c LEN: 0x01b8 VLD: 0x01 SCN: 0x0003.377c2626 SUBSCN: 1 12/12/2018 09:24:29 CHANGE #1 TYP:0 CLS:36 AFN:3 DBA:0x00c001c9 OBJ:4294967295 SCN:0x0003.377c2626 SEQ:27 OP:5.1 ENC:0 RBL:0 ktudb redo: siz: 164 spc: 4640 flg: 0x0022 seq: 0x0f52 rec: 0x28 xid: 0x000a.01f.00005369 ktubu redo: slt: 31 rci: 39 opc: 13.29 objn: 90551 objd: 90551 tsn: 4 Undo type: Regular undo Undo type: Last buffer split: No Tablespace Undo: No 0x00000000 Segment Header Undo Seghdr dba: 0x0100020a Mapblock dba: 0x00000000 Mapredo Offset: 4 scls: 4 mcls: 7 Both the HWMs Low HWM Highwater:: 0x01000757 ext#: 24 blk#: 87 ext size: 128 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ #blocks in seg. hdr's freelists: 0 #blocks below: 1239 mapblk 0x00000000 offset: 24 lfdba: 0x01000701 High HWM Highwater:: 0x01000757 ext#: 24 blk#: 87 ext size: 128 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ #blocks in seg. hdr's freelists: 0 #blocks below: 1239 mapblk 0x00000000 offset: 24 lfdba: 0x01000701 hint dba: 0x01000209 Lasts in Header LF: 16779009 LS: 16777737 LT: 0 FT: 0 --//前面部分undo的信息. CHANGE #2 TYP:0 CLS:4 AFN:4 DBA:0x0100020a OBJ:90551 SCN:0x0003.377c2626 SEQ:1 OP:13.28 ENC:0 RBL:0 Both the HWMs Low HWM Highwater:: 0x0100020b ext#: 0 blk#: 3 ext size: 8 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ #blocks in seg. hdr's freelists: 0 #blocks below: 0 mapblk 0x00000000 offset: 0 lfdba: 0x01000208 High HWM Highwater:: 0x0100020b ext#: 0 blk#: 3 ext size: 8 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ #blocks in seg. hdr's freelists: 0 #blocks below: 0 mapblk 0x00000000 offset: 0 lfdba: 0x01000208 hint dba: 0x01000209 Lasts in Header LF: 16779009 LS: 16777737 LT: 0 FT: 0 --//修改的信息 CHANGE #3 TYP:0 CLS:8 AFN:4 DBA:0x01000208 OBJ:90551 SCN:0x0003.377c2626 SEQ:3 OP:13.22 ENC:0 RBL:0 Redo on Level1 Bitmap Block Redo to set hwm Opcode: 32 Highwater:: 0x0100020b ext#: 0 blk#: 3 ext size: 8 #blocks in seg. hdr's freelists: 0 #blocks below: 0 mapblk 0x00000000 offset: 0 --//0x01000208=16777736,16777736= alter system dump datafile 4 block 520 --//实际上还修改dba=4,520 ... REDO RECORD - Thread:1 RBA: 0x0002ff.0000001a.0010 LEN: 0x0070 VLD: 0x05 SCN: 0x0003.377c2628 SUBSCN: 1 12/12/2018 09:24:29 (LWN RBA: 0x0002ff.0000001a.0010 LEN: 0001 NST: 0001 SCN: 0x0003.377c2628) CHANGE #1 TYP:0 CLS:8 AFN:4 DBA:0x01000208 OBJ:90551 SCN:0x0003.377c2626 SEQ:4 OP:13.22 ENC:0 RBL:0 Redo on Level1 Bitmap Block Redo to Change Opcode Opcode: 9locking transaction: REDO RECORD - Thread:1 RBA: 0x0002ff.0000001a.0080 LEN: 0x00bc VLD: 0x01 SCN: 0x0003.377c2628 SUBSCN: 1 12/12/2018 09:24:29 CHANGE #1 TYP:0 CLS:36 AFN:3 DBA:0x00c001c9 OBJ:4294967295 SCN:0x0003.377c2626 SEQ:28 OP:5.1 ENC:0 RBL:0 ktudb redo: siz: 72 spc: 4474 flg: 0x0022 seq: 0x0f52 rec: 0x29 xid: 0x000a.01f.00005369 ktubu redo: slt: 31 rci: 40 opc: 13.23 objn: 90551 objd: 90551 tsn: 4 Undo type: Regular undo Undo type: Last buffer split: No Tablespace Undo: No 0x00000000 Undo for Lev1 Bitmap Block L1 DBA: 0x01000208 L2 DBA: 0x00000000 fcls: 8 scls: 0 offset: 0 Redo on Level1 Bitmap Block Change objd Newobjd: 90551 CHANGE #2 TYP:0 CLS:8 AFN:4 DBA:0x01000208 OBJ:90551 SCN:0x0003.377c2628 SEQ:1 OP:13.22 ENC:0 RBL:0 Redo on Level1 Bitmap Block Change objd Newobjd: 90552 --//新的Newobjd: 90552. --//可以通过如上信息确定如何修改段头. --//总结: 1.truncate reuse storage 没有修改段头的Extent Map,Auxillary Map.恢复相对简单. 2.修复先修复数据字典. UPDATE tab$ set dataobj#=90551 where obj#=90551; UPDATE seg$ set hwmincr=90551 where hwmincr=90552; update obj$ set dataobj#=90551 where obj#=90551; commit ; 3.修复段头的高低水位信息: m /x 18 offset 48 --//高HWM的ext# m /x 57 offset 52 --//高HWM的blk#(从0开始) m /x 80 offset 56 --//高HWM的ext size# m /x 5707 offset 60 --//HWM的dba地址(低位) m /x 0001 offset 62 --//HWM的dba地址(高位) m /x 18 offset 92 --//低HWM的ext# m /x 57 offset 96 --//低HWM的blk#(从0开始) m /x 80 offset 100 --//低HWM的ext size m /x 5707 offset 104 --//低HWM的dba地址(低位) m /x 0001 offset 106 --//低HWM的dba地址(高位) --//注意大小头问题。 --//90551=0x161b7 大小头对调 b7 61 01 00 m /x b761 offset 272 --//修改obj# m /x 0100 offset 274 --//修改obj# 4.相关修改信息可以通过redo转储确定.

编辑推荐：

返回列表

[20181212]关于truncate reuse storage.txt

编辑推荐：

相关推荐

excel表格怎样在数字前输入0

excel表格里怎么替换数据

excel表格里面如何插入圆形

excel表格里怎么添加表格数据透视表

excel表格中怎么添加线边框

excel表格里怎样设置条件自动排序

excel表格例怎样导入数据库

Excel表格如何设置数据排序

excel表格里怎么选择性粘贴快捷键

excel表格怎么插入分隔符号

excel表格怎么设置多行多列数据内容排序

Excel表格如何设置行和列相互转换

excel表格连接公式怎么使用

excel表格怎样链接到cad中

雷神推出 MIX PRO II 迷你主机：基于 Ultra 200H，玻璃上盖 + ARGB 灯效

制造商 Musnap 推出彩色墨水屏电纸书 Ocean C：支持手写笔、第三方安卓应用

最新软件资讯

热文推荐

天极热推