dul恢复oracle数据,学习笔记:Oracle dul数据挖掘 使用DUL数据恢复软件恢复分区表中...

使用Oracle dul数据恢复工具对Oracle数据库分区表中的数据进行恢复

创建SALES分区表案例

CREATE TABLE SALES

(

PRODUCT_ID VARCHAR2(5),

SALES_DATE DATE,

SALES_COST NUMBER(10),

STATUS VARCHAR2(20)

)

PARTITION BY RANGE(SALES_DATE)

SUBPARTITION BY LIST (STATUS)

SUBPARTITION TEMPLATE

(

SUBPARTITION SUB1 VALUES ('ACTIVE') ,

SUBPARTITION SUB2 VALUES ('INACTIVE')

)

(

PARTITION P1 VALUES LESS THAN (TO_DATE('2003-01-01','YYYY-MM-DD')),

PARTITION P2 VALUES LESS THAN (TO_DATE('2003-03-01','YYYY-MM-DD'))

)

/

INSERT INTO SALES VALUES('00001','01-Jan-02',100,'ACTIVE')

/

INSERT INTO SALES VALUES('00002','01-Jan-01',200,'ACTIVE')

/

INSERT INTO SALES VALUES('00003','01-Feb-03',300,'INACTIVE')

/

INSERT INTO SALES VALUES('00004','04-Feb-03',300,'INACTIVE')

/

INSERT INTO SALES VALUES('00005','04-Feb-02',300,'INACTIVE')

/

查询结果

SQL> select * from sales;

PRODU SALES_DATE SALES_COST STATUS

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

00001 01-JAN-02 100 ACTIVE

00002 01-JAN-01 200 ACTIVE

00005 04-FEB-02 300 INACTIVE

00003 01-FEB-03 300 INACTIVE

00004 04-FEB-03 300 INACTIVE

SQL> select * from sales PARTITION(p1);

PRODU SALES_DATE SALES_COST STATUS

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

00001 01-JAN-02 100 ACTIVE

00002 01-JAN-01 200 ACTIVE

00005 04-FEB-02 300 INACTIVE

SQL> select * from sales PARTITION(p2);

PRODU SALES_DATE SALES_COST STATUS

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

00003 01-FEB-03 300 INACTIVE

00004 04-FEB-03 300 INACTIVE

SQL> select * from sales SUBPARTITION(p1_sub1);

PRODU SALES_DATE SALES_COST STATUS

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

00001 01-JAN-02 100 ACTIVE

00002 01-JAN-01 200 ACTIVE

SQL> select * from sales SUBPARTITION(p1_sub2);

PRODU SALES_DATE SALES_COST STATUS

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

00005 04-FEB-02 300 INACTIVE

SQL> select * from sales SUBPARTITION(p2_sub1);

no rows selected

SQL> select * from sales SUBPARTITION(p2_sub2);

PRODU SALES_DATE SALES_COST STATUS

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

00003 01-FEB-03 300 INACTIVE

00004 04-FEB-03 300 INACTIVE

启动dul

[oracle@xifenfei dul]$ ./dul

Data UnLoader: 10.2.0.5.20 - Internal Only - on Sat Jan 19 17:37:45 2013

with 64-bit io functions

Copyright (c) 1994 2013 Bernard van Duijnen All rights reserved.

Strictly Oracle Internal Use Only

DUL: Warning: Recreating file "dul.log"

Reading USER.dat 91 entries loaded

Reading OBJ.dat 74764 entries loaded and sorted 74764 entries

Reading TAB.dat 2882 entries loaded

Reading COL.dat 94598 entries loaded and sorted 94598 entries

Reading SEG.dat 17 entries loaded

Reading EXT.dat 43 entries loaded and sorted 43 entries

Reading TABPART.dat 150 entries loaded and sorted 150 entries

Reading TABCOMPART.dat 3 entries loaded and sorted 3 entries

Reading TABSUBPART.dat 36 entries loaded and sorted 36 entries

Reading INDPART.dat 169 entries loaded and sorted 169 entries

Reading INDCOMPART.dat 0 entries loaded and sorted 0 entries

Reading INDSUBPART.dat 0 entries loaded and sorted 0 entries

Reading IND.dat 5150 entries loaded

Reading LOB.dat

DUL: Warning: Increased the size of DC_LOBS from 1024 to 8192 entries

1286 entries loaded

Reading ICOL.dat 7569 entries loaded

Reading COLTYPE.dat 3003 entries loaded

Reading TYPE.dat 2872 entries loaded

Reading ATTRIBUTE.dat 11127 entries loaded

Reading COLLECTION.dat 985 entries loaded

Reading COMPATSEG.dat 0 entries loaded

Reading BOOTSTRAP.dat 60 entries loaded

Reading LOBFRAG.dat 1 entries loaded and sorted 1 entries

Reading LOBCOMPPART.dat 0 entries loaded and sorted 0 entries

Reading UNDO.dat 21 entries loaded

Reading TS.dat 10 entries loaded

Reading PROPS.dat 36 entries loaded

Database character set is ZHS16GBK

Database national character set is AL16UTF16

Found db_id = 4188950066

Found db_name = ORA11G

unload 语法

UNLOAD [TABLE] [ schema_name . ] table_name

[ PARTITION( partition_name ) ]

[ SUBPARTITION( sub_partition_name ) ]

[ ( column_definitions ) ]

[ cluster_clause ]

[ storage_clause ] ;

unload整个表

DUL> unload table chf.SALES;

. unloading table SALES

. Unloading partition P1

. Unloading sub partition P1_SUB1

. Unloading sub partition P1_SUB2

. Unloading partition P2

