[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

相關文章