odu的drop恢复

找了一个win32的odu测试了一下drop的恢复，odu通过直接读取数据文件抽取数据，确实可以在非常时期解决问题，一则测试用例，大家参考下，详细的odu手册大家可以参考老熊的odu专栏。

SQL> create table odu_xiaoyu01 as select * from dba_objects;

Table created

SQL> insert into odu_xiaoyu01 select * from odu_xiaoyu01;

71939 rows inserted

SQL> insert into odu_xiaoyu01 select * from odu_xiaoyu01;

143878 rows inserted

SQL> insert into odu_xiaoyu01 select * from odu_xiaoyu01;

287756 rows inserted

SQL> commit;

Commit complete

SQL> select count(*) from odu_xiaoyu01;

COUNT(*)

----------

575512

SQL> drop table odu_xiaoyu01 purge;

Table dropped

SQL> select member from v$logfile where group# in (select group# from v$log where status='CURRENT');

MEMBER

--------------------------------------------------------------------------------

F:APPORADATAXIAOYUREDO03.LOG

SQL> execute sys.dbms_logmnr.add_logfile(logfilename=>'F:APPORADATAXIAOYUREDO03.LOG');

PL/SQL procedure successfully completed

SQL> execute sys.dbms_logmnr.start_logmnr(options=>sys.dbms_logmnr.dict_from_online_catalog);

PL/SQL procedure successfully completed

SQL> col timestamp for a50

SQL> select scn,timestamp,sql_redo from v$logmnr_contents where operation='DDL' and sql_redo like '%odu_xiaoyu01%';

SCN TIMESTAMP SQL_REDO

---------- -------------------------------------------------- --------------------------------------------------------------------------------

1459346 2012/8/2 20:48:36

create table odu_xiaoyu01 as select * from dba_objects

;

1460755 2012/8/2 20:49:34

drop table odu_xiaoyu01 purge

;

SQL> select sql_redo from v$logmnr_contents where timestamp=to_date('2012-08-02 20:49:34','yyyy-mm-dd hh24:mi:ss');

SQL_REDO

--------------------------------------------------------------------------------

;

…….

delete from "SYS"."OBJ$" where "OBJ#" = '73544' and "DATAOBJ#" = '73544' and "OW

略去了很多的信息，重要的是上面的这行的object_id。

SQL> execute sys.dbms_logmnr.end_logmnr;

PL/SQL procedure successfully completed

SQL> select ts#,file#,rfile#,name from v$datafile where ts# in (select ts# from v$tablespace where name='USERS');

TS# FILE# RFILE# NAME

---------- ---------- ---------- --------------------------------------------------------------------------------

4 4 4 F:APPORADATAXIAOYUUSER01.DBF

获取底层文件的信息。

开始用odu直接从数据文件抽取数据。

load control file 'config.txt' successful

loading default control file ......

ts# fn rfn bsize blocks bf offset filename

---- ---- ---- ----- -------- -- ------ ----------------------------------------

----

4 4 4 8192 10400 N 0 F:APPORADATAXIAOYUUSER01.DBF

load control file 'control.txt' successful

loading dictionary data......

ODU> scan extent tablespace 4

scan extent start: 2012-08-02 21:14:10

scanning extent...

scanning extent finished.

scan extent completed: 2012-08-02 21:14:12

ODU> unload object 73544 sample

Unloading Object,object ID: 73544, Cluster: 0

output data is in file : 'dataODU_0000073544.txt'

Sample result:

object id: 73544

tablespace no: 4

sampled 1064 rows

column count: 14

column 1 type: VARCHAR2

column 2 type: VARCHAR2

column 3 type: RAW

column 4 type: NUMBER

column 5 type: NUMBER

column 6 type: VARCHAR2

column 7 type: DATE

column 8 type: DATE

column 9 type: VARCHAR2

column 10 type: VARCHAR2

column 11 type: VARCHAR2

column 12 type: VARCHAR2

column 13 type: VARCHAR2

column 14 type: NUMBER

COMMAND:

unload object 73544 tablespace 4 column VARCHAR2 VARCHAR2 RAW NUMBER NUMBER VARC

HAR2 DATE DATE VARCHAR2 VARCHAR2 VARCHAR2 VARCHAR2 VARCHAR2 NUMBER

ODU> unload object 73544 tablespace 4 column VARCHAR2 VARCHAR2 RAW NUMBER NUMBER

VARCHAR2 DATE DATE VARCHAR2 VARCHAR2 VARCHAR2 VARCHAR2 VARCHAR2 NUMBER

Unloading Object,object ID: 73544, Cluster: 0

575512 rows unloaded

数据已经全部通过odu从数据文件抽取出来。接下来通过data目录下的文件

然后将users表空间上线。

SQL> alter tablespace users online;

Tablespace altered

通过odu的data目录下的文件来创建表的元数据，下面根据实际情况修改表的名称和列的名称。

SQL> CREATE TABLE odu_xiaoyu01

2 (

3 "C0001" VARCHAR2(4000) ,

4 "C0002" VARCHAR2(4000) ,

5 "C0003" RAW(2000) ,

6 "C0004" NUMBER ,

7 "C0005" NUMBER ,

8 "C0006" VARCHAR2(4000) ,

9 "C0007" DATE ,

10 "C0008" DATE ,

11 "C0009" VARCHAR2(4000) ,

12 "C0010" VARCHAR2(4000) ,

13 "C0011" VARCHAR2(4000) ,

14 "C0012" VARCHAR2(4000) ,

15 "C0013" VARCHAR2(4000) ,

16 "C0014" NUMBER

17 );

根据实际情况修改sqlldr的控制文件，将infile和table name改成对应的路径位置和表明。

Sqlldr xiaoyu/xiaoyu control=E:toolodu_307odu_307_win32odudata

ODU_0000073544.ctl log=d:odu01.log

Sqlldr插入完毕后查看下数据，已经全部抽取完毕并加载到表中

SQL> select count(*) from odu_xiaoyu01;

COUNT(*)

----------

575512

[@more@]

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/25362835/viewspace-1059069/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/25362835/viewspace-1059069/