. Unloading sub partition P2_SUB1

. Unloading sub partition P2_SUB2

. table SALES total 5 rows unloaded

[root@xifenfei dul]# ls -l CHF_SALES*

-rw-r--r-- 1 oracle oinstall 421 Jan 19 18:09 CHF_SALES.ctl

-rw-r--r-- 1 oracle oinstall 251 Jan 19 18:09 CHF_SALES.dat

[root@xifenfei dul]# more CHF_SALES.dat

|00001| |01-JAN-2002 AD 00:00:00| |100| |ACTIVE|

|00002| |01-JAN-2001 AD 00:00:00| |200| |ACTIVE|

|00005| |04-FEB-2002 AD 00:00:00| |300| |INACTIVE|

|00003| |01-FEB-2003 AD 00:00:00| |300| |INACTIVE|

|00004| |04-FEB-2003 AD 00:00:00| |300| |INACTIVE|

unload 分区表

DUL> unload table chf.SALES PARTITION(p1);

. unloading table SALES

. Unloading partition P1

. Unloading sub partition P1_SUB1

. Unloading sub partition P1_SUB2

. table SALES total 3 rows unloaded

[root@xifenfei dul]# ls -l CHF_SALES_P1*

-rw-r--r-- 1 oracle oinstall 424 Jan 19 18:10 CHF_SALES_P1.ctl

-rw-r--r-- 1 oracle oinstall 149 Jan 19 18:10 CHF_SALES_P1.dat

[root@xifenfei dul]# more CHF_SALES_P1.dat

|00001| |01-JAN-2002 AD 00:00:00| |100| |ACTIVE|

|00002| |01-JAN-2001 AD 00:00:00| |200| |ACTIVE|

|00005| |04-FEB-2002 AD 00:00:00| |300| |INACTIVE|

unload 子分区表

DUL> unload table chf.SALES SUBPARTITION(p2_SUB2);

. unloading table SALES

. Unloading partition P1

. Unloading partition P2

. Unloading sub partition P2_SUB2

. table SALES total 2 rows unloaded

[root@xifenfei dul]# ls -l CHF_SALES_P2_SUB2*

-rw-r--r-- 1 oracle oinstall 429 Jan 19 18:14 CHF_SALES_P2_SUB2.ctl

-rw-r--r-- 1 oracle oinstall 102 Jan 19 18:14 CHF_SALES_P2_SUB2.dat

[root@xifenfei dul]# more CHF_SALES_P2_SUB2.dat

|00003| |01-FEB-2003 AD 00:00:00| |300| |INACTIVE|

|00004| |04-FEB-2003 AD 00:00:00| |300| |INACTIVE|

验证控制文件

[root@xifenfei dul]# ls -l CHF_SALES*

-rw-r--r-- 1 oracle oinstall 421 Jan 19 18:09 CHF_SALES.ctl

-rw-r--r-- 1 oracle oinstall 251 Jan 19 18:09 CHF_SALES.dat

-rw-r--r-- 1 oracle oinstall 424 Jan 19 18:10 CHF_SALES_P1.ctl

-rw-r--r-- 1 oracle oinstall 149 Jan 19 18:10 CHF_SALES_P1.dat

-rw-r--r-- 1 oracle oinstall 429 Jan 19 18:14 CHF_SALES_P2_SUB2.ctl

-rw-r--r-- 1 oracle oinstall 102 Jan 19 18:14 CHF_SALES_P2_SUB2.dat

[root@xifenfei dul]# more CHF_SALES.ctl

load data

CHARACTERSET ZHS16GBK

infile 'CHF_SALES.dat'

insert

into table "CHF"."SALES"

fields terminated by whitespace

(

"PRODUCT_ID" CHAR(5) enclosed by X'7C'

,"SALES_DATE" DATE "DD-MON-YYYY AD HH24:MI:SS" enclosed by X'7C'

,"SALES_COST" CHAR(3) enclosed by X'7C'

,"STATUS" CHAR(8) enclosed by X'7C'

)

[root@xifenfei dul]# more CHF_SALES_P1.ctl

load data

CHARACTERSET ZHS16GBK

infile 'CHF_SALES_P1.dat'

insert

into table "CHF"."SALES"

fields terminated by whitespace

(

"PRODUCT_ID" CHAR(5) enclosed by X'7C'

,"SALES_DATE" DATE "DD-MON-YYYY AD HH24:MI:SS" enclosed by X'7C'

,"SALES_COST" CHAR(3) enclosed by X'7C'

,"STATUS" CHAR(8) enclosed by X'7C'

)

[root@xifenfei dul]# more CHF_SALES_P2_SUB2.ctl

load data

CHARACTERSET ZHS16GBK

infile 'CHF_SALES_P2_SUB2.dat'

insert

into table "CHF"."SALES"

fields terminated by whitespace

(

"PRODUCT_ID" CHAR(5) enclosed by X'7C'

,"SALES_DATE" DATE "DD-MON-YYYY AD HH24:MI:SS" enclosed by X'7C'

,"SALES_COST" CHAR(3) enclosed by X'7C'

,"STATUS" CHAR(8) enclosed by X'7C'

)

这里证明所有的控制文件中的表结构都是整个表的结构，而不是分区表，在实际处理过程中，可以考虑交换分区来实现

-----------------温馨提示--------------------

操作有风险，动手需谨慎 本文由大师惜分飞原创分享，转载请尽量保留本站网址

--------------------------------------ORACLE-DBA----------------------------------------

最权威、专业的Oracle案例资源汇总之学习笔记:Oracle dul数据挖掘 使用DUL数据恢复软件恢复分区表中

原文唯一网址：

关键词：