分类目录归档：Oracle备份恢复

dul处理分区表

发表于 2013 年 2 月 18 日 由 惜分飞

创建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'       
)

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

发表在 非常规恢复 | 标签为 , | 2 条评论

完美解决dul处理clob字段乱码问题

发表于 2013 年 2 月 13 日 由 惜分飞

使用过dul的人都知道，dul在处理clob字段的时候，经常性出现乱码，而且官方没有提供好的处理方法，通过不断的测试折腾终于在2013年春节解决了2012年困惑在我心中的疑惑，不再因为dul不可以处理clob而觉得odu/aul更加高级。这个算是2013年给自己的第一份ORACLE数据库恢复方面大礼包.
在dul中，虽然提供了LDR_OUTPUT_IN_UTF8选项，让所有的clob变为UTF8，但是在实际测试中，没有成功.这里对于clob字段处理结果对比：
配置init.dul参数

osd_big_endian_flag=false
osd_dba_file_bits=10
osd_c_struct_alignment=32
osd_file_leader_size=1
osd_word_size = 32
feedback = 1000
dc_columns=2000000
dc_tables=10000
dc_objects=1000000
dc_users=400
dc_segments=100000
USE_LOB_FILES =TRUE
control_file = control.dul
LDR_ENCLOSE_CHAR=|
db_block_size=8192
export_mode=FALSE
compatible=11

正常情况下测试clob字段

[oracle@xifenfei dul]$ ./dul

Data UnLoader: 10.2.0.5.20 - Internal Only - on Sat Jan 19 00:19:05 2013
with 64-bit io functions

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

 Strictly Oracle Internal Use Only


DUL> unload table chf.EVT_T_COMMON_LOG;
. unloading (index organized) table     LOB024001aa
DUL: Warning: Recreating file "LOB024001aa.ctl"
       0 rows unloaded
. unloading (index organized) table     LOB024001da
DUL: Warning: Recreating file "LOB024001da.ctl"
       0 rows unloaded
. unloading (index organized) table     LOB0240020a
DUL: Warning: Recreating file "LOB0240020a.ctl"
       0 rows unloaded
. unloading (index organized) table     LOB0240023a
DUL: Warning: Recreating file "LOB0240023a.ctl"
       0 rows unloaded
. unloading (index organized) table     LOB0240026a
DUL: Warning: Recreating file "LOB0240026a.ctl"
       0 rows unloaded
Preparing lob metadata from lob index
Reading LOB024001aa.dat 0 entries loaded and sorted 0 entries
Preparing lob metadata from lob index
Reading LOB024001da.dat 0 entries loaded and sorted 0 entries
Preparing lob metadata from lob index
Reading LOB0240020a.dat 0 entries loaded and sorted 0 entries
Preparing lob metadata from lob index
Reading LOB0240023a.dat 0 entries loaded and sorted 0 entries
Preparing lob metadata from lob index
Reading LOB0240026a.dat 0 entries loaded and sorted 0 entries
. unloading table          EVT_T_COMMON_LOG
DUL: Warning: Recreating file "CHF_EVT_T_COMMON_LOG.ctl"
    1863 rows unloaded

--修改CHF_EVT_T_COMMON_LOG.ctl中的导入表名为TEST.T_TEST

--创建测试表
SQL> create table t_test as
  2   select * from chf.EVT_T_COMMON_LOG where 1=0;

Table created.

--导入数据
[oracle@xifenfei dul]$ sqlldr test/test control=CHF_EVT_T_COMMON_LOG.ctl 

SQL*Loader: Release 11.2.0.3.0 - Production on Fri Jan 18 23:50:32 2013

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Commit point reached - logical record count 64
Commit point reached - logical record count 128
…………
Commit point reached - logical record count 1856
Commit point reached - logical record count 1863

--测试数据是否乱码
SQL> desc chf.EVT_T_COMMON_LOG
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                        NOT NULL VARCHAR2(64)
 CASES_OF_STATISTICAL_SITATION                      CLOB
