测试环境:
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: magic007cn@gmail.com
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.oracleodu.com/cn/recovery-truncated-table-in-asm-using-odu.html