truncate表的数据恢复（一）

测试环境：

windows xp；Oracle 10.2.0.1 32bit 单实例文件系统，后期会对ASM环境进行测试

--truncate表恢复前的操作
SQL> select ts#,file#,rfile#,name,block1_offset From v$datafile;

       TS#      FILE#     RFILE# NAME                                                                          
---------- ---------- ---------- -------------------------------------------------------------------
         0          1          1 E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM01.DBF                            
         1          2          2 E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\UNDOTBS01.DBF                           
         2          3          3 E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSAUX01.DBF                            
         4          4          4 E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\USERS01.DBF                             
         6          5          5 E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\ZAKI01.DBF                              

SQL> create table ceshi as select * from sys.dba_objects;

表已创建。

SQL>
SQL> select count(1) from ceshi;

  COUNT(1)
----------
     50075

SQL>
SQL> truncate table ceshi;

表被截断。
SQL>
SQL> alter system checkpoint;

系统已更改。

SQL> conn/as sysdba
已连接。
SQL>
SQL> shutdown immediate
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。
SQL>
SQL> startup
ORACLE 例程已经启动。

Total System Global Area  612368384 bytes
Fixed Size                  1250428 bytes
Variable Size             184552324 bytes
Database Buffers          419430400 bytes
Redo Buffers                7135232 bytes
数据库装载完毕。
数据库已经打开。
SQL>

--以下是windows平台下的操作，在数据库关闭状态下进行数据恢复
1、把以下内容写入control.txt文件
0          1          1 E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM01.DBF     8192
1          2          2 E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\UNDOTBS01.DBF    8192
2          3          3 E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSAUX01.DBF     8192
4          4          4 E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\USERS01.DBF      8192
6          5          5 E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\ZAKI01.DBF       8192

2、运行odu.exe应用程序
Oracle Data Unloader:Release 2.4.0

Copyright (c) 2008,2009 XiongJun. All rights reserved.

Web: http://www.laoxiong.net
Email: [e-mail]

loading default config.......

byte_order little
block_size  8192
data_path   data
lob_path    lob
charset_name ZHS16GBK
ncharset_name AL16UTF16
output_format text
lob_storage file
clob_byte_order little
trace_level 1
delimiter |
load control file 'config.txt' successful
loading default control file ......

 ts#   fn  rfn bsize   blocks bf offset filename
---- ---- ---- ----- -------- -- ------ ----------------------------------------
----
   0    1    1  8192    62720 N       0 E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SY
STEM01.DBF
   1    2    2  8192     7040 N       0 E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\UN
DOTBS01.DBF
   2    3    3  8192    44800 N       0 E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SY
SAUX01.DBF
   4    4    4  8192      640 N       0 E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\US
ERS01.DBF
   6    5    5  8192    62720 N       0 E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\ZA
KI01.DBF
load control file 'control.txt' successful
loading dictionary data......

