[20250504]drop table的恢复思路.txt

--//节前做了一次truncate table的恢复，理论也可以应用到drop table的情况，只要建立与原来数据结构一样的表，修改段号等于以前
--//drop table的段号，再扫描对应数据文件就可以恢复drop table的信息。

--//我看了一些的一些恢复操作笔记，当时思路很乱，好像drop table清除段头的信息，还只能通过扫描数据文件，先检查drop后段头的
--//情况。

1.环境：
SCOTT@book01p> @ver2
==============================
PORT_STRING                   : x86_64/Linux 2.4.xx
VERSION                       : 21.0.0.0.0
BANNER                        : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
BANNER_FULL                   : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0
BANNER_LEGACY                 : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
CON_ID                        : 0
PL/SQL procedure successfully completed.

2.建立测试环境：
SCOTT@book01p> create table t as select * from all_objects;
Table created.

SCOTT@book01p> @ o2 t
SCOTT@book01p> @ pr
==============================
O_OWNER                       : SCOTT
O_OBJECT_NAME                 : T
O_OBJECT_TYPE                 : TABLE
SEG_PART_NAME                 :
O_STATUS                      : VALID
OID                           : 125488
D_OID                         : 125488
CREATED                       : 2025-05-04 11:03:30
LAST_DDL_TIME                 : 2025-05-04 11:03:30
PL/SQL procedure successfully completed.

SCOTT@book01p> @ seg2 t ''
SCOTT@book01p> @pr
==============================
SEG_MB                        : 12
SEG_OWNER                     : SCOTT
SEG_SEGMENT_NAME              : T
SEG_PARTITION_NAME            :
SEG_SEGMENT_TYPE              : TABLE
SEG_TABLESPACE_NAME           : USERS
BLOCKS                        : 1536
HDRFIL                        : 12
HDRBLK                        : 170
PL/SQL procedure successfully completed.

3.查看drop table前后段头的情况。

SCOTT@book01p> alter system checkpoint;
System altered.

SCOTT@book01p> alter system dump datafile 12 block 170;
System altered.

--//检查转储文件，发现如下信息：
Dump of buffer cache for pdb 3 tsn 5 rdba 0x30000aa at level 2 done.
Block dump from disk:
buffer tsn: 5 rdba: 0x030000aa (12/170)
scn: 0x2311560 seq: 0x01 flg: 0x04 tail: 0x15602301
frmt: 0x02 chkval: 0x4589 type: 0x23=PAGETABLE SEGMENT HEADER
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x00007FA28C042000 to 0x00007FA28C044000
....

Extent Control Header
 -----------------------------------------------------------------
 Extent Header:: spare1: 0      spare2: 0      #extents: 27     #blocks: 1536
                 last map  0x00000000  #maps: 0      offset: 2716
     Highwater::  0x03000662  ext#: 26     blk#: 98     ext size: 128
 #blocks in seg. hdr's freelists: 0
 #blocks below: 1506
 mapblk  0x00000000  offset: 26
                  Unlocked
 --------------------------------------------------------
 Low HighWater Mark :
     Highwater::  0x03000662  ext#: 26     blk#: 98     ext size: 128
 #blocks in seg. hdr's freelists: 0
 #blocks below: 1506
 mapblk  0x00000000  offset: 26
 Level 1 BMB for High HWM block: 0x03000601
 Level 1 BMB for Low HWM block: 0x03000601
 --------------------------------------------------------
 Segment Type: 1 nl2: 1      blksz: 8192   fbsz: 0
 L2 Array start offset:  0x00001434
 First Level 3 BMB:  0x00000000
 L2 Hint for inserts:  0x030000a9
 Last Level 1 BMB:  0x03000601
 Last Level II BMB:  0x030000a9
 Last Level III BMB:  0x00000000
    Map Header:: next  0x00000000  #extents: 27   obj#: 125488 flag: 0x10000000
 Inc # 0
 Extent Map
 -----------------------------------------------------------------
  0x030000a8  length: 8
  0x030000b0  length: 8
  0x030000b8  length: 8
  0x030000c0  length: 8
  0x030000c8  length: 8
  0x030000d0  length: 8
  0x030000d8  length: 8
  0x030000e0  length: 8
  0x030000e8  length: 8
  0x030000f0  length: 8
  0x030000f8  length: 8
  0x03006000  length: 8
  0x03006008  length: 8
  0x03006010  length: 8
  0x03006018  length: 8
  0x03006020  length: 8
  0x03000100  length: 128
  0x03000180  length: 128
  0x03000200  length: 128
  0x03000280  length: 128
  0x03000300  length: 128
  0x03000380  length: 128
  0x03000400  length: 128
  0x03000480  length: 128
  0x03000500  length: 128
  0x03000580  length: 128
  0x03000600  length: 128
