04 – 二月 – 2013 – 专业Oracle数据库恢复，或许是您恢复数据的最后机会@phone:17813235971

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.

SQL> select count(*) from t_xifenfei; COUNT(*) ---------- 67854 SQL> desc t_xifenfei Name Null? Type ----------------------------------------- -------- ---------------------------- OWNER VARCHAR2(30) OBJECT_NAME VARCHAR2(128) SUBOBJECT_NAME VARCHAR2(30) OBJECT_ID NUMBER DATA_OBJECT_ID NUMBER OBJECT_TYPE VARCHAR2(19) CREATED DATE LAST_DDL_TIME DATE TIMESTAMP VARCHAR2(19) STATUS VARCHAR2(7) TEMPORARY VARCHAR2(1) GENERATED VARCHAR2(1) SECONDARY VARCHAR2(1) NAMESPACE NUMBER EDITION_NAME VARCHAR2(30) SQL> select object_id,data_object_id from user_objects where object_name='T_XIFENFEI'; OBJECT_ID DATA_OBJECT_ID ---------- -------------- 68332 68332 SQL> TRUNCATE TABLE T_XIFENFEI; Table truncated. SQL> ALTER SYSTEM CHECKPOINT; System altered. SQL> select object_id,data_object_id from user_objects where object_name='T_XIFE NFEI'; OBJECT_ID DATA_OBJECT_ID ---------- -------------- 68332 68468 SQL> SELECT TABLESPACE_NAME FROM DBA_SEGMENTS WHERE SEGMENT_NAME='T_XIFENFEI'; TABLESPACE_NAME ------------------------------ USERS SQL> SELECT FILE_ID FROM DBA_DATA_FILES WHERE TABLESPACE_NAME='USERS'; FILE_ID ---------- 4 SQL> SELECT file#,RFILE#,NAME FROM V$DATAFILE; FILE# RFILE# NAME ---------- ---------- -------------------------------------------------- 1 1 E:\ORACLE\ORADATA\XIFENFEI\SYSTEM01.DBF 2 2 E:\ORACLE\ORADATA\XIFENFEI\SYSAUX01.DBF 3 3 E:\ORACLE\ORADATA\XIFENFEI\UNDOTBS01.DBF 4 4 E:\ORACLE\ORADATA\XIFENFEI\USERS01.DBF 5 5 E:\ORACLE\ORADATA\XIFENFEI\UNDO01.DBF 6 6 E:\ORACLE\ORADATA\XIFENFEI\CZUM01.DBF 6 rows selected.

