继续测试一下drop表用ODU是怎么恢复的，
http://www.laoxiong.net/odu_recover_drop_table.html
[@more@]SQL> conn myuser/myuserConnected.
SQL> create table odu_test2 as select * from all_tables;
Table created.
SQL> desc odu_test2;
Name Null? Type
----------------------------------------- -------- ----------------------------
OWNER NOT NULL VARCHAR2(30)
TABLE_NAME NOT NULL VARCHAR2(30)
TABLESPACE_NAME VARCHAR2(30)
CLUSTER_NAME VARCHAR2(30)
IOT_NAME VARCHAR2(30)
STATUS VARCHAR2(8)
PCT_FREE NUMBER
PCT_USED NUMBER
INI_TRANS NUMBER
MAX_TRANS NUMBER
INITIAL_EXTENT NUMBER
NEXT_EXTENT NUMBER
MIN_EXTENTS NUMBER
MAX_EXTENTS NUMBER
PCT_INCREASE NUMBER
FREELISTS NUMBER
FREELIST_GROUPS NUMBER
LOGGING VARCHAR2(3)
BACKED_UP VARCHAR2(1)
NUM_ROWS NUMBER
BLOCKS NUMBER
EMPTY_BLOCKS NUMBER
AVG_SPACE NUMBER
CHAIN_CNT NUMBER
AVG_ROW_LEN NUMBER
AVG_SPACE_FREELIST_BLOCKS NUMBER
NUM_FREELIST_BLOCKS NUMBER
DEGREE VARCHAR2(10)
INSTANCES VARCHAR2(10)
CACHE VARCHAR2(5)
TABLE_LOCK VARCHAR2(8)
SAMPLE_SIZE NUMBER
LAST_ANALYZED DATE
PARTITIONED VARCHAR2(3)
IOT_TYPE VARCHAR2(12)
TEMPORARY VARCHAR2(1)
SECONDARY VARCHAR2(1)
NESTED VARCHAR2(3)
BUFFER_POOL VARCHAR2(7)
ROW_MOVEMENT VARCHAR2(8)
GLOBAL_STATS VARCHAR2(3)
USER_STATS VARCHAR2(3)
DURATION VARCHAR2(15)
SKIP_CORRUPT VARCHAR2(8)
MONITORING VARCHAR2(3)
CLUSTER_OWNER VARCHAR2(30)
DEPENDENCIES VARCHAR2(8)
COMPRESSION VARCHAR2(8)
DROPPED VARCHAR2(3)
SQL> insert into odu_test2 select * from odu_test2;
100 rows created.
SQL> /
200 rows created.
SQL> /
400 rows created.
SQL> /
800 rows created.
SQL> /
1600 rows created.
SQL> /
3200 rows created.
SQL> /
6400 rows created.
SQL> select count(*) from odu_test2;
COUNT(*)
----------
12800
SQL> select count(*) from odu_test2;
COUNT(*)
----------
12800
SQL> commit;
SQL> drop table odu_test2 purge;
Table dropped.
在发现重要的表被意外drop掉的时候，应该立即停止应用，offline那个表所在的表空间或关闭数据库。这里
odu_tes2t表是建在users表空间下，先将users表空间offline：
SQL> conn / as sysdba
Connected.
SQL> alter tablespace users offline;
Tablespace altered.
然后需要使用logminer来查找被drop表的data object id：
SQL> exec sys.dbms_logmnr.add_logfile(logfilename=>'/u01/app/oracle/oradata/orcl/redo05.dbf');
PL/SQL procedure successfully completed.
SQL> exec sys.dbms_logmnr.start_logmnr(options=>sys.dbms_logmnr.dict_from_online_catalog);
PL/SQL procedure successfully completed.
SQL> select scn,to_char(timestamp,'mm-dd-yy:hh24:mi:ss') timestamp,sql_redo from v$logmnr_contents
where operation='DDL' and sql_redo like'%odu_test2%' order by 2;
SCN TIMESTAMP
---------- -----------------
SQL_REDO
--------------------------------------------------------------------------------
1629274 07-06-09:17:23:28
create table odu_test2 as select * from all_tables;
1629488 07-06-09:17:25:38
drop table odu_test2 purge;
SQL> select scn,timestamp,sql_redo from v$logmnr_contents where timestamp=to_date('07-06-
09:17:25:38','mm-dd-yy:hh24:mi:ss') order by 1;
按drop的时间没有找到，往向加一秒看看,找到obj#为55443，应该就是这个了
SQL> select scn,timestamp,sql_redo from v$logmnr_contents where timestamp=to_date('07-06-
09:17:25:39','mm-dd-yy:hh24:mi:ss') order by 1;
SCN TIMESTAMP
---------- ---------
SQL_REDO
----------------------------------------------------------------------------------------------------
1629494 06-JUL-09
delete from "SYS"."CON$" where "OWNER#" = '64' and "NAME" = 'SYS_C006570' and "CON#" = '6570' and
"SPARE1" IS NULL and "SPARE2" IS NULL and "SPARE3" IS NULL and "SPARE4" IS NULL and "SPARE5" IS NULL
and "SPARE6" IS NULL and ROWID = 'AAAAAcAABAAAN4lAB5';
1629495 06-JUL-09
delete from "SYS"."OBJ$" where "OBJ#" = '55443' and "DATAOBJ#" = '55443' and "OWNER#" = '64' and
"NAME" = 'ODU_TEST2' and "NAMESPACE" = '1' and "SUBNAME" IS NULL and "TYPE#" = '2' and "CTIME" =
TO_DATE('06-JUL-09', 'DD-MON-RR') and "MTIME" = TO_DATE('06-JUL-09', 'DD-MON-RR') and "STIME" =
TO_DATE('06-JUL-09', 'DD-MON-RR') and "STATUS" = '1' and "REMOTEOWNER" IS NULL and "LINKNAME" IS NULL
and "FLAGS" = '0' and "OID$" IS NULL and "SPARE1" = '6' and "SPARE2" = '1' and "SPARE3" IS NULL and
"SPARE4" IS NULL and "SPARE5" IS NULL and "SPARE6" IS NULL and ROWID = 'AAAAASAABAAAMRsAAH';
找到obj#就可以用ODU来恢复了。
[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 152000 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> scan extent tablespace 4;
scan extent start: 2009-07-06 17:53:34
scanning extent...
scanning extent finished.
scan extent completed: 2009-07-06 17:55:16
这里假设我们不知道这个表有多少列，每个列的数据类型，我们可以通过ODU的抽样来自动判断数据的类型：
ODU> unload object 55443 sample
Unloading Object,object ID: 55443, Cluster: 0
output data is in file : 'data/ODU_0000055443.txt'
Sample result:
object id: 55443
tablespace no: 4
sampled 1031 rows
column count: 49
column 1 type: VARCHAR2
column 2 type: VARCHAR2
column 3 type: VARCHAR2
column 4 type: RAW
column 5 type: RAW
column 6 type: VARCHAR2
column 7 type: NUMBER
column 8 type: NUMBER
column 9 type: NUMBER
column 10 type: NUMBER
column 11 type: NUMBER
column 12 type: RAW
column 13 type: NUMBER
column 14 type: NUMBER
column 15 type: RAW
column 16 type: NUMBER
column 17 type: NUMBER
column 18 type: VARCHAR2
column 19 type: VARCHAR2
column 20 type: NUMBER
column 21 type: NUMBER
column 22 type: NUMBER
column 23 type: NUMBER
column 24 type: NUMBER
column 25 type: NUMBER
column 26 type: NUMBER
column 27 type: NUMBER
column 28 type: VARCHAR2
column 29 type: VARCHAR2
column 30 type: VARCHAR2
column 31 type: VARCHAR2
column 32 type: NUMBER
column 33 type: DATE
column 34 type: VARCHAR2
column 35 type: VARCHAR2
column 36 type: VARCHAR2
column 37 type: VARCHAR2
column 38 type: VARCHAR2
column 39 type: VARCHAR2
column 40 type: VARCHAR2
column 41 type: VARCHAR2
column 42 type: VARCHAR2
column 43 type: VARCHAR2
column 44 type: VARCHAR2
column 45 type: VARCHAR2
column 46 type: RAW
column 47 type: VARCHAR2
column 48 type: VARCHAR2
column 49 type: VARCHAR2
COMMAND:
unload object 55443 tablespace 4 column VARCHAR2 VARCHAR2 VARCHAR2 RAW RAW VARCHAR2 NUMBER NUMBER
NUMBER NUMBER NUMBER RAW NUMBER NUMBER RAW NUMBER NUMBER VARCHAR2 VARCHAR2 NUMBER NUMBER NUMBER
NUMBER NUMBER NUMBER NUMBER NUMBER VARCHAR2 VARCHAR2 VARCHAR2 VARCHAR2 NUMBER DATE VARCHAR2 VARCHAR2
VARCHAR2 VARCHAR2 VARCHAR2 VARCHAR2 VARCHAR2 VARCHAR2 VARCHAR2 VARCHAR2 VARCHAR2 VARCHAR2 RAW
VARCHAR2 VARCHAR2 VARCHAR2
可以看到，ODU比较准确地判断出了列类型，RAW也判断出来了.
从输出的内容可以看到，可以在 ‘data/ODU_ODU_0000055443.txt’ 中看到抽样的数据，同时可以在’
data/sample.txt‘中看到更详细的抽样输出。
也同时列出来恢复所有数据的COMMAND.下面只要复制COMMAND的内容就可以恢复数据了。
ODU> unload object 55443 tablespace 4 column VARCHAR2 VARCHAR2 VARCHAR2 RAW RAW VARCHAR2 NUMBER
NUMBER NUMBER NUMBER NUMBER RAW NUMBER NUMBER RAW NUMBER NUMBER VARCHAR2 VARCHAR2 NUMBER NUMBER
NUMBER NUMBER NUMBER NUMBER NUMBER NUMBER VARCHAR2 VARCHAR2 VARCHAR2 VARCHAR2 NUMBER DATE VARCHAR2
VARCHAR2 VARCHAR2 VARCHAR2 VARCHAR2 VARCHAR2 VARCHAR2 VARCHAR2 VARCHAR2 VARCHAR2 VARCHAR2 VARCHAR2
RAW VARCHAR2 VARCHAR2 VARCHAR2
Unloading Object,object ID: 55443, Cluster: 0
12800 rows unloaded
现在可以online表空间来重建这个表，再sqlldr数据。
SQL> alter tablespace users online;
Tablespace altered.
修改一下 ODU_0000055443.sql文件的表名，建立table.
SQL> CREATE TABLE MYUSER.ODU_TEST2
(
"C0001" VARCHAR2(4000) ,
"C0002" VARCHAR2(4000) ,
"C0003" VARCHAR2(4000) ,
"C0004" RAW(2000) ,
"C0005" RAW(2000) ,
"C0006" VARCHAR2(4000) ,
"C0007" NUMBER ,
"C0008" NUMBER ,
"C0009" NUMBER ,
"C0010" NUMBER ,
"C0011" NUMBER ,
"C0012" RAW(2000) ,
"C0013" NUMBER ,
"C0014" NUMBER ,
"C0015" RAW(2000) ,
"C0016" NUMBER ,
"C0017" NUMBER ,
"C0018" VARCHAR2(4000) ,
"C0019" VARCHAR2(4000) ,
"C0020" NUMBER ,
"C0021" NUMBER ,
"C0022" NUMBER ,
"C0023" NUMBER ,
"C0024" NUMBER ,
"C0025" NUMBER ,
"C0026" NUMBER ,
"C0027" NUMBER ,
"C0028" VARCHAR2(4000) ,
"C0029" VARCHAR2(4000) ,
"C0030" VARCHAR2(4000) ,
"C0031" VARCHAR2(4000) ,
"C0032" NUMBER ,
"C0033" DATE ,
"C0034" VARCHAR2(4000) ,
"C0035" VARCHAR2(4000) ,
"C0036" VARCHAR2(4000) ,
"C0037" VARCHAR2(4000) ,
"C0038" VARCHAR2(4000) ,
"C0039" VARCHAR2(4000) ,
"C0040" VARCHAR2(4000) ,
"C0041" VARCHAR2(4000) ,
"C0042" VARCHAR2(4000) ,
"C0043" VARCHAR2(4000) ,
"C0044" VARCHAR2(4000) ,
"C0045" VARCHAR2(4000) ,
"C0046" RAW(2000) ,
"C0047" VARCHAR2(4000) ,
"C0048" VARCHAR2(4000) ,
"C0049" VARCHAR2(4000)
);
Table created.
也修改一下ODU_0000055443.ctl的INTO TABLE为ODU_TEST2,开始导入数据。
[oracle@rhel140 data]$ sqlldr myuser/myuser control=ODU_0000055443.ctl
SQL*Loader: Release 10.2.0.1.0 - Production on Mon Jul 6 18:08:19 2009
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Commit point reached - logical record count 67
Commit point reached - logical record count 134
Commit point reached - logical record count 201
Commit point reached - logical record count 268
Commit point reached - logical record count 335
Commit point reached - logical record count 402
Commit point reached - logical record count 469
Commit point reached - logical record count 536
Commit point reached - logical record count 603
Commit point reached - logical record count 670
Commit point reached - logical record count 737
Commit point reached - logical record count 804
Commit point reached - logical record count 871
Commit point reached - logical record count 938
Commit point reached - logical record count 1005
Commit point reached - logical record count 1072
Commit point reached - logical record count 1139
Commit point reached - logical record count 1206
Commit point reached - logical record count 1273
Commit point reached - logical record count 1340
Commit point reached - logical record count 1407
Commit point reached - logical record count 1474
Commit point reached - logical record count 1541
Commit point reached - logical record count 1608
Commit point reached - logical record count 1675
Commit point reached - logical record count 1742
Commit point reached - logical record count 1809
Commit point reached - logical record count 1876
Commit point reached - logical record count 1943
Commit point reached - logical record count 2010
Commit point reached - logical record count 2077
Commit point reached - logical record count 2144
Commit point reached - logical record count 2211
Commit point reached - logical record count 2278
Commit point reached - logical record count 2345
Commit point reached - logical record count 2412
Commit point reached - logical record count 2479
Commit point reached - logical record count 2546
Commit point reached - logical record count 2613
Commit point reached - logical record count 2680
Commit point reached - logical record count 2747
Commit point reached - logical record count 2814
Commit point reached - logical record count 2881
Commit point reached - logical record count 2948
Commit point reached - logical record count 3015
Commit point reached - logical record count 3082
Commit point reached - logical record count 3149
Commit point reached - logical record count 3216
Commit point reached - logical record count 3283
Commit point reached - logical record count 3350
Commit point reached - logical record count 3417
Commit point reached - logical record count 3484
Commit point reached - logical record count 3551
Commit point reached - logical record count 3618
Commit point reached - logical record count 3685
Commit point reached - logical record count 3752
Commit point reached - logical record count 3819
Commit point reached - logical record count 3886
Commit point reached - logical record count 3953
Commit point reached - logical record count 4020
Commit point reached - logical record count 4087
Commit point reached - logical record count 4154
Commit point reached - logical record count 4221
Commit point reached - logical record count 4288
Commit point reached - logical record count 4355
Commit point reached - logical record count 4422
Commit point reached - logical record count 4489
Commit point reached - logical record count 4556
Commit point reached - logical record count 4623
Commit point reached - logical record count 4690
Commit point reached - logical record count 4757
Commit point reached - logical record count 4824
Commit point reached - logical record count 4891
Commit point reached - logical record count 4958
Commit point reached - logical record count 5025
Commit point reached - logical record count 5092
Commit point reached - logical record count 5159
Commit point reached - logical record count 5226
Commit point reached - logical record count 5293
Commit point reached - logical record count 5360
Commit point reached - logical record count 5427
Commit point reached - logical record count 5494
Commit point reached - logical record count 5561
Commit point reached - logical record count 5628
Commit point reached - logical record count 5695
Commit point reached - logical record count 5762
Commit point reached - logical record count 5829
Commit point reached - logical record count 5896
Commit point reached - logical record count 5963
Commit point reached - logical record count 6030
Commit point reached - logical record count 6097
Commit point reached - logical record count 6164
Commit point reached - logical record count 6231
Commit point reached - logical record count 6298
Commit point reached - logical record count 6365
Commit point reached - logical record count 6432
Commit point reached - logical record count 6499
Commit point reached - logical record count 6566
Commit point reached - logical record count 6633
Commit point reached - logical record count 6700
Commit point reached - logical record count 6767
Commit point reached - logical record count 6834
Commit point reached - logical record count 6901
Commit point reached - logical record count 6968
Commit point reached - logical record count 7035
Commit point reached - logical record count 7102
Commit point reached - logical record count 7169
Commit point reached - logical record count 7236
Commit point reached - logical record count 7303
Commit point reached - logical record count 7370
Commit point reached - logical record count 7437
Commit point reached - logical record count 7504
Commit point reached - logical record count 7571
Commit point reached - logical record count 7638
Commit point reached - logical record count 7705
Commit point reached - logical record count 7772
Commit point reached - logical record count 7839
Commit point reached - logical record count 7906
Commit point reached - logical record count 7973
Commit point reached - logical record count 8040
Commit point reached - logical record count 8107
Commit point reached - logical record count 8174
Commit point reached - logical record count 8241
Commit point reached - logical record count 8308
Commit point reached - logical record count 8375
Commit point reached - logical record count 8442
Commit point reached - logical record count 8509
Commit point reached - logical record count 8576
Commit point reached - logical record count 8643
Commit point reached - logical record count 8710
Commit point reached - logical record count 8777
Commit point reached - logical record count 8844
Commit point reached - logical record count 8911
Commit point reached - logical record count 8978
Commit point reached - logical record count 9045
Commit point reached - logical record count 9112
Commit point reached - logical record count 9179
Commit point reached - logical record count 9246
Commit point reached - logical record count 9313
Commit point reached - logical record count 9380
Commit point reached - logical record count 9447
Commit point reached - logical record count 9514
Commit point reached - logical record count 9581
Commit point reached - logical record count 9648
Commit point reached - logical record count 9715
Commit point reached - logical record count 9782
Commit point reached - logical record count 9849
Commit point reached - logical record count 9916
Commit point reached - logical record count 9983
Commit point reached - logical record count 10050
Commit point reached - logical record count 10117
Commit point reached - logical record count 10184
Commit point reached - logical record count 10251
Commit point reached - logical record count 10318
Commit point reached - logical record count 10385
Commit point reached - logical record count 10452
Commit point reached - logical record count 10519
Commit point reached - logical record count 10586
Commit point reached - logical record count 10653
Commit point reached - logical record count 10720
Commit point reached - logical record count 10787
Commit point reached - logical record count 10854
Commit point reached - logical record count 10921
Commit point reached - logical record count 10988
Commit point reached - logical record count 11055
Commit point reached - logical record count 11122
Commit point reached - logical record count 11189
Commit point reached - logical record count 11256
Commit point reached - logical record count 11323
Commit point reached - logical record count 11390
Commit point reached - logical record count 11457
Commit point reached - logical record count 11524
Commit point reached - logical record count 11591
Commit point reached - logical record count 11658
Commit point reached - logical record count 11725
Commit point reached - logical record count 11792
Commit point reached - logical record count 11859
Commit point reached - logical record count 11926
Commit point reached - logical record count 11993
Commit point reached - logical record count 12060
Commit point reached - logical record count 12127
Commit point reached - logical record count 12194
Commit point reached - logical record count 12261
Commit point reached - logical record count 12328
Commit point reached - logical record count 12395
Commit point reached - logical record count 12462
Commit point reached - logical record count 12529
Commit point reached - logical record count 12596
Commit point reached - logical record count 12663
Commit point reached - logical record count 12730
Commit point reached - logical record count 12797
Commit point reached - logical record count 12800
SQL> select count(*) from myuser.ODU_TEST2;
COUNT(*)
----------
12800
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/271283/viewspace-1024235/ ，如需转载，请注明出处，否则将追究法律责任。
请登录后发表评论
登录
全部评论
