[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转储确定.
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/267265/viewspace-2284921/，如需转载，请注明出处，否则将追究法律责任。