e:\dul10>dul.exe Data UnLoader 10.2.4.37 - Oracle Internal Only - on Mon Feb 04 00:20:08 2013 with 64-bit io functions Copyright (c) 1994 2010 Bernard van Duijnen All rights reserved. Strictly Oracle Internal use Only DUL: Warning: Recreating file "dul.log" Reading USER.dat 65 entries loaded Reading OBJ.dat 67944 entries loaded and sorted 67944 entries Reading SCANNEDLOBPAGE.dat 8 entries loaded and sorted 8 entries Reading TAB.dat 1869 entries loaded Reading COL.dat 77409 entries loaded and sorted 77409 entries Reading SEG.dat 23 entries loaded Reading EXT.dat 54 entries loaded and sorted 54 entries Reading TABPART.dat 110 entries loaded and sorted 110 entries Reading TABCOMPART.dat 1 entries loaded and sorted 1 entries Reading TABSUBPART.dat 32 entries loaded and sorted 32 entries Reading INDPART.dat 127 entries loaded and sorted 127 entries Reading INDCOMPART.dat 0 entries loaded and sorted 0 entries Reading INDSUBPART.dat 0 entries loaded and sorted 0 entries Reading IND.dat 3729 entries loaded Reading LOB.dat DUL: Warning: Increased the size of DC_LOBS from 1024 to 8192 entries 1109 entries loaded Reading ICOL.dat 4868 entries loaded Reading COLTYPE.dat 2823 entries loaded Reading TYPE.dat 2082 entries loaded Reading ATTRIBUTE.dat 8736 entries loaded Reading COLLECTION.dat 591 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 24 entries loaded Reading TS.dat 7 entries loaded Reading PROPS.dat 36 entries loaded Database character set is ZHS16GBK Database national character set is AL16UTF16 Found db_id = 1422012639 Found db_name = XIFENFEI DUL> SCAN DATAFILE 4; DUL: Warning: Recreating file "EXT.dat" DUL: Warning: Recreating file "SEG.dat" DUL: Warning: Recreating file "COMPATSEG.dat" DUL: Warning: Recreating file "SCANNEDLOBPAGE.dat" Scanning tablespace 4, data file 4 ... 23 segment header and 1601 data blocks tablespace 4, data file 4: 2079 blocks scanned Reading EXT.dat 54 entries loaded and sorted 54 entries Reading SEG.dat 23 entries loaded Reading COMPATSEG.dat 0 entries loaded Reading SCANNEDLOBPAGE.dat 8 entries loaded and sorted 8 entries DUL> ALTER SESSION SET USE_SCANNED_EXTENT_MAP = TRUE; Parameter altered DUL> scan extents; DUL: Warning: Recreating file "seen_tab.dat" DUL: Warning: Recreating file "seen_col.dat" Scanning extents without segment header Scanning extent id (dba 0, obj 68332) Analyzing segment: data object id 68332 heap organized table Col Seen Max PCT PRINT NUMBERS DATES TIMESTAMP WITH TZ INTRVAL ROWIDS LOB no count Size NUL 75%100% AnyNice AnyNice AnyNice AnyNice Y2M D2S AnyNice 1 67854 18 0 100 100 0 0 0 0 0 0 0 0 0 0 44 0 0 ………… 14 67854 2 0 0 0 100 100 0 0 0 0 0 0 0 0 0 0 0 |SYS| |C_FILE#_BLOCK#| || |8| |8| |CLUSTER| |20-JAN-2013 AD 17:13:18| |20-JAN-20 ………… :18| |2013-01-20:17:13:18| |VALID| |N| |N| |N| |1| UNLOAD TABLE OBJNO68332 ( COL001 VARCHAR2(18), COL002 VARCHAR2(30), COL003 VARCH AR2(27) , COL004 NUMBER, COL005 NUMBER, COL006 VARCHAR2(18), COL007 DATE , COL008 DATE, COL009 VARCHAR2(19), COL010 VARCHAR2(7), COL011 VARCHAR2( 1) , COL012 VARCHAR2(1), COL013 VARCHAR2(1), COL014 NUMBER ) STORAGE( DATAOBJNO 68332 ); --自己拼接语句 DUL> UNLOAD TABLE t_xifenfei_1(OWNER VARCHAR2(30),OBJECT_NAME VARCHAR2(128),SUB OBJECT_NAME VARCHAR2(30),OBJECT_ID NUMBER,DATA_OBJECT_ID NUMBER,OBJECT_TY PE VARCHAR2(19),CREATED DATE,LAST_DDL_TIME DATE,TIMESTAMP VARCHAR2(19),ST ATUS VARCHAR2(7),TEMPORARY VARCHAR2(1),GENERATED VARCHAR2(1),SECONDARY VARCHAR2(1),NAMESPACE NUMBER,EDITION_NAME VARCHAR2(30)) STORAGE( DATAOBJNO 6 8332 ); . unloading table T_XIFENFEI_1 DUL: Warning: Recreating file "T_XIFENFEI_1.ctl" 67854 rows unloaded

专业Oracle数据库恢复技术支持

专业Oracle数据库恢复，或许是您恢复数据的最后机会@phone:17813235971

Daily Archives: 2013 年 02 月 04 日

dul恢复drop表测试

dul恢复truncate表测试

2013 年二月
S	M	T	W	T	F	S
« Jan				Mar »
					1	2
3	4	5	6	7	8	9
10	11	12	13	14	15	16
17	18	19	20	21	22	23
24	25	26	27	28