Auxillary Map
 --------------------------------------------------------
  Extent 0     :  L1 dba:  0x030000a8 Data dba:  0x030000ab
  Extent 1     :  L1 dba:  0x030000a8 Data dba:  0x030000b0
  Extent 2     :  L1 dba:  0x030000b8 Data dba:  0x030000b9
  Extent 3     :  L1 dba:  0x030000b8 Data dba:  0x030000c0
  Extent 4     :  L1 dba:  0x030000c8 Data dba:  0x030000c9
  Extent 5     :  L1 dba:  0x030000c8 Data dba:  0x030000d0
  Extent 6     :  L1 dba:  0x030000d8 Data dba:  0x030000d9
  Extent 7     :  L1 dba:  0x030000d8 Data dba:  0x030000e0
  Extent 8     :  L1 dba:  0x030000e8 Data dba:  0x030000e9
  Extent 9     :  L1 dba:  0x030000e8 Data dba:  0x030000f0
  Extent 10    :  L1 dba:  0x030000f8 Data dba:  0x030000f9
  Extent 11    :  L1 dba:  0x030000f8 Data dba:  0x03006000
  Extent 12    :  L1 dba:  0x03006008 Data dba:  0x03006009
  Extent 13    :  L1 dba:  0x03006008 Data dba:  0x03006010
  Extent 14    :  L1 dba:  0x03006018 Data dba:  0x03006019
  Extent 15    :  L1 dba:  0x03006018 Data dba:  0x03006020
  Extent 16    :  L1 dba:  0x03000100 Data dba:  0x03000102
  Extent 17    :  L1 dba:  0x03000180 Data dba:  0x03000182
  Extent 18    :  L1 dba:  0x03000200 Data dba:  0x03000202
  Extent 19    :  L1 dba:  0x03000280 Data dba:  0x03000282
  Extent 20    :  L1 dba:  0x03000300 Data dba:  0x03000302
  Extent 21    :  L1 dba:  0x03000380 Data dba:  0x03000382
  Extent 22    :  L1 dba:  0x03000400 Data dba:  0x03000402
  Extent 23    :  L1 dba:  0x03000480 Data dba:  0x03000482
  Extent 24    :  L1 dba:  0x03000500 Data dba:  0x03000502
  Extent 25    :  L1 dba:  0x03000580 Data dba:  0x03000582
  Extent 26    :  L1 dba:  0x03000600 Data dba:  0x03000602
 --------------------------------------------------------
  Second Level Bitmap block DBAs
  --------------------------------------------------------
  DBA 1:   0x030000a9
End dump data blocks tsn: 5 file#: 12 minblk 170 maxblk 170
 
--//030000a9= set dba 12,169 = alter system dump datafile 12 block 169 = 50331817

SCOTT@book01p> drop table t purge ;
Table dropped.

SCOTT@book01p> @ ti
New tracefile_identifier = /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_4757_0001.trc

SCOTT@book01p> alter system checkpoint;
System altered.

SCOTT@book01p> alter system dump datafile 12 block 170;
System altered.

--//检查转储文件，发现如下信息：

