本實驗模拟使用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的表的操作完成。