ODU恢复truncate table的测试

1、模拟truncate

SQL> select count(*) from tt;

  COUNT(*)
----------
     26472

SQL> truncate table tt;

表被截断。

SQL> select count(*) from tt;

  COUNT(*)
----------
         0

2、查询数据文件路径相关信息：select ts#,file#,rfile#,name,BLOCK_SIZE from v$datafile;

　　将其结构填入odu目录下的control.txt文件中

　　格式如下：

#ts #fno   #rfno     filename                                          block_size
0          1          1 D:\APP\ADMINISTRATOR\ORADATA\ZYH\SYSTEM01.DBF                 8192
1          2          2 D:\APP\ADMINISTRATOR\ORADATA\ZYH\SYSAUX01.DBF                 8192
2          3          3 D:\APP\ADMINISTRATOR\ORADATA\ZYH\UNDOTBS01.DBF                8192
4          4          4 D:\APP\ADMINISTRATOR\ORADATA\ZYH\USERS01.DBF                  8192
5          5          5 D:\APP\ADMINISTRATOR\ORADATA\ZYH\ECCH_DATA01.DBF              8192
6          6          6 D:\APP\ADMINISTRATOR\ORADATA\ZYH\ECCH_IDX01.DBF               8192
5          7          7 D:\APP\ADMINISTRATOR\ORADATA\ZYH\ECCH_DATA02.DBF              8192
6          8          8 D:\APP\ADMINISTRATOR\ORADATA\ZYH\ECCH_IDX02.DBF               8192
0          9          9 D:\APP\ADMINISTRATOR\ORADATA\ZYH\SYSTEM02.DBF                 8192
1         10         10 D:\APP\ADMINISTRATOR\ORADATA\ZYH\SYSAUX02.DBF                 8192
5         11         11 D:\APP\ADMINISTRATOR\ORADATA\ZYH\ECCH_DATA03.DBF              8192
7         12         12 D:\APP\ADMINISTRATOR\ORADATA\ZYH\SJDATA.DBF                   8192
　

　　3、打开oud

　　4、执行命令：unload dict

　　5、执行命令：scan extent （需等一会儿时间）

　

ODU> scan extent

scan extent start: 2013-02-27 15:41:13
scanning extent...
scanning extent finished.
scan extent completed: 2013-02-27 15:50:05

6、执行命令：desc [用户名].[被删除数据的表名]
ODU> DESC SJ.TT

