ITPub博客

首页 > 数据库 > Oracle > [20181212]关于truncate reuse storage.txt

[20181212]关于truncate reuse storage.txt

原创 Oracle 作者:lfree 时间:2018-12-12 11:05:01 0 删除 编辑

[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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
熟悉oracle相关技术,擅长sql优化,rman备份与恢复,熟悉linux shell编程。

注册时间:2008-01-03

  • 博文量
    2858
  • 访问量
    6644481