测试ODU的truncate表恢复

老熊的ODU主页地址：

http://www.laoxiong.net/category/odu

测试平台是linux 32bit+oracle10.2.0.1

测试ODU的truncate表恢复

老熊的ODU主页地址：

http://www.laoxiong.net/category/odu

测试平台是linux 32bit+oracle10.2.0.1

SQL> conn myuser/myuser
Connected.
SQL> create table ODU_TEST as
2 select * From all_objects;

Table created.

SQL> select count(*) from ODU_TEST;

COUNT(*)
----------
40726

SQL> truncate table ODU_TEST;

Table truncated.

先查看此表所在的表空间,将其offline,然后做一个Checkpoint，让ODU能够读到最新的数据字典数据。

SQL> select tablespace_name from user_tables where table_name='ODU_TEST';

TABLESPACE_NAME
------------------------------
USERS

SQL> conn / as sysdba
Connected.
SQL> alter users offline;

SQL> alter tablespace users offline;

Tablespace altered.

SQL> alter system checkpoint;

System altered.


现在来恢复这个表试试


[oracle@rhel140 ~]$ tar -zxvf odu_307_linux_x86.tar.gz
odu/
odu/odu
tar: odu/odu: time stamp 2009-07-12 21:05:30 is 546270 s in the future
odu/control.txt
odu/config.txt
odu/data/


[oracle@rhel140 odu]$ ./odu

Oracle Data Unloader:Release 3.0.7

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
db_timezone -7
client_timezone 8
data_path data
charset_name ZHS16GBK
ncharset_name AL16UTF16
output_format text
lob_storage infile
clob_byte_order little




load control file 'config.txt' successful
loading default control file ......


ts# fn rfn bsize blocks bf offset filename
---- ---- ---- ----- -------- -- ------ --------------------------------------------
load control file 'control.txt' successful
loading dictionary data......

ODU> open

ts# fn rfn bsize blocks bf offset filename
---- ---- ---- ----- -------- -- ------ --------------------------------------------
load control file 'control.txt' successful


不知为什么open时读数据文件信息读不出来。
后来我手工将数据文件信息写到control文件中。sql如下：
select ts#,file#,rfile#,name,block1_offset From v$datafile


[oracle@rhel140 odu]$ ./odu

Oracle Data Unloader:Release 3.0.7

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
db_timezone -7
client_timezone 8
data_path data
charset_name ZHS16GBK
ncharset_name AL16UTF16
output_format text
lob_storage infile
clob_byte_order little




load control file 'config.txt' successful
loading default control file ......


ts# fn rfn bsize blocks bf offset filename
---- ---- ---- ----- -------- -- ------ --------------------------------------------
0 1 1 8192 64000 N 0 /u01/app/oracle/oradata/orcl/system01.dbf
1 2 2 8192 10240 N 0 /u01/app/oracle/oradata/orcl/undotbs01.dbf
2 3 3 8192 39680 N 0 /u01/app/oracle/oradata/orcl/sysaux01.dbf
4 4 4 8192 63360 N 0 /u01/app/oracle/oradata/orcl/users01.dbf
6 5 5 8192 12800 N 0 /u01/app/oracle/oradata/orcl/example01.dbf
8 6 6 8192 2560 N 0 /u01/app/oracle/oradata/orcl/L5MSPACE.dbf
load control file 'control.txt' successful
loading dictionary data......

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


#用desc命令来显示表的段头信息，格式是：desc username.tablename

ODU> desc myuse.odu_test

table 'MYUSE.ODU_TEST' does not exist.
ODU> desc myuser.odu_test


Object ID:55433
Storage(Obj#=55433 DataObj#=55434 TS#=4 File#=4 Block#=62555 Cluster=0)

NO. SEG INT Column Name Null? Type
--- --- --- ------------------------------ --------- ------------------------------
1 1 1 OWNER NOT NULL VARCHAR2(30)
2 2 2 OBJECT_NAME NOT NULL VARCHAR2(30)
3 3 3 SUBOBJECT_NAME VARCHAR2(30)
4 4 4 OBJECT_ID NOT NULL NUMBER
5 5 5 DATA_OBJECT_ID NUMBER
6 6 6 OBJECT_TYPE VARCHAR2(19)
7 7 7 CREATED NOT NULL DATE
8 8 8 LAST_DDL_TIME NOT NULL 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)

从上面的输出中，我们可以看到，TEST.T1表所在的表空间号为4，数据段头部为4号文件的62555号块。