Object ID:183304
Storage(Obj#=183304 DataObj#=183304 TS#=7 File#=12 Block#=370 Cluster=0)

NO. SEG INT Column Name                    Null?     Type
--- --- --- ------------------------------ --------- ------------------------------
  1   1   1 OWNER                          NOT NULL  VARCHAR2(30)
  2   2   2 TABLE_NAME                     NOT NULL  VARCHAR2(30)
  3   3   3 TABLESPACE_NAME                          VARCHAR2(30)
  4   4   4 CLUSTER_NAME                             VARCHAR2(30)
  5   5   5 IOT_NAME                                 VARCHAR2(30)
  6   6   6 STATUS                                   VARCHAR2(8)
  7   7   7 PCT_FREE                                 NUMBER
  8   8   8 PCT_USED                                 NUMBER
  9   9   9 INI_TRANS                                NUMBER
 10  10  10 MAX_TRANS                                NUMBER
 11  11  11 INITIAL_EXTENT                           NUMBER
 12  12  12 NEXT_EXTENT                              NUMBER
 13  13  13 MIN_EXTENTS                              NUMBER
 14  14  14 MAX_EXTENTS                              NUMBER
 15  15  15 PCT_INCREASE                             NUMBER
 16  16  16 FREELISTS                                NUMBER
 17  17  17 FREELIST_GROUPS                          NUMBER
 18  18  18 LOGGING                                  VARCHAR2(3)
 19  19  19 BACKED_UP                                VARCHAR2(1)
 20  20  20 NUM_ROWS                                 NUMBER
 21  21  21 BLOCKS                                   NUMBER
 22  22  22 EMPTY_BLOCKS                             NUMBER
 23  23  23 AVG_SPACE                                NUMBER
 24  24  24 CHAIN_CNT                                NUMBER
 25  25  25 AVG_ROW_LEN                              NUMBER
 26  26  26 AVG_SPACE_FREELIST_BLOCKS                NUMBER
 27  27  27 NUM_FREELIST_BLOCKS                      NUMBER
 28  28  28 DEGREE                                   VARCHAR2(40)
 29  29  29 INSTANCES                                VARCHAR2(40)
 30  30  30 CACHE                                    VARCHAR2(20)
 31  31  31 TABLE_LOCK                               VARCHAR2(8)
 32  32  32 SAMPLE_SIZE                              NUMBER
 33  33  33 LAST_ANALYZED                            DATE
 34  34  34 PARTITIONED                              VARCHAR2(3)
 35  35  35 IOT_TYPE                                 VARCHAR2(12)
 36  36  36 TEMPORARY                                VARCHAR2(1)
 37  37  37 SECONDARY                                VARCHAR2(1)
 38  38  38 NESTED                                   VARCHAR2(3)
 39  39  39 BUFFER_POOL                              VARCHAR2(7)
 40  40  40 ROW_MOVEMENT                             VARCHAR2(8)
 41  41  41 GLOBAL_STATS                             VARCHAR2(3)
 42  42  42 USER_STATS                               VARCHAR2(3)
 43  43  43 DURATION                                 VARCHAR2(15)
 44  44  44 SKIP_CORRUPT                             VARCHAR2(8)
 45  45  45 MONITORING                               VARCHAR2(3)
 46  46  46 CLUSTER_OWNER                            VARCHAR2(30)
 47  47  47 DEPENDENCIES                             VARCHAR2(8)
 48  48  48 COMPRESSION                              VARCHAR2(8)
 49  49  49 COMPRESS_FOR                             VARCHAR2(18)
 50  50  50 DROPPED                                  VARCHAR2(3)
 51  51  51 READ_ONLY                                VARCHAR2(3)

从上面的输出中，我们可以看到，TEST.T1表所在的表空间号为7，数据段头部为12号文件的370号块。
我们使用ODU来确定TT表原来的data object id。一般来说，数据段的数据块，一般是在段头后面相邻的块中。但是我们可以从段头来确认：

ODU> dump datafile 12 block 370
Block Header:
block type=0x23 (ASSM segment header block)
block format=0xa2 (oracle 10)
block rdba=0x03000172 (file#=12, block#=370)
scn=0x0000.d1518a0f, seq=1, tail=0x8a0f2301
block checksum value=0xb6c9=46793, flag=4
Data Segment Header:
  Extent Control Header
  -------------------------------------------------------------
  Extent Header:: extents: 1  blocks: 8
                  last map: 0x00000000  #maps: 0  offset: 2716
      Highwater:: 0x03000173  (rfile#=12,block#=371)
                  ext#: 0  blk#: 3   ext size:8
      #blocks in seg. hdr's freelists: 0
      #blocks below: 0
      mapblk: 0x00000000   offset: 0
  --------------------------------------------------------
  Low HighWater Mark :
      Highwater::  0x03000173  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: 0x03000170
  Level 1 BMB for Low HWM block: 0x03000170
  --------------------------------------------------------
  Segment Type: 1 nl2: 1      blksz: 8192   fbsz: 0
  L2 Array start offset:  0x00001434
  First Level 3 BMB:  0x00000000
  L2 Hint for inserts:  0x03000171
  Last Level 1 BMB:  0x03000170
  Last Level 1I BMB:  0x03000171
  Last Level 1II BMB:  0x00000000
     Map Header:: next  0x00000000  #extents: 1    obj#: 183313  flag: 0x210000000
  Extent Map
  -------------------------------------------------------------
   0x03000170  length: 8

  Auxillary Map
  -------------------------------------------------------------
   Extent 0      :  L1 dba:  0x03000170 Data dba:  0x03000173
  -------------------------------------------------------------

   Second Level Bitmap block DBAs
  -------------------------------------------------------------
   DBA 1:   0x03000171
  
   从上面的输出中的“ Extent 0      :  L1 dba:  0x03000170 Data dba:  0x03000173”可以看到，段的第1个数据块的RDBA为0x03000173，也就是12号文件的371块。

　　dump第12号文件的371块头，来得到表TT原来的data object id：

ODU> dump datafile 12 block 371 header
Block Header:
block type=0x06 (table/index/cluster segment data block)
block format=0xa2 (oracle 10)
block rdba=0x03000173 (file#=12, block#=371)
scn=0x0000.d15033d0, seq=1, tail=0x33d00601
block checksum value=0x2498=9368, flag=4
Data Block Header Dump:
 Object id on Block? Y
 seg/obj: 0x2cc08=183304  csc: 0x00.d15033c7  itc: 3  flg: E  typ: 1 (data)
     brn: 0  bdba: 0x3000170 ver: 0x01

 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0xffff.000.00000000  0x00000000.0000.00  C---    0  scn 0x0000.d15033c7
0x02   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
0x03   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
Data Block Dump:
================
flag=0x0 --------
ntab=1
nrow=32
frre=-1
fsbo=0x52
ffeo=0x3a8
avsp=0x356
tosp=0x356

可以看到，TT表原来的data object id就是183304。

SQL> select object_id from user_objects where object_name='TT';

 OBJECT_ID
----------
    183304
 
 7. 使用ODU来unload数据：  
ODU> unload table sj.tt object 183304

Unloading table: TT,object ID: 183304
Unloading segment,storage(Obj#=183304 DataObj#=183304 TS#=7 File#=12 Block#=370 Cluster=0)
26472 rows unloaded

UNLOAD的行数与之前查的26472一致

8、使用sqlldr导入我们恢复的数据：打开cmd命令，执行E:\ODU\data>sqlldr 用户名/密码@数据库id control=sj_TT.ctl

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/23891491/viewspace-754872/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/23891491/viewspace-754872/