…………
 DEPARTMENT_ID                                      VARCHAR2(40)
 MOTIFY_MAN_ID                                      VARCHAR2(40)

SQL>  select CASES_OF_STATISTICAL_SITATION from t_test where rownum<3;

CASES_OF_STATISTICAL_SITATION
--------------------------------------------------------------------------------
b*kb

     _SfZz

通过试验证明，在dul处理clob字段的时候，很容易出现乱码，因为这里涉及到很多中情况(NLS_LANG,LANG,LDR_OUTPUT_IN_UTF8参数等)，经过了多次试验，均不能成功，这里就是为了给出来一个大概的结论：dul在正常情况下不能完美的处理非英文的clob

修改后lob字段文件属性后测试clob字段

--修改CHF_EVT_T_COMMON_LOG.ctl中的导入表名为TEST.EVT_T_COMMON_LOG
--导入数据
[oracle@xifenfei dul]$ sqlldr test/test control=CHF_EVT_T_COMMON_LOG.ctl 

SQL*Loader: Release 11.2.0.3.0 - Production on Fri Jan 18 23:50:32 2013

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Commit point reached - logical record count 64
Commit point reached - logical record count 128
…………
Commit point reached - logical record count 1856
Commit point reached - logical record count 1863

--测试数据是否乱码
SQL> select CASES_OF_STATISTICAL_SITATION from EVT_T_COMMON_LOG where rownum<3;

CASES_OF_STATISTICAL_SITATION
--------------------------------------------------------------------------------
1、案件统计情况截止至交班时间Ｃ时间 0 分），今日立难点问题Ｃ7，国庆北路桂门岭社区丽都花园路口，多次上

测试证明:修改了clob文件的相关属性后，完美实现dul处理clob乱码问题

发表在 非常规恢复 | 标签为 , , , | 评论关闭

dul恢复drop表测试

发表于 2013 年 2 月 4 日 由 惜分飞

dul对被drop对象进行恢复，需要提供两个信息
1.被删除表所属表空间(非必须)
2.被删除表结构(必须)
模拟删除表

--创建测试表
SQL> create table t_dul_drop tablespace czum
  2  as
  3  select * from dba_tables;

Table created.

--备份被删除表数据，便于比较和提供测试表结构
SQL> create table t_dul_drop_bak tablespace users
  2  as select * from t_dul_drop;

Table created.

SQL> alter system switch logfile;

System altered.

SQL> select count(*) from t_dul_drop;

  COUNT(*)
----------
      1785

SQL> drop table chf.t_dul_drop purge;

Table dropped.

SQL> alter system checkpoint;

System altered.

使用logminer找到data_object_id