ODU>
ODU> unload dict
CLUSTER C_USER# file_no: 1 block_no: 89
TABLE OBJ$ file_no: 1 block_no: 121
CLUSTER C_OBJ# file_no: 1 block_no: 25
CLUSTER C_OBJ# file_no: 1 block_no: 25
found IND$'s obj# 19
found IND$'s dataobj#:2,ts#:0,file#:1,block#:25,tab#:3
found TABPART$'s obj# 266
found TABPART$'s dataobj#:266,ts#:0,file#:1,block#:2121,tab#:0
found INDPART$'s obj# 271
found INDPART$'s dataobj#:271,ts#:0,file#:1,block#:2161,tab#:0
found TABSUBPART$'s obj# 278
found TABSUBPART$'s dataobj#:278,ts#:0,file#:1,block#:2217,tab#:0
found INDSUBPART$'s obj# 283
found INDSUBPART$'s dataobj#:283,ts#:0,file#:1,block#:2257,tab#:0
found IND$'s obj# 19
found IND$'s dataobj#:2,ts#:0,file#:1,block#:25,tab#:3
found LOB$'s obj# 151
found LOB$'s dataobj#:2,ts#:0,file#:1,block#:25,tab#:6
found LOBFRAG$'s obj# 299
found LOBFRAG$'s dataobj#:299,ts#:0,file#:1,block#:2393,tab#:0
ODU>
ODU>
ODU> desc test01.ceshi
Object ID:57599
Storage(Obj#=57599 DataObj#=57599 TS#=6 File#=5 Block#=27 Cluster=0)

NO. SEG INT Column Name                    Null?     Type

--- --- --- ------------------------------ --------- ---------------------------
---
  1   1   1 OWNER                                    VARCHAR2(30)

  2   2   2 OBJECT_NAME                              VARCHAR2(128)

  3   3   3 SUBOBJECT_NAME                           VARCHAR2(30)

  4   4   4 OBJECT_ID                                NUMBER

  5   5   5 DATA_OBJECT_ID                           NUMBER

  6   6   6 OBJECT_TYPE                              VARCHAR2(19)

  7   7   7 CREATED                                  DATE

  8   8   8 LAST_DDL_TIME                            DATE
  9   9   9 TIMESTAMP                                VARCHAR2(19)

 10  10  10 STATUS                                   VARCHAR2(7)

 11  11  11 TEMPORARY                                VARCHAR2(1)

 12  12  12 GENERATED                                VARCHAR2(1)

 13  13  13 SECONDARY                                VARCHAR2(1)

ODU> scan extent tablespace 6

scanning extent...
scanning extent finished.

ODU> dump datafile 5 block 27
Block Header:
block type=0x23 (ASSM segment header block)
block format=0xa2 (oracle 10)
block rdba=0x0140001b (file#=5, block#=27)
scn=0x0000.00565c27, seq=1, tail=0x5c272301
block checksum value=0x48a4=18596, flag=4
Data Segment Header:
  Extent Control Header
  -------------------------------------------------------------
  Extent Header:: extents: 21  blocks: 768
                  last map: 0x00000000  #maps: 0  offset: 2716
      Highwater:: 0x0140034e  (rfile#=5,block#=846)
                  ext#: 20  blk#: 69   ext size:128
      #blocks in seg. hdr's freelists: 0
      #blocks below: 709
      mapblk: 0x00000000   offset: 20
  --------------------------------------------------------
  Low HighWater Mark :
      Highwater::  0x0140034e  ext#: 20      blk#: 69      ext size: 128
  #blocks in seg. hdr's freelists: 0
  #blocks below: 709
  mapblk  0x00000000  offset: 20
  Level 1 BMB for High HWM block: 0x0140030a
  Level 1 BMB for Low HWM block: 0x0140030a
  --------------------------------------------------------
  Segment Type: 1 nl2: 1      blksz: 8192   fbsz: 0
  L2 Array start offset:  0x00001434
  First Level 3 BMB:  0x00000000
  L2 Hint for inserts:  0x0140001a
  Last Level 1 BMB:  0x0140030a
  Last Level 1I BMB:  0x0140001a
  Last Level 1II BMB:  0x00000000
     Map Header:: next  0x00000000  #extents: 21    obj#: 57599  flag: 0x2100000
00
  Extent Map
  -------------------------------------------------------------
   0x01400019  length: 8
   0x01400021  length: 8
   0x01400029  length: 8
   0x01400031  length: 8
   0x01400039  length: 8
   0x01400041  length: 8
   0x01400049  length: 8
   0x01400051  length: 8
   0x01400059  length: 8
   0x01400061  length: 8
   0x01400069  length: 8
   0x01400071  length: 8
   0x01400079  length: 8
   0x01400081  length: 8
   0x01400089  length: 8
   0x01400091  length: 8
   0x01400109  length: 128
   0x01400189  length: 128
   0x01400209  length: 128
   0x01400289  length: 128
   0x01400309  length: 128

  Auxillary Map
  -------------------------------------------------------------
   Extent 0      :  L1 dba:  0x01400019 Data dba:  0x0140001c
   Extent 1      :  L1 dba:  0x01400019 Data dba:  0x01400021
   Extent 2      :  L1 dba:  0x01400029 Data dba:  0x0140002a
   Extent 3      :  L1 dba:  0x01400029 Data dba:  0x01400031
   Extent 4      :  L1 dba:  0x01400039 Data dba:  0x0140003a
   Extent 5      :  L1 dba:  0x01400039 Data dba:  0x01400041
   Extent 6      :  L1 dba:  0x01400049 Data dba:  0x0140004a
   Extent 7      :  L1 dba:  0x01400049 Data dba:  0x01400051
   Extent 8      :  L1 dba:  0x01400059 Data dba:  0x0140005a
   Extent 9      :  L1 dba:  0x01400059 Data dba:  0x01400061
   Extent 10     :  L1 dba:  0x01400069 Data dba:  0x0140006a
   Extent 11     :  L1 dba:  0x01400069 Data dba:  0x01400071
   Extent 12     :  L1 dba:  0x01400079 Data dba:  0x0140007a
   Extent 13     :  L1 dba:  0x01400079 Data dba:  0x01400081
   Extent 14     :  L1 dba:  0x01400089 Data dba:  0x0140008a
   Extent 15     :  L1 dba:  0x01400089 Data dba:  0x01400091
   Extent 16     :  L1 dba:  0x01400109 Data dba:  0x0140010b
   Extent 17     :  L1 dba:  0x01400189 Data dba:  0x0140018b
   Extent 18     :  L1 dba:  0x01400209 Data dba:  0x0140020b
   Extent 19     :  L1 dba:  0x01400289 Data dba:  0x0140028b
   Extent 20     :  L1 dba:  0x01400309 Data dba:  0x0140030b
  -------------------------------------------------------------

   Second Level Bitmap block DBAs
  -------------------------------------------------------------
   DBA 1:   0x0140001a

ODU>
ODU> dump datafile 5 block 846 header  --此步骤在测试的时候可以省略
dump datafile <file#> block <block#>
ODU>
ODU> unload table test01.ceshi object 57599

Unloading table: CESHI,object ID: 57599
Unloading segment,storage(Obj#=57599 DataObj#=57599 TS#=6 File#=5 Block#=27 Clus
ter=0)

ODU>

cd E:\oracle\product\10.2.0\odu_240_win32\odu\data
cd E:
E:\oracle\product\10.2.0\odu_240_win32\odu\data>sqlldr test01/test01 control=E:\oracle\product\10.2.0\odu_240_win32\odu\
data\TEST01_CESHI.ctl

SQL> conn test01/test01
已连接。
SQL> 
SQL> select count(1) from ceshi;

  COUNT(1)
----------
     50075

参考文档：http://www.xtopace.com/database/%E4%BD%BF%E7%94%A8odu%E6%81%A2%E5%A4%8D%E8%A2%ABtruncate%E7%9A%84%E8%A1%A8.html

参考文档：http://www.oracleodu.com/cn/recovery-truncated-table-in-asm-using-odu.html