本实验模拟使用ODU恢复被DROP掉的表的恢复，有关ODU软件的下载和使用说明详见老熊的BLOG http://www.laoxiong.net
本实验以上一篇文章《使用ODU恢复被TRUNCATE的表分区》为基础，延用测试表，该测试表有4个分区，每个分区一条记录。
SQL> select * from dbdream;
ID NAME TEST
---------- ---------- --------------------
1 dbdream is test
2 stream yes
3 wind no
4 chunfeng ttt
SQL> select * from dbdream partition(part1);
ID NAME TEST
---------- ---------- --------------------
1 dbdream is test
SQL> select * from dbdream partition(part2)
ID NAME TEST
---------- ---------- --------------------
2 stream yes
SQL> select * from dbdream partition(part3)
ID NAME TEST
---------- ---------- --------------------
3 wind no
SQL> select * from dbdream partition(part4)
ID NAME TEST
---------- ---------- --------------------
4 chunfeng ttt
DROP表
SQL> DROP TABLE DBDREAM PURGE;
Table dropped.
查询被DROP掉的表的OBJECT ID
如果这个表的元数据信息在SYSTEM回滚段内没有被清除，可以通过闪回查询到该表的OBJECT ID等信息。
SQL> select obj# || ',' || dataobj# || ',' || owner# || ',' || name || ',' || subname || ',' || type# text from sys.obj$ as of timestamp(to_date('2012-11-29 17:50:00','yyyy-mm-dd hh24:mi:ss')) where name='DBDREAM';
TEXT
-----------------------------------------------------------------------------
46249,46249,45,DBDREAM,PART1,19
46250,46250,45,DBDREAM,PART2,19
46251,46251,45,DBDREAM,PART3,19
46252,46252,45,DBDREAM,PART4,19
46248,,45,DBDREAM,,2
登录ODU
[[email protected] odu]$ ./odu
Oracle Data Unloader:Release 3.0.8
Copyright (c) 2008,2009 XiongJun. All rights reserved.
Web: http://www.laoxiong.net
Email: [email protected]
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 dmp
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 48640 N 0 /u01/app/oracle/oradata/orac/system01.dbf
4 4 4 8192 640 N 0 /u01/app/oracle/oradata/orac/users01.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
扫描数据文件
此步骤如果不执行，可能UNLOAD出来的结果不准确。
ODU> scan extent
scan extent start: 2012-11-29 22:58:34
scanning extent...
scanning extent finished.
scan extent completed: 2012-11-29 22:58:34
抽取数据
ODU> unload object 46249 tablespace 4 column NUMBER VARCHAR2 VARCHAR2
Unloading Object,object ID: 46249, Cluster: 0
1 rows unloaded
ODU> unload object 46250 tablespace 4 column NUMBER VARCHAR2 VARCHAR2
Unloading Object,object ID: 46250, Cluster: 0
1 rows unloaded
ODU> unload object 46251 tablespace 4 column NUMBER VARCHAR2 VARCHAR2
Unloading Object,object ID: 46251, Cluster: 0
1 rows unloaded
ODU> unload object 46252 tablespace 4 column NUMBER VARCHAR2 VARCHAR2
Unloading Object,object ID: 46252, Cluster: 0
1 rows unloaded
查看DMP文件
[[email protected] data]$ ls *.dmp
ODU_0000046249.dmp ODU_0000046251.dmp ODU_0000046252.dmp ODU_0000046250.dmp
恢复数据
[[email protected] data]$ imp scott/tiger file=ODU_0000046249.dmp full=y
[[email protected] data]$ imp scott/tiger file=ODU_0000046252.dmp full=y
[[email protected] data]$ imp scott/tiger file=ODU_0000046251.dmp full=y
[[email protected] data]$ imp scott/tiger file=ODU_0000046250.dmp full=y
查询
由于不知道被DROP表的元数据，ODU恢复的数据是按照ODU的规则建立表结构。
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
ODU_0000046249 TABLE
ODU_0000046252 TABLE
ODU_0000046251 TABLE
ODU_0000046250 TABLE
10.描述表结构
SQL> desc ODU_0000046251
Name Null? Type
----------------------------------------- -------- ---------------------
C0001 NUMBER
C0002 VARCHAR2(4000)
C0003 VARCHAR2(4000)
11.重建测试表表结构
SQL> create table dbdream(id number,name varchar2(10),test varchar2(20))
2 partition by list (id)
3 (
4 partition part1 values(1),
5 partition part2 values(2),
6 partition part3 values(3),
7 partition part4 values(default));
Table created.
12.将数据插入到新表
SQL> insert into DBDREAM select * from ODU_0000046249;
1 row created.
SQL> c/ODU_0000046249/ODU_0000046251
1* insert into DBDREAM select * from ODU_0000046251
SQL> /
1 row created.
SQL> c/ODU_0000046251/ODU_0000046252
1* insert into DBDREAM select * from ODU_0000046252
SQL> /
1 row created.
SQL> c/ODU_0000046252/ODU_0000046250
1* insert into DBDREAM select * from ODU_0000046250
SQL> /
1 row created.
SQL> commit;
Commit complete.
13.查询新表数据
SQL> select * from dbdream;
ID NAME TEST
---------- ---------- --------------------
1 dbdream is test
2 stream yes
3 wind no
4 chunfeng ttt
SQL> a partition (part1);
1* select * from dbdream partition (part1)
SQL> /
ID NAME TEST
---------- ---------- --------------------
1 dbdream is test
SQL> c/part1/part2
1* select * from dbdream partition (part2)
SQL> /
ID NAME TEST
---------- ---------- --------------------
2 stream yes
SQL> c/part2/part3
1* select * from dbdream partition (part3)
SQL> /
ID NAME TEST
---------- ---------- --------------------
3 wind no
SQL> c/part3/part4
1* select * from dbdream partition (part4)
SQL> /
ID NAME TEST
---------- ---------- --------------------
4 chunfeng ttt
14.删除ODU规则命名的表
SQL> drop table ODU_0000046249 purge;
Table dropped.
SQL> drop table ODU_0000046250 purge;
Table dropped.
SQL> drop table ODU_0000046251 purge;
Table dropped.
SQL> drop table ODU_0000046252 purge;
Table dropped.
至此，使用ODU恢复被DROP的表的操作完成。