ODU> scan extent tablespace 4

scan extent start: 2009-07-06 14:03:34
scanning extent...
scanning extent finished.
scan extent completed: 2009-07-06 14:04:05


我们使用ODU来确定T1表原来的data object id。一般来说，数据段的数据块，一般是在段头后面相邻

的块中。但是我们可以从段头来确认

ODU> dump datafile 4 block 62555
Block Header:
block type=0x23 (ASSM segment header block)
block format=0xa2 (oracle 10)
block rdba=0x0100f45b (file#=4, block#=62555)
scn=0x0000.0018a25f, seq=3, tail=0xa25f2303
block checksum value=0x71ef=29167, flag=4
Data Segment Header:
Extent Control Header
-------------------------------------------------------------
Extent Header:: extents: 1 blocks: 8
last map: 0x00000000 #maps: 0 offset: 2716
Highwater:: 0x0100f45c (rfile#=4,block#=62556)
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:: 0x0100f45c 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: 0x0100f459
Level 1 BMB for Low HWM block: 0x0100f459
--------------------------------------------------------
Segment Type: 1 nl2: 1 blksz: 8192 fbsz: 0
L2 Array start offset: 0x00001434
First Level 3 BMB: 0x00000000
L2 Hint for inserts: 0x0100f45a
Last Level 1 BMB: 0x0100f459
Last Level 1I BMB: 0x0100f45a
Last Level 1II BMB: 0x00000000
Map Header:: next 0x00000000 #extents: 1 obj#: 55434 flag: 0x210000000
Extent Map
-------------------------------------------------------------
0x0100f459 length: 8

Auxillary Map
-------------------------------------------------------------
Extent 0 : L1 dba: 0x0100f459 Data dba: 0x0100f45c
-------------------------------------------------------------

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



从上面的输出中的"Extent 0 : L1 dba: 0x0100f459 Data dba: 0x0100f45c"
可以看到，段的第1个数据块的RDBA为0×0x0100f45c也就是4号文件的62556块。

我们dump第4号文件的62556块头，来得到表ODU_TEST原来的data object id：

ODU> dump datafile 4 block 62556 header
Block Header:
block type=0x06 (table/index/cluster segment data block)
block format=0xa2 (oracle 10)
block rdba=0x0100f45c (file#=4, block#=62556)
scn=0x0000.0018a18d, seq=2, tail=0xa18d0602
block checksum value=0xf49e=62622, flag=4
Data Block Header Dump:
Object id on Block? Y
seg/obj: 0xd889=55433 csc: 0x00.18a18c itc: 3 flg: E typ: 1 (data)
brn: 0 bdba: 0x100f459 ver: 0x01

Itl Xid Uba Flag Lck Scn/Fsc
0x01 0xffff.000.00000000 0x00000000.0000.00 C--- 0 scn 0x0000.0018a18c
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=80
frre=-1
fsbo=0xb2
ffeo=0x400
avsp=0x34e
tosp=0x34e

可以看到，T1表原来的data object id就是55433

ODU> unload table myuser.odu_test object 55433

Unloading table: ODU_TEST,object ID: 55433
Unloading segment,storage(Obj#=55433 DataObj#=55433 TS#=4 File#=4 Block#=62555 Cluster=0)
40726 rows unloaded

在odu/data下面的目录中发现生成了三个以文件名开始的文件，ctl是sqlldr的控制文件，txt是数据文

件主，sql是建立表的create脚本。

[oracle@rhel140 data]$ ls
MYUSER_ODU_TEST.ctl MYUSER_ODU_TEST.sql MYUSER_ODU_TEST.txt



最后online表空间，将数据sqlldr导进去。

SQL> alter tablespace users online;

Tablespace altered.


[oracle@rhel140 data]$ sqlldr myuser/myuser control=MYUSER_ODU_TEST.ctl

SQL*Loader: Release 10.2.0.1.0 - Production on Mon Jul 6 14:15:22 2009

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Commit point reached - logical record count 7025
Commit point reached - logical record count 14050
Commit point reached - logical record count 21075
Commit point reached - logical record count 28100
Commit point reached - logical record count 35125
Commit point reached - logical record count 40726


测试一下，OK，老熊太牛了，牛B。

[oracle@rhel140 data]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Jul 6 14:15:37 2009

Copyright (c) 1982, 2005, Oracle. All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options


SQL> select count(*) from myuser.odu_test;

COUNT(*)
----------
40726