使用ODU恢复被DROP的表
Dec032012
本实验模拟使用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
[oracle@10205 odu]$ ./odu Oracle Data Unloader:Release 3.0.8 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 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文件
[oracle@10205 data]$ ls *.dmp ODU_0000046249.dmp ODU_0000046251.dmp ODU_0000046252.dmp ODU_0000046250.dmp
- 恢复数据
[oracle@10205 data]$ imp scott/tiger file=ODU_0000046249.dmp full=y [oracle@10205 data]$ imp scott/tiger file=ODU_0000046252.dmp full=y [oracle@10205 data]$ imp scott/tiger file=ODU_0000046251.dmp full=y [oracle@10205 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的表的操作完成。
使用ODU恢复被DROP的表：等您坐沙发呢！
发表评论