Dump of buffer cache for pdb 3 tsn 5 rdba 0x30000aa at level 2 done.
Block dump from disk:
buffer tsn: 5 rdba: 0x030000aa (12/170)
scn: 0x23119f5 seq: 0x1b flg: 0x04 tail: 0x19f5231b
frmt: 0x02 chkval: 0x5957 type: 0x23=PAGETABLE SEGMENT HEADER
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x00007FA28C042000 to 0x00007FA28C044000
7FA28C042000 0000A223 030000AA 023119F5 041B0000  [#.........1.....]
7FA28C042010 00005957 00000000 00000000 00000000  [WY..............]
7FA28C042020 00000000 00000001 00000008 00000A9C  [................]
7FA28C042030 00000001 00000062 00000080 03000662  [....b.......b...]
7FA28C042040 00000000 00000001 00000000 000005E2  [................]
7FA28C042050 0021000A 00001E5F 00000001 0000001A  [..!._...........]
7FA28C042060 00000062 00000080 03000662 00000000  [b.......b.......]
7FA28C042070 0000001A 00000000 000005E2 03000601  [................]
7FA28C042080 03000601 00000000 00000000 00000000  [................]
7FA28C042090 00000000 00000000 00000000 00000000  [................]
        Repeat 3 times
7FA28C0420D0 00000001 00002000 00000000 00001434  [..... ......4...]
7FA28C0420E0 00000000 030000A9 00000001 03000601  [................]
7FA28C0420F0 030000A9 00000000 00000000 00000000  [................]
7FA28C042100 00000000 00000000 00000001 00000000  [................]
7FA28C042110 0001EA30 12000000 030000A8 00000008  [0...............]
7FA28C042120 00000000 00000000 00000000 00000000  [................]
        Repeat 152 times
7FA28C042AB0 030000A8 030000AB 00000000 00000000  [................]
7FA28C042AC0 00000000 00000000 00000000 00000000  [................]
        Repeat 151 times
7FA28C043440 00000000 00000000 030000A9 00000000  [................]
7FA28C043450 00000000 00000000 00000000 00000000  [................]
        Repeat 185 times
7FA28C043FF0 00000000 00000000 00000000 19F5231B  [.............#..]
  Extent Control Header
  -----------------------------------------------------------------
  Extent Header:: spare1: 0      spare2: 0      #extents: 1      #blocks: 8
                  last map  0x00000000  #maps: 0      offset: 2716
      Highwater::  0x03000662  ext#: 1      blk#: 98     ext size: 128
  #blocks in seg. hdr's freelists: 0
  #blocks below: 1506
  mapblk  0x00000000  offset: 1
      Disk Lock:: Locked by xid:  0x000a.021.00001e5f
  --------------------------------------------------------
  Low HighWater Mark :
      Highwater::  0x03000662  ext#: 26     blk#: 98     ext size: 128
  #blocks in seg. hdr's freelists: 0
  #blocks below: 1506
  mapblk  0x00000000  offset: 26
  Level 1 BMB for High HWM block: 0x03000601
  Level 1 BMB for Low HWM block: 0x03000601
  --------------------------------------------------------
  Segment Type: 1 nl2: 1      blksz: 8192   fbsz: 0
  L2 Array start offset:  0x00001434
  First Level 3 BMB:  0x00000000
  L2 Hint for inserts:  0x030000a9
  Last Level 1 BMB:  0x03000601
  Last Level II BMB:  0x030000a9
  Last Level III BMB:  0x00000000
     Map Header:: next  0x00000000  #extents: 1    obj#: 125488 flag: 0x12000000
  Inc # 0
  Extent Map
  -----------------------------------------------------------------
   0x030000a8  length: 8

  Auxillary Map
  --------------------------------------------------------
   Extent 0     :  L1 dba:  0x030000a8 Data dba:  0x030000ab
  --------------------------------------------------------

   Second Level Bitmap block DBAs
   --------------------------------------------------------
   DBA 1:   0x030000a9
End dump data blocks tsn: 5 file#: 12 minblk 170 maxblk 170

--//对比可以发现drop后，段头清除许多信息，Extent Map以及Auxillary Map仅仅剩下1个项目，要恢复里面的信息难道相对较大。
--//只能通过扫描数据文件收集需要恢复的记录,另外写一篇blog恢复drop table的数据。