delete from "SYS"."OBJ$" where "OBJ#" = '68474' and "DATAOBJ#" = '68474' 
and "OWNER#" = '61' and "NAME" = 'T_DUL_DROP' and "NAMESPACE" = '1' and 
"SUBNAME" IS NULL and "TYPE#" = '2' and "CTIME" = TO_DATE('04-FEB-13', 'DD-MON-RR') 
and "MTIME" = TO_DATE('04-FEB-13', 'DD-MON-RR') and "STIME" = TO_DATE('04-FEB-13', '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" = '61' and 
"SPARE4" IS NULL and "SPARE5" IS NULL and "SPARE6" IS NULL and ROWID = 'AAAAASAABAAAPzCAAV';

这里可以知道，被删除表的data_object_id为68474

DUL恢复被删除表

--dul版本
E:\dul10>dul.exe

Data UnLoader 10.2.4.37 - Oracle Internal Only - on Mon Feb 04 23:49:50 2013
with 64-bit io functions

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

 Strictly Oracle Internal use Only

DUL>  ALTER SESSION SET USE_SCANNED_EXTENT_MAP = TRUE;
Parameter altered

--扫描所属表空间
DUL> scan tablespace 6;
Scanning tablespace 6, data file 6 ...
  13 segment header and 331 data blocks
  tablespace 6, data file 6: 1279 blocks scanned
Reading EXT.dat 13 entries loaded and sorted 13 entries
Reading SEG.dat 13 entries loaded
Reading COMPATSEG.dat 0 entries loaded
Reading SCANNEDLOBPAGE.dat 0 entries loaded and sorted 0 entries

--scan tables得到需求表(可以核对数据样例)
DUL> scan tables;
UNLOAD TABLE OBJNO68474 ( COL001 VARCHAR2(11), COL002 VARCHAR2(30), COL003 VARCHAR2(6)
        , COL004 VARCHAR2(20), COL005 VARCHAR2(30), COL006 VARCHAR2(5), COL007 NUMBER
        , COL008 NUMBER, COL009 NUMBER, COL010 NUMBER, COL011 NUMBER
        , COL012 NUMBER, COL013 NUMBER, COL014 NUMBER, COL015 CHAR
        , COL016 NUMBER, COL017 NUMBER, COL018 VARCHAR2(3), COL019 VARCHAR2(1)
        , COL020 NUMBER, COL021 NUMBER, COL022 NUMBER, COL023 NUMBER
        , COL024 NUMBER, COL025 NUMBER, COL026 NUMBER, COL027 NUMBER
        , COL028 VARCHAR2(10), COL029 VARCHAR2(10), COL030 VARCHAR2(5), COL031 VARCHAR2(7)
        , COL032 NUMBER, COL033 DATE, COL034 VARCHAR2(3), COL035 VARCHAR2(12)
        , COL036 VARCHAR2(1), COL037 VARCHAR2(1), COL038 VARCHAR2(3), COL039 VARCHAR2(7)
        , COL040 VARCHAR2(7), COL041 VARCHAR2(7), COL042 VARCHAR2(8), COL043 VARCHAR2(3)
        , COL044 VARCHAR2(2), COL045 VARCHAR2(15), COL046 VARCHAR2(8), COL047 VARCHAR2(3)
        , COL048 VARCHAR2(3), COL049 VARCHAR2(8), COL050 VARCHAR2(8), COL051 VARCHAR2(5)
        , COL052 VARCHAR2(2), COL053 VARCHAR2(2), COL054 VARCHAR2(3), COL055 VARCHAR2(7) )
    STORAGE( DATAOBJNO 68474 );

--恢复删除表(业务提供表结构)
DUL> unload table t_dul_drop(
  2  OWNER                              VARCHAR2(30),
  3  TABLE_NAME                         VARCHAR2(30),
  4  TABLESPACE_NAME                                    VARCHAR2(30),
  5  CLUSTER_NAME                                       VARCHAR2(30),
  6  IOT_NAME                                           VARCHAR2(30),
  7  STATUS                                             VARCHAR2(8) ,
  8  PCT_FREE                                           NUMBER      ,
  9  PCT_USED                                           NUMBER      ,
 10  INI_TRANS                                          NUMBER      ,
 11  MAX_TRANS                                          NUMBER      ,
 12  INITIAL_EXTENT                                     NUMBER      ,
 13  NEXT_EXTENT                                        NUMBER      ,
 14  MIN_EXTENTS                                        NUMBER      ,
 15  MAX_EXTENTS                                        NUMBER      ,
 16  PCT_INCREASE                                       NUMBER      ,
 17  FREELISTS                                          NUMBER      ,
 18  FREELIST_GROUPS                                    NUMBER      ,
 19  LOGGING                                            VARCHAR2(3) ,
 20  BACKED_UP                                          VARCHAR2(1) ,
 21  NUM_ROWS                                           NUMBER      ,
 22  BLOCKS                                             NUMBER      ,
 23  EMPTY_BLOCKS                                       NUMBER      ,
 24  AVG_SPACE                                          NUMBER      ,
 25  CHAIN_CNT                                          NUMBER      ,
 26  AVG_ROW_LEN                                        NUMBER      ,
 27  AVG_SPACE_FREELIST_BLOCKS                          NUMBER      ,
 28  NUM_FREELIST_BLOCKS                                NUMBER      ,
 29  DEGREE                                             VARCHAR2(20),
 30  INSTANCES                                          VARCHAR2(20),
 31  CACHE                                              VARCHAR2(10),
 32  TABLE_LOCK                                         VARCHAR2(8) ,
 33  SAMPLE_SIZE                                        NUMBER      ,
 34  LAST_ANALYZED                                      DATE        ,
 35  PARTITIONED                                        VARCHAR2(3) ,
 36  IOT_TYPE                                           VARCHAR2(12),
 37  TEMPORARY                                          VARCHAR2(1) ,
 38  SECONDARY                                          VARCHAR2(1) ,
 39  NESTED                                             VARCHAR2(3) ,
 40  BUFFER_POOL                                        VARCHAR2(7) ,
 41  FLASH_CACHE                                        VARCHAR2(7) ,
 42  CELL_FLASH_CACHE                                   VARCHAR2(7) ,
 43  ROW_MOVEMENT                                       VARCHAR2(8) ,
 44  GLOBAL_STATS                                       VARCHAR2(3) ,
 45  USER_STATS                                         VARCHAR2(3) ,
 46  DURATION                                           VARCHAR2(15),
 47  SKIP_CORRUPT                                       VARCHAR2(8) ,
 48  MONITORING                                         VARCHAR2(3) ,
 49  CLUSTER_OWNER                                      VARCHAR2(30),
 50  DEPENDENCIES                                       VARCHAR2(8) ,
 51  COMPRESSION                                        VARCHAR2(8) ,
 52  COMPRESS_FOR                                       VARCHAR2(12),
 53  DROPPED                                            VARCHAR2(3) ,
 54  READ_ONLY                                          VARCHAR2(3) ,
 55  SEGMENT_CREATED                                    VARCHAR2(3) ,
 56  RESULT_CACHE                                       VARCHAR2(7))
 57   STORAGE( DATAOBJNO 68474 );
. unloading table                T_DUL_DROP
DUL: Warning: Recreating file "T_DUL_DROP.ctl"
    1785 rows unloaded

模拟业务规则提供，创建表

SQL> create table t_dul_drop as select * from t_dul_drop_bak where 1=0;

Table created.

导入数据

e:\dul10>sqlldr chf/xifenfei control=T_DUL_DROP.ctl

SQL*Loader: Release 11.2.0.3.0 - Production on Mon Feb 4 23:35:57 2013

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Commit point reached - logical record count 64
Commit point reached - logical record count 128
Commit point reached - logical record count 192
Commit point reached - logical record count 256
Commit point reached - logical record count 320
Commit point reached - logical record count 384
Commit point reached - logical record count 448
Commit point reached - logical record count 512
Commit point reached - logical record count 576
Commit point reached - logical record count 640
Commit point reached - logical record count 704
Commit point reached - logical record count 768
Commit point reached - logical record count 832
Commit point reached - logical record count 896
Commit point reached - logical record count 960
Commit point reached - logical record count 1024
Commit point reached - logical record count 1088
Commit point reached - logical record count 1152
Commit point reached - logical record count 1216
Commit point reached - logical record count 1280
Commit point reached - logical record count 1344
Commit point reached - logical record count 1408
Commit point reached - logical record count 1472
Commit point reached - logical record count 1536
Commit point reached - logical record count 1600
Commit point reached - logical record count 1664
Commit point reached - logical record count 1728
Commit point reached - logical record count 1785

恢复数据结果

SQL> select count(*) from t_dul_drop;

  COUNT(*)
----------
      1785

SQL> select owner,table_name from t_dul_drop where rownum<10;

OWNER                          TABLE_NAME
------------------------------ ------------------------------
SYS                            IDL_CHAR$
SYS                            IDL_UB2$
SYS                            IDL_SB4$
SYS                            ERROR$
SYS                            SETTINGS$
SYS                            NCOMP_DLL$
SYS                            PROCEDUREJAVA$
SYS                            PROCEDUREC$
SYS                            PROCEDUREPLSQL$

9 rows selected.
发表在 非常规恢复 | 标签为 , , , | 